1 / 60

Chapter 9: Simulation

Spreadsheet-Based Decision Support Systems. Chapter 9: Simulation. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview. 9.1 Introduction 9.2 Defining Simulation 9.3 What-If Analysis Tools of Excel

claire-mcdowell
Download Presentation

Chapter 9: Simulation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Spreadsheet-Based Decision Support Systems Chapter 9: Simulation Prof. Name name@email.com Position (123) 456-7890 University 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 ScenarioManager 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. 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

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

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

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

  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 • Click on: Risk Solver Platform > Parameters > Parameters command. • From the drop down menu select Identify. • The Tornado Sensitivity Chart appears. Use the Tornado Sensitivity Chart to identify problem inputs that greatly impact problem outputs.

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

More Related