1、Dr. C. Lightner Fayetteville State University,1,Chapter 13: Simulation,Make sure that you have reviewed the EXCEL TUTORIAL provided in the Simulation folder on Blackboard. Even if you are very familiar with Excel, look over the entire tutorial. There are several helpful techniques that will assist y
2、ou with completing this chapter.,Dr. C. Lightner Fayetteville State University,2,Chapter 13 Simulation,Introduction Advantages and Disadvantages of Using Simulation Overall Simulation Modeling Random Variables and Generating Random Variables Examples,Dr. C. Lightner Fayetteville State University,3,I
3、ntroduction,A simulation model is a computer model that imitates a real-life situation. Simulation models are useful for determining how sensitive a system is to changes in operating conditions. A benefit of computer simulations is that it enables us to answer what-if questions without actually chan
4、ging a physical system.,Dr. C. Lightner Fayetteville State University,4,Simulation,Simulation is one of the most frequently employed management science techniques. Spreadsheet simulation modeling is quite similar to other modeling applications. The main difference is that simulation models decision
5、making scenarios where at least one input parameter is a random variable. Example: The demand for a product, the process of customers arriving to a store, the number of customers that will cancel reservations for an airline flight, etc. These random processes are typically too complex to be solved b
6、y analytical methods.,Dr. C. Lightner Fayetteville State University,5,Simulation Programs,The computer program that performs the simulation is called a simulator. Flowcharts can be useful in writing such a program. While this program can be written in any general purpose language (e.g. BASIC, FORTRA
7、N, C+, etc.) special languages which reduce the amount of code which must be written to perform the simulation have been developed. Special simulation languages include SIMSCRIPT, SPSS, DYNAMO, and SLAM.IN THIS CHAPTER WE WILL FOCUS ON SPREADSHEET MODELING USING EXCEL.,Dr. C. Lightner Fayetteville S
8、tate University,6,Advantages of Simulation,Among the advantages of simulation is the ability to gain insights into the model solution which may be impossible to attain through other techniques. Also, once the simulation has been developed, it provides a convenient experimental laboratory to perform
9、“what if“ and sensitivity analysis.,Dr. C. Lightner Fayetteville State University,7,Disadvantages of Simulation,A large amount of time may be required to develop the simulation. There is no guarantee that the solution obtained will actually be optimal. Simulation is, in effect, a trial and error met
10、hod of comparing different policy inputs. It does not determine if some input which was not considered could have provided a better solution for the model.,Dr. C. Lightner Fayetteville State University,8,Simulation Modeling,Simulation models use mathematical expressions and logical expressions that
11、describe how to compute the value of output based upon input values. There are two categories of input: Controllable Input (CI) Input values that can be established by the analyst Probabilistic Input (PI) Input based upon a random processSimulation conducts a series of experiments using a variety of
12、 values for CI to determine how changes in CI values affect output. The goal is to make recommendations for CI that will provide the best output values.,Dr. C. Lightner Fayetteville State University,9,Simulation Modeling,In order to build a simulation model you must: Determine the output that you wa
13、nt to measure. Determine the CI and PI values that are needed to compute the output. Set the CI values. Determine the proper probability distributions that best models each PI value. Generate values for each PI parameter. Establish the precise mathematical formulas and/or logical expressions that ex
14、press how to compute output based upon CI and generated PI values. Run the simulation model, generating PI, and computing output based upon CI and generated PI values. We will use Excel to simulate a variety of scenarios.,Dr. C. Lightner Fayetteville State University,10,Simulation Modeling,Ideally,
15、the simulation should be run using actual past data. Predictions from the simulation model should be compared with historical results. For each policy (CI) under consideration by the decision maker, the simulation is run by considering a long sequence of potential PI input data values . Whenever pos
16、sible, different policies should be compared by using the same sequence of input data.,Dr. C. Lightner Fayetteville State University,11,Random Variables,Random variable values are utilized in the model through a technique known as Monte Carlo simulation. Each random variable is mapped to a set of nu
17、mbers so that each time one number in that set is generated, the corresponding value of the random variable is given as an input to the model. The mapping is done in such a way that the likelihood that a particular number is chosen is the same as the probability that the corresponding value of the r
18、andom variable occurs. We will use Excel to generate PI using Monte Carlo simulation.,Dr. C. Lightner Fayetteville State University,12,Generating PI,We will review the process of generating random variables from5 common probability distributions: Discrete random variables Normal random variables Dis
19、crete Uniform random variables Uniform random variables Exponential random variables,Dr. C. Lightner Fayetteville State University,13,Generating Discrete Random Variables,Excel has a RAND() function which randomly generates values greater than or equal to 0 but less than 1. In order to use this func
20、tion to generate values following a discrete distribution, you must set up a lookup table.Example: Suppose we want to generate a x according to the following discrete probability:x p(x)0 0.21 0.352 0.253 0.154 0.05,We want to generate values so that approx. 20% of the time the value is 0, 35% of the
21、 time the value is 1, 25% of the time the value is 2, 15% of the time the value is 3, and 5% of the time the value is 4.,Dr. C. Lightner Fayetteville State University,14,Generating Discrete Random Variables,First we must set up a look up table as follows:Lower Upperlimit limit x0 0.2 00.2 0.55 10.55
22、 0.8 20.8 0.95 30.95 1 4,A lookup table is always set up so that the difference between the upper and lower limit for each x value equals the probability of the x value occurring.,Dr. C. Lightner Fayetteville State University,15,Generating Discrete Random Variables,Then we generate a Random number b
23、etween 0 and 1 in Excel use =RAND() If the random value: Return the value: Falls between 0 and 0.2: 0Is greater than 0.2, but less than 0.55: 1Is greater than 0.55, but less than 0.8: 2Is greater than 0.8, but less than 0.95: 3Is greater than 0.95, but less than 1: 4,Dr. C. Lightner Fayetteville Sta
24、te University,16,EXCEL: Generating Discrete Random Variables,Enter the lookup table in cells C2:E7 as shown below. Then to generate a Random variable according to this probability distribution, enter the following formula in cell A10: =VLOOKUP(RAND(),$C$3:$E$7,3).,You may get a different value in ce
25、ll A10 since your RAND() function returned a different value.,Value to lookup,Lookup table Location,Column of the table return values,Dr. C. Lightner Fayetteville State University,17,Excel Note,Noticed we used $ when referencing the lookup table range. Recall (Excel tutorial) that this will ensure t
26、hat the table location is correctly referenced when copying and pasting formula. Each time any cell is changed in Excel new RAND() values are generated, thus your x values will change. The only way to stop this from happening is to select Tools/Options on the menu bar, then click on the calculations
27、 tab. Under the calculation section, click Manual. Now you must manually press F9 if you want the computer to generate Random values using RAND() function. If you do this, in the future when copying and pasting formulas you must press F9 after pasting formula to generate different values.,See the fi
28、le Generate Random Variables in the Simulation formula for Excel file on generating discrete random variables,Dr. C. Lightner Fayetteville State University,18,Generating Normal Random Variables,In Excel enter the mean for the variable in cell B4. In cell B5 enter the standard deviation. To generate
29、a normal random variable in cell A5 with this Mean and standard deviation, enter this formula: =NORMINV(RAND(),$B$4,$B$5).,Dr. C. Lightner Fayetteville State University,19,Generating Discrete Uniform Random Variables,In Excel enter the smallest value that the random value can assume in cell D3. Ente
30、r the largest value that the random variable can assume in cell D4. To generate a uniform discrete random variable in cell A8, enter this formula: =RANDBETWEEN($D$3,$D$4). UPDATE: This function does not work in Excel XP. If working in XP environment use GENERATING UNIFORM R.V. And round values to th
31、e nearest integer.,You may get a different value in cell A8 since your RAND() function returned a different value.,Dr. C. Lightner Fayetteville State University,20,Generating Uniform Random Variables,In Excel enter the smallest value that the random value can assume in cell D3. Enter the largest val
32、ue that the random variable can assume in cell D4. To generate a uniform random variable in cell A8, enter this formula: =$D3 + RAND()*($D$4-$D$3),You may get a different value in cell A8 since your RAND() function returned a different value. Discrete Uniform returns integers, while uniform returns
33、noninteger values.,Dr. C. Lightner Fayetteville State University,21,Generating Exponential Random Variables,In Excel enter your parameter in cell D5. To generate an exponential variable in cell A6, enter this formula: -$D$5*LN(RAND(). NOTE: For Interarrival times, the parameter is the mean time betw
34、een arrivals. For service time, the parameter is the average service time.,In the example in the spreadsheet, there were 15 arrivals per hour. This means an Arrival comes (on average) once every 4 minutes. The parameter is 4.,Dr. C. Lightner Fayetteville State University,22,More Info Generating Valu
35、es,An Excel file is located in the Simulation Folder entitled gen_rand_num.xls. This file contains files for generating all of the random variables that we have discussed.,Dr. C. Lightner Fayetteville State University,23,Example: Dynogen, Inc.,The price change of shares of Dynogen, Inc. has been obs
36、erved over the past 50 trades. The frequency distribution is as follows:Price Change Number of Trades-3/8 4-1/4 2-1/8 80 20+1/8 10+1/4 3+3/8 2+1/2 1Total = 50 Create a lookup chart to simulate the price changes of Dynogen shares.,Anderson, Sweeney, and Williams,Dr. C. Lightner Fayetteville State Uni
37、versity,24,Example: Dynogen, Inc.,Relative Frequency Distribution provides a discrete probability distribution.Price Change Relative Frequency -3/8 .08 -1/4 .04 -1/8 .16 0 .40 +1/8 .20 +1/4 .06 +3/8 .04 +1/2 .02 TOTAL 1.00,Dr. C. Lightner Fayetteville State University,25,Example: Dynogen, Inc.,Relat
38、ive Frequency Lower Upper Price Change .08 0.00 0.08 -3/8 .04 0.08 0.12 -1/4 .16 0.12 0.28 -1/8 .40 0.28 0.68 0.20 0.68 0.88 +1/8 .06 0.88 0.94 +1/4 .04 0.94 0.98 +3/8 .02 0.98 1.0 +1/2,Dr. C. Lightner Fayetteville State University,26,Example: Dynogen, Inc.,If the current price per share of Dynogen
39、is 23, use the random numbers below to simulate the price per share over the next 10 trades. Use the following stream of random numbers:0.21, 0.84, 0.07, 0.30, 0.94, 0.57, 0.57, 0.19, 0.84, 0.84,Dr. C. Lightner Fayetteville State University,27,Example: Dynogen, Inc.,Simulation Worksheet (Start at $2
40、3)Trade Random Price StockNumber Number Change Price1 0.21 -1/8 22 7/82 0.84 +1/8 233 0.07 -3/8 22 5/84 0.30 0 22 5/85 0.94 +3/8 236 0.57 0 237 0.57 0 238 0.19 -1/8 22 7/89 0.84 +1/8 2310 0.84 +1/8 23 1/8,Dr. C. Lightner Fayetteville State University,28,Set up A Formal Simulation Model,We can simula
41、te the stock price of Dynogen in Excel by following the steps outlined on slide 9. Step 1: We want to measure the stock price. Step 2: There are no Controllable Inputs (CI). The Price change is our only Probabilistic Input (PI). Step 4: Our PI follows the discrete distribution on slide 24. We can cr
42、eate the lookup table on slide 25 in Excel and generate this discrete distribution as shown on slide 19.Step 5: The only math formula that we need to compute our output based upon our PI is:Ending Stock Price = Starting Stock Price + Price change On the next slide we simulate this problem in Excel.,
43、Dr. C. Lightner Fayetteville State University,29,Example: Dynogen, Inc.,Spreadsheet for Stock Price Simulation,Dr. C. Lightner Fayetteville State University,30,Creating the Dynogen INC Spreadsheet in Excel,Our Lookup table was entered in cells A4 through C11. In cell E3 we generated a random variabl
44、e (price change) from this distribution using the formula:VLOOKUP(RAND(),$A$4:$C$11,3). Copy this formula into cells E4 through E12 to generate price changes for all ten trades. In cell F3 we compute the stock price incorporating the change and the original price using the formula:= 23 + $E$3 In cel
45、l F4 compute the stock price based upon the change and the “current stock price” (based upon the last trade) using the following formula:=$F3 + $E$4 Copy the formula in cell F4 into cells F5 through F12 to compute the ending stock price for the remaining trades.,Dr. C. Lightner Fayetteville State Un
46、iversity,31,Example: Dynogen, Inc.,Theoretical Results and Observed ResultsBased on the probability distribution, the expected price change per trade can be calculated by: (.08)(-3/8) + (.04)(-1/4) + (.16)(-1/8) + (.40)(0)+ (.20)(1/8) + (.06)(1/4) + (.04)(3/8) + (.02)(1/2) = +.005The expected price
47、change for 10 trades is (10)(.005) = .05. Hence, the expected stock price after 10 trades is 23 + .05 = 23.05. Compare this ending price with the spreadsheet simulation and “manual” simulation results on the previous slides. We can see that both are relatively close to the expected stock price after 10 trades above.,
