# Montecarlo Simulation

Topics: Cumulative distribution function, Spreadsheet, Randomness Pages: 11 (3314 words) Published: December 28, 2010
Monte Carlo Simulation Using RiskSim

10

10.1 RISKSIM OVERVIEW
RiskSim is a Monte Carlo Simulation add-in for Microsoft Excel 2000–2010 (Windows) and Microsoft Excel 2004 (Macintosh). RiskSim provides random number generator functions as inputs for your model, automates Monte Carlo simulation, and creates charts. Your spreadsheet model may include various uncontrollable uncertainties as input assumptions (e.g., demand for a new product, uncertain variable cost of production, competitor reaction), and you can use simulation to determine the uncertainty associated with the model's output (e.g., annual profit). RiskSim automates the simulation by trying hundreds of what-ifs consistent with your assessment of the uncertainties. To use RiskSim, you (1) (2) (3) (4) (5) (6) create a spreadsheet model optionally use SensIt to identify critical inputs enter one of RiskSim's fourteen random number generator functions in each input cell of your model in Excel 2007 or 2010, choose Add-Ins | Risk Simulation | One Output; in Excel 2003 and earlier versions, choose Tools | Risk Simulation | One Output from Excel's menu specify the model output cell and the number of what-if trials interpret RiskSim's histogram and cumulative distribution charts.

RiskSim facilitates Monte Carlo simulation by providing: Fourteen random number generator functions Ability to set the seed for random number generation Automatic repeated sampling for simulation Frequency distribution of simulation results Histogram and cumulative distribution charts All of RiskSim’s functionality, including its built-in help, is a part of the RiskSim XLA file. There is no separate setup file or help file. When you use RiskSim on a Windows computer, it does not create any Windows Registry entries (although Excel may use such entries to keep track of its addins).

98

Chapter 10 Monte Carlo Simulation Using RiskSim

10.2 USING RISKSIM FUNCTIONS
RiskSim adds fourteen random number generator functions to Excel. You can use these functions as inputs to your model by typing in a worksheet cell or by using the Function Wizard. From the Insert menu choose Function, or click the Function Wizard button. RiskSim's functions are listed in a User Defined category. The fourteen functions are: RANDBINOMIAL(trials,probability_s) RANDBIVARNORMAL(mean1,stdev1,mean2,stdev2,correl12) RANDCUMULATIVE(value_cumulative_table) RANDDISCRETE(value_discrete_table) RANDEXPONENTIAL(lambda) RANDINTEGER(bottom,top) RANDLOGNORMAL(Mean,StDev) RANDNORMAL(mean,standard_dev) RANDPOISSON(mean) RANDSAMPLE(population) RANDTRIANGULAR(minimum,most_likely,maximum) RANDTRUNCBIVARNORMAL(mean1,stdev1,mean2,stdev2,correl12, min1,max1,min2,max2) RANDTRUNCNORMAL(Mean,StDev,MinValue,MaxValue)) RANDUNIFORM(minimum,maximum) RiskSim's RAND... functions include extensive error checking of arguments. After verifying that the functions are working properly, you may want to substitute RiskSim's FAST... functions which have minimal error checking and therefore run faster. From the Edit menu choose Replace; in the Replace dialog box, type =RAND in the "Find What" edit box, type =FAST in the "Replace with" edit box, and click the Replace All button.

10.3 UPDATING LINKS TO RISKSIM FUNCTIONS
When you insert a RiskSim random number generator function in a worksheet cell, the function is linked to the disk location of the RiskSim XLA file you are currently using. During the current Excel session, the formula bar shows only the name of the RiskSim function. But when you save and close the workbook, Excel saves the complete path to the disk location of RiskSim function. For example, after closing and reopening the workbook, the formula bar might show C:\MyAddIns\risk240s.xla\RandNormal(100, 10). This is standard behavior for Excel user defined functions like the ones contained in the RiskSim XLA file. When you open the workbook, Excel looks for the RiskSim XLA file using the saved path. If Excel cannot find the RiskSim XLA...