Step 1

This is a good exercise to understand how to simulate (a) Binomial and (b) Normal distributions. The data comes really nicely and gives some tangible results about the number of times a claim is actually made, i.e. the amount exceeds $4000 in order to get an insurance value, the percentage of these claims as the total of all incidence when a damage was made, the average insurance amount etc.

So the work is actually excel based and cannot be pasted here. I am giving a walkthrough of the process followed to simulate the data so you can understand how it works.

Step 2

First, we use the Inverse Binomial distribution function on excel, to generate 100 incidences with the parameter \(\displaystyle{p}={0.075}\). The exact input command is =BINOM.INV(1,0.075,RAND()). This generates 1000 values of 0 and 1, with proportions of 1's being very close to 0.075.

Second, we do a similar simulation of the Normal distribution, again 1000 times, with the command =NORM.INV(RAND(),4500,1500). We get a nicely laid out set of values.

The third step is to multiply, along each row, the value obtained from the above two distributions. What that does is, give us a sample output of the number of times a claim is expected to be made, with those values following the mean and standard deviation as defined earlier.

Now we shall have a certain proportion of those 1000 rows having non-zero values. This is important data because it tells us how many times some damage was borne by the person, irrespective of whether a claim ensued or not.

Lastly what we now need to do is look at only those values in this column that exceed 4500, and find their value post deductible. So we do the command =MAX(0, D3-5000) on the damage made, where D3 is the cell reference for the damage.

Voila!! You are all set to do summary statistics on the values obtained. Here is a sample output of the simulation

\(\begin{array}{|c|c|} \hline \text{Summary Statistic} & \text{Value} \\ \hline \text{No of Claim Incidence} & 76 \\ \hline \text{No of Claims Made}&40\\ \hline \text{Claims Made as % of incidence}&52.63\%\\ \hline \text{Average Amount of Claim Post Deductible}&1094.626732\\ \hline \end{array}\)

These values are random on excel, so on the file you download, they will change every time you click anywhere on the sheet. In case you need to report a fixed set of values, just copy the entire sheet, go to a new tab and use Paste with Values only.