Managerial Decision Modeling with Spreadsheets

1. Managerial Decision Modeling with Spreadsheets Chapter 10 Simulation Modeling

2. Learning Objectives • Understand basic steps of conducting a simulation. • Explain advantages and disadvantages of simulation. • Tackle wide variety of problems by simulation. • Set up and solve simulation models using Excel’s standard functions. • Use Crystal Ball add-in for Excel to solve simulation models.

3. 10.1 Introduction • What is Simulation? • Try to duplicate features, appearance, and characteristics of real system. • Idea behind Simulation • Imitate real-world situation mathematically. • Study its properties and operating characteristics. • Draw conclusions and make action decisions based on results of simulation.

4. Process of a Simulation

5. 10.2 Advantages And Disadvantages Of Simulation • Advantages • Relatively straightforward and flexible. • Used to analyze large and complex real-world situations. • Allows “what-if ? ” types of questions. • Does not interfere with real-world system. • Allows study of interactive effects of individual components or variables to determine which ones are important. • Time compression. • Allows for inclusion of real-world complications.

6. 10.2 Advantages And Disadvantages Of Simulation • Disadvantages • Good models can be very expensive. • Often it is a long, complicated process to develop model. • Does not generate optimal solutions to problems. • Managers must generate all of conditions and constraints for solutions to be examined. • Each simulation model is unique and not easily transferable.

7. 10.3 Monte Carlo Simulation • Applicable when system contains elements that exhibit chance behavior. • Experimentation based on chance elements through random sampling. • Steps of Monte Carlo Simulation - • Set up probability distribution for each variable in model subject to chance. • Use random numbers to simulate values from probability distribution for each variable in Step 1. • Repeat process for series of replications or trials.

8. Step 1 - Establish Probability Distribution for Each Variable • Basic idea in Monte Carlo simulation is to generate values for variables in model being studied. • Example of these variables are: • Product demand. • Lead time for orders to arrive. • Times between machine breakdowns. • Times between arrivals at a service facility. • Service times. • Times to complete project activities. • Number of employees absent from work each day. • To establish probability distribution for given variable, one can examine historical outcomes of variable.

9. Harry's Auto Tire Shop Example • Monthly demand for radial tires over past 60 months. • Assume past demand rates will hold in future. • Convert data to probability distribution. • Divide each demand frequency by total number of months 60. • Distributions can either be empirical or known such as normal, binomial, Poisson, or exponential patterns.

10. Step 2 - Simulate Values From the Probability Distributions Harry’s Auto Tire Shop • Simulate demand for a specific month? • Actual demand value is 300, 320, 340, 360, 380, or 400. • There is 5% chance monthly demand is 300, • 10% chance that it is 320. • 20% chance that it is 340. • 30% chance that it is 360. • 25% chance that it is 380. • 10% chance that it is 400.

11. Step 2 - Simulate Values From the Probability Distributions Harry’s Auto Tire Shop • For long run - •   Expected monthly demand= S (demand Di) x • (probability of Di) • = (300)(0.05) + (320)(0.10) + (340)(0.20) + • + (360)(0.30) + (380)(0.25) + (400)(0.10) • = 358 tires • In short term, occurrence of demand may be quite different from these probability values.

12. Step 2 - Simulate Values From Probability Distributions Harry’s Auto Tire Shop • Procedure needed to do following: • Generate random demand values that do not exhibit any specific pattern. • Expected value need not necessarily equal 358 tires per month. • Generate random demand values that conform exactly to required probability distribution. • Expected value must equal 358 tires per month.

13. Random Numbers • In simulation, use random numbers to achieve preceding objectives. • Random number is number that has been selected by totally random process. • Assume generate an integer valued random number from set 0, 1, 2, …, 97, 98, 99. • One way to do this would be: • 1. Take 100 identical balls and mark each one with • unique number between 00 and 99. • 2. Put all balls in large bowl and mix thoroughly. • 3. Select one ball from bowl and write down number. • 4. Replace ball in bowl and mix again. Go to step 2.

14. Random Numbers • Instead of balls in bowl, one could have used spin of roulette wheel that has 100 slots to accomplish this task. • Another commonly used means is to choose numbers from table of random digits such as table of random numbers. • Table of random numbers appears on next slide.

15. Table of Random Numbers

16. Using Random Numbers to Simulate Demand Harry’s Auto Tire Shop • Converting probability distribution in table to cumulative probability distribution. • Cumulative probability for each demand level is sum of probability of demand and all demands less than that demand. • Cumulative probability for demand of 340 tires is sum of probabilities for 300, 320, or 340 tires. • Obviously cumulative probability for demand of 400 tires ( maximum demand) is 1.

17. Using Random Numbers to Simulate Demand Harry’s Auto Tire Shop

18. Using Random Numbers Simulate Demand Harry’s Auto Tire Shop • Use cumulative probabilities to assign random numbers. • Random numbers are two-digit numbers from 00 to 99. • Create random number intervals by assigning these 100 random numbers to represent different possible demand values. • Since there is 5% probability that demand is 300 tires, assign 5% of random numbers to denote this level of demand. • Assign first five random numbers possible (namely, 00, 01, 02, 03, and 04) to denote demand of 300 tires.

19. Step 3 - Repeat Process for Series of Trials Harry’s Auto Tire Shop • Very risky to draw any hard and fast conclusion regarding simulation model from only few simulation trials. • Expected demand is 358 tires per month. • It is likely one will get different values for average from short simulation of few months. • Run simulation model for several thousand trials in order to gather meaningful results.

20. 10.4 Role Of Computers In Simulation • Easier to simulate by computer than manual procedure. • Computer software have built-in procedures for generation of random numbers. • Easy to simulate values from many probability distributions rather then manual process. • For simulation results to be valid, it is necessary to replicate process thousands of times which is matter of seconds using software packages. • Keep track of several input parameters and output statistics is easier with software packages in any simulation model.

21. Types of Simulation Software Packages • General-Purpose Programming Languages. • VisualBasic, C++, and FORTRAN • Special-Purpose Simulation Languages and Programs. • GPSS/H, Simscript II.5, SLAM II, and GASP, Extend, MicroSaint, BuildSim, AweSim, ProModel, and Xcell. • Spreadsheet Models. • Generate random numbers and use them to select values from several probability distributions makes spreadsheets excellent tools for conducting simulations.

22. Random Generation From Probability Distributions Using Excel • Generate Random Numbers in Excel. • Excel uses RAND function to generate random numbers. • Format for using function is:  = RAND ( ) • It returns random value between 0 and 1 (actually between • 0 and 0.9999...) each time calculate key (F9 key) is • pressed. • Generate various distributions in Excel. • Uniform Distribution: = a + (b - a ) *RAND() • Normal Distribution: = NORMINV(RAND(),m, s ) • Exponential Distribution: = -m*LN(RAND())

23. Excel Example Using Vlookup Draw From Discrete Distribution

24. Simulation Using Vlookup Table

25. Summary of Excel Formulas Used for Simulations

26. 10.5 Using Simulation To Compute Expected Profit Harry’s Auto Tire Shop • Monthly demand of auto tires is 300, 320, 340, 360, 380, or 400. • Average selling price follows discrete uniform distribution between $60 and $80. • Profit margin follows continuous uniform distribution between 20% and 30%. • Fixed operating cost is $2,000 per month. • Using this information, simulate and calculate average profit per month from sale of auto tires.

27. 10.5 Using Simulation To Compute Expected Profit Harry’s Auto Tire Shop • Using this information, simulate and calculate average profit per month from of auto tires.

28. Setting Up Model Harry’s Auto Tire Shop

29. Excel Layout and Formulas

30. Results for Simulation Model

31. Analyzing Results Harry’s Auto Tire Shop • Cells G4 to G203 show monthly profit for 200 replications (months). • Calculate following statistics. • Average monthly profit (cell G204) = $4,277.89 • Standard deviation of monthly profit (cell G205) • = $1024.88 • Requires monthly profit of at least $4,000. • What is the probability for this level of profit? • Number of months (of the 200 months) in which profit exceeded $4,000 (shown in cell G206) divided 200 gets probability value (cell G207). • Shows 59% chance of monthly profit in excess of $4,000.

32. 10.6 Using Simulation For Inventory Problem • There are usually two main questions in most inventory problems: • (1) how much to order, and • (2) when to order. • In many inventory situations several inventory parameters are random variables (demand, lead time). • This implies that inventory for the item may run out before next consignment is received, causing stockout.

33. Simkin’s Hardware Store Example • Sells electric drills. • Daily demand for drill is relatively low but subject to some variability. • Over past 300 days, actual demand is shown in column 2 of table. • Actual frequency is converted into a probability distribution for the variable daily demand (column 3).

34. 10.6 Using Simulation For Inventory Problem Simkin’s Hardware Store • Lead time is probabilistic variable. • Based on past 100 orders, lead time follows discreteuniform distribution between 1 and 3 days. • 7 drills in stock, and no orders due. • Identify order quantity, Q, and reorder point, R, to reduce total monthly costs. • Total cost includes following components: • Fixed ordering cost. • Holding cost for each drill held in inventory from one period to next. • Stockout cost for each drill not available to satisfy demand.

35. 10.6 Using Simulation For Inventory Problem Simkin’s Hardware Store • Fixed cost of placing order with supplier is $20. • Cost of holding a drill in stock is $0.50 per drill per month ( $0.02 per drill per day). • Cost of a stockout is $8 per drill. • Two decision variables (Q [order quantity], and R [reorder point] and two probabilistic components (demand and lead time). • Using simulation, try different (Q,R) combinations to see which combination yields lowest total cost. • First examine policy that has Q = 10 and R = 5. • Each time inventory at end of day drops to 5 or less, place an order for 10 drills with supplier.

36. Setting Up Model

37. One-variable Data Table

38. Two-variable Data Table

39. 10.7 Using Crystal Ball To Simulate Inventory Problem • Crystal Ball 2000, an add-in for Excel, is published by Decisioneering Inc. • Reasons for Using Add-In Programs for simulation. • Built-in functions simulate not only probability distributions but also many other distributions (such as the binomial, triangular, and lognormal distributions). • Make it easy to replicate simulation several hundred or several thousand times. • Make it easy to collect information on various output measures.

40. Summary of Functions Available in Crystal Ball

41. Excel Layout and Results Using Crystal Ball

42. Replicating Model Simkin’s Hardware Store • Defining Forecast Cell in Crystal Ball

43. Replicating Model Simkin’s Hardware Store • Setting Run Preferences in Crystal Ball

44. Graphical and Tabular Results From Crystal Ball Simkin’s Hardware Store

45. Using Decision Table in Crystal Ball Simkin’s Hardware Store

46. Setting Up DecisionTable in Crystal Ball - Step 1 of 3 Simkin’s Hardware Store

47. Setting Up DecisionTable in Crystal Ball - Step 2 of 3 Simkin’s Hardware Store

48. Setting Up DecisionTable in Crystal Ball - Step 3 of 3 Simkin’s Hardware Store

49. Results From Decision Table in Crystal Ball Simkin’s Hardware Store

50. 10.8 Using Crystal Ball To Find Best Reservation Policy • Judith’s Airport Limousine Service. • Use Crystal Ball to simulate problem to find optimal number of reservations for trip. • Nearest airport is 50 miles away. • On average there are 45 people from Six Mile, South Carolina (and its vicinity) who need rides to, or rides from, airport each day. • Complete data regarding this problem is in text.