Descriptive Simulation

Important Instruction:

Please use 10,000 replications in your simulation.

Vinton Auto In5urance is deciding how much money to keep in its checking accounts to cover insurance claims. In the past, the company held some of the premiums it received in interestbearing checking accounts and put the rest into investments that are not quite as liquid, but tend to generate a higher investment return. The company wants to study cash flows to determine how much money it should keep in its checking accounts to pay claims. There are two types of claims: \"repair\" claims, and \"totaled\" claims. After reviewing historical data, the company has determined that the number of repair claims led each week is a random variable that follows the probability distribution shown in the following table: # Repair Claims 0.030 0.106 0.185 0.216 0.189 0.132 0.077 0.039 0.017 0.007 0.002 The company has also determined that the average cost pg repair claim is normally distributed with a mean of $1,200 and standard deviauon of $300 (with no negative values). To be clear, the costs of covering of each individual repair claim are n_ot normally distributed; rather, the mg;- cost per repair claim for a given week is normally distributed with a mean of $1,200 and a standard deviation of $300. In addition to repair claims, the company also receives claims for cars that have been \"totaled\" and cannot be repaired. There is a 15% chance of receiving one claim of this type in any week, and there is no chance of receiving more than one in any week. The cost for \"totaled\" cars is given by the following: $7500 * X, where X is a lognormal random variable with a mean parameter of 0.15 and a standard deviation parameter of 0.5. a. Develop a descriptive model of this scenario; idenn'fy and name random and nonrandom variables along the way. You may develop a owchart for youISelf to help you visuaIiZe, but do not attach it to the submission. . List all random variables, their distributions, and parameters. c. ICode the model in Excel and replicate it 10,000 times. Answer the following questions (Q n_c.-t attach the spreadsheet): i. What is the weekly average cost of all claims? ii. SuppoSe that the company decides to keep $15,000 cash on hand to pay claims. What is the probability that this aniount will n_0t be adequate to cover claims in any given week