SpreadsheetBased Decision Support Systems Chapter 20 Simulation ReVisited

Spreadsheet-Based Decision Support Systems Chapter 20: Simulation Re-Visited Prof. Name Position University Name name@email. com (123) 456 -7890

Overview v v v 20. 1 Introduction 20. 2 Review of Chapter 9 20. 3 Simulation using Object-Oriented API in Risk Solver Platform 20. 4 Applications 20. 5 Summary 2

Introduction v Creating and modifying a simulation problem using Object-Oriented API v Performing dynamic simulation runs using VBA v Creating simulation animation using VBA v Dynamic analysis using histograms and VBA v A full dynamic simulation application with animation 3

Review of Chapter 9 v Defining Simulation v Simulation Using Risk Solver Platform 4

Review of Chapter 9 v In Chapter 9 we defined simulation and discussed how to create simulation models in Excel using Risk Solver Platform. v In this chapter we will show some parallel functionality that can be accomplished using the Object-Oriented API in VBA. v First let us review how simulation is defined and how to create a simulation model in Excel. 5

Defining Simulation v Simulation is a modeling tool which we use to imitate a real-world process or system in order to understand its behavior. v We use simulation to evaluate multiple strategies and predict the future performance of the system. v Simulation is a useful tool in that it helps us make observations from trial and error without the cost of materials, labor and time that would be necessary to observe the same results on the original process or system. v Simulation differs from optimization in that instead of seeking to optimize an objective function value, it simulates the behavior of a system to assess its performance under several scenarios. 6

Simulation Using Risk Solver Platform v Risk Solver Platform is a useful tool to build simulation models in Excel. v The first step in building a simulation model using Risk Solver Platform is to setup our Excel worksheet. v In a simulation model there is at least one problem input that is stochastic in nature. v We model stochasticity by generating random numbers from a particular distribution function. v We can generate random numbers from a given distribution using the RAND function of Excel and Psi Distribution functions of Risk Solver Platform. 7

Building a Simulation Model To build a simulation model using Risk Solver platform: v Define Uncertain Variable (random problem input): v Define Uncertain Function (random problem output): - v These are ranges of cells that contain Psi. Distribution functions. These cell refer problem input cells. To the formula in these cells type ‘+ Psi. Output()’ Define Statistic Function: provide summary statistics over all simulation runs: - ‘= Psi. Mean()’, ‘=Psi. Std. Dev()’, ‘=Psi. Min()’, ‘=Psi. Max()’, etc. Run the simulation by clicking on: Risk Solver Platform > Solve Action > Simulate command. 8

Simulation Using Risk Solver Platform (cont’d) v The Model tab of the of Risk Solver’s Task Pane lists – All problem inputs, which are referred to as Uncertain Variables. – All problem outputs which are referred to as Uncertain Functions. – Statistic Functions v We set the value of Trials per Simulation property at the Platform tab of the Task Pane and run the simulation. v Risk Solver Platform generates a wide variety of simulation reports. – We double click on an output cell to activate the Simulations Results dialog box which summarizes the results of the simulation. – Risk Solver Platform provides tools to perform sensitivity analysis with respect to different problem parameters. 9

Simulation using Object-Oriented API in Risk Solver Platform v Creating a Simulation Problem v Making Runs and Collecting Data v Animation v Analysis 10

Simulation using Object-Oriented API in Risk Solver Platform v The Risk Solver Platform tab on the Ribbon provides a user friendly interface to build analyze advanced Monte Carlo simulation models. v It is recommended to initially build your simulation model using Risk Solver Platform in Excel, and then use Object-Oriented API in VBA to: – Control solver engine parameters – Perform a simulation – Collect simulation statistics v We will also discuss how to use VBA to add animation to our simulations. 11

Creating a Simulation Model v Use Object-Oriented API in VBA to create an instance of the Problem object. – Check that Risk Solver Platform xx Type Library is selected From Tools > References list on the VBE. v The Problem object represents the whole simulation problem. – Dim My. Prob As New RSP. Problem v We use the Init method to initialize an instance of the Problem object using the simulation model in the current workbook. – My. Prob. Init Active. Workbook v Upon initializing our problem, a collection of Variable objects and a collection of Function objects are created automatically. 12

Creating a Simulation Model (cont’d) v Add an uncertain variable to an existing model. – Range(“A 11”). Formula = “=Psi. Exponential(10)” v Add an uncertain function to an existing model. – Range(“B 11”). Formula = Range(“B 11”). Formula + “+Psi. Output()” v Note: – Each Variable object corresponds to a range of one or more cells that contain Psi Distribution functions. – Each Function object corresponds to an output (formula) cell which contains an Excel function that manipulates problem variables, and references the Psi. Output() function. 13

Setting Engine Parameters and Running the Simulation v We present a procedure that can be used to cerate and initialize a problem, set Solver and Engine parameters, and simulate the problem. Sub Simulate() Dim My. Prob as New RSP. Problem My. Prob. Init Active. Workbook My. Prob. Solver. Num. Trials = 5000 My. Prob. Engine. Params("Sampling. Method") = 2 My. Prob. Engine. Params("Random. Seed") = 12 My. Prob. Solver. Simulate Set My. Prob = Nothing End Sub 14

Collecting Simulation Results Sub Simulate() Dim My. Prob as New RSP. Problem, i as Integer, j as Integer My. Prob. Init Active. Workbook My. Prob. Solver. Simulate For i = 0 To My. Prob. Functions. Count -1 For j = 0 To My. Prob. Functions(i). Size -1 Msg. Box My. Prob. Functions(i). Statistics. Min(j) Msg. Box My. Prob. Functions(i). Statistics. Mean(j) Msg. Box My. Prob. Functions(i). Statistics. Max(j) Msg. Box My. Prob. Functions(i). Statistics. Std. Dev(j) Next j Next i End Sub 15

Collecting Simulation Results (cont’d) v All. Trials is a property of Variable and Function objects. – It is used to access the value that the variable/function cells take during each simulation trial. – This property yields a Double. Matrix object that takes two subscripts: one indicating the location of the variable, and the other indicating the simulation trial. – The following lines of code displays the value that a random variable takes during the i-th simulation trial. § My. Prob. Variables(0). All. Trials(1, i-1) – The following lines of code display the value that a random Function takes during the i-th simulation trial. § My. Prob. Functions(1). All. Trials(2, i-1) 16

Collecting Simulation Results (cont’d) v v Instead of the summary statistics, we often want to know exactly the value of an output cell during each simulation trial. We can use the All. Trials property to access the value that output cells take during each simulation trial. For i = 0 To My. Prob. Functions. Count -1 For j = 0 To My. Prob. Functions(i). Size -1 For k = 0 to My. Prob. Solver. Num. Trials – 1 Msg. Box My. Prob. Functions(i). All. Trials(j, k) If (i = 1) then Range(“D 1”). Offset(k, j). Value= My. Prob. Functions(i). All. Trials(j, k) End if Next k Next j Next i 17

Animation v The two VBA methods that we often use to create a simulation program are the Screen. Updating and Wait methods of the Application object. v The Screen. Updating method should be set to False before the simulation runs begin. – This reduces screen flickering while the program runs and increase efficiency (decrease running time) if the screen is not updated after every action in the code. v It should be set to True when the runs have been completed. v The Wait method can be used to create some delays in the simulation. – For example, if we are simulating a production line, we may have several parts being received and shipped from different work stations. We use the Wait method to ensure that the user sees a delay between these actions. 18

An Example of Animation v We are simulating a production line. This line has several parts being received and shipped from different work stations. Each work station has a different distribution for processing time. v We plan to animate the movement of parts in and out of Work Station 1. We use the Psi. Log. Normal() function to randomly generate the time it takes for a part to be processed in this workstation. 19

An Example of Animation (cont’d) v We collect the processing time at Work Station 1 using All. Trials property of the Variable object. Re. Dim Work. Station 1(Batch. Size) As Double For i = 0 To Batch. Size - 1 Work. Station 1(i) = My. Prob. Variables(0). All. Trials(i, 3) Next i v We then create a cumulative time array using the following VBA code. Re. Dim Cum. Work 1(Batch. Size) As Double Cum. Work 1 (0) = 0 For i = 1 To Batch. Size - 1 Cum. Work 1(i) = Work. Station 1(i) + Work. Station 1(i - 1) Next i 20

An Example of Animation (cont’d) v Now we can run a loop to show that a product leaves Work Station 1 at each time value. v To do this, we may highlight some cell which changes position, or disappears and reappears, every time this action occurs. v To create this action we would pick some range to highlight (by changing the color with the Interior property) and each time we loop through a run, we un-highlight this cell and highlight the next cell (using the Offset property). v To ensure that the user sees a delay between these actions, we use the Wait method. 21

An Example of Animation (cont’d) For i = 0 To Batch. Size - 1 Application. Screen. Updating = False Range(“Time”). Value = Cum. Work 1(i) Range(“A 1”). Offset(i, 0). Interior. Color. Index = 0 Range(“A 1”). Offset(i+1, 0). Interior. Color. Index = 5 Application. Wait(Now + Time. Value(“ 0: 0: 03”)) Application. Screen. Updating = True Next i v Running this code will create an animated simulation which appears to move a product from one cell to the next at each iteration. v This idea can be modified to create an animation that better reflects any particular system. 22

Analysis v The motivation for using simulation is to perform some analysis on a system. v There are several ways to analyze the results of a simulation. v For the output produced, a graph can be created, the maximum or minimum can be found, etc. v We can use Chart objects to accomplish these analsyis. v We can also use the Analysis Toolpack with features such as Histograms. 23

Figure 20. 1 v Use properties and methods of Statistics objects to collect statistics about simulation model outputs. 24

Histograms v Histograms calculate the number of occurrences of values in a particular interval. v There are four main parts to creating a histogram. – Input = range of data on the worksheet. – Bins = the intervals into which values can be counted; they can be defined by the user or can be evenly distributed among the data. – Output = range where the frequency calculations for each bin will be printed. – Charts options = simple chart, cumulative percentages for each bin value, and/or a Pareto organization of the chart. 25

Histogram (cont’d) 26

Histogram (cont’d) v To create the histogram using VBA, we will use the Application object and the Run method. – The Run method is used to specify the Add-In file from which we will be running the analysis. – The arguments for this method include all of the parameters we have previously seen. Application. Run “ATPVBAEN. XLAM!Histogram”, Input. Range, Output. Range, Bin. Range, Labels, Pareto, Cumulative, Chart v Note that, if we run this code multiple times with the Chart parameter set to True, we will have multiple charts created on the spreadsheet. v For better code efficiency, we recommend creating a histogram in Excel first (as we have seen with some Chart applications) and then modifying your code to have the chart option parameters all set to False. 27

Histogram (cont’d) v You will also see a message warning you that some data will be overwritten if you repeatedly print the output of the histogram to the same cell. v We therefore recommend clearing this output range of cells before calling the histogram function each time. v Other analysis from the Analysis Toolpack may be useful to your application. v To discover the particular code and parameters for each analysis tool, record a macro first in Excel (most analysis tools will still run an extension of the ATPVBAEN. XLAM file). 28

Applications v Single Server Queuing Problem Revisited 29

Description v The purpose of building this simulation model is to mimic the behavior of an ATM system to determine the expected customer waiting time, and the utilization of the ATM. v The ATM system consists of the ATM and its queue. v Upon the arrival in the system, if the queue is empty, the customer is served at the ATM; otherwise, the customer waits in the queue. v The interarrival time of customers in the system and the corresponding service time are randomly generated using Psi functions. v Interarrival and service times are used to calculate the waiting time in queue and time in the system for each customer. 30

VBA Simulation v VBA will allow us to perform the simulation and analyze the results. v In addition, VBA is used to create a user friendly interface for this problem and to animate the queue. 31

Figure 20. 2 v The spreadsheet setup for the ATM simulation model. 32

Figure 20. 3 v The Simulation Input form is used to get user’s input about the total number of customers to simulate in each simulation trial, the mean interarrival time, and minimum, maximum and average service time at the ATM. v The input box is used to get user’s input about the total number of simulation trials. 33

Figure 20. 4 v We animate the activities at the ATM and at the queue to visualize and better understand system’s behavior. – The yellow cell represents the customer being served. Customers arrive at a queue line; if the server is “Idle, ” then the customer begins service; if the server is “Busy, ” the customers stay in the queue until the server becomes “Idle” again. 34

Figure 20. 5 v In the end of the simulation, the user is presented a number of related statistics, such as, the expected mean waiting time in the queue, standard deviation of the mean waiting time, maximum waiting time, etc. v We use the mean waiting time from all simulation trials to create a histogram. 35

Code v The Main procedure is used to – – – v Call the user input frame, and subroutines that clear previous values. Create the simulation model in Excel. Simulate the model using Risk Solver Platform. Present simulation results. Animate the model. The Clear. All procedure used to clear: – The data in the Excel spreadsheet model. – The results of the simulation. – The data used to create the histogram. 36

Figure 20. 6 37

Figure 20. 7 v We use the form frm. Input to get user’s input about the total number of customers, customer mean interarrival time in the system, and minimum, average and maximum service time at the ATM. v We assign to public variables the values entered by the user. 38

Figure 20. 8 v The Excel. Sim. Model uses user’s input (already stored in public variables) to complete the spreadsheet Simulation model for this problem. v We are ready now to simulate the model using Simulate. SS procedure. 39

Figure 20. 9 v The Simulation. Results procedure prints the results of the simulation in Excel, and creates a histogram of customer waiting time in the queue. 40

Figure 20. 10 v This timeline table is generated by the VBA code in Figure 20. 11. 41

Figure 20. 11 v The Animate procedure imitates the movement of customers through the queue to the server. 42

Figure 20. 12 v This part of the Animate procedure browses through the timeline table. 43

Application Conclusion v The application is now complete. v Assigning the Main procedure to the RUN SIMULATION button. We are ready now to use this application. 44

Summary v v v v v Simulation is a modeling tool which we use to imitate a real-world process in order to understand system behavior. The three most important parts of a simulation model created using Risk Solver Platform are the: uncertain variables, uncertain functions, and statistic functions. We use the RAND function of Excel and the galleries of Psi Distribution functions provided by Risk Solver Platform to generate the input data for a simulation model. We use the Simulate method of Solver object to run a simulation using Object. Oriented API in VBA. When building a simulation model, we often need to determine if the data and the results of the specified calculations will be stored in arrays or in a worksheet. We can use the Wait method to create some animation of the simulation. We can use the Analysis Toolpack to perform many types of data analysis, including histograms. Histograms calculate the number of occurrences of values in a particular interval. We use the Application object and the Run method in VBA to specify the Add-In file from which we will be running the analysis. 45

Additional Links v (place links here) 46