DECISION MODELING WITH MICROSOFT EXCEL

1. DISCRETE EVENT SIMULATION DECISION MODELING WITH MICROSOFT EXCEL Chapter 10 Part 1 Copyright 2001 Prentice Hall

2. DISCRETE EVENT SIMULATION INTRODUCTION Typically, simulation refers to a specific class of dynamic models involving the detailed observation of a complex probabilistic system over time. Continuous simulation models involve aggregate variables that change more or less continuously with time (such as those models in Chapter 9). Discrete event simulation is applied whenever individual items are tracked and in which abrupt or nonsmooth changes in the timing of events is the norm.

3. Discrete event simulation models can be used to delve into the fine details of complex systems with many interactions. However, discrete event simulation models can become extremely complex to build and analyze. Discrete event simulation models are most commonly used to create detailed operational systems representing demands among activities requiring scarce resources over time.

4. EXAMPLE 1A SIMULATION MODEL IN EXCEL A single server queuing situation will be used to illustrate a discrete event simulation. We will illustrate the building of such models using a non-spreadsheet simulation package called Extend. Extend can be used for both continuous simulation and discrete event simulation. We will investigate the relative efficiency of an airport car rental business (Hervis) by modeling the movement of customers through the rental office.

5. Customers arrive in an airport van and queue up, first come – first serve, for service at the rental counter. filling out the rental forms choosing the rental options receiving some driving instructions etc. Eventually, a given customer moves to the head of the queue and, when the rental clerk is free, will receive service that consists of Finally, customers depart, collect their rental car and drive away.

6. Consider the following data giving the relative frequency distribution of customer arrivals in any given 5-minute block of time throughout a typical 24-hour day. Let’s simulate a week of 24-hour operations and segment time into 5-minute blocks. We assume that it will always take exactly 5 minutes to serve a waiting customer.

7. Because 7*24*60/5 = 2016 time blocks (exceeding the 256-column limitation in Excel), the time blocks will be modeled in rows. To build the Hervis model in Excel, first label the time blocks by using the Fill Series option from the Edit menu.

8. Next, create a probability distribution table: Cell K8 computes the expected number of customer arrivals.

9. The computer will be driven by randomly drawn probabilities, which in turn will determine the number of customers arriving in any 5-minute time block. The RAND() function will draw a random fraction greater than or equal to 0 and less than 1, one for each of the 2016 time blocks. Set the Tools – Options – Calculation option to Manual to avoid drawing new random numbers each time the worksheet is changed.

10. Place the =RAND() formula in cell B5 and copy it down to cell B2020 to create all 2016 random fractions. =K4 =M5 + K5 =M6 + K6 =VLOOKUP(B5,$M$4:$N$7,2) Next, create a modified probability table based on the =RAND() formulas (the inputs). The number of customer arrivals, as outputs, are computed by =VLOOKUP() formulas.

11. Column D formulas compute the number of customers in line and being served. =C5 + F4 =D5 + E5 =MIN(D5,$C$2) =MAX(D$5:D$2020) =SUM(D$5:D$2020) =AVERAGE(D$5:D$2020) Column F computes the number of customers remaining unserved at the end of the 5-minute block. Column E formulas compute the number of customers receiving rental counter service as being the minimum of the number in line and the number of rental counter stations in C2.

12. The initial results show that on the average, ½ customer arrives in each 5-min. block (or 1 customer every 10 minutes). It always takes only one 5-min. interval to serve a customer, thus, the rental counter is busy only ½ the time on average. However, during peak times, you could have 9 customers awaiting service, leading to a delay of 45 minutes for the 9th customer. To alleviate this wait, we could add another rental station. We can use this model to evaluate the effect of adding another rental station, thus allowing up to two waiting customers to receiver service simultaneously.

13. Below is the revised model for 2 rental stations. The revised statistics indicate that the maximum number of customers delayed falls from an average of .499 to an average of .025.

14. EXAMPLE 2A SIMULATION MODEL IN EXTEND The use of Excel for discrete event simulation suffers from two primary shortcomings: 1. For reasonably sized models, the resulting Excel spreadsheet quickly fills up with cells occupied with formulas. 2. Discrete event simulation models in Excel do not scale well.

15. Where Excel falls short in handling discrete even simulation models, Extend has the modeling capability to: 1. Scale to finer time grids and longer planning horizons. 2. Compact the model representation to avoid generating large spreadsheets that are cumbersome to manipulate and debug. 3. Easily manipulate and generate results. 4. Develop an environment that embodies the latest in graphic user interfaces to facilitate easy model building and self-documentation.

16. Extend’s models for discrete event simulation are object oriented and are built using predefined building blocks. A block is Extend’s object for specifying an action, process, accumulation, statistical summary, or other object for creating the simulation. The blocks are linked together by drawing lines to form a network of interrelated activities. Items (e.g., customers) flow through the connected network of blocks while Extend gathers statistics on their movements.

17. Links connect blocks: These links provide the paths that allow items to flow through the model’s blocks. These links appear as small connectors on the edge of each block. These links pass data and parameters about items between blocks. These links appear as connectors on each parameter values and for reviewing its results after a simulation run. two accommodate item input and output, and two accommodate value input and output.

18. Each block in Extend has a dialog window for specifying parameter values and for reviewing its results after a simulation run. Here is an Extend Activity, Delay block typically used to hold an item for an amount of time and then release it to the next block.

19. BUILDING THE HERVIS SIMULATION MODEL Open the Simulation Setup dialog by selecting Simulation Setup from the Run menu. Here, you can specify how the simulation will run and for how long. For most purposes, start with the default start time of 0. The Number of runs option can be left at 1.

20. Now, because Hervis models the rental car operations minute – by – minute for a week, click on the Global time units button and select the minutes option. Hervis will run the simulation for a week of simulation time (24*7*60 = 10,080 minutes). Enter 10080 into the End simulation at time field and click OK.

21. Choose New Model from Extend’s File menu to open a new model window. To add a block to the model window: 1. Open the library containing Extend’s predefined blocks, if necessary.

22. 2. Add the desired block to the model by selecting it from the library. 3. Click-drag to move the block to its desired position in the model window.

23. 4. Connect its input and output connectors to other blocks. In order to copy a predefined Extend block into a model, the library in which that block resides must be open. For the Hervis modeling in this chapter, open the Generic, Discrete Event, Plotter,and Stats libraries.

24. To open the Discrete Event library, choose Open Library … from Extend’s Library menu. Locate the Libs subdirectory and select Discrete Event library (DE.lix) and click OK. Now, open the Generic (GENERIC.lix), Plotter (PLOTTER.lix) and Stats (STATS.lrx) libraries.

25. To add a block to a model, click on the Library menu and select the name of the library that holds the desired block. A hierarchical menu of the different types of blocks in the library will appear. To the right of this menu a list of the names of the blocks will appear. Selecting the desired block will put a copy of the block in the Extend window.

26. For discrete event simulation modeling, Extend always requires an Executive block from the Discrete Event library to be placed as the leftmost block in the model window. This block, with an icon of a wall clock, determines its stopping conditions and handles the details of event scheduling. Normally, there is no connection to the Executive block, and once placed, can be ignored.

27. The Hervis model begins with the arrival of an airport van carrying rental-car customers. This is represented by Extend’s Generator block. Add a Generator block from the Generators submenu of the Discrete Event library.

28. Double-click on the block to open the settings dialog. Select a Constant distribution with a parameter setting of 5 and minutes as the Time Units. Also, label the block Customers.

29. Each time a van arrives, it contains a variable number of customers (a batch). By default, Extend’s Generator block creates a batch of size one. This can be overridden by providing another number via the V value input connector. In this case, we want the batch size to be a random number of customers between 0 and 3.

30. Specify the random number of customers in the Input Random Number block. Go to the Inputs/Outputs menu in the GENERIC.lix library and add the Input Random Number block.

31. Double click to open the dialog. Select Empirical Table and Discrete and enter the data shown in the previous table. Label the block # of Customers and click OK.

32. Add a text box by double-clicking on the white background. Type Customers Come from Airport Van in the resulting edit field. Blocks are hooked together through their connectors by connection lines. To connect the blocks, position the mouse cursor over one of the connectors. When the cursor changes to a pen, click-drag it to the other tab.

33. Now, connect the output value connector to the V input connector. When you release the pen/cursor, a thin line will appear, connecting the two blocks. This allows information to flow from the output of the Input Random Number block to the V input connector of the Generator block. This connection will generate an arrival of a batch of items (customers) varying from 0 to 3 in size according to the previous probabilities.

34. Now, in order to have the batch of customers queue First in, First Out (FIFO) in front of the rental station, add a Queue, FIFO block from the Queues menu of the DE.lix library.

35. Use the cursor-pen as before and connect the input connector from the Queue, FIFO block to the output connector of the Generator block. This allows the customers in the batch to join the queue. Double-click on the block to open it. Enter a label for the block.

36. Note that the connecting lines over which items (customers) flow among blocks are hollow while the connection lines among blocks providing information (values) are thin solid lines. Also note that items (customers) produced by a Generator block are pushed out immediately and must have someplace to go in the model. This usually requires a direct connection from a Generator block to one of Extend’s Queue or Resource blocks. Otherwise the Generator block may lose items, giving incorrect results.

37. In the original Hervis model, the rental clerk will delay each customer for 5 minutes to complete the car rental transaction. You can introduce this delay in the Activity, Delay block. However, this block only allows for one item. Therefore, use the Activity, Multiple block from the Activities menu of the DE.lix library.

38. Items leave the simulation model via Extend’s Exit block found on the Routing menu of the DE.lix library.

39. Another useful block is the Plotter, Discrete Event block found in the PLOTTER.lix library. This block captures data and plots up to four variables during the simulation run.

40. The Utilization of the Rental Station and the customer waiting time, W, is captured from the Queue, FIFO block. The variables to be collected and plotted are determined by connecting the Plotter, Discrete Event block’s connectors to the appropriate value connectors of other blocks in the model.

41. RUNNING THE MODEL Click on the Run - Run Simulation pull-down menu or on the Run Simulation button to begin the simulation. (Note that results may differ due to the randomness in the simulation data for customer batch size.) When the simulation begins, Extend will: 1. Display a Status bar at the bottom of the screen based on the designated settings.

42. 2. Open a window for the plotter to dynamically report its variables, server utilization and waiting time. Note how the wait time in line jumps in discrete units of 5 minutes. The max. wait was 35 min. The avg. utilization was 50%. This implies that the station will be busy ½ the time on avg. The avg. utilization is erratic in early time periods but settles to its long run average value.

43. To manipulate the graph, click on the Trace Properties icon at the top of the graph. This will bring up the Trace Lines Properties window. Y1/Y2 Variable Label Style of Line Show Trace Variable Color Line Symbol Line Weight Line Pattern Format Each of the plotter variables occupies two rows: the properties of the variable’s time stamp and the properties of the variable.

44. Note that a variable’s time stamp can have only its numerical format changed. Click on the model’s Queue, FIFO block and Activity, Multiple block to open their respective dialog windows. Next, click on the Results tab to reveal the simulation run statistics for each block.

45. To see what the effect of adding an additional sales clerk, double-click on the Activity, Multiple block and change the Maximum Number in Activity from 1 to 2. Now, rerun the simulation to produce the following results.

46. The average utilization of 2 servers tends to be 25%.

47. The max. queue length drops to 4 with the max waiting time of 5 min.

48. EXAMPLE 3 - HERVIS CAR RENTAL MODEL EXPANDED Now, Hervis knows the airport vans experience traffic delays and don’t always arrive exactly every 5 minutes. To model this, modify the original Extend model to allow for a range of interarrival times for airport vans from 3 minutes to 7 minutes, with the most likely interarrival time being 5 minutes.

49. Note that a triangular distribution is used for simplicity. So far, we have assumed that it always takes exactly 5 minutes for the clerks to complete the car rental contract. To add some variability to this, add another Input Random Number block.

50. Go to the Library pull-down menu and choose Generic.lix – Inputs/Outputs – Input Random Number block.