Question

A small insurance company, is trying to decide how much money to keep in liquid asset to cover auto insurance claims. The company holds some of the premiums it receives in interest bearing checking accounts and puts the rest into investments that are not quite as liquid, but generate a higher return. The company wants to study cash flows to determine how much money it should keep in liquid assets to pay claims. A review of the company’s data has shown the following: Repair bills per claim have a Normal distribution with a mean of $3000 and a standard deviation of $1000. The number of repair claims filed each week is distributed as follows:

No. of repair claims 1 2 3 4 5 6 7 8 9

Probability (respective to no. of repair claims): 0.05 0.06 0.10 0.17 0.28 0.14 0.08 0.07 0.05

In addition to repair claims, the company also receives claims for cars that have been “totaled”, that is, damaged beyond repair. On average, there is a 20% chance of occurrence of this type of claim in any week. Typically, these claims cost anywhere from $3000 to $35000, the most common cost being around $18000. Suppose that the company decides to keep $35000 cash on hand to pay claims.

What is the probability that this amount would not be adequate to cover claims in any week? Please illustrate on excel.

Answer 1

This is a Simulation exercise, of the sum of a Normal distribution and a Triangular distribution. FIrst, we need to identify the parameters for both.

The expected number of repair claims is 4.96, as illutrated below

Repair Claims	Probability	Expectation
1	0.05	0.05
2	0.06	0.12
3	0.1	0.3
4	0.17	0.68
5	0.28	1.4
6	0.14	0.84
7	0.08	0.56
8	0.07	0.56
9	0.05	0.45
		4.96

From the given Mean and Standard Deviation of a single repair claim, the corresponding parameters for the total repair claims can be computed as,

Command to generate random normal distribution values, based on these parameters is

=NORM.INV(RAND(), 14880, 4960)

The process to simulate the triangular distribution is more involved. The combined simulation of both distributions looks like below

Triangular Distribution Parameters					Totaled	Repair Claims	Result
Lower Bound (a)	3000				3822.4	19620.96236	Adequate
Most Likely (m)	18000				4091.8	10287.54226	Adequate
Upper Bound (b)	35000				2066	15053.57937	Adequate
Number	Probability	CDF	Percentage	Number	4710.6	14499.23097	Adequate
3000	0	0	0.000000000%	3000	3191.6	13397.16393	Adequate
3001	4.1667E-09	4.16667E-09	0.000000417%	3001	3779.6	15236.90326	Adequate
3002	8.3333E-09	1.25E-08	0.000001250%	3002	3794.4	24401.51315	Adequate
3003	1.25E-08	0.000000025	0.000002500%	3003	2822.6	21588.84172	Adequate
3004	1.6667E-08	4.16667E-08	0.000004167%	3004	4952.4	20649.29047	Adequate
3005	2.0833E-08	6.25E-08	0.000006250%	3005	2669.2	19080.40445	Adequate
3006	2.5E-08	8.75E-08	0.000008750%	3006	3914	12445.03484	Adequate
3007	2.9167E-08	1.16667E-07	0.000011667%	3007	5558.6	18081.37525	Adequate
3008	3.3333E-08	0.00000015	0.000015000%	3008	5266	11597.84725	Adequate
3009	3.75E-08	1.875E-07	0.000018750%	3009	3564.8	16604.26928	Adequate
3010	4.1667E-08	2.29167E-07	0.000022917%	3010	2429.8	27800.46629	Adequate
3011	4.5833E-08	0.000000275	0.000027500%	3011	4018.4	22913.36866	Adequate
3012	0.00000005	0.000000325	0.000032500%	3012	3935	11775.14344	Adequate
3013	5.4167E-08	3.79167E-07	0.000037917%	3013	2909.2	15913.40716	Adequate
3014	5.8333E-08	4.375E-07	0.000043750%	3014	2938	12827.77321	Adequate
3015	6.25E-08	0.0000005	0.000050000%	3015	2787.2	233.4513194	Adequate
3016	6.6667E-08	5.66667E-07	0.000056667%	3016	1784.8	11033.20451	Adequate
3017	7.0833E-08	6.375E-07	0.000063750%	3017	2055.4	8175.894392	Adequate

This simulation was run for 50,000 rows. The values of Triangular distribution Probabilities were computed as per

As highlighted here, the formula for cell B6 onwards in that column is

=IF(A6<$B$2,0, IF(A6<$B$3,2*(A6-$B$2)/(($B$4-$B$2)*($B$3-$B$2)), IF(A6<$B$4,2*($B$4-A6)/(($B$4-$B$2)*($B$4-$B$3)),0)))

This is based on the formula for PDF of a triangular distribution.

and the formula for Totaled in Cell F2 onwards in that column is

=0.2*VLOOKUP(RAND(),$D$6:$E$32006,2,TRUE)

where we need to multiply with 0.2, since the probability of a car being totaled in any week is 0.2.

Finally, the formula for the Result from cell H2 onwards in that column is

=IF(F2+G2<=35000,"Adequate","Inadequate")

This simulation gives the following result

As highlighted above, the final count of number of times 35000 dollars fell short of the requirement is 36, out of a total of 50,000 simulations. Hence, the probability as per this particular run of the simulation is

where X and Y are the random variables that denote the cost of Totaled card and Repair Claims. If this simulation is run again and again, the value of Inadequate hovers mostly between 20 to 45.

Do ask for any doubts or explanation required