APPENDIX C
Excel Tips and Useful Functions
A significant amount of the content in this appendix is taken from Appendix D in my earlier book on waiting line applications.1 The content has been updated and augmented with some new content to better serve the discussion of integrating information considerations more thoroughly with other business processes. Readers interested in a more in-depth coverage of process simulation methods using Excel should find the waiting line book a useful companion reference.
Data Analysis Pack
This option in Excel needs to be activated for simulations since many of the functions mentioned require it. It is loaded when the software is installed, but for reasons unknown it is left to a user to activate it. Similarly, Excel’s Solver option must also be activated by the user. To activate it, follow the appropriate instructions below.
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 can then be 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 can then be 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 instead of the big button.
Useful Excel Functions
Excel has a fairly good help menu for learning new functions. A good reference to have for quickly learning how to use any given Excel function is the book by Held.2 Some functions you may find particularly useful are:
IF (condition, true answer, false answer) function. Returns one result if the condition is true (e.g., X ≥ 60), another if it is false (e.g., X < 60). By concatenating IF functions (that is, using another IF function for a true answer and/or a false answer), different sets of outcomes can be determined depending on the input conditions.
COUNTIF (range, criteria) function. Useful for counting the number of values within a defined range in a much larger set of data. An example would be the number of Utah and Nevada residents in a customer address database.
SUMPRODUCT (array1, array2, array3, …array n) function. Useful when the total of a number of products of individual items is desired. An example would be four columns of data: column A listing the name for an item, column B listing the quantity on hand for that item, column C listing the unit cost of that item, and column D listing the inventory tax rate for that item. Therefore, the potential inventory cost for an item is the quantity on hand times the unit cost times the tax rate. To get the total inventory cost without having to calculate individual item costs in a fifth column and then sum that column, one uses the SUMPRODUCT function using the data in columns B, C, and D as three input arrays to get the total in one cell operation. This function is particularly useful when setting up conditions for Excel’s Solver to find an optimal solution.
VLOOKUP and HLOOKUP functions. These are used to select one piece of data in a table based on a correlating piece of data in that table when there is not an equation that can be used to relate the two values directly. An example would be selecting the arrival rate associated with a given probability of occurrence.
Sparkline Tools. These compact within-a-cell charting functions are available via the “Insert” menu for Excel 2010. See Figure 2.1 in the text for an example of their use.
Conditional Formatting functions. These functions are useful for highlighting various data values according to rules you define. Most Excel users are familiar with highlighting negative values by displaying them in red; these functions expand the range of possibilities by offering data bars, colors, or symbols to highlight values of interest. They are available via the “Styles” submenu on the “Home” menu for Excel 2007 or Excel 2010.
Descriptive Statistics, Histogram, Regression, Rank and Percentile functions. These are found in the Data Analysis pack. The other functions provided in the pack are also useful from time to time, but these functions are more commonly needed.
•Descriptive Statistics allows you to just select a range of data values and obtain results such as the average, min, max, standard deviation, median, kurtosis, and so forth, using just one keystroke.
•Histogram sorts the selected data into “bins” (bin sizes can be specified by you) and can also create a histogram chart of the results automatically.
•Regression allows calculating the intercept and slope constants from a set of x and y data for use in a straight-line approximation of this data.
•Rank and Percentile sorts a set of integer data from highest occurrence to lowest and calculates the percentage for each occurrence value as a percent of the whole. This function can be useful for determining probabilities for discrete distributions in simulations.
Inverse Probability Distributions. These expressions are used for simulating operating values using random number (RN) inputs. The Excel function for RN is RAND(). Some of these distributions can also be used in simulating failure probabilities for maintenance service applications or for distributing customer 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 Distribution3: t = tavg + σ[Σ12i=1 RN.i – 6]; 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.
Data Tables. This useful, but obscure capability of Excel is useful for summarizing simulation results. For example, when you run a queueing simulation for 100 customers who might represent a day’s work and summarize the results—average line length, average waiting time, max and min values, and so forth, it is laborious to press the F9 key to execute another 100-customer run, repeat the summarization, and so on, to accumulate maybe a month’s worth of work. Data tables allow you to collect a number of such summaries with a single press of the F9 key, a major savings in time for simulation analysis. Note: For those of you unfamiliar with this use of the F9 key, pressing it causes Excel to recalculate all of the calculations on the worksheet. This includes selecting new random number values where the RAND() function is used on the worksheet.
Most Excel handbooks are quite vague about how to do this and even finding the software menu for data tables is difficult. In Excel 2010, the menu is part of the “What If Analysis” submenu under the “Data” tab on the toolbar. The book by Weida et al.4 has a fair introduction to the use of data tables for queuing simulations if you are using an older version of Excel. Chapter 15 of Winston5 and pages 656–661 of Harvey6 provide a number of examples on how to set them up for one-way and two-way applications. When you want to apply them to summarizing simulation data, many of the handbooks rarely discuss how to use a variable that is not actually used in the calculations (like the simulation run number) to trigger the number of repetitions desired. Weida et al.7 does provide some advice as to how to do that, but it can be tricky to make it work right. The other difficulty is that help searches for data tables often assume that you are inquiring about pivot tables and the search takes you there. They are not the same!
It may take some effort to get a data table to work correctly for you, but the effort is well worth it.
Pivot Tables. The advantage of Pivot Tables in Excel is that a variety of ways for depicting tabular information is possible without the need for entering formulas in separate tables of data for calculating averages, medians, and so forth, so that those calculated values can be charted or plotted. Pivot Tables extract the desired data from the tabular information and process that data as desired for either a summary table or a desired graphic output, or both. The processing options are extensive and include filtering, comparing, sorting, logical, math functions, and value testing. If you need to analyze a significant amount of data on a regular basis, becoming proficient in the use of Pivot Tables will be worth your time. I would recommend becoming familiar with the basic Pivot Table functions before downloading the Power-Pivot application from Microsoft®. To be honest, learning how to use Pivot Tables is not a trivial exercise and not to be undertaken for the first time when one is working within a limited time constraint. Some useful references for self-study are the two books by Jelen.8
A major disadvantage is that there is no Undo or CTRL-Z function for Pivot Table operations, so mistakes are harder to correct. Pivot Tables also cannot be edited the way other Excel tables can be edited. That is, you cannot insert an extra column or row in a Pivot Table using the “Insert” function. There may be a simple trick for canceling a Pivot Table and starting anew, but I have not found it yet.
Hence, it is a good idea to save a separate file copy of your data, like that shown in Figure C.1, when you are learning how to use Pivot Tables.
Figure C.1. Customer volume and sales volume (next page) for two store locations for a given week..
To show some of the outputs possible we will consider some typical data collected by a retail business as shown in Figure C.1. This business has two store locations in a city with a population of about 150,000 people. The locations are super-sized convenience stores located near commuter transportation centers. The stores sell popular gift items, some kitchen utensils, household repair items, and gourmet food items in addition to the usual convenience store products of snack food, dairy products, beverages, ready-cooked items, and commonly used over-the-counter medicines. The managers are considering changing the number of hours a location is open, maybe expanding a location if the demand justifies it, and so forth. To this end, they have collected some POS data for several weeks from the two locations. To keep this example simple, we will look at just two sets of tabular data for each location: the number of customers and the total amount of sales per hour of operation in each store. Some of the possible analysis results are shown in Figure C.2.
Figure C.2. Some analysis summaries using the data shown in Figure C.1. When using Pivot Tables to produce these plots, the field buttons must be hidden and some tweaking of the labels is necessary.
Some conclusions to be drawn here is that both stores have similar customer and sales patterns with the Westgate store doing slightly better overall because of its higher customer volume. It is interesting to note that weekend customers appear to spend more and sales tend to be higher toward the end of the day. The data suggest that increasing opening hours at the end of the day on weekends might be the best choice for expansion. It is speculation, but maybe weekend commuters have more leisure time to shop and are out later.