Montecarlo Simulation Free Essays

string(115) " random number generator functions as inputs for your model, automates Monte Carlo simulation, and creates charts\." 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. You read "Montecarlo Simulation" in category "Papers" Your spreadsheet model may include various uncontrollable uncertainties as input assumptions (e. We will write a custom essay sample on Montecarlo Simulation or any similar topic only for you Order Now 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, th e 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 isk240s. xlaRandNormal(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 file at the saved path location (e. . , if you deleted the RiskSim XLA file from the C:MyAddIns folder or if you opened the workbook on another 10. 3 Updating Links To RiskSim Functions 99 computer where the RiskSim XLA file is not located at the same path), Excel displays a dialog box like the one shown below. Figure 10. 1 Excel 2003 Warning To Update Links If you see this dialog box or a similar warning when you open an Excel file, choose the â€Å"Don’t Update† option. The workbook will be opened, but any cell containing a reference to a RiskSim function will display the #NAME? or similar error code.To update the links after the workbook is open, be sure that a RiskSim XLA file is open. Then choose Edit | L inks to see the dialog box shown below. In Excel 2007 or 2010, if you open a workbook with RiskSim functions referring to a RiskSim file location that no longer exists, you may see a warning. In Excel 2007, if you click the Options button of the security warning, you can click OK in the Security Options dialog box to dismiss the warning. Before you update the links, be sure that the RiskSim XLA file is open. In Excel 2007, choose Office Button | Prepare | Edit Links to Files to see the dialog box shown below.In Excel 2010, choose File | Info | Edit Links to Files to see the Edit Links dialog box. Figure 10. 2 Edit Links Dialog Box To update the links, click the Change Source button. A file browser window will open, where you can navigate to the RiskSim XLA file that is open. After you select the file using the file browser, click OK. Back in the Edit Links dialog box, click the Close button. 100 Chapter 10 Monte Carlo Simulation Using RiskSim In Excel 2003 the Edit Links dialog box has a Startup Prompt button. To avoid possible problems when Excel tries to automatically update links while a ile is being opened, we recommend the default â€Å"Let users choose to display the alert or not. † Figure 10. 3 Excel 2003 Startup Prompt Dialog Box 10. 4 MONTE CARLO SIMULATION After specifying random number generator functions as inputs to your model, from the Tools choose Risk Simulation | One Output. Figure 10. 4 RiskSim Dialog Box Optionally, select the â€Å"Output Label Cell† edit box, and point or type a reference to a cell containing the name of the model output (for example, a cell whose contents is the text label â€Å"Net Profit†). Select the â€Å"Output Formula Cell† edit box, and point to a single cell on your worksheet or type a cell reference.The output cell of your model must contain a formula that depends, usually indirectly, on the model inputs determined by the random number generator functions. Leave the Random Number Seed unchanged, or select the â€Å"Random Number Seed† edit box, and type a number between 1 and 2,147,483,647. Use an integer value without commas or other separators. Select the â€Å"Number Of Trials† edit box, and type an integer value between 2 and 32,000. This value, sometimes called the sample size or number of iterations, specifies the number of times the worksheet will be recalculated to determine output values of your model. 0. 5 Random Number Seed 101 10. 5 RANDOM NUMBER SEED The â€Å"Random Number Seed† edit box on the RiskSim dialog box allows you to set the seed for RiskSim’s random number generator functions. The seed must be an integer in the range 1 through 2,147,483,647. RiskSim’s random number generator functions depend on RiskSim’s own uniform random number function that is completely independent of Excel’s built-in RAND(). Random numbers generated by the computer are actually pseudo-random. The numbers appear to be r andom, and they pass various statistical tests for randomness.But they are actually calculated by an algorithm where each random number depends on the previous random number. Such an algorithm generates a repeatable sequence. The seed specifies where the algorithm starts in the sequence. A Monte Carlo simulation model usually has uncontrollable inputs (uncertain quantities using random number generator functions), controllable inputs (decision variables that have fixed values for a particular set of simulation iterations), and an output variable (a performance measure or operating characteristic of the system).For example, a simple queuing system model may have an uncertain arrival pattern, a controllable number of servers, and total cost (waiting time plus server cost) as output. To evaluate a different number of servers, you would specify the same seed before generating the uncertain arrivals. Then the variation in total cost should depend on the different number of servers, not o n the particular sequence of random numbers that generates the arrivals. 10. 6 ONE-OUTPUT EXAMPLE In this example the decision maker has described his subjective uncertainty using normal, triangular, and discrete probability distributions.Figure 10. 5 One-Output Example Model Display 1 2 3 4 5 6 7 8 9 A B Software Decision Analysis Unit Price Units Sold Unit Variable Cost Fixed Costs Net Cash Flow $29 739 $8. 05 $12,000 $3,485 C D E F G H Price is controllable and constant. Normal Mean = 700, StDev = 100 Triangular Min = $6, Mode = $8, Max = $11 Discrete Value Probability $10,000 0. 25 $12,000 0. 50 $15,000 0. 25 Figure 10. 6 One-Output Example Model Formulas 1 2 3 4 5 6 7 8 A Software Decision Analysis Unit Price Units Sold Unit Variable Cost Fixed Costs Net Cash Flow B 29 =INT(RANDNORMAL(700,100)) =RANDTRIANGULAR(6,8,11) =RANDDISCRETE(E7:F9) =B4*(B3-B5)-B6 102 Chapter 10 Monte Carlo Simulation Using RiskSim Figure 10. 7 RiskSim Dialog Box for One-Output Example 10. 7 RISKSIM OUTPU T FOR ONE-OUTPUT EXAMPLE When you click the Simulate button, RiskSim creates a new worksheet in your Excel workbook named â€Å"RiskSim Summary 1. † A summary of your inputs and the output is shown in cells L1:R9 with the accompanying histogram and cumulative distribution charts. 10. 7 RiskSim Output for One-Output Example 103 Figure 10. RiskSim Summary Output for One-Output Example 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 L RiskSim Date Time Workbook Worksheet Output Cell Output Label Seed Trials M (current date) (current time) (file name) Simulation Model for One Output $B$8 Net Cash Flow 9999 1000 RiskSim  ? Histogram 80 70 60 N O P Q Mean St. Dev. Mean St. Error Minimum First Quartile Median Third Quartile Maximum Skewness R $2,207 $2,816 $89 -$5,917 $184 $2,281 $4,148 $11,291 0. 0409 Frequency 50 40 30 20 10 0 ? 6,000 ? $4,000 ? $2,000 $0 $2,000 $4,000 $6,000 $8,000 $10,000 $12,0 00 Net  Cash  Flow RiskSim  ? Cumulative  Chart 1. 0 0. 9 Cumulative  Probability 0. 8 0. 7 0. 6 0. 5 0. 4 0. 3 0. 2 0. 1 0. 0 ? $6,000 ? $4,000 ? $2,000 $0 $2,000 $4,000 $6,000 $8,000 $10,000 $12,000 Net  Cash  Flow The histogram is based on the frequency distribution in columns I:J. The cumulative distribution is based on the sorted output values in column C and the cumulative probabilities in column D. 104 Chapter 10 Monte Carlo Simulation Using RiskSim Figure 10. 9 RiskSim Numerical Output for One-Output ExampleA 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 B Trial Net Cash Flow 1 $2,815 2 -$1,381 3 -$496 4 -$866 5 $5,795 6 -$3,320 7 $3,295 8 $8,360 9 -$2,157 10 $4,313 11 -$1,375 12 $2,198 13 -$744 14 $3,104 15 -$2,814 16 $4,165 17 -$2,575 18 $2,643 19 -$3,676 20 $2,186 21 -$3,492 22 -$847 23 $3,335 24 $4,641 25 $4,814 26 $536 27 -$909 28 $4,477 29 -$597 30 $4,018 31 $3,012 32 $4,590 33 $174 34 $3,257 3 5 $6,952 36 $6,252 37 $4,017 38 $5,386 C D Sorted Cumulative -$5,917 0. 0005 -$5,860 0. 0015 -$5,422 0. 025 -$4,702 0. 0035 -$4,646 0. 0045 -$4,601 0. 0055 -$4,513 0. 0065 -$4,439 0. 0075 -$4,057 0. 0085 -$4,037 0. 0095 -$4,027 0. 0105 -$3,884 0. 0115 -$3,846 0. 0125 -$3,715 0. 0135 -$3,684 0. 0145 -$3,676 0. 0155 -$3,561 0. 0165 -$3,525 0. 0175 -$3,492 0. 0185 -$3,468 0. 0195 -$3,320 0. 0205 -$3,285 0. 0215 -$3,250 0. 0225 -$3,178 0. 0235 -$3,118 0. 0245 -$3,104 0. 0255 -$3,071 0. 0265 -$3,065 0. 0275 -$3,054 0. 0285 -$2,960 0. 0295 -$2,957 0. 0305 -$2,830 0. 0315 -$2,820 0. 0325 -$2,814 0. 0335 -$2,795 0. 0345 -$2,671 0. 0355 -$2,654 0. 0365 -$2,638 0. 375 E F Percent 0% 5% 10% 15% 20% 25% 30% 35% 40% 45% 50% 55% 60% 65% 70% 75% 80% 85% 90% 95% 100% G Percentile -$5,917 -$2,459 -$1,405 -$809 -$196 $184 $694 $1,032 $1,442 $1,835 $2,281 $2,634 $2,972 $3,324 $3,737 $4,148 $4,617 $5,142 $5,673 $6,736 $11,291 H I J Upper Limit Frequency -$6,000 0 -$5,500 2 -$5,000 1 -$4,500 4 -$4,000 4 -$3,500 7 -$3,000 11 -$2,500 19 -$2,000 23 -$1,500 26 -$1,000 35 -$500 39 $0 62 $500 47 $1,000 65 $1,500 59 $2,000 64 $2,500 63 $3,000 75 $3,500 65 $4,000 57 $4,500 58 $5,000 49 $5,500 51 $6,000 30 $6,500 22 $7,000 18 $7,500 15 $8,000 7 $8,500 7 $9,000 7 $9,500 2 $10,000 4 $10,500 0 $11,000 1 $11,500 1 $12,000 0In column D, the cumulative probabilities start at 1/(2*N), where N is the number of trials, and increase by 1/N. The rationale is that the lowest ranked output value of the sampled values is an estimate of the population’s values in the range from 0 to 1/N, and the lowest ranked value is associated with the median of that range. Column B contains the original sampled output values. Columns F:G show percentiles based on Excel’s PERCENTILE worksheet function. Refer to Excel’s online help for the interpolation method used by the PERCENTILE function.The summary measures in columns Q:R are also based on Excel worksheet functions: AVERAGE, STDEV, QUARTILE, an d SKEW. 10. 8 CUSTOMIZING RISKSIM CHARTS If the labels on the horizontal axis are numbers with many digits, some of the labels may wrap around so that some of the digits display below the others. One way to remedy this anomaly is to 10. 8 Customizing RiskSim Charts 105 widen the chart (click just inside the outer border of the chart so that eight chart handles are shown and then drag the middle chart handle on the left or right to widen the chart).Another way is to select the horizontal axis (click between the labels on the horizontal axis so that â€Å"Value (X) axis† appears in the name box in the upper left of Excel) and change to a smaller font size using the Font Size drop-down edit box on the the Formatting tool bar. The histogram chart is a combination chart using a column chart type for the vertical bars and an XY (Scatter) chart type for the horizontal axis. The two chart types align properly as long as the horizontal axis retains the same minimum and maximum values. For example, if you want more spacing between the dollar labels on the horizontal axis, select the horizontal axis (so that â€Å"Value (X) axis† appears in the name box in the upper left of Excel), choose Format | Selected Axis | Scale, and change the â€Å"Major unit† from 1000 to 2000. In Excel 2007 or 2010, select the axis, right-click, and choose Format Axis | Axis Options. Do not change the Minimum = –6000 or the Maximum = 12000. The histogram appears as shown below. Figure 10. 0 Original Histogram With Modified Horizontal Axis Major Unit RiskSim  ? Histogram 80 70 60 Frequency 50 40 30 20 10 0 ? $6,000 ? $4,000 ? $2,000 $0 $2,000 $4,000 $6,000 $8,000 $10,000 $12,000 Net  Cash  Flow The cumulative chart is a standard XY (Scatter) chart type, so you can change the major unit as described above, but you can also change the minimum and maximum without affecting the integrity of the chart. Another way to obtain more spacing on the horizontal axis of the histogram or cumulative chart is to use a custom format.For example, if you want to show values in thousands instead of the original units, select the horizontal axis (click between the labels on the horizontal axis so that â€Å"Value (X) axis† appears in the name box in the upper left of Excel), choose Format | Selected Axis | Number | Custom, and enter a comma at the end of the current format shown in the â€Å"Type:† edit box. In Excel 2007 or 2010, right-click the horizontal axis, choose Format Axis | Number, change the Format Code, and click the Add button. After changing the original format â€Å"$#,##0† to â€Å"$#,##0,† and modifying the horizontal axis title, the cumulative chart appears as shown below. 06 Chapter 10 Monte Carlo Simulation Using RiskSim Figure 10. 11 Original Cumulative Chart With Horizontal Axis Custom Format RiskSim  ? Cumulative  Chart 1. 0 0. 9 Cumulative  Probability 0. 8 0. 7 0. 6 0. 5 0. 4 0. 3 0. 2 0. 1 0. 0 ? $6 ? $4 ? $2 $0 $2 $4 $6 $8 $10 $12 Net  Cash  Flow,   in  thousands  of  dollars 10. 9 RANDOM NUMBER GENERATOR FUNCTIONS RandBinomial Returns a random value from a binomial distribution. The binomial distribution can model a process with a fixed number of trials where the outcome of each trial is a success or failure, the trials are independent, and the probability of success is constant.RANDBINOMIAL counts the total number of successes for the specified number of trials. If n is the number of trials, the possible values for RANDBINOMIAL are the non-negative integers 0,1,†¦ ,n. RANDBINOMIAL Syntax: RANDBINOMIAL(trials,probability_s) Trials (often denoted n) is the number of independent trials. Probability_s (often denoted p) is the probability of success on each trial. RANDBINOMIAL Remarks Returns #N/A if there are too few or too many arguments. Returns #NAME! if an argument is text and the name is undefined. Returns #NUM! if trials is non-integer or less than one, or probability_s is less than zero or more than one.Returns #VALUE! if an argument is a defined name of a cell and the cell is blank or contains text. RANDBINOMIAL Example A salesperson makes ten unsolicited calls per day, where the probability of making a sale on each call is 70 percent. The uncertain total number of sales in one day is =RANDBINOMIAL(10,0. 7) 10. 9 Random Number Generator Functions 107 Figure 10. 12 RandBinomial Example Probability Mass Function Probability, P(X=x) 0. 30 0. 20 0. 10 0. 00 0 1 2 3 4 5 6 7 8 9 10 Total Numbe r of Sa le s in 10 Ca lls, x Figure 10. 13 RandBinomial Example Cumulative Probability Function 1. 0 0. 9 Cumulative Probability, P(X How to cite Montecarlo Simulation, Papers