Question to be solved using Crystal ball on Excel

1. Vinton Auto Insurance 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 interest-bearing 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. After reviewing historical data, the company has determined that the number of repair claims filed each week is a random variable that follows the probability distribution shown in the following table:

Number of Claims	1	2	3	4	5	6	7	8	9	10
Probability	0.21	0.06	0.21	0.05	0.18	0.01	0.03	0.03	0.19	0.03

The company has also determined that the average repair bill per claim is normally distributed with a mean of $1,200 and standard deviation of $300. To be clear, the repair bills of each individual claim are not normally distributed with a mean of $1,200 and a standard deviation of $300. Rather, the average repair bill of a batch of claims 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 repair bills for totaled cars follow a triangular distribution with a minimum cost of $3,000, a maximum cost of $30,000 and a most likely cost of $10,000.

Using Crystal Ball:

1. Develop a simulation model and report the mean total cost of all claims incurred by the company in any week. [15pts]
2. Suppose that the company decides to keep $22,000 cash on hand to pay claims. What is the probability that this amount will not be adequate to cover claims in any week? [3pts] (Attach the frequency chart which displays the probability value. There is no need to display statistics output here)
3. What level of cash would the company have to have to be 97% certain they could pay all the claims in any week? [2pts] (Attach the frequency chart which displays level of cash. There is no need to display statistics output here)