Monte Carlo analysis Introductory Tutorial and Problem Set

Skin Cream Additive Example

Copyright 2004 David M. Hassenzahl

Problem Components:

• This problem is covered in a Monte Carlo PowerPoint Lecture
• The problem, also available as an Adobe Document)
• Excel template (skincreamdemo.xls, available at the RATL website)
• Crystal Ball software. A one week trial version can be downloaded from the Crystal Ball website. In addition, academic versions (one-year licenses) are available to students, while there are a number of professional versions with a variety of add-ons. See www.crystalball.com for details, as well as lots of useful material and links for Monte Carlo modeling.

Introduction:
This problem set explores the use of Monte Carlo analysis for propagating some types of uncertainty in health risk problem solving. It begins as a tutorial, and segues into a problem set. This assignment is designed with Crystal Ball, but can be adapted to any number of other programs. The skin cream additive example was explored in the PowerPoint lecture.

Deliverables:
You should produce two things, and submit them electronically (WebCT is available, email is ok too):
a) a Skin Cream Demo excel file; and
b) a document that discusses findings and explains the two spreadsheets. You will want to import some of the files and tables from excel into your document.

Specific deliverables are requested throughout. Please be sure to provide a brief narrative explanation of each finding. Do NOT turn in every output you generate; provide only those that meaningfully answer the problem. Explain every table, chart, etc. that you do provide in clear language. Be sure to discuss uncertainties that are not covered by distributional data.

Part One: Analytical calculation.
a. Download and open the file skincreamdemo.xls from the Risk Analysis Teaching and Learning Website. Go to the worksheet "skin cream analytical." Be sure that Crystal Ball is running with excel. Resave this file as "yourlastname_montecarlo_ps.xls" You will be turning this in as part of your homework!

b. Risk (as percent chance of irritation) associated with the skin cream additive is a function of exposure and effect:
risk = exposure effect 100%
Calculate the mean and "conservative" estimates of risk by putting "=b6*b5*100" in cell b8, and copy and paste this to cell d8. Note that cell d8 is a 1-0.052 or 99.75% upper estimate. Save! Note: you may want to save several versions as you go, so you have a backup if something goes wrong.

Part Two: Running Monte Carlo trials.

a. Now go to the sheet "skin cream Monte Carlo." Under the column "Monte Carlo," seed values have been provided in appropriate cells. In this part, you will set up and run a Monte Carlo model. First you will need to set up an assumption (or input) cell. Your two assumptions are that 1) exposure is normal (0.020 mg, 0.0050 mg) and 2) effect is uniform {0.05, 0.10}. Do exposure first. Select cell c5. Click on the "define assumption" icon on the Crystal Ball toolbar. This will give you a range of options. Select normal. It will have the default mean as the seed value. You will need to change the standard deviation to 0.005. Click OK: you have now defined an assumption. Save now!

b. Next, in cell C8 put in the appropriate risk calculation. Then, on the Crystal Ball toolbar, click the button for "single step" (This can also be done from the pull-down menu). Crystal Ball will randomly draw a value from the assumption distribution. Note that the calculated risk (cell C8) changes as well. Repeat this several times. Okay, that's enough!

c. Now you want to tell Crystal Ball to repeatedly calculate risk, and save the calculations. To do this, you will need a "forecast" or output cell. Select cell c8, and click on the "define forecasts" cell. Name the forecast "risk" with units of "percent." You now have one forecast and one assumption cell. Add the second assumption cell by selecting cell c6, clicking "define assumption," and setting up an assumption that is uniform (you will have to go to the "Gallery" to set it up) from 0.05 to 0.10. Now click the "single step" button. Transcribe the values to column 1 of the "ten iterations" table. Do nine more iterations and record the values. Calculate (ok to use excel) the means and standard deviations of the inputs and the output. Compare these to the original (given) values. Save! You have now done a ten-iteration Monte Carlo calculation. Comment on your finding.

d. Now you will run a Monte Carlo model: in Crystal Ball, a set of recorded iterations is called a "run." On the main menu, go to "run" and click on "run preferences." Look through the possibilities: trials, sampling, speed, macros and options. Keep all of the current defaults, except in options: 1) turn on "sensitivity analysis." This tells you which input distributions have the greatest effect on eventual output variance. 2) have Crystal Ball set assumptions to "estimated means." Click OK. Now click "Start Simulation," and watch the magic happen…as the forecast of "risk" slowly stabilizes. When it's over, click "okay." Now you have a forecast window that represents binned outcomes of 1000 iterations.

e. Mess around with this chart: First, change the "Certainty" to 95%. Then go (on chart menu) file to copy. Go to the worksheet "Charts" and paste this chart. You may want to insert a text box to remind you of what the chart is. Go back to the "skin Cream Monte Carlo" sheet and mess around with other settings. Change the certainty and upper and lower bounds to see what happens. View as a cumulative and frequency chart. Record the mean and standard deviation. Is there a mode? If not, why not? Copy and paste other versions of the chart that you find informative. (Not too many!) Next, under the run menu open "open sensitivity chart." Change preferences to "contribution to variance." Explain what you find.

f. Reset Crystal Ball and repeat d and e. Click create report. Choose the options you think are relevant and click ok. Move the resulting sheet (which is created as a new excel file) to your main excel file. SAVE!

g. Go to the charts you've saved. Use arrows and text boxes (from drawing menu) to explain the various parts of your saved charts. SAVE!

Part Three: exploring input distributions.
a. Go to the Monte Carlo sheet. Reset Crystal Ball. Select the three Monte Carlo cells, and use the eraser button to remove the assumptions and forecasts. Then, using the techniques you learned in part Two, make cells f5 and f6 into input cells and f8 into a forecast cell. Use the same distribution as before for cell f5, but proposed an alternate distribution for cell f6 (based on what we know…ie two tests, with results of 0.05 and 0.10). Explain why you made your choice! Run this new Monte Carlo model, and save appropriate charts and/or generate a meaningful report. SAVE! Compare your findings to your part Two findings.

b. Repeat (a) twice, this time proposing some alternative input distributions for both variables. Be sure to save your file regularly! Again, compare and discuss appropriate statistics, charts, sensitivities, and so on. Is the output highly dependent on the input? How so? What does this tell you about which information is most important? What more would you want to know before you felt comfortable making claims about the effects of using the skin cream?

Copyright information: This problem and all other materials on the RATL website are available free for use by individuals learning on their own, and for use in courses that are part of the standard catalog at accredited degree-granting institutions. I retain the rights for all materials that I have created. For all other uses, including but not limited to professional workshops and for-profit seminars, including those sponsored by accredited institutions but done outside the normal curriculum, please contact me or the contact individual listed on the materials you wish to use. In all cases, please include the attribution in your presentation.