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

- 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

- Specification to follow....