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

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

25. Setting Up Model Harry’s Auto Tire Shop

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

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

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

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

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

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

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

33. 10.8 Other Types Of Simulation Models • Simulation models often broken into three categories. Monte Carlo method, operational gaming,andsystems simulation. • Operational Gaming • Simulation involving two or more competing players. • Examples are military games and business games. • Allow participants to match management and decision-making skills in hypothetical situations of conflict. • Systems Simulation • Similar to business gaming allows users to test various managerial policies and decisions to evaluate effect on the operating environment. • Variation of simulation models dynamics of large systems.

34. Summary • Discussed concept of simulation as problem-solving tool, building mathematical model to describe real-world situation. • Monte Carlo method uses random numbers to generate random variable values from probability distributions. • Used Excel’s functions and Data Table to run replications of simulation models. • Used Crystal Ball to develop and run simulation models.

35. Summary • Advantages of using add-ins were: • (1) Availability of easy formulas for many • probability distributions. • (2) Ability to set up and run many replications of • model. • (3) Ability to easily collect statistical information. • Discussed operational gaming and systems simulation.