Simulation Information
This appendix serves as a reference for readers who are interested in developing their own simulation models using Excel. Figure D.1 shows the Excel expressions used for the M/M/1 simulation module described in Figures 7.6 and 7.7. Additional expressions for using random numbers to generate input values for simulation are provided along with some general tips for using Excel. A description of useful Excel functions and capabilities for simulation applications completes this appendix.
Basic simulation module set up for an M/M/1 model. Note the necessary differences in the equations for the first three customers or items. To help identify what the cell addresses in the formulas are referring to, the cell address for the “Arrival Time” label is C11. See Figure D.1.
Inverse probability distributions expressions for simulating operating values using random number (RN) inputs. Some of these distributions can be used in simulating failure probabilities for maintenance service applications or for distributing arrivals.
Exponential distribution. t = – (1/(λ or µ) × ln(RN)
Uniform distribution. (min value = a, max value = b): t = a + RN(b – a); for an equivalent Excel function, use RANDBETWEEN(a,b)
Normal distribution.1 
for an equivalent Excel function, use NORM.INV(RN,mean,sigma)
Beta distribution. Use Excel’s BETA.INV(RN,α,β,A,B) function.
Gamma distribution (often used instead of Erlang’s distribution). Use Excel’s GAMMA.INV(RN,α,β) function.
Log normal distribution. Use Excel’s LOGNORM.INV(RN,mean,standard_dev) function.
Figure D.1.
Excel Application Information and Tips
Data Analysis Package
The Analysis ToolPak option in Excel needs to be activated for simulations because many of the functions mentioned require it. It is loaded when the software is installed, but for reasons unknown, the user must activate it. Similarly, Excel’s Solver option must also be activated by the user. To activate both options, use the appropriate instructions given here.
Excel Version 2003: Select the Tools menu, scroll down to the Add-Ins choice, select, and you will see a list of add-ins. Click the box for Analysis ToolPak. If not already activated, clicking the boxes for the other choices that do not have VBA (Visual Basic for Applications) at the end would be a good idea for future use in business problems. (VBA is for advanced Excel users who wish to program their own special functions.) Click OK and return to Excel. The data analysis add-in is then found as a choice on the Tools menu.
Excel Version 2007: Click on the big button in the upper left corner, select Excel Options at the lower right of the menu, then select Add-Ins in the left column of choices. Then check to see if the Analysis ToolPak and the Solver Add-in applications are active. If not, click Go on the Manage Excel Add-ins menu at the bottom and then check the Analysis ToolPak and Solver Add-ins menu that appears and click OK. The Data Analysis and Solver Add-ins are then found on the far right of the Data tab menu.
Excel Version 2010: The instructions are similar to those for Excel 2007, but you select the File menu to access the Options menu instead of clicking on the big button.
Useful Excel Functions for Simulation Model Creation
Excel has a fairly good help menu for learning new functions. The functions you may find particularly useful for simulations are presented here.
IF Function
Returns one result if the expression is true (e.g., X > 60), another if it is false. By concatenating IF functions, different sets of outcomes can be determined depending on the input conditions.
VLOOKUP and HLOOKUP Functions
These functions are used to select one piece of data in a table based on a correlating piece of data in that table when there is no equation to relate the two values directly. An example would be selecting the arrival rate associated with a given probability of occurrence.
COUNTIF Function
This function (=COUNTIF(range,criteria) is very useful for determining the current line length for a waiting line simulation. Examples are given in the M/M/1 model shown in Figure D.1. The criteria statement in the function to tell it when to count an item is generally easy to set up as illustrated in the Help files, but the criteria here for counting when one cell value in a range of cells is greater than a reference cell value is tricky when that reference cell address keeps changing. That is, the critieria in Figure D.1 is “>” (cell address for customer n’s arrival time) and the range is all of the service end times for customers 1 through n – 1.
Descriptive Statistics, Histogram, Regression, Rank, and Percentile Functions
These functions are found in the Analysis ToolPak. The other functions provided are also useful from time to time, but the descriptive statistics, histogram, regression, rank, and percentile functions are more commonly needed. If you are unfamiliar with using any of these functions, the Excel Help button provided with each function provides a basic tutorial regarding its use:
- Descriptive statistics allows you to select a range of data values and obtain results, such as average, min, max, standard deviation, median, and kurtosis, with just one keystroke.
- Histogram sorts the selected data into bins and can also automatically create a histogram chart of the results. Bin sizes can be specified by you as described in chapter 7, or the function will automatically choose an appropriate bin size if you wish. However, the automatic choice of bin size is likely to reduce the resolution of different arrival rates or service times in discrete probability lookup tables created from the histogram results.
- Regression allows calculating the intercept and slope constants from a set of x and y data in a straight-line approximation of this information.
- Rank and Percentile sorts a set of integer data from highest occurrence to lowest and calculates the percentage for each occurrence value as a percentage of the whole. This function can be useful for determining probabilities for discrete distributions in simulations.
Data Tables
This obscure capability of Excel is useful for summarizing simulation results. For example, when you run a simulation for 100 customers that might represent one day’s work and summarize the result—for example, average line length, max and min values—it is laborious to press the F9 key to execute another 100-customer simulation run, repeat the summarization, and so on for perhaps a month’s worth of work. Data tables allow you to collect a number of such summaries with one press of the F9 key, a major savings in time for simulation analysis.
The problem is that most Excel handbooks are quite vague about how to set data tables up for simulation runs and even finding the menu for more general use of data tables is difficult. In Excel 2010, the menu is part of the What If Analysis submenu under the Data tab on the toolbar. Chapter 26 in the book by Jelen (2010) has a short discussion of What-If tables (data tables) which is a useful introduction. I attempted to use the Scenario Manager in the Excel 2010 What-IF Analysis menu for setting up a simulation data table, but had no success. The Scenario Manager may be able to help, but at this time I am unable to provide the proper inputs because its nomenclature does not seem to correlate with any familiar simulation terms.
The book by Weida et al. (2001) has a fair introduction to the use of data tables for queuing simulations if you are using an older version of Excel than the 2007 and 2010 versions. Chapter 15 of Winston (2004) provides several examples on how to set them up for one-way and two-way applications. The problem is that to apply them to summarizing simulation data, the handbooks rarely discuss how to use a variable that is not actually used in the calculations (such as the simulation run number) to trigger the number of repetitions desired. Normally, when you use a sequence of run numbers as the one input variable, but do not use it in the data table calculations, all you get is one result. The trick is recognizing that the use of the random number function in the simulation equations causes the results to change each time a run is done. When, and only when, random numbers are involved, you can designate the run numbers as the input variable to trigger the run calculations. Weida et al. (2001) does that in chapter 13.2.2, but it is often tricky to make it work right. The other difficulty is that help searches for data tables often assume that you meant pivot tables, so the search takes you there. But data tables and pivot tables are not the same!
I am working on a much clearer explanation on how to use data tables in later Excel versions for simulation applications, which I plan to include as an appendix in a future revision of this monograph. In the meantime, it might take some effort to get a data table to work for you, but the effort is well worth it.