Finance case
ACFI 476 Insurance and Risk Management Fall 2021 Case 5 CS1 (82 points) Due by 11:59 PM on Tuesday, November 30, 2021 Record your responses to the questions in a Word or PDF file named YourLastName_Case5 and your calculations in an Excel file also named YourLastName_Case5. Your Excel file SHOULD NOT CONTAIN WRITTEN RESPONSES; it should ONLY CONTAIN CALCULATIONS. The event tree for Question 3, subpart a. may be incorporated into your Word or PDF file or may be hand drawn, scanned or photo’d and saved as a PDF or JPG file. If you provide this in a separate file, it should be named to YourLastName_EventTree. Your written responses in your Word or PDF file should be free of grammar, spelling and punctuation errors. External sources used to complete your case should be noted in a Reference section at the bottom of your Word or PDF document, with information cited throughout. References and cites should be in APA format. Upload and submit your files by the deadline. 1. Download the data file, Case5DataAuto, from the Case 5 assignment in Blackboard. This is a csv delimited file. You must either import it into an actual Excel workbook or save it as an Excel workbook for your work to be saved as required for this case. Use this data and Excel to answer the following questions. a. How many car accidents happen each year? (6 points) b. Given the total number of car trips and the total number of car accidents each year, what is the probability of a car accident each year? (3 points) c. Conduct regression analysis to predict the number of car accidents from the number of car trips. For your analysis, the number of car trips is the independent variable and the number of car accidents is the dependent variable. Based on your analysis, how many car accidents are expected in 2021 if the company expects 30,000 car trips in 2021? (10 points) d. Split the physical damage losses (for all accidents, not just car accidents) into categories based on the following sizes: Less than $2,000, equal to $2,000 to less than $4,000; equal to $4,000 to less than $6,000; equal to $6,000 to less than $8,000; equal to or greater than $8,000. For each category, (i) provide the number of accidents that fall into that category and (ii) the probability of the category based on the total number of accidents. (15 points) e. Prepare a histogram of the number of accidents by category of physical damage losses you compiled in response to subpart 1.d(i). (4 points) f. What is the average physical damage loss? (3 points) g. What is the standard deviation of physical damage losses? (3 points) h. What is the coefficient of variation for physical damage losses? (2 points) 2. Based on your results in your Excel file, answer the following questions. (These answers should be in a Word of PDF file, not in Excel.) a. What is the trend in car accidents from 2018 to 2020? How many car accidents are expected in 2021? How is this information useful to the company’s risk manager? (6 points) b. Can the risk manager rely on the regression analysis used to predict the 2021 number of car accidents? Why or why not? (4 points) c. Is the distribution of physical damage losses a normal distribution? Why or why not? (4 points) d. Explain how the risk manager can use the average physical damage loss, its standard deviation and its coefficient of variation in making decisions about the risk of physical damage resulting from accidents. (6 points) 3. The risk manager for the company needs to better understand the risk of car accidents once the driver leaves the company premises at location A. He has gathered the following information. • The probability of having a car accident is 0.25%. • The probability of the brakes failing and causing that car accident is 3.25%. • The probability of high speed being involved in that car accident is 20%. a. Prepare an event tree that shows the probability of each of the following outcomes: (3 points each) i. The probability that the brakes failed and high speed was involved in the accident. ii. The probability that the brakes failed and high speed was not involved in the accident. iii. The probability that the brakes did not fail and high speed was involved in the accident. iv. The probability that the brakes did not fail and high speed was not involved in the accident. b. How does the event tree in 3.a. help the risk manager when making decisions about the risk of car accidents when drivers leave location A? (4 points)