TM Software Reference http://www.analycorp.com XLSim is an Excel add-in that performs Monte Carlo simulation. It requires Excel 97 or higher. Installing XLSim Installation is accomplished by running Setup.exe. All examples and documentation are stored in the C:\XLSimFiles directory. NOTE: When too many add-ins are attached to Excel, they tend to interfere with each other, sometimes freezing Excel. We recommend that you minimize the number of add-ins used in conjunction with XLSim. Use the Tools Add-ins menu from Excel, and remove the check marks from those add-ins not needed. In particular, if you have previously used SIM.xla, eSIM or SIM25k, make sure to unload them before using XLSim. Then exit and restart Excel. Running XLSim Run Excel and open XLSim.xla from the File menu. Auto Load Option If you want XLSim to load every time you launch Excel Select Add-ins from the Tools menu in Excel. Select XLSim from list of add-ins and click "O.K." You can later go back and deselect XLSim from the Add-in menu to prevent Excel from loading it automatically. What's New XLSim.xla is quite similar to and upward compatible from SIM.xla. The primary enhancements are listed below. 1 Million-iteration limit. Over 100 Output Cells Additional Distributions. All generators now have minimal documentation within function wizard. Open gen_Functions.xls and use the Thaw command to view demonstrations of all random number generators. Gen_Resample may now be used as an array formula, removing the need for ResampleSync, which has been dropped. gen_Shuffle is an array formula that permutes an existing range of data into a new range. Gen_MVNormal is an array formula for generating multi-variate normals. Gen_Myerson is a generalization of the Lognormal distribution developed by Professor Roger B. Myerson of Northwestern University Freeze and Thaw commmands allow for easy shipment of models. Before sending a model to someone else, use the freeze command to store all gen functions in comments. To use a frozen model, simply Thaw it then save on your hard drive. NOTE: TO USE YOUR EXISTING SIM.xla models, do a Freeze followed by a Thaw. All graphs are now accessed from a menu, rather than buttons on the SimStat sheet. A new graph type available for common graphs and parameterized simulations allows for scrolling through distributions to view an animation. Data Range command analyzes spreadsheet data. Includes Histograms with variable numbers of bins. Menu and Dialog Boxes   Once XLSim is loaded the Simulate menu will appear. Random input cells are specified with the Excel Function Wizard.  Simulation initiates a simulation run of the specified output cells. Results are posted in a new workbook entitled SimStats.xls.  Parameterized Sim repeatedly runs the same simulation of a single output cell with different parameter values.  Data Range uses data from a worksheet instead of randomly generated numbers. Great for making Histograms of data.  Graphs draws histograms and cumulative graphs of the simulation data.  Common Graphs draws histograms and cumulative graphs of multiple series on the same axis. Be sure to try the interactive Histogram.  Freeze is used before saving a spreadsheet model so that other XLSim users can read it on a different computer.  Thaw is used to make a model live again after it has been frozen. Use Freeze and Thaw to upgrade your models from SIM and SIM25k.  View Trials displays the actual simulation data and convergence-to-mean graphs.  Remove XLSim closes XLSim. Optionally removes XLSim from Tools Ad-Ins start up list.       Simulation    The dialog box below will appear when you select Simulation. Parameterized Sim    The dialog box below will appear when you select Parameterized Sim. Data Range    This command is used when you want to read in data from a worksheet and analyze it with XLSim. It provides a useful Histogram in which you can adjust the number of bins (unlike Excel's histogram in the Analysis Tools). The following dialog box will appear when you select Data Range from the Simulate menu. Simulation Results    XLSim can produce the following outputs: statistics, histograms, cumulative graphs and series graphs. All outputs except for statistics are Excel graphs and can be edited with standard Excel commands. Statistics Upon completion of the simulation, statistics are displayed for each output cell.   In the case of Simulation, statistics appear as shown below. In the case of Parameterized Simulation, statistics will be displayed for each parameter value as shown below. In this case the parameter is Demand and the output is Revenue. Histograms and Cumulative Graphs    Histograms are available after a simulation and are created by selecting Graphs from the Simulate menu. The following dialog box will appear. Once you have selected which outputs to graph, click on the Graphs tab at the top of the dialog box. A separate histogram and cumulative graph are created for each of the selected outputs. The histogram appears on a sheet called Output Name H and the cumulative graph on a sheet called Output Name C.   Histogram with 5 bins and 2 decimal places Cumulative frequency graph Common Graphs    Sometimes it is useful to compare the different outputs of a simulation side-by-side on the same set of axes. This is especially useful when you have run a parameterized simulation and would like to see the output for each of the parameters. Say we have run a parameterized simulation with parameter values 1, 2, 3 and 4. To compare the outputs, use the Common Graphs command on the Simulate menu.. The following dialog box will appear. Once you have selected which outputs to include, click on the Graph tab at the top of the dialog box. Common Histogram Types Interactive This option produces histograms on separate sheets for each of the selected outputs. The histograms all have the same axes so that a meaningful comparison can be made between them. The sheets are called Output Name I, and can be paged through using Ctrl-PgUp and Ctrl-PgDn. 3D Bar This option draws a 3D histogram showing all of the outputs on one set of axes. 2D Line This option also draws all the outputs on one axis, but represents them as 2D lines. Common Cumulative Graph The Cumulative button on the Common Graph dialog box draws the cumulative frequency graphs of the selected series all on the same axis. Series Graphs Series graphs plot the confidence interval of the selected outputs on a common axis. They are accessed using the Series Graph tab on the Common Graph Dialog box. A Series Graph is useful for assessing the risk associated with one parameter against versus another.  Freeze and Thaw    Use the Freeze command on the Simulation menu before you send an XLSim model to another user of XLSim. Freezing a workbook means the random numbers are replaced by their current value, and the formulas are stored in the cell comments. Once a workbook is frozen it can be given to another user of XLSim who can use the Thaw command to restore the random number formulas and recreate the model. To update a model written with SIM.xla or SIM25k.xla, first Freeze it, then Thaw it. View Trials    You can view the results of the simulation trials by selecting View Trials from the Simulation menu. The following dialog box will appear. The trial data appears on a new sheet, along with the convergence data if the Draw convergence graph box is checked. The convergence graphs appear on sheets named Output Name Conv. Random Number Generators    Make sure that XLSim is open before entering random number generating functions or opening an Excel workbook, which uses these functions. Be sure to look at gen_Functions.xls to see applications of the various functions. Using the Function Wizard To place a random number generating function in the spreadsheet, place the cursor in the desired cell, then click on the function icon . You should see a screen from which you select Statistical from the left menu and the desired function name from the right menu. Click on OK and you will be prompted for the distribution's parameters. If, for example, you selected gen_Normal, you will be prompted for the Normal distribution's mean and standard deviation as shown below. Fill in the requested parameters and click OK. Each time you press the calculate key - the F9 key in Windows - you should see a new random number generated in the cell.   Software Limitations XLSim has been designed to provide an introduction to Monte Carlo Simulation for spreadsheet users. Although intended to solve real problems, the emphasis has been on ease of use rather than on extensive features. Two good commercial spreadsheet add-ins, @RISK and Crystal Ball provide greater Monte Carlo performance in several areas, albeit at higher costs, and steeper learning curves. In addition there are several stand-alone products that do what is known as Discrete Event simulation. These can simulate complex systems that evolve through time, such as workflow on a factory floor, or a telephone call center with waiting lines. A nice product in this category is Extend from Imagine That Inc. Advice on Upgrading If you believe that you have a spreadsheet model to which Monte Carlo simulation may be applied, it is recommended that you prototype it using XLSim. You may find that XLSim provides valuable insights and that nothing more is needed. On the other hand the XLSim model may convince you that further analysis will have a great impact, in which case you should consider upgrading to more powerful software. The primary areas of increased performance available through upgrading to more powerful software are: Monte Carlo Simulation Packages Larger number of idealized distributions. XLSim is limited to those shown under the function wizard above. Latin hypercube sampling. This sophisticated method of generating random numbers can speed up the rate of convergence of a simulation. Correlations may be directly specified between random inputs for distributions other than Multivariate Normal. Sensitivity of specified output and input cells is provided in terms of R2 values. Discrete Event Simulation Packages Graphical interface for modeling your project Time dimension through which simulation evolves

Introduction

Section I Uncertain Numbers: Random Variables

Section II Combining Uncertain Numbers: Diversification

Section III Plans Based on Uncertain Inputs: Functions of Random Variables

Section IV Uncertain Numbers that Depend on Other Uncertain Numbers: Statistical Dependence

Command Reference

References

Acknowledge- ments