EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 9 Simulation
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 9.1 Introduction
• 9.2 Defining Simulation
• 9.3 Applications
• 9.4 Summary

3
Introduction

• What is simulation and how it is useful
• Performing a simple spreadsheet-simulation using
  Data Tables and the Scenario Manager
• Running advanced simulation models from
  generating random parameter values from
  distributions
• Three examples of simulation models

4
Defining Simulation

• Data Tables
• Scenario Manager
• Generating Random Numbers within Distributions

5
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.
• Excel offers two simple tools for performing
  simulation Data Tables and the Scenario Manager.

6
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

7
Data Tables (cont)

• A list of inputs and outputs should be created
  first.
• Next, you will create a list of the various input
  values you want to experiment with.
• If you are creating a one-way data table, you
  would put these values in a single column.
• If you are creating a two-way data table, you
  would create one column and one row of varying
  input values for the two inputs of interest.
• You must then enter the output formulas you want
  the Data Table to calculate for observation.
• For one-way data tables, these output cells would
  be in the columns adjacent to the input column.
• For two-way data tables, this output cell would
  be placed in the upper corner of the data table.

8
Data Tables (cont)

• Select Data gt Table from the Excel menu.
• 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 we
  are varying two inputs.

9
Figure 9.2

• 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.

10
Figures 9.4 and 9.5

• 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.

11
Figures 9.7 and 9.8

• 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.

12
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.
• Then go to Tools gt Scenarios to view the Scenario
  Manager.

13
Scenario Manager (cont)

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

14
Scenario Manager (cont)

• Next, specify the values these inputs should take
  for the scenario we are creating.

15
Scenario Manager (cont)

• Click Summary to create the Scenario Report.
• The Scenario Summary dialog box asks us to select
  the outputs we want to observe for the various
  scenarios of inputs.

16
Figure 9.13

• We are interested in the companys after tax
  profits for each of the five years as well as
  their total NPV.
• We want to consider three different scenarios for
  year 1 sales, sales growth, and year 1 price.

17
Figures 9.14 and 9.15

• Create each scenario

18
Figures 9.16 and 9.17

• After all three scenarios have been created,
  create the Summary Report.

19
Figure 9.18

• The Scenario Report is complete

20
Generating Random Numbers

• Some Excel functions can be used to generate
  various input data values for several scenarios,
  or runs of a simulation.
• Once we know the distribution of a certain
  parameter, we can generate random numbers within
  this distribution create the simulation.

21
Generating Random Numbers (cont)

• The RAND and RANDBETWEEN functions are 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()(n-1) 1

22
Figure 9.19

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

23
Generating Random Numbers (cont)

• To generate a random integer, use the INT
  function with the RAND function.
• The INT function rounds a number down to the
  nearest integer.
• INT(number)
• INT(range_name)
• INT(cell_referenced)
• To generate random integers, we would apply the
  INT function to the RAND function by typing
• INT(RAND())
• INT(RAND()(n-1) 1)

24
Generating Random Numbers (cont)

• The RANDBETWEEN function takes two parameters,
  which are the lower and upper limits of a range.
• RANDBETWEEN(lower_limit, upper_limit)
• To create random numbers between 1 and 10, you
  could type
• RANDBETWEEN(1,10)

25
Generating Random Numbers (cont)

• To generate a random number from a particular
  distribution, we can use Excels inverse
  distribution functions.
• The general distribution Excel function is
• DIST(x_value, distribution_parameters,
  cumulative_value)
• The general inverse distribution Excel function
  is
• DISTINV(probability, distribution_parameters)

26
Generating Random Numbers (cont)

• We will use the RAND function as our value for
  the probability parameter to generate some number
  between 0 and 1.
• For example, to generate random numbers from the
  Normal distribution, we would follow the format
  
• NORMINV(RAND(), mean, std dev)
• This inverse distribution function can be
  similarly used for other distributions
• BETAINV
• BINOMINV
• LOGINV

27
Figure 9.20

• We generate a set of numbers from the Normal
  distribution with mean 50 and standard deviation
  15.
• NORMINV(RAND(), 50, 15)

28
Applications

• News Vendor Problem
• Game of Craps
• Bidding

29
News Vendor Problem

• A bookstore must determine how many 2006 comic
  calendars to order in September of 2005.
• It costs 2.30 to order each calendar, and they
  sell each one for 4.70. After January 1, 2006,
  any unsold calendars are returned to the supplier
  for a salvage value of 0.75 each.
• The best guess is 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?

30
Figure 9.21

• The spreadsheet is prepared using formulas to
  determine unknown values.

31
Figure 9.22

• The NORMINV function is used with the RAND
  function to generate a random number from the
  Normal distribution.

32
Figure 9.23

• Runs can be made to compare profit values for
  several random input values.

33
Game of Craps

• In the game of Craps, a player rolls two dice.
• If the first roll yields a sum of 2, 3, or 12,
  the player loses.
• If the first roll yields a sum of 7 or 11, the
  player wins.
• Otherwise, the player continues rolling the dice
  until she matches the value thrown on the first
  roll or rolls a sum of 7.
• Rolling a match for the first roll wins the game,
  rolling a value of 7 before a match loses the
  game.
• How many times will a player win on average in 1,
  2, 3, 4, or 5 rolls?

34
Figure 9.24

• The conditions for winning and losing on one or
  multiple rolls are summarized on the spreadsheet.

35
Figure 9.25

• To perform the simulation, the outcome of rolling
  two dice for 20 different games is recorded for
  random results of each game.

36
Figure 9.26

• The number of wins can be recorded when 1, 2, 3,
  4, or 5 rolls are played in a game.

37
Figure 9.26

• We can create a summary table below the
  simulation table by counting the number of wins
  for each roll among 20 games, or runs.
• To perform this conditional counting, we use the
  COUNTIF function.
• COUNTIF(table_range, condition)

38
Bidding

• A contractor is planning to make a bid on a
  construction project.
• He believes that it will cost 12,000 to complete
  the project.
• Three competitors are going to bid against him.
• Based on past history, he believes that each
  competitors bid is equally likely to be any
  value between his cost and triple his cost of
  completing the project.
• He also believes that each competitors bid is
  independent of the other bids.
• Which bid will maximize his expected profit?

39
Figure 9.27

• The inputs and output can be calculated using
  several formulas.

40
Figure 9.28

• Simulation runs can now be generated to find the
  maximum profit.

41
Summary

• Simulation is a modeling tool used for analyzing
  a process running under different parameters.
• Scenario Analysis performs all possible
  alternative actions and notes the varying results
  from these different situations.
• To perform random number generation in
  distributions, use the RAND() function as the
  first parameter in the inverse distribution
  functions.
• Inverse Distribution functions are the following
  NORMINV, LN, BETAINV, BINOMINV.
• Applications of simulation include the News
  Vendor Problem, Cash Flows, the Game of Craps,
  and Bidding.

42
Additional Links

• (place links here)