In this exercise you will use the statistical machinery we developed over the last few weeks to construct a Monte Carlo simulation that will estimate an aggregate loss distribution. The exercise has two parts: the first is a relatively simple problem for which you can develop the answer analytically (Questions A-C); the second part requires you estimate the aggregate loss distribution once you are confident in your simulating abilities (Questions D-F). In addition to the Excel spreadsheet commands we have used already, the following new commands will be used to complete this question: IF(), RAND(), and VLOOKUP().

Use the following data to answer the following questions.

Frequency		Saverity
#claims	Prob	Loss	Prob
0	.5	500	1/3
1	.2	3000	1/3
2	.3	5000	1/3

A. Determine the mean and standard deviation statistics for the frequency and severity distributions respectively. Show your calculation.

B. Calculate the mean and standard deviation of the aggregate loss distribution from your answers to question A.1 Show your calculation.

C. Determine the aggregate loss distribution by calculating the probability of each outcome analytically. Show your calculation.

D. Simulate the aggregate loss distribution. Use the FREQUENCY() function to summarize your simulation as a probability distribution of all possible outcomes. You should conduct with simulation with N=100 trials and N=1000 trials. I need a print out of your problem setup (cumulative probabilities, frequency and severity tables for simulation, etc.) as well as values and formulas for the first 5 trials of simulation. To show formulas, press Ctrl and ~ keys. When printing out the formulas, please print out column and row headings in excel (Page Layout Page Setup Sheet now check row and column headings).

E. Calculate the mean and standard deviation of simulated aggregate losses using your probability distribution from part D. Discuss how well you were able to replicate the actual distribution and compare your simulation results based on 100 trial and 1000 trials.

F. Plot in a chart the actual probability distribution based on part C and the two loss distributions based upon the N=100 and N=1000 trials. (That is, you should draw three graphs for this question.)

1 Mean of the aggregate loss distribution:

, s f

where is the mean of the frequency and is the mean of the severity. f s

Variance of the aggregated loss distribution:

, 2 f 2s 2s f 2

where is the variance of the frequency and is the variance of the severity. 2f 2s