<meta http-equiv="refresh" content="1; url=/nojavascript/"> Excel Computer Simulation | CK-12 Foundation
Dismiss
Skip Navigation
You are reading an older version of this FlexBook® textbook: CK-12 Modeling and Simulation for High School Teachers: Principles, Problems, and Lesson Plans Go to the latest version.

Lesson Objectives

  • Construct a computer simulation in Excel by:
    • creating a random observation based on a probability distribution
    • using the spreadsheet to keep track of how many customers are waiting in line for service at a fast food restaurant that is simulated in this lesson
    • computing statistics and graphs that show the results of the computer simulation
    • using the simulation spreadsheet to explore different sales associate staffing levels

Vocabulary

first-come, first-served
In setting up a business operation, a manager must decide the sequence in which customers are to be served or the sequence in which orders are to be processed when manufacturing physical goods. The first-come, first-served (FCFS) method is one in which customers are served in the same order that they arrive. This can be implemented by having customers stand in a line or by having them take a number from a sequential set of numbers that are available as they enter the business. For example, at meat counters or delis, you might be familiar with taking a number from a red dispenser; this effectively implements a FCFS policy.
observation
In a simulation analysis, an observation can refer to observing what happens to one customer as the computer mimics a business operation. An observation can also refer to running an entire simulation and then computing the statistics that describe what happened as many customers were served.
queue
A queue of people refers to a line of people who are waiting to be served, for example, at a restaurant or at a movie ticket office. The word queue is also used to describe work that builds up for a person or machine to do, when the work is not represented by a person. For example, there can be a queue of invoices to be processed, a queue of orders to be fulfilled, or a queue of automobile engines waiting to be tested.
random variable
A random variable is a numeric quantity whose value cannot be predicted in advance, where the probability of each possible value is defined by a probability distribution. More generally, random variables can also be other non-numeric outcomes of events, such as when a coin flip can result in either a heads or a tails.

Check Your Understanding

Recalling Prior Knowledge

This lesson describes how to construct a simulation model using Microsoft Excel, for which students must have the requisite spreadsheet skills, including knowing these spreadsheet functions:

  • MIN
  • MAX
  • IF
  • VLOOKUP
  • RAND

Students must also be familiar with these concepts and techniques having to do with spreadsheets:

  • ranges of cells
  • recalculation
  • copy and pasting cell contents

Students must also understand these concepts, as explained in prior lessons in this chapter:

  • probability distributions
  • random number generators

Introduction

The lesson "How Variation Makes Decisions Difficult" demonstrated that customer service outcomes may be quite different from what a manager expects because of variation in how customers arrive and how quickly customers are served. One way to eventually determine how many sales associates should staff a sales counter in these situations, would be to experiment with the actual process. A manager might try out adding to or reducing the number of sales associates to figure out what the right number is to achieve the target level of customer service. This is an approach in which decisions are based on actual observations. In the fast food restaurant example, adding a sales associate could be a good approach when it is relatively inexpensive for a manager to pay a second sales associate for a few days to do the experiment, and the experiment can be quickly ended. Reducing the number of sales associates might be a more risky experiment if customers were poorly served because it might hurt future business.

In other situations, however, it is impractical to temporarily increase capacity to try a different level of capacity. For example, if increasing capacity meant that another burger grill would need to be purchased, then increasing the capacity in this way cannot be tried in an inexpensive manner. Once an expensive grill is purchased, it cannot be returned for a refund. If the manager then finds out that the new grill doesn’t increase customer service as much as he or she thought, the manager has made an unwise investment and the profit of the business will be reduced. Every time a manager makes such an investment, he or she does so only when he or she thinks the investment will increase the revenues by more than the investment. If this is not the case, then profit will be reduced. Experimenting with expensive investments is not a good idea.

This lesson is about how a manager can avoid making the mistake of making an investment that costs more than its value to the business in terms of increased customer service. The method we will describe to do this is called a computer-based simulation. We will describe how to conduct an experiment on the computer that approximates the result obtained by the manager in the experiment described in the previous paragraph. Computer simulation is a computer program that mimics the actual operation in which a decision is being made, and we can use the program to estimate changes in customer service and profit without having to experiment with the actual operation. This method reduces business risk because no chances are taken with disrupting customer service, and it eliminates the need to buy expensive equipment without having a good idea whether the equipment will increase profit or improve customer service. In addition, this method eliminates the cost of the extra wages for the additional sales associate in the experiment, and the simulation, in this case, can be relatively quick to construct. However, a company must have an employee that is capable of constructing a simulation model and analyzing the results, and the company must be willing to dedicate that employee to the simulation project for the number of hours required. Alternately, the company must be willing to pay a consultant to perform the simulation analysis if they do not have an employee with expertise in simulation analysis.

Lesson Content

The Excel Computer Simulation lesson is divided into these three sections:

A. Creating a Random Observation from a Probability Distribution

B. Creating a Spreadsheet Simulation Model

C. Calculating Statistics for the Simulation Model

Performing the tasks described in the three sections results in a working simulation model that could help a manager make a better decision about the number of sales associates to have in a fast food restaurant — without having to observe their operation over a long period of time or risk making initial decisions that would dissatisfy customers.

Excel files representing the examples used in this lesson and various stages of the simulation development can be downloaded from this web site: http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/

Creating a Random Observation from a Probability Distribution

We are uncertain about how many customers will arrive at a fast food restaurant during a particular one-minute interval of time because many different outcomes could occur: maybe no customers arrive, maybe one customer arrives, maybe two customers arrive, and so forth. This is the type of situation where we need a probability distribution to reflect the likelihood of each possible number of customers that could arrive within a one-minute window. (The term "window" is often used to describe a period of time over which something happens.) That probability distribution might look like the one shown in Figure below, which corresponds to the numerical data shown in Figure below.

Probability Distribution for Customer Arrivals

Table of Probabilities

If we are to mimic this customer arrival scenario in a computer simulation, we want no customers to arrive in 8.2% of the one-minute windows over which we simulate an operation, one customer to arrive in 20.5% of the time intervals, and two customers to arrive in 25.7% of the intervals. This is reflected in the probability distribution graph in Figure above and in the numerical data in Figure above.

We will describe a method to accomplish the task of choosing the number of customers that arrive in the desired percentages by first subdividing the values from 0 to 1 into segments and assigning one segment to each potential number of customers that could arrive. The length of the segment that we assign to each possible number of customers that could arrive in a one-minute window will be equal to its probability. (The sum of all probabilities in a probability distribution always is 1, and so this method will always apportion all the values between 0 and 1, never more and never less.) For example, the probability of no arrivals is 8.2%, so we want to assign 8.2% of the values between 0 and 1 to the event that no customers arrive. Therefore, 8.2% of 1 is 0.082, and so we assign the values from 0 to 0.082 to the event that no customers arrive. Similarly, the probability of one customer arriving is 20.5%, and so we want to assign 20.5% of the range of values from 0 to 1 to the outcome that one customer arrives. We have already assigned the values from 0 to 0.082, and so the segment of values that we assign to one customer arrival starts at 0.082. Thus, we would assign the range of values from 0.082 to 0.287 to the outcome of one customer arriving (0.082 + 0.205 = 0.287). In about 25.7% of the time windows, two customers arrive, so we would assign a range of values 0.257 long to the outcome that two customers arrive. Thus, we assign the segment from 0.287 to 0.544 to that outcome. The remaining segment assignments are calculated similarly, as shown in Figure below.

Assignments of Values between 0 and 1 to Possible Number of Customer Arrivals

Suppose that we had a number generator that would generate a sequence of numbers in which each number was equally likely to be any value between 0 and 1, so that the percentage of numbers that were picked on some segment between 0 and 1 was equal to the length of that segment. For example, 8.2% of the time, the number generator would pick a value between 0 and 0.082. Furthermore, suppose that the numeric quantities generated had no discernible pattern, that is, it is unlikely that a long sequence of numbers would have similar values or that any distinct trends in numbers existed, either up or down. Simply put, the numbers would vary from one to the next and their values would be unpredictable. With such a number generator, we could generate the number of customers that arrived in successive one-minute intervals where the probabilities corresponded to the probabilities in Figure above, using the following steps for each time window:

  1. Generate a value between 0 and 1 with the number generator.
  2. Pick the row in the table in Figure above where the random number from the first step is contained between the lower value and upper value of the segments that have been assigned to each number of possible customers who could arrive.
  3. Select the number of customers from the third column in Figure above that corresponds with the segment that was identified in the previous step.

Fortunately, such a number generator does exist in an Excel function and was described in the "Spreadsheet Skills for Computer Simulation" lesson. The Excel function is called RAND, and it generates random numbers between 0 and 1, just as is needed to generate numbers for the steps above. The numbers that RAND generates are called random variables, as defined at the beginning of this lesson, and the name for the probability distribution that describes the numbers generated by RAND is called the continuous uniform distribution on the range between 0 and 1. The word uniform indicates that each value between 0 and 1 is equally likely or, in other words, the probability for each value is uniform. The technical term continuous means that any decimal value between 0 and 1 is possible.

The only remaining task, now that we can generate random values between 0 and 1, is to devise a spreadsheet method to associate those random numbers with the appropriate number of customers. The Excel spreadsheet function VLOOKUP, as also described in the "Spreadsheet Skills for Computer Simulation" lesson, can be used to accomplish this task. To demonstrate, and to begin developing our spreadsheet simulation model, we will create a new Excel workbook, and we will dedicate one worksheet within the workbook to generate the arrivals of customers. Developing a separate worksheet for generating demand helps keep the workbook organized and understandable, and it makes sense because the structure of the cells to generate demand will be different than the worksheet on which we will do the computer simulation (each will have different column widths, different formatting, etc.).

So, open Excel and, in a new workbook, give one of the worksheets the name "Demand" so the purpose of the worksheet is apparent, as shown in Figure below. Create the table of data from Figure above. (Alternately, a template named FastFoodSimulationTemplate.xlsx with these steps completed can be downloaded from http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/.)

Demand Worksheet

Those who have done the previous lesson on "Spreadsheet Skills for Computer Simulation," or who otherwise know how to use the VLOOKUP function, understand that it looks down the first column of a range of cells and identifies the value in that column that is the greatest number in the column that is less than or equal to a target value. VLOOKUP selects the row that contains that value, and then it returns a value from that row from a particular column in the range, which the user specifies. We will have VLOOKUP consider the range of data in rows 5 through 13 and columns A through C of the Demand worksheet in Figure above. If we generate a random number using Excel’s RAND function and use it as a reference value comparing it against column A, then 8.2% of the time RAND will generate a number between 0 and 0.082 and VLOOKUP will select row 5; 20.5% of the time RAND will generate a value between 0.082 and 0.287 and VLOOKUP will select row 6; 25.7% of the time RAND will generate a number between 0.287 and 0.544, and VLOOKUP will select row 7; and so on. If we then have VLOOKUP return a value from column C in the row that was selected based on the data in column A, then we will pick the number of customers according to the correct probabilities in Figure above. The following VLOOKUP function implements this idea:

=VLOOKUP(RAND(),A5:C13,3)

where the "3" indicates the third column of the range being evaluated, which is column C. Entering this formula into cell B2 results in a worksheet like the one shown in Figure below.

Using VLOOKUP and RAND to Generate Customer Arrivals

Every time F9 is struck to recalculate the workbook, a random number for the number of customer arrivals is generated in cell B2. Note that it is possible that two sequential numbers could represent the same number of customers. If we collected many random numbers generated this way in succession and plotted the percentage of times that each number of customers arrived, we could check to see that we were correctly reflecting the actual demand probability distribution. Figure below does that comparison for 1,000,000 different one-minute windows of time. That figure shows that the method above accurately reflects the intended probability distribution. The experiment deviates from the probabilities for only two possible levels of demand, and by only a small amount. If more than 1,000,000 observations of demand were made, then we would expect the resulting percentages to be even closer to the real probabilities that we are mimicking.

Percentage of One-Minute Windows where VLOOKUP Selected Various Numbers of Customer Arrivals

Mimicking the number of customers that arrive as we have done is technically called generating random variables of demand. These numbers are random in the sense that the next value cannot be predicted, and they are variables in that the number of customers varies from observation to observation. Note that these random variables are not totally random because their probabilities are defined by a known probability distribution.

In the simulation model that we will construct, we will want to construct the random variables on another worksheet besides the Demand worksheet. Figure below shows a VLOOKUP function that is used to generate random variables of demand on a worksheet entitled "Sheet1." This equation can be entered by first typing =VLOOKUP(RAND(), then navigating to the Demand worksheet by left-clicking on the appropriate tab at the bottom of the Excel window and highlighting the range of cells that is used to create the random variable, specifically the range A5:C13. Then the formula is completed, as in the previous example, by typing ,3). This approach yields a slightly different result than we observed previously. Specifically, Figure below shows that formula as:

=VLOOKUP(RAND(),Demand!A5:C13,3)

The difference between this formula and the previous one is that the range A5:C13 is displayed as Demand!A5:C13. The designation Demand! indicates that the range A5:C13 is on the worksheet named Demand. This designation is necessary because we typed the VLOOKUP formula on the Sheet1 worksheet. Without such a designation, Excel would have assumed that the range A5:C13 was on the same worksheet as where the formula appears.

VLOOKUP Function for Random Variable Entered in a Worksheets Named Simulation Creating a Spreadsheet Simulation Model

In constructing a spreadsheet simulation model we need to mimic how customers arrive and the number of customers who are served each minute. We have described how to create a worksheet to mimic demand arrivals in the previous subsection of this lesson. Creating the worksheet to mimic how quickly customers are served is posed as a review problem at the end of this lesson. Its development is exactly the same as for the customer arrival worksheet, except the data for how many customers are served is different than for how many customers arrive. Also, the cells used to generate how many customers can be served in each one-minute window should be placed on its own worksheet, named "Service," using the same structure as the Demand worksheet. Once the worksheet for customer service is constructed, what remains to be done is to account correctly for arriving customers, customers who are served, and customers who remain waiting in line at the end of each one-minute period in the operation that we are mimicking. This task is actually fairly simple: we have already done the most technically difficult part of the simulation, which is to develop the worksheets to mimic demand and how fast customers are served.

Take the workbook in which you created the Demand and Service worksheets and name an additional worksheet "Simulation." On that sheet, create column headings as shown in Figure below, or download the template that has been completed to this point from this web site: http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/

Layout of the Simulation Worksheet

The Simulation worksheet will use one row for each one-minute increment of time, and we will use a sequence of integers in column A to indicate which minute during the lunch period is being analyzed in a particular row. Note that only the first 10 sequence numbers for the first 10 minutes have been entered in Figure above. We will extend the number of rows that we use later to encompass a three-hour lunch period. The data in column B shows the number of people already waiting at the beginning of each one-minute interval. Note that a value of 0 has been entered in cell B4 because we are assuming that there are no customers in line or waiting outside the restaurant doors when the restaurant opens for lunch. We could easily change this number to something other than 0 if some other value was more representative of the real situation: this could even be some random number of customers.

Column C shows the number of customers that arrive each minute, which we will determine using the Demand worksheet. Column D shows the total number of customers that are available to be served any particular minute. This includes both those customers who have just arrived (column C) as well as those customers who arrived previously but who have not yet been served (column B). Column E indicates how quickly the sales associate is working in each one-minute interval or, more precisely, how many customers the sales associate could serve at their current work pace assuming they could work the entire one-minute period without running out of customers to serve. Column F indicates how many customers were actually served in each minute interval: this is the minimum of the number of available customers and the number of customers that could be served if the sales associate did not run out of customers. Column G shows the number of customers who were present but who were not served in each interval. Customers remain waiting after the end of a time interval when there are more customers than the sales associate can serve. These customers will need to be served in subsequent one-minute intervals.

The formulas that are used to generate the data for these columns are entered into the cells noted in Figure below for the first minute of the simulation (and one formula for Minute 2) before being copied down to rows for the remaining duration of the simulation.

Formulas for Simulation Worksheet

Note that the references to the customer arrival data and customer service data in the VLOOKUP functions in Figure above use dollar signs to create absolute addresses. This is needed because we will be copying these formulas to other rows in the worksheet, and we do not want the references to these tables of data to change. For a review of this topic see the subsection on "Copying and Pasting Cell Contents" in the "Spreadsheet Skills for Computer Simulation" lesson. Also note that these formulas assume that the Service worksheet has been constructed using exactly the same cells as the Demand worksheet. Specifically, the formulas above assume that the probability data is in the range A5:C13. If some other range is used for the service rate data, then the reference to the range of cells needs to be changed appropriately.

After entering the formulas listed in Figure above, copy the formulas in cells D7 through G7 down one row. The worksheet should look like the one shown in Figure below, although the quantities will be different due to the random nature of the arrivals and number of customers served. Row 8 now contains a complete row of formulas that can be copied down to other rows. Copy cell A16 down through cell A186, then copy the formulas from the range B8:G8 down through row 186 also. (Note that we could not copy the formulas from row 7 because the value in cell B7 is not what we want in subsequent time intervals.) We want to analyze a three-hour lunch period, and copying the formulas down through row 186 gives us 180 one-minute time intervals, or three hours of observations. It can now be observed how the worksheet values change every time F9 is struck and new random variables of demand and service are generated.

Simulation with Formulas for First Two One-Minute Periods

Calculating Statistics for the Simulation Model and Graphing Results

Interpreting the results of the simulation requires that we create graphs and compute statistics. To do this, first, plot the number of customers waiting in line using the values in column A for the x-axis data and the values in column G for the y-axis values. If you are not familiar with creating graphs in Excel, then see the earlier lesson on "Spreadsheet Skills for Computer Simulation."

Although the graph will change each time F9 is struck, Figure below gives an example of what that graph might look like. Each time the F9 key is struck and the workbook is recalculated, the graph will look different. In simulation studies we say that each one of these different results is called an observation. Viewing various possible observations gives us an intuitive feel for the variety of outcomes that are possible over a lunch period. The graph in Figure below gives us an idea of the maximum number of people in line, the minimum number of people waiting, and some general idea of what the average number of people in line might be.

Sample Graph of Number of People Waiting in Line

Our goal in creating the simulation model was to analyze how well customers would be served if we had only one sales associate working at the counter. Besides viewing the graph, above we can compute numerical statistics that describe customer service. How long customers need to wait on average to get their food is one such statistic that describes customer service. The average time a customer waits is the sum of all the time that all customers wait divided by the number of customers that have been served:

\text{Average Customer Waiting Time} = \dfrac{\text{Total Waiting Time for All Customers}}{\text{Number of Customers Served}}

Computing the exact average waiting time would require that we track the precise waiting time of each customer individually, but we will be happy with computing an approximate value for average customer waiting time because approximations are often sufficient for making good business decisions. We can develop a good estimate using the logic in Figure below, which estimates how long each customer was present during each one-minute interval, depending on the customer’s situation. The logic upon which Figure below is based assumes that customers are served in the same order as they arrived, or on a first-come, first-served basis.

Approximation Logic for Average Customer Waiting Time

We must accumulate the waiting time for every customer that was present in every one-minute interval over the 180-minute lunch period, both for customers who were already waiting at the beginning of the interval and for those who arrived during the interval. One minute of waiting time will be accumulated for each customer who was waiting before each one-minute interval began and was still waiting after the one-minute interval. This is actually the precise amount of waiting time for customers in this situation. We need to accumulate one half of minute for every other customer who is present during each time interval. If we can figure out the number of customers for whom we should accumulate one minute, then this makes the calculation easy because we can accumulate one half of a minute for every other customer who was present.

To that end we start by calculating the number of customers who are waiting an entire one-minute period by comparing the number of customers waiting at the beginning of a particular period with the number of customers served in that period. If customers are served in a first-come, first-served manner, then those already waiting will be served first. If 10 customers were already waiting, and the sales associate served 3 customers during a one-minute interval, then all the customers who were served were already waiting before the interval started, and, therefore, there are still 7 customers waiting that were already waiting before the interval started. It is those 7 customers for whom we need to accumulate one minute of waiting time. In this case, we can calculate the number of customers for whom we need to accumulate one-minute of waiting time with this equation:

Number of Customers Already Waiting - Number of Customers Served = 10 - 3 = 7.

However, what if the number of customers already waiting was fewer than the number who were served? This would be the case, for example, if 2 customers were already waiting at the beginning of the period and the sales associate served 4 customers. In that case, everybody who was already waiting was served and there are no customers for whom we need to accumulate one-minute of waiting time. If we performed the calculation above in that case, it would result in a negative number:

Number of Customers Already Waiting - Number of Customers Served = 2 - 4 = -2.

A negative number of people, however, doesn’t make sense: there is no such thing as a negative number of customers. In fact, when the number of customers served exceeds the number who were waiting at the beginning of the period, there are no customers in that case who are still remaining from the original number who were waiting. So, in that case, the answer should be that 0 customers have waited the entire one-minute period, and we need to find some way to enter a formula in Excel to give us zero in this case and a positive number when the starting number of customers is greater than the number served in an interval. This can be done using the Excel MAX function.

In Figure below, the function in cell H7 for minute 1 is =MAX(B7-E7,0). Cell B7 contains the number of customers at the beginning of minute 1 and E7 is the number of customers served during minute 1. Therefore, B7 - E7 implements the calculation discussed above. However, using the MAX function in this way returns a 0 if B7 - E7 is negative because B7 - E7 < 0 and the MAX function displays the greatest of the two values within the parentheses that are separated by the comma. (Note in Figure below that column headings have been added in cells H7, I7, and J7 that we will be using.)

MAX Function for Calculating the Number of Customers Waiting Entire One-Minute Period

Column D in Figure below shows the total number of customers who are seeking service in a particular one-minute period. Because we have calculated the number of customers who are waiting the entire minute, and our estimation assumes that all other customers will wait one half of a minute, then the number of people waiting one half of a minute is the total number of customers minus those who have waited the entire period. Figure below shows this calculation to be D7 - H7 for minute 1, which has been entered as a formula in cell I7. Copying these two functions from cells H7 and I7 down through row 186 implements this function for the other one-minute periods.

Number of Customers Waiting One Half of a Minute

Cell B3 has been reserved to report the average customer waiting time, as noted in the caption in cell A3. The formula to implement the equation for the average customer waiting time from earlier in this subsection in cell B3 is as follows:

=(SUM(H7:H186)*1+SUM(I7:I186)*0.5)/SUM(C7:C186)

This formula:

  • sums all the customers who waited a full one-minute interval and multiples that number by 1 minute (the value 1 is unnecessary but is added here for clarity);
  • sums the number of customers whom we assumed waited one half of a minute and multiplies that number by one half of a minute;
  • uses parentheses so that those two previous values are added together to arrive at the total waiting time for all customers before the final computation is performed;
  • divides the total customer waiting time by the number of customers who arrived in the restaurant over the lunch period.

We also want to keep track of how much time a sales associate is idle. For example, a manager would want to know if a worker was not working much of the time, in which case the manager could find other work for the person to do. We will make an estimate of idle time in the same manner as we did for waiting time. The idea is to estimate what portion of each one-minute interval a worker was busy and what portion they were idle. To do this, we will divide the number of customers that were served by the number of customers that could have been served if the sales associate hadn’t run out of customers. For example, if the sales associate could have served four customers but could serve only two because of a lack of customers, we will say that the sales associate was busy for one half of a minute (2/4) and idle for one half of a minute 1 - 2/4 = 1/2. We have both of the numbers required for this calculation in the Simulation worksheet, and so this calculation is easy. The maximum number that could be served in the period is in column E, and the actual number served is in column F.

We need to revise this idle time formula in one case, which is when a sales associate can serve 0 customers at most. This represents circumstances when there is a “breakdown” that totally disrupts service, such as the cash register malfunctioning or waiting for a manager for approval of a refund or a special price. In this case, we will assume that the sales associate will be busy the entire time trying to resolve the issue, and so there is no idle time. We can use an Excel IF function to implement this logic as shown in Figure below for minute 1. This function is:

=IF(E7=0,0,1-F7/E7)

If the sales associate can serve no customers (if E7 is 0), then a 0 is displayed for idle time. Otherwise, the calculation 1-F7/E7 implements the formula discussed in the previous paragraph. Again, this formula should be copied down through row 186 to implement it for all 180 periods.

Calculating Idle Time

Cell B4 has been reserved to report the percentage of time that the sales associate is idle, as indicated by the caption in cell A4. This is calculated as the total minutes idle divided by the number of minutes in the simulation. The Excel formula to implement that logic in cell B4 is as follows:

=SUM(J7:J186)/180

This formula sums the number of minutes of idle time in column J over the entire simulation and divides that sum by the number of minutes in the simulation (180). This is the percentage of the three-hour period in which the sales associate was idle. The results of these statistics for one simulation observation for average customer wait time and percentage of idle time are shown in Figure below.

Sample Results for Average Wait Time and Percentage of Time Idle

Exploring the Simulation Study

Before undertaking this section it is a good idea for students to complete the review questions at the end of the lesson regarding enhancing the basic simulation model that was created in the previous section. In particular, creating a statistic for the maximum number of customers waiting at the ends of all the 180 one-minute intervals and graphing the number of waiting customers gives further insight into the simulation results.

With the simulation model complete, the customer service results of lunch periods on successive days can be observed by repeatedly striking the F9 key to recalculate the worksheet. This gives the same type of results as were discussed in the "Variation Makes Decisions Difficult" section of the "Variation in Capacity and Demand" lesson. In that subsection, the observations of the operation raised the question of whether it would be wise to have another sales associate serving customers. There are actually many ways that another worker might be incorporated into the operation. For example, a second cash register could be used, and each of the two sales associates at the counter could handle customers from start to finish. Another alternative would be to split the tasks associated with serving a customer and have one sales associate do some of those tasks and another worker do the remaining tasks. One way to subdivide the duties would be to have one worker stationed at the cash register to take orders and receive payment while the other worker gathered the required menu items, bagged them, and delivered them to the customer at the counter. Let’s consider the first alternative of having two sales associates, each of whom handles a customer from start to finish.

Preparing our simulation spreadsheet for this new scenario requires that we have a probability distribution for how many customers are served in each one-minute interval with two sales associates. We will not explain how the probability distribution in Figure below for two associates was calculated using the original probability distribution for one sales associate. However, making this calculation is one of the topics that would be taught in a probability and statistics course. So, if a student wants to learn how to use advanced simulation techniques, this is the type of course he or she should take.

To revise the simulation for the two-employee circumstance, we need to replace the data in the Service worksheet with the appropriate data for two sales associates. To do this, take the probability data from Figure below and create the corresponding data table for the VLOOKUP function as shown in Figure below, as we did originally for the number of customers who arrive. This table should be placed in the Service worksheet in the range from cell A5, in the upper left corner, to cell C21, in the lower right corner. The reference to the service data in the VLOOKUP function in column E of the Simulation worksheet needs to be changed to reflect the new data. The formula in cell E7 should be changed to

=VLOOKUP(RAND(),Service!$A$5:$C$21,3)

to reflect that the new data requires more rows. This formula can then be copied down to all 180 rows that are used in column E.

Service Probability Distribution with Two Sales Associates

Revised VLOOKUP Table for Two Sales Associates

One other change is required in the simulation model for two sales associates. That is, because there are now two sales associates, we must account for both sales associates being idle when no more customers remain to be served. That change is implemented by revising the formula in cell B4 to

=2*SUM(J7:J186)/360

Multiplying the numerator by 2 is appropriate because for every minute the sales counter is idle, there are two sales associates who have been idled for 1 minute each. Dividing by 360 is appropriate because there are 360 minutes of paid time for sales associates over the 180-minute lunch period when two sales associates are employed. Both spreadsheet simulation models, one for one sales associate and one for two sales associates, can now be run and the results compared. Both simulation models were run 10 times and the averages taken of the 10 observations of average customer wait time, sales associate idle time, and maximum number of customers in line. (Excel’s AVERAGE function was used to calculate these averages.) The results for one sales associate are shown in Figure below, and the results for two sales associates are shown in Figure below.

Simulation Results with One Sales Associate

Simulation Results with Two Sales Associates

A comparison of Figure above and Figure above shows that adding the second sales associate would have a dramatic effect on customer service, which is much improved. Customers wait approximately 1/6 as long when two sales associates are working, and the maximum number of customers in line is less than 1/4 as many. However, the cost of these improvements in customer service is getting another cash register, paying an additional sales associate, and increasing the amount of idle time that both sales associates have. With two sales associates, an average of 43.1% of the sales associates’ time is idle versus an average of 6.2% with one sales associate. A manager would need to make the decision whether the improvement in customer satisfaction was worth the cost. However, being able to run this simulation would give a manager the information so that he or she could make the decision without having to implement it and observe what happened as a result. If the customers’ experiences with how fast they were served were a factor in whether they visited the restaurant again, then the prospect of increased business should also be considered by the manager in his or her decision. Deciding whether the costs of an extra sales associate are less than or equal to the benefits of having an additional sales associate is an example of what is called a cost-benefit analysis in business.

Lesson Summary

  • An Excel simulation spreadsheet model is composed of these components:
    • A worksheet to generate customer arrivals.
    • A worksheet to generate numbers that represent how many customers sales associates can serve in each one-minute window.
    • A Simulation worksheet on which to perform the simulation.
    • Columns in the Simulation worksheet to accumulate statistics that describe what happened in the simulation in terms of customer waiting time and employee idle time.
    • Cell formulas to summarize waiting time and idle time statistics.
  • Simulation analysis can be done to evaluate improvements in customer service with staffing changes.

Review Questions

  1. Follow the directions in the "Creating a Random Observation from a Probability Distribution" section to start building a simulation spreadsheet model.
  2. Create a worksheet named "Service" in the same Excel workbook used for the question above that uses a VLOOKUP and RAND function to generate values for how many customers a sales associate could serve in one-minute windows if the sales associate can serve, on average, 2.6 customers per minute. Use the probability distribution from the Table below to develop an appropriate table of data to use a VLOOKUP function to generate a random number as was done in the Demand worksheet.
Service Probability Distribution
Possible Number of Customers Probability Cumulative Probability
0 7.4% 7.4%
1 19.3% 26.7%
2 25.1% 51.8%
3 21.8% 73.6%
4 14.1% 87.7%
5 7.4% 95.1%
6 3.2% 98.3%
7 1.2% 99.5%
8 0.5% 100.0%
  1. Follow the instructions in the "Creating a Spreadsheet Simulation Model" section to create your own simulation model.
  2. Why were absolute addresses needed in the VLOOKUP functions that were described in Figure above?
  3. Enhance the simulation spreadsheet as described in this lesson by doing the following:
    • Create a statistic at the top of the simulation worksheet for the maximum number of customers remaining after a one-minute interval over the 180-minute lunch period.
    • Replace the explicit range definitions in the formulas in cells B3 and B4 with range names that you create for the cells that contain the customer waiting time data and the sales associate idle time over the 180-minute lunch period.
    • Graph the number of customers waiting at the end of the one-minute intervals over the three-hour lunchtime.
    • Replace the 180 (the number of minutes in the simulation) in the denominator of cell B4 by creating a cell that designates the number of minutes in the simulation, and then references that cell in the formula in cell B4.
  4. Follow the instructions in the "Calculating Statistics for the Simulation Model" section to continue creation of your own simulation model.
  5. Use the simulation spreadsheet that you create to do an analysis of one versus two sales associates as we did in the "Exploring the Simulation Study" section to evaluate the effect of adding a second sales associate to serve customers. The average statistics that you calculate should be similar to those shown in the "Exploring the Simulation Study" section, but not exactly the same. A simulation analysis will give a different result every time!
    • Run each of the worksheets with the one-employee and the two-employee scenarios ten times by striking the F9 key 10 times for each worksheet. Accumulate statistics for average customer wait time, idle time, and maximum number of employees. Also, observe the graphs of the number of customers waiting at the end of each one-minute interval. You may save these graphs also by copying and pasting them onto another spreadsheet or alternative document.
    • How does each of these statistics vary between the two scenarios? Which statistics favor the one-employee scenario versus the two-employee scenario?

Points to Consider

  • All models and associated simulations are approximations of real-world systems. For example, the probability distributions that are used in simulations may not exactly replicate reality. Also, each time a simulation is run, the sequence of random numbers that generates demand and service rates is different, so each simulation run gives us a good idea of how the system works, but not exactly how it will behave. Finally, simulation models do not include all the detail that we would find in reality.
  • Simplifying simulation models in this way is necessary because including some details does not change the simulation results significantly and increases the time required by the computer to run the simulation. The simulation created in this lesson ignores how kitchen operations affect how quickly customers are served. If the kitchen staff cannot keep up with the orders, then the sales associates will need to wait for the kitchen before customers can be served. A more comprehensive simulation would have incorporated this aspect of capacity, and perhaps others.
  • The decision of whether to include each facet of capacity in a simulation study depends, first, on whether it is anticipated that that aspect of capacity substantially affects customer service. If an aspect of capacity can be ignored in a simulation model, then the analysis is simplified, and the simulation will run faster.
  • In the simulation that was constructed in this lesson, we assumed that we knew the probability distributions for customer arrivals and service. These data need to be collected from the operation when a simulation study is done on an actual operation. The basic method would be to collect statistics on customer arrivals and sales associates to determine the percentage of times that a certain number of customers arrived or were served, respectively. These are essentially the probabilities. However, some technical issues arise in collecting such data, such as determining if the data from the past is representative of what future demand will be like. If this is a new restaurant, for example, data from the first two weeks when everybody is trying out the restaurant may not reflect the level of demand six months later. Another technical issue arises, even when future demand is like past demand, which is whether enough data has been collected so that it accurately reflects the true probabilities.
  • When we study statistics, we find that the more data we collect, the more likely it is to be representative of the actual situation. Remember that customer demand is varying all the time, and if we collect data for only one day that day might have had unusually high or unusually low demand.
  • Courses in statistics and probability give a student the tools to evaluate these questions, and they are, therefore, essential to being able to create accurate simulation models. If somebody wants to learn more about simulation and use it effectively, then he or she should be encouraged to take probability and statistics courses.

Image Attributions

Description

Categories:

Grades:

Date Created:

Aug 06, 2012

Last Modified:

Sep 04, 2014
Files can only be attached to the latest version of None

Reviews

Please wait...
Please wait...
Image Detail
Sizes: Medium | Original
 
CK.SCI.ENG.SE.1.Modeling-and-Simulation-for-High-School-Teachers.6.4

Original text