Attcahed
Project 1 Risk Assessment The goal of this project is to explore the topic of risk assessment. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit and show all work by referencing cells where necessary (do not simply type in answers). In addition, type up a report in Word (1-2 pages) with a separate section for the introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (conclusions, summary). Make sure to follow all directions or points will be deducted. This is an individual project so be sure to submit your own work. Upon completion, please submit both your Word report and Excel file to blackboard at the same time. Each question (A, B, C, D, E, F, G, H, I, J) in the Excel file is worth 9 points (total of 90 points) and the report is worth 10 points. Risk Assessment Part 1: Bespin Car Rental predicts that the annual probability of one of its cars being destroyed in a crash is 1 in 1,000,000. If destroyed, the value of the property damage to the car equals $45,000. Assume that there are no partial losses; the car is either destroyed in a crash or suffers no loss. A) Show the physical damage loss distribution for Bespin Car Rental’s automobiles and calculate the expected value of the physical damage loss. (9 Points) B) Show the calculations for the variance and the standard deviation. (9 Points) Part 2: Mara Jade is analyzing the fire damage losses in her insurance firm that occurred over a one-year period, based on the following data: Number of Claims Filed Number of Houses Total Number of Claims 0 9,855 0 1 137 137 2 8 16 C) Fill in the table to find the average frequency of losses per house. (9 Points) D) Use the information in the table to estimate a probability distribution for the frequency distribution of losses per house in a year. (9 Points) Part 3: Mara Jade also analyzes the severity from all claims that occurred over a one-year period, based on the following data: Range of Loss Amount Midpoint Dollar Amount of Losses Number of Losses $1 - $7,999 $4,000 100 $8,000 - $20,000 $14,000 50 Greater than $20,000 $80,000 3 E) Fill in the table to find the average severity per claim. (9 Points) F) Use the information in the table to estimate a probability distribution for the loss severity per claim. (9 Points) G) Use convolution to find the average loss. (9 Points) Part 4: Alderan Toy Store faces the following probability distribution of fire losses in its store over the next year: Probability 0.83 0.15 0.02 Loss $0 $10,000 $25,000 H) Calculate the expected value and standard deviation of Alderan’s losses for the year. (9 Points) I) Assume that Alderan pools his losses with Dagobah’s store, which has an identical loss distribution. Dagobah’s losses are independent of Alderan’s. Alderan and Dagobah agree to split the total losses in the pool equally. Show the revised probability distribution for the mean loss from the pool. (9 Points) J) Calculate the expected value and standard deviation of the pooled mean losses. (9 Points)