Chapter 9: Simulation

Spreadsheet-Based Decision Support Systems

Prof. Name name@email.comPosition (123) 456-7890University Name

2

Overview

9.1 Introduction 9.2 Defining Simulation 9.3 What-If Analysis Tools of Excel 9.4 Simulation Using Risk Solver Platform 9.5 Applications 9.6 Summary

3

Introduction

What is simulation and how it is useful?

Perform what-if analysis using Data Tables and the Scenario Manager.

Build advanced simulation models using: input analysis, output analysis, and random number generating tools of Risk Solver Platform.

Three examples of simulation models.

4

Simulation

Simulation is a modeling tool which is used to imitate a real-world process in order to understand system behavior.

The true behavior of a system is estimated using distributions.

Random numbers from these distributions can be generated to evaluate multiple strategies and predict future performance.

In what-if analysis we change the value of an uncertain problem input in order to observe its impact on some problem outputs.

Excel provides two simple what-if-analysis tools: Scenario Manager and Data Tables.

5

What-If Analysis

Data Tables

Scenario Manager

6

Scenario Manager

The Scenario Manager allows you to vary up to 32 input cells for various values, or scenarios, and observe the results of several output cells.

The Scenario Manager will create a Scenario Report which shows the resulting output values for each scenario of input values.

Preparation requires an initial list of inputs or outputs. Appropriate values and formulas should be filled in these cells.

Click on: Data > Data Tools > What-If Analysis command. – From the drop-down list that appears, select Scenario Manager. – Click on: Add command in the Scenario Manager dialog box to add a new

scenario.

7

Figure 9.1

We are interested in the company’s after tax profits for each of the five years as well as their total NPV.

We consider three different scenarios for year 1 sales, sales growth, and year 1 price.

8

Figures 9.2 and 9.3

Add a new scenario. Cell references should be to the list of inputs created in the

spreadsheet preparation. Next, specify the values these inputs should take for the scenario we

are creating.

9

Figure 9.4

Repeat the same steps to create the other scenarios. Scenario manager dialog box illustrates the list of scenarios that are

created

10

Figure 9.5

Click Summary to create the Scenario Report.

The Scenario Summary dialog box prompts us to select the outputs we want to observe for the various scenarios of inputs.

11

Figure 9.6

The Scenario Report exhibits the result cells, for each scenario as well as the current values from the initial tables

12

Data Tables

Data Tables are used to determine how some outputs vary in response to changes in input.

Data Tables use the spreadsheet to refer to cells which may contain formulas or functions for some output and input of some problem.

There are two types of Data Tables: – one-way data tables: determine how changing one input will change any

number of outputs

– two-way data tables: determine how changing two inputs would change a single output

13

Data Tables (cont’d)

Create a list of problem inputs and outputs.

Click on: Data > Data Tools > What-if Analysis command.

From the drop-down menu that appears, select Data Table.

If we are creating a one-way data table, the column input cell will be the only reference we give.

If we are creating a two-way data table, we will reference both a row and column input since two inputs are varying.

14

Figure 9.7

Example: We are given a list of inputs and outputs for ticket sales.

The Total Profit is calculated by finding the unit profit (price minus cost per ticket) and multiplying this value by the number of salespersons and the average number of tickets sold per person.

15

Figures 9.8 and 9.9

The first data table we want to create will show the different profit values as we vary the price per ticket. This will be a one-way data table.

16

Figures 9.10 and 9.11

Now suppose we are curious to see how the combination of price per ticket and number of salespersons affects our total profit; this will now be a two-way data table.

17

Risk Solver Platform provides a full-featured Monte Carlo simulation tool.

Risk Solver Platform makes it very easy to perform a large number of simulation trials.

The results from these trials are summarized using graphs and tables.

It provides tools for – Generating Random Numbers within Distributions

– Fitting a Distribution to a Set of Data

– Analyzing Simulation Results

Simulation Using Risk Solver Platform

18

1. Generating Random Numbers within Distributions

The RAND function is used to generate random numbers in Excel.

The RAND function does not have any parameters; it returns a randomly chosen fractional number between 0 and 1. =RAND()

You can manipulate this RAND value if you want to generate values outside the interval between 0 and 1.=RAND()*(UB-LB) + LB

19

Figure 9.12

To generate heights, widths, and depths to calculate some probable packaging volumes, we create random numbers between 1 and 10.=RAND()*9 + 1

20

Risk Solver Platform offers galleries of Psi functions to generate random numbers.– Click on:

Risk Solver Platform > Simulation Model > Distributions command.

1. Generating Random Numbers within Distributions

21

Double-click on a cell to activate this dialog box which:– Provides graphical representations

of the corresponding density function and cumulative distribution function.

– Shows distribution percentiles and other statistics.

– The default values of mean and standard deviations can be modified.

1. Generating Random Numbers within Distributions

22

Use PsiDiscrete() function to generate numbers from a distribution not listed in Risk Solver Platform.– PsiDiscrete(values, weights)

The values and weights of the distribution can be modified using the corresponding windows on the bottom of the dialog box.

1. Generating Random Numbers within Distributions

23

2. Fitting a Distribution to a Set of Data

Identify the function that describes best the behavior of a problem input. – Gather historical data about the problem input.

– Analyze the data to identify its distribution using Risk Solver Platform.

On Risk Solver Platform:– Click on: Risk Solver Platform > Tools > Fit command.

– Type the location of the sample data on the Fit Options dialog box.

– Select one of the distribution types: Continuous or Discrete.

– Check Chi-Square statistics Goodness of Fit Test checkbox.

– Click on: Fit command.

The Risk Solver Platform fits a number of distributions to the sample data; ranks them based on goodness of fit criteria; and displays the best fitting distributions.

24

Figure 9.16

We use historical data to identify the distribution of customer interarrival time at the ATM machine.

25

Figure 9.17

Based on the results displayed, exponential is the distribution that best fits our data.

26

3. Analyzing Simulation Results

Risk Solver Platform offers a number of tools to analyze the results of a simulation model.

Simulation Report:– Gives general simulation information, such as, the number of simulation runs,

trials per simulation, random number generator used, sampling method, etc.

– Provides summary information about the uncertain variables and functions used in the model.

Parameters and Sensitivity Analysis reports:– The goal of sensitivity analysis is to identify input parameters that greatly

impact the outputs of a model.

27

Figure 9.18 To activate the simulation report:

– Select Simulation from Risk Solver Platform > Analysis > Reports drop-down menu on the Ribbon.

– Select Simulation from the flyout menu that appears.

28

Figure 9.19 Use the Tornado Sensitivity Chart to identify problem inputs that greatly

impact problem outputs.

– Click on: Risk Solver Platform > Parameters > Parameters command.

– From the drop down menu select Identify.

– The Tornado Sensitivity Chart appears.

29

Figure 9.20 We may be interested to see the changes on the output (total profit) when

a problem input (sales price) varies between some lower and upper bounds. – Select: Risk Solver Platform > Analysis > Reports drop-down menu.

– Select: Simulation > Parameter Analysis report.

30

Applications

News Vendor Problem

A Single Server Queuing Problem

Retirement Planning Problem

31

News Vendor Problem

A bookstore must determine how many 2012 comic calendars to order in September of 2011.

It costs $2.30 to order each calendar. A calendar sells for $4.70. After January 1, 2012, any unsold calendars are returned to the supplier. The salvage value is $0.75 per calendar.

It is estimated that the number of calendars demanded is governed by the following probabilities:– Demand: 150, 200, 250– Probability: 0.3, 0.3, 0.4

How many calendars should the company order?

32

Figure 9.21 The spreadsheet is setup by listing problem inputs and outputs. Inputs: ordering cost, sales price, salvage value, and demands with

corresponding probabilities. Output: total profit which depends on the number of calendars sold and

the number ordered.

33

Simulation Model

Build the simulation model using Risk Solver platform: Define Uncertain Variable (random problem input): Demand

- Use PsiDiscrete(E4:E6,F4:F6) function to randomly generate demand

Define Uncertain Function (random problem output): Profit- Cell I10: ‘= G10 + H10 - F10 + PsiOutput()’

Define Statistic Function: provide summary statistics over all simulation runs:

– Cell C12: ‘= PsiMean(I10)’ Cell C13: ‘=PsiStdDev(I10)’

Click on: Risk Solver Platform > Solve Action > Simulate command.

34

Figure 9.22

Simulation results when the quantity of calendars ordered is 150.

35

Figure 9.23 Model tab of the Risk Solver Task Pane.

The task pane lists the uncertain variables, uncertain functions, and the statistical functions in this model.

36

Figure 9.24 Double-click on cell I10 to view this dialog box. Frequency graph

displays the distribution of the total profit values calculated during the simulation.

37

Figure 9.25 The expected profits change when order size is increased to 250.

Use the PsiTarget() function to calculate the probability that profits will be greater than a target value of $500.

38

Figure 9.26 Simulation Results dialog box. The maximum profit is $600, and the minimum profit is $205.

39

A Single Server Queuing Problem

Consider an automatic teller machine (ATM) where customers arrive at a mean interarrival time of 3 minutes.

Interarrival times are exponentially distributed.

The service time at this ATM has a triangular distribution with parameters 2, 4 and 9 minutes.

We want to know the average and maximum customer waiting time in the queue to be served, and the utilization of the ATM machine.

40

Figure 9.27

Setup the model for the problem: – Col. (1):Interarrival Time

PsiExponential($F$7)

– Col. (2): Arrival Time of a customer

– Col. (3): Beginning Service Time is the maximum of the customer’s arrival time and the departure time of the previous customer.

– Col. (4): Service Time

PsiTriangular($F$8,$F$9,$F$10)

– Col. (5): Departure Time

– Col. (6): Time in Queue

– Col. (7): Total Time in System

41

Figure 9.28

Collect statistics about the mean waiting time, mean time in the system, and utilization;– Cell G40: =AVERAGE(G15:G39) + PsiOutput()

– Cell G41: =AVERAGE(H15:H39) + PsiOutput()

– Cell G42: =SUM(E15:E39)/F39 + PsiOutput()

Set Trials per Simulation property equal to 5,000 using the Platform tab at the Risk Solver Task Pane.

Prepare a summary of results table:– PsiMean(G40), PsiStdDev(G40),

and PsiMax(G40)

– PsiMean(G41), PsiStdDev(G41),

and PsiMax(G41)

– PsiMean(G42), PsiStdDev(G42),

and PsiMax(G42)

42

Figures 9.29 and 9.30(a)

Run the simulation model by clicking on the green arrow at the top right corner of the Task Pane.

The Simulation results are obtained.

43

Figure 9.30(b)

Double click on cell G40 to open the Risk Solver Platform Summary of Statistics dialog box.

The waiting time varies however anywhere between 5 and 52 minutes.

44

Figure 9.30(c)

The Sensitivity chart indicates that waiting time is highly sensitive to customer arrival in the system and service time.

45

Figure 9.30(d)

Only 25% of the customers wait up to 20 minutes.

46

Figure 9.31

Consider that we can reduce the maximum service time at the ATM by replacing the existing machine with a faster one.

The different machines that are available in the market can decrease the maximum service time from 9 to 5, 6, 7 and 8.

We perform 5 simulation runs. In each run we change to 5, 6,..,9 the third parameter of the triangular distribution we use to randomly generate the service time.

47

Figure 9.32

Results of the simulation show that faster ATM machines reduce the waiting time in queue.

Trend chart reaches the same conclusion.– Click on: Risk Solver Platform > Analysis > Charts command.

– From the drop-down menu, select Multiple Simulations.

– In the Multiple Simulation flyout menu we select Trend.

48

Figure 9.33 We want to know how sensitive the simulation results are to changes in

customer interarrival time in the system.– Cell F7 ‘=PsiSenParam(3,10)’

– Set Simulations to Run property to 5; and Simulate the model.

– Click on: Risk Solver Platform > Analysis > Charts command.

– From the drop-down menu, select Sensitivity Analysis.

– In the Sensitivity Analysis flyout menu select Parameter Analysis.

49

Retirement Planning Problem Jane is building a retirement plan for herself. She wants to ensure that

she has enough funds when she retires, 25 years from now.

Jane plans investing her current savings and a percentage of her salary increase to this retirement plan. The salary increase is uncertain.

She plans to invest on T. Bills, Bonds and Stocks. The return on these investments is uncertain.

Will Jane achieve her desired return?

50

Simulation Model

Build the simulation model using Risk Solver platform: Define Uncertain Variables (random

problem input): Market returns

Jane has collected historical data (1993 to 2011) about the annual rate of return of the three investment options.

The market annual return for each investment option is randomly generated using the PsiResample() function.

For T. Bills:

Cell B14:B39: ‘=PsiResample(P$14:P$32)’

51

Simulation Model

Define Uncertain Variables (random problem input): Salary increase

Jane believes that her expected salary increase rate will be between 5% and 10%.

Cell D5: ‘=PsiUniform(0.05, 0.1)’

Half of the salary increase is invested (cell D6). These savings are in range J14:J39.

52

Simulation Model Define Uncertain Functions (problem output): Total return

Cell H39 gives the total amount Jane would have saved in 25 years.Cell H39: ‘=SUM(E39:G39)+PsiOutput()’

Define Statistic Functions: provide summary statistics over all simulation runs:

The expected total return and corresponding standard deviation Cell I4: ‘=PsiMean(H39)’ Cell I5: ‘=PsiStdDev(H39)’

Calculate the probability that the total returns are greater than the desired return from this retirement plan Cell I6: ‘=1-PsiTarget(H39,I3)’

53

Simulation Results

The expected returns from this retirement plan are $952,279.

There is a 39.2% probability that Jane will achieve her desired total return before retirement.

54

Figure 9.38

Jane builds an optimization-simulation model to identify an allocation of her assets that maximize the expected profits.

Optimization Model Tab of the Task Pane presents the optimization model setup.

55

Figure 9.39

Based on the results of this optimization-simulation model, Jane should be solely investing on Stocks.

56

Figure 9.40

However, Jane wants a diverse portfolio to minimize her risk. She performs ten simulations, each with a different investment

allocation scheme that she is comfortable with.

57

Figure 9.41 (a)

The results from this multiple-simulation run.

58

Figure 9.41(b)

The Box-Whisker chart presents the mean, median, 25th percentile, 75th percentile, minimum, and maximum values of expected return for each simulation run.

– Select: Risk Solver Platform > Analysis > Charts drop-down menu.

– From the list of options, select Multiple Simulations.

– From the corresponding flyout menu, choose Box-Whisker.

59

Summary

Simulation is a tool used to model and analyze the behavior of complex systems.

In What-If analyses we change the value of a certain problem input to observe its impact on problem outputs. Excel provides two what-if analysis tools, the scenario manager and data tables.

A random number generator is an algorithm used to generate identical, and uniformly distributed numbers between 0 and 1. The RAND() function of Excel generates random numbers.

Risk Solver Platform uses galleries of Psi functions to generate numbers from a particular distribution. The most frequently used function are: PsiNormal, PsiDiscrete, PsiBeta, PsiBinomial.

The goal of Sensitivity Analysis is to estimate the impact of changes on problem inputs to problem outputs.

Applications of simulation include the News Vendor Problem, Single Server Queuing Problem, and the Retirement Planning Problem.

60

Additional Links

(place links here)