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....