Transcript EIN 4905/ESI 6912 Decision Support Systems Excel

Spreadsheet-Based Decision Support Systems
Chapter 9: Simulation
Prof. Name
Position
University Name
[email protected]
(123) 456-7890
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
2
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.
3
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.
4
What-If Analysis

Data Tables

Scenario Manager
5
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.
6
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.
7
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.
8
Figure 9.4


Repeat the same steps to create the other scenarios.
Scenario manager dialog box illustrates the list of scenarios that are
created
9
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.
10
Figure 9.6

The Scenario Report exhibits the result cells, for each scenario as well
as the current values from the initial tables
11
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
12
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.
13
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.
14
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.
15
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.
16
Simulation Using Risk Solver Platform

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
17
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
18
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
19
1. Generating Random Numbers within Distributions

Risk Solver Platform offers galleries of Psi functions to generate random
numbers.
– Click on:
 Risk Solver Platform > Simulation Model > Distributions command.
20
1. Generating Random Numbers within Distributions

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.
21
1. Generating Random Numbers within Distributions

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.
22
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.
23
Figure 9.16

We use historical data to identify the distribution of customer interarrival
time at the ATM machine.
24
Figure 9.17

Based on the results displayed, exponential is the distribution that best
fits our data.
25
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.
26
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.
27
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.
28
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.
29
Applications

News Vendor Problem

A Single Server Queuing Problem

Retirement Planning Problem
30
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?
31
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.
32
Simulation Model
Build the simulation model using Risk Solver platform:
 Define Uncertain Variable (random problem input): Demand

Define Uncertain Function (random problem output): Profit
-

Cell I10: ‘= G10 + H10 - F10 + PsiOutput()’
Define Statistic Function: provide summary statistics over all simulation
runs:
–

Use PsiDiscrete(E4:E6,F4:F6) function to randomly generate demand
Cell C12: ‘= PsiMean(I10)’ Cell C13: ‘=PsiStdDev(I10)’
Click on: Risk Solver Platform > Solve Action > Simulate command.
33
Figure 9.22
Simulation results when the quantity of calendars ordered is 150.
34
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.
35
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.
36
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.
37
Figure 9.26


Simulation Results dialog box.
The maximum profit is $600, and the minimum profit is $205.
38
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.
39
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
40
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)
41
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.
42
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.
43
Figure 9.30(c)

The Sensitivity chart indicates that waiting time is highly sensitive to
customer arrival in the system and service time.
44
Figure 9.30(d)

Only 25% of the customers wait up to 20 minutes.
45
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.
46
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.
47
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.
48
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?
49
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)’
50
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.
51
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)’
52
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.
53
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.
54
Figure 9.39

Based on the results of this optimization-simulation model, Jane should
be solely investing on Stocks.
55
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.
56
Figure 9.41 (a)

The results from this multiple-simulation run.
57
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.
58
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.
59
Additional Links

(place links here)
60