EIN 4905ESI 6912 Decision Support Systems Excel

Document Sample
EIN 4905ESI 6912 Decision Support Systems Excel Powered By Docstoc
					     Spreadsheet-Based Decision Support Systems
                  Chapter 9: Simulation

Prof. Name                                name@email.com
Position                                    (123) 456-7890
University Name
   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

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

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

                       What-If Analysis
   Data Tables

   Scenario Manager

                       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

                             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.

                     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.

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

                            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.

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

                           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

                     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.

                              Figure 9.7
 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.

                      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.

                   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.

     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

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.

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

                           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

1. Generating Random Numbers within Distributions
   Risk Solver Platform offers galleries of Psi functions to generate random
     – Click on:
          Risk Solver Platform > Simulation Model > Distributions command.

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
     – Shows distribution percentiles and
       other statistics.
     – The default values of mean and
       standard deviations can be

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.

    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.

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

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

           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.

                             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.

                             Figure 9.19
   Use the Tornado Sensitivity Chart to identify problem inputs that greatly
    impact problem outputs.

– Click on: Risk Solver Platform
  > Parameters > Parameters
– From the drop down menu
  select Identify.
– The Tornado Sensitivity
  Chart appears.

                            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
     – Select: Risk Solver Platform > Analysis > Reports drop-down menu.
     – Select: Simulation > Parameter Analysis report.

   News Vendor Problem

   A Single Server Queuing Problem

   Retirement Planning Problem

                    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?

                           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.

                         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
    –   Cell C12: ‘= PsiMean(I10)’ Cell C13: ‘=PsiStdDev(I10)’

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

                           Figure 9.22
Simulation results when the quantity of calendars ordered is 150.

                            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.

                             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

                              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.

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

        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.

                              Figure 9.27
   Setup the model for the problem:
    – Col. (1):Interarrival Time
    – Col. (2): Arrival Time of a
    – Col. (3): Beginning Service Time
      is the maximum of the
      customer’s arrival time and the
      departure time of the previous
    – Col. (4): Service Time
    – Col. (5): Departure Time
    – Col. (6): Time in Queue
    – Col. (7): Total Time in System

                            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)

                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.

                        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.

                          Figure 9.30(c)
   The Sensitivity chart indicates that waiting time is highly sensitive to
    customer arrival in the system and service time.

                        Figure 9.30(d)
   Only 25% of the customers wait up to 20 minutes.

                            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.

                             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.

                              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.

            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?

                      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)’

                        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.

                        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

    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)’

                     Simulation Results

   The expected returns from
    this retirement plan are

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

                            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.

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

                            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.

                         Figure 9.41 (a)
   The results from this multiple-simulation run.

                             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

–   From the list of options, select
    Multiple Simulations.

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

   Simulation is a tool used to model and analyze the behavior of complex
   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.

                         Additional Links
   (place links here)


Shared By: