Risk Analytics - Problem Set 1

Risk Analytics - Problem Set

Handed out:September 23rd

Due: Mon October 10th AT THE START OF CLASS

The goal of this problem set is for you to get your hands dirty building simple Monte Carlo style models in Excel.

PART A: Tutorial

Work through the Monte Carlo Simulation Tutorial located at http://excelmontecarlo.com/. It gives a good intro the the pragmatic concerns of implementing simulations in MS Excel. A spreadsheet with some related examples can be found here: ExcelMC.zip.
Review the class notes on MS Simulations in Excel, especially those on implementing commonly used distributions.
Review the VBA implementation of standard distributions provided in the spreadsheet RandomNumberTools

PART B: Implement a Monte Carlo simulation for the Profit Projection Analysis example from the class notes

Use the spreadsheet SalesModel as a starting point.
Using the "Excel Only" implementation style and reasonable assumptions about the distributions generate a table of trials
On a second "Analysis" tab add to your model an analysis of the results. Use descriptive statistics, histograms, etc to analyze the results.
On a third "Executive Summary" tab, write a executive summary for "management". Given your analysis what can you as about likely profit/loss? Which parameters are most sensitive?

PART C: Craps

Using the "Excel + VBA" implementation style implement the Craps Simulation discussed in the lecture notes. Your simulation should explicitly use a discrete probability distribution for the dice (see the DiscreteDistributionExample) so that you can play with "non-standard" dice. Your simulation should
- Allow the user to specify the number of trials, edit the discrete probability distribution for the dice, and provide the seed of the random number generator
- It should have a "Go" button that runs the simulation
- It should interactively create and update a chart in the spirit of the chart below

Convergence of probability estimate

Do a set of experiments and document the results on a separate tab. Questions you might think about include:

How well does your MC model match the analytical model?
What is the relationship between number of trials and quality of the results? How can you quantify this?
What is the effect of different seeds?

PART D: Very Simple Catastrophe Model