SupplementCIntroductiontoSimulation

,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,Supplement C,Introduction to Simulation,Operations Management,by,R. Dan Reid & Nada R. Sanders,2nd Edition Wiley 2005,PowerPoint Presentation by,Roger B. Grinde, University of New Hampshire,1,Learning Objectives,Explain why simulation is a valuable tool for decision making.,Define the steps involved in the simulation modeling process,Generate random numbers from various distributions in Excel,Develop and run a simulation model in Excel,Analyze the results from a simulation in Excel,2,Computer Simulation,A model that mimics what might happen in reality.,Examples,Weather forecasting,Rocket simulators,Military war-game simulations,Business Examples,Capital projects,Business process redesign,Production process analysis,Service system analysis,3,Computer Simulation (continued),Much of the time, uncertainty is present in the system we wish to study.,Simulation provides a way to directly model the uncertainty and/or dynamic behavior of the system.,Monte-Carlo simulation is the focus of this chapter. It focuses on assessing the uncertainty and risk of a particular situation or decision.,Discrete-Event simulation is another major branch of simulation. It focuses on studying the dynamic behavior of systems as they operate over time.,4,Monte-Carlo Simulation Schematic,Some inputs are fixed, or known with certainty (e.g., the price of our product).,Some inputs are uncertain, or random (e.g., the demand for our product at a given price).,We may have decision variables (e.g., quantity to produce).,Since some of the inputs are random, the output of the simulation is also random.,5,Simulation Modeling Process,Develop a deterministic spreadsheet model.,Determine the appropriate probability distributions and parameters to use for the random inputs.,Modify the deterministic model by incorporating the random inputs.,Re-calculate the model many times (each calculation is called a replication or trial).,Analyze the probability distribution of the output using statistical concepts.,6,Probability Distributions,Probability Distributions are used to model the random inputs.,Youre already probably familiar with some probability distributions, even if you dont know their names.,Normal (“bell” curve),Bernoulli (e.g., flip of a coin),Discrete Uniform (e.g., roll of a die),A very important part of simulation is modeling the random behavior of a situation using probability distributions.,As noted before, since some of the inputs are random, the output of a simulation is also random.,However, the probability distribution of the output of a simulation does not necessarily look like one of the standard probability distributions.,The next few slides show how we can generate random values in Excel from several different probability distributions.,7,Random Number Generation In Excel,Key to random number generation is generating random values that are “uniformly” distributed between 0 and 1.,It turns out that if we can do this, then we can transform this value into a sample value from any probability distribution.,“Uniformly” distributed simply means that any value in between 0 and 1 is equally likely.,Fortunately, Excel has a built-in function, =RAND(), which does exactly this.,=RAND() (empty parentheses are required!) returns a value between 0 and 1.,If you enter this function in a cell, and then copy it to some other cells, all the values will be different!,Also, if you hit the F9 key (which recalculates the worksheet), the values of cells with =RAND() in them will change!,This is simulation at work. Just like in real life, some things are uncertain (e.g., commuting time, waiting time at the food court).,8,=RAND(),=RAND() entered in one cell, then copied.,All values are different.,If you do this, your values will be different too.,Hit F9 to re-calculate all 50 values.,RAND() produces a “U(0,1)” random number.,9,How “Uniform” are the values from RAND?,As more values are generated, histograms become more uniform.,Just as in sampling, a bigger sample brings more precision.,10,Bernoulli Distribution,Bernoulli Distribution: 2 outcomes,Example: Flip of a coin,How to convert a value from RAND() to a “heads” or a “tails”?,Equal probability,=IF(RAND()0.5, “heads”, “tails”),Nothing special about the 0.5could simulate an unfair coin, or defective/non-defective parts, complaining or non-complaining customers, etc.,11,Bernoulli Distribution Example,Formulas entered in row 4, then copied down to simulate 100 coin flips.,Some rows hidden here.,Count up number of heads, tails.,What would happen if we were to re-calculate this spreadsheet?,What would happen if we were to simulate 10,000 coin flips rather than 100?,12,Discrete Uniform Distribution,Finite number of outcomes, each with the same probability.,Coin flip is a simple example of this.,Roll of a die is a more complex example.,Practical example: selecting someone at random from a known number of entries.,Implementation,We could use an IF statement as before, but this becomes difficult because of the many possible outcomes. We would have to “nest” IF statements within others, and there is an Excel limit on this.,A better way is to use the VLOOKUP function in Excel.,13,Discrete Uniform: Roll a Die,Key: Divide up the range between 0 and 1 into 6 equal intervals. Assign the possible die rolls (1, 2, , 6) to each one of these intervals.,Table in D5:G12 sets up the intervals.,Column A generates a U(0,1) value.,Column B converts the U(0,1) value into a die roll by looking up the U(0,1) value in the table to see which interval it falls into. Then it returns the corresponding die roll.,Results section tallies up the results from 100 die rolls.,Is there anything special about the equal probabilities? Could we simulate an “unfair” die just by changing the probabilities in D7:D12?,14,General Discrete Distribution,In the last example, is there anything special about the equal probabilities?,Could we simulate an “unfair” die just by changing the probabilities in D7:D12?,General Discrete Distributions are handled in the same way.,Examples,Demand for products or services,Number of machines breaking down in a day,15,General Discrete Distribution Example,Note the different probabilities for the different possible demand values.,Results for 100 trials roughly correspond to the input probabilities (obviously, more trials would result in a closer match).,16,Continuous Probability Distributions,So far, weve only dealt with discrete distributions.,Discrete distribution is one where the outcome can be only one of a finite number of possibilities (technically, a “countable” number possibilities).,Continuous distribution allow any possible value, possibly bounded above and/or below.,Actually, RAND() is an example of a continuous probability distribution, U(0,1).,Here well look at the uniform distribution, the normal distribution, and the exponential distribution.,17,Continuous Uniform Distribution,Uniform distribution between,a,(minimum) and,b,(maximum) is designated U(,a,b,).,RAND() returns a U(0,1) random value.,Convert value from RAND() into value from U(,a,b,).,X = a + (ba)*RAND(),If a=10, b=50, and RAND()=0.37, then X = 10 + (50-10)*0.37 = 10 + 14.8 = 24.8,What if RAND()=0? What if RAND()=1?,Examples,Time to complete a task, based on minimum and maximum time estimates.,Unit costs,Demand,18,U(10,50) Example: Histogram based on 250 trials,19,Discrete Uniform Distribution: A Reprise,Earlier we used a VLOOKUP function to simulate a Discrete Uniform distribution,If the possible values are integers, we can use what weve learned about the continuous uniform distribution to be more efficient.,Suppose we want a discrete uniform distribution between a and b, inclusive, designated DU(a,b).,Use =INT(a+(ba+1)*RAND(),Example: DU(10,50), suppose RAND()=0.37,X=INT(10+(5010+1)*0.37) = INT(25.17) = 25,INT returns the integer part of a number,The “+1” is needed to ensure nothing smaller than a is returned, and that b is an actual possibility.,This approach is easier than the VLOOKUP approach especially when there are many possibilities (e.g., choosing one person at random out of a list of 5000 entries).,20,Normal Distribution,Normal Distribution characterized by a mean () and standard deviation (,).,Designated N(,),Random Number Generation,=NORMINV(RAND(),),NORMINV is the “inverse” of the normal distribution function. RAND acts like a cumulative probability value (between 0 and 1).,If =0 and =1, then =NORMINV(RAND(),0,1) essentially returns a random Z-value from a normal distribution.,21,N(80,10) Example: Histogram based on 250 trials,22,Exponential Distribution,Very common distribution when modeling customer arrivals to service systems, machine breakdowns, etc.,Characterized by a mean, denoted . We refer to an exponential distribution as EXP().,For example, would represent the average time between customer arrivals, the average time between machine breakdowns, etc.,Generating an EXP() random value:,=*LN(RAND(),LN is the natural logarithm function (which is the mathematical inverse of the EXP function.,The minus sign is needed because the natural logarithm of a value between 0 and 1 is negative.,Example,Suppose =25, and RAND()=0.68,Then X = 25*LN(0.68) = 9.64,23,EXP(10) Example: Histogram of 250 trials,24,Example: DG Outerwear,Decide number of coats to order,Place order in June, but demand not realized until Fall. No chance for another order.,If we order too few, we lose out on sales.,If we order too many, we must sell the remainder at a loss.,Demand for coats at the regular price is random. We believe we can sell any coats left over, but the salvage price itself is uncertain.,25,DG Outerwear Problem Parameters,Unit Cost: $75,Regular Sales Price: $100,Demand at regular price: Uniformly distributed between 20 and 40.,Salvage Price: May be $15, $20, $25, or $30 with respective probabilities 0.05, 0.30, 0.50, and 0.15.,Assume all leftover coats will be sold at a single salvage price.,DG considering ordering 35 coats. Is this a good idea?,26,Simulation Model,Key Formulas,B17, C17: =RAND() D17 = INT(E$4+(E$5E$4+1)*B17),E17 = MIN(B$8,D17) F17 = B$8E17,G17 = VLOOKUP(C17,E$10:G$13,3) H17 = B$5*E17,I17 = G17*F17 J17 = H17+I17(B$8*B$4),27,Simulation Model (continued),This is for one replication, or trial. We need to run many trials to get a good sense of the results of this decision.,Weve used relative and absolute cell references in the model so that we can copy the formulas in Row 17 down.,Well copy this row down so that we have 250 trials of the simulation.,Then, well calculate summary statistics of the resulting profit values.,28,Simulation Model Replicated, with Summary Statistics,Note: Many rows hidden (but calculations use all rows),Summary Statistics for a Purchase Quantity of 35,Average Profit = $435,Std.Dev. = $392,Minimum =,$325,Maximum = $875,95% Confidence Interval = ($386, $483),29,DG Coats Example: Comments,Purchasing 35 coats results in an average profit of $435. Can we do better? What do the standard deviation, minimum, and maximum values tell us?,95% confidence interval on mean profit,We are 95% confident the true value of the mean profit lies somewhere in the interval from $386 to $483.,What effect would more (or less) simulation trials have on this confidence interval?,What does the confidence interval say about an,individual,value of profit, such as what we will get this year if we order 35 coats?,30,DG: Finding Optimal Order Quantity,We can change the purchase quantity, press F9, and calculate the 250 trials and summary statistics for this new purchase quantity. This is a powerful tool!,Purchase Quantity,Average,Standard Deviation,Minimum,Maximum,20,$500,$0,$500,$500,25,$568,$117,$225,$625,30,$551,$264,-$50,$750,35,$435,$392,-$325,$875,40,$230,$460,-$600,$1000,The highest average profit occurs when we purchase 25 coats.,Thought Question: The average demand is 30 (remember demand was uniformly distributed between 20 and 40). Why is the highest average profit at a purchase quantity less than this average?,Does making decisions based on the average always make sense?,31,DG: Histogram of Profit Values,Purchase Quantity = 25,Histogram of profit values,Why does this histogram shape make sense?,32,Simulation Using Data Tables,In the DG example we simply copied the formulas down to replicate the model 250 times. This only works when the logic of the model is simple enough to arrange in a single row of the spreadsheet.,For more complex models, we need to use Excels Data Table feature (first used in Supplement A).,With a Data Table approach, we build the logic of the simulation for one single trial. Then the Data Table effectively recalculates the model for as many times as we wish.,33,Data Table Approach for DG Problem,We only need the logic for a single trial (Row 17).,Next slide gives steps for Data Table.,34,Data Table Steps for DG Problem,Steps,Two columns needed, for replications and profit. In the replications column, from A22 to A271, enter the numbers 1250. Do not put anything in Cell A21.,Cell B21, enter “=J17.” This references the profit value from the simulation logic.,Select A21:B271.,Keeping A21:B271, go to Data/Table, “Row Input Cell” blank, and click on A21 (or any blank cell on the worksheet) for the “Column Input Cell.” Click “OK.” The results from 250 replications should now be showing in B22:B271.,If all the values in B22:B271 are the same, press the F9 key to force recalculation of the worksheet.,Compute summary statistics from the results.,If desired, freeze the results from the simulation in Cells B22:B271 using Edit/Copy, Edit/Paste Special/Values.,Comments,The “column input cell” must not have anything in it. This is a different use of the Data Table than in Supplement A. Here, the Data Table is being “faked” into recalculating the simulation output measure 250 times.,35,Supplement C Highlights,A computer simulation is a model that mimics what might happen in reality.,Computer simulations model the uncertainty present in a system by generating random numbers from known probability distributions.,Simulation is a valuable tool because it can simultaneously consider the uncertainty present in many factors of a problem, and provide outputs that show how theis “input” uncertainty translates into uncertainty in the output measure.,Monte-Carlo Simulation can be conducted using Excel without any aAdd -Iins. Commercial aAdd -iIns such as Crystal Ball and Risk provide additional functionality that is more difficult to employ using stand-alone Excel.,Simple Discrete- Event Simulations can be conducted in Excel, but separate software products, such as ProModel, ProcessModel, and Extend are better suited to modeling of systems whose state and behavior change over time.,The simulation modeling process in spreadsheets consists of developing a deterministic model with correct logic, determining the appropriate probability distributions to use for the random inputs, incorporating those distributions in the model itself, running many replications of the simulation model, any analyzing the simulation results by computing and interpreting summary statistical measures.,36,Supplement C Highlights (continued),Each time Excels RAND() function calculates, it generates a uniformly -distributed random number between 0 and 1, denoted U(0,1).,Random numbers from probability distributions (e.g., Bernoulli, discrete uniform, general discrete, continuous uniform, normal, and exponential, among others) are derived from a U(0,1) random number through mathematical calculations.,Replications of simulation models in Excel can be performed by copying the entire logic itself or by using Excels Data Table feature. For simple models where the logic fits into a single row, copying the logic itself is acceptable. However, for more complex models, the Data Table feature should be used.,At a minimum, one should consider basic summary statistics such as the average, standard deviation, minimum, and maximum when interpreting results from a simulation. One should also compute a confidence interval to assess the precision of the estimate for the mean, and to determine whether additional replications should be run. It is also a good idea to generate a histogram of the results to see the actual probability distribution of the output measure.,37,The End,Copyright 2005 John Wiley & Sons, Inc. All rights reserved. Reproduction or translation of this work beyond that permitted in Section 117 of the 1976 United State Copyright Act without the express written permission of the copyright owner is unlawful. Request for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for distribution or resale. The Publisher assumes no responsibility for errors, omissions, or damages, caused by the use of these programs or from the use of the information contained herein.,38,