FINA338 Corporate Risk Management;Learning Objectives;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 exerci;se 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 distribu;tion;once you are confident in your simulating abilities;(Questio;ns D;-;F).;In addition to the Excel spreadsheet commands we have used already;the following new commands will b;e used to complete this question;: IF(), RAND(), and;VLOOKUP().;Please;write down your seat number;beside your name in the answer;sheets.;Use the;following data to answer the following questions.;Frequency;Severity;# 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.;B. Calculate the mean and standard deviation;of the aggregate loss distribution from;your answers to question A.;1;C. Determine the aggregate loss distribution by calculating the probability of each;outcome analytically.;D. S;imulate the aggregate lo;ss;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;1;Mean of the aggregate;loss distribution;s;f;?;?;?;?;?;where;f;?;is the mean of the frequency and;s;?;is the mean of the severity.;Variance of the aggregated loss distribution;?;?;?;?;2;f;2;s;2;s;f;2;?;?;?;?;?;?;?;?;?;where;2;f;?;is the variance of the frequency and;2;s;?;is the variance of the severity.;2;your problem setup as well as valu;es 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;t;he;mean and;standard deviation;of;simulated;aggregate losses;using;your;probability distribution;from part;D. Discuss;how well you were able to re;plicate 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;los;s;distributions based;upon the N=100 and N=1000 trials.
Paper#28190 | Written in 18-Jul-2015Price : $27