Monte Carlo Methods

1. Monte Carlo Methods • A Monte Carlo simulation creates samples from a known distribution • For example, if you know that a coin is weighted so that heads will occur 90% of the time, then you might assign the following values:

2. Monte Carlo Methods • If you tossed the coin, the expected value would be 0.9 • However, a sample simulation might yield the results 1, 1, 1, 0, 1, 1, 0, 1, 0, 1 • The average of the sample is 0.7 (close, but not the same as the expected average)

3. Monte Carlo Methods • Another type of simulation can be run using the RAND function • RAND chooses a random number between 0 and 1 • Entered as RAND( ) • Used for continuous random variable simulations

4. Monte Carlo Methods • The outputs will include as many decimal places as Excel can keep • This is used to model situations where you have a continuous random variable • There would be an infinite number of possible outcomes

5. Monte Carlo Methods • The IF function in Excel determines a value based upon a logical TRUE/FALSE scenario • If math formula is true, then one outcome happens If math formula is false, then another outcome happens

6. Monte Carlo Methods • Ex. The situation where heads occurs 90% of the time can be simulated by using RAND and IF functions. =IF(RAND()<=0.90,1,0) • We can use COUNTIF to count the number of times an outcome occurs

7. Monte Carlo Methods • If we have a variable with a known distribution, we may construct the c.d.f. function • Once we have this, a simulation can be run from the inverse of the c.d.f.

8. Monte Carlo Methods • For example, if we have an exponential function with a known value • The inverse function is • Here x would be replaced by RAND( )

9. Monte Carlo Methods • Focus on the Project: • Enter mean time between arrivals for variable A in cell B31 of the sheet 1 ATM for the Excel file Queue Focus.xls.

10. Monte Carlo Methods • Focus on the Project: • The formula in cell G35 of the sheet 1 ATM for the Excel file Queue Focus.xls needs to be changed • Original: =IF(ISNUMBER(F35),VLOOKUP(RANDBETWEEN(1,7634), Data!$G$45:Data!$H$7678,2),"")

11. Monte Carlo Methods • Focus on the Project: • Change the numbers indicated to match your data • Copy your new formula into cells G36:G194

12. Monte Carlo Methods • Focus on the Project: • Note that my simulation (from my posted SampleData.xls) must accommodate 170 customers • Drag the information in cells B195:C195 down until the last value in column B is one more than the number of customers (for me, 171)

13. Monte Carlo Methods • Focus on the Project: • Drag the information in cells E195:F195 down until the last values are at the same row as the values in columns B and C. • Drag the information in cells G194:L195 down until the last values are one row above the values in columns E and F.

14. Monte Carlo Methods • Focus on the Project: • The finished columns E through L should look like: • Note: columns E and F have one extra cell

15. Monte Carlo Methods • Focus on the Project: • The formulas in column L need a special modification • The formulas in cell L193 is: =IF(ISNUMBER(F193),DCOUNT($I$34:I192,,Y349:Y350),"") • The formula in cell L194 is: =IF(ISNUMBER(F194),DCOUNT($I$34:I193,,Y351:Y352),"") • Notice as we go down 1 row, Y349:Y350 becomes Y351:Y352

16. Monte Carlo Methods • Focus on the Project: • You must modify the formulas according to this pattern • So for cell L195, the formulas would be: =IF(ISNUMBER(F195),DCOUNT($I$34:I194,,Y353:Y354),"") • Continue this pattern for the extra rows you added . . . • In my example, I added 10 rows in column L, so my last modification appears in cell L204: =IF(ISNUMBER(F204),DCOUNT($I$34:I203,,Y371:Y372),"")

17. Monte Carlo Methods • Focus on the Project: • Cells Y351 and Y352 should be copied and pasted several times • My simulation must accommodate 170 customers (compared to 160 from the original class file) • This means I must copy and paste Y351 and Y352 ten times

18. Monte Carlo Methods • Focus on the Project: • Cell Y351 is blank, so new cells Y353, Y355, Y357, etc. will also be blank • Cell Y352 contained the formula =($F$194<=I35)

19. Monte Carlo Methods • Focus on the Project: • Cell Y352 contained the formula =($F$194<=I35) • Cell Y354 should have the formula =($F$195<=I35) • Cell Y356 should have the formula =($F$196<=I35) • Cell Y358 should have the formula =($F$197<=I35) • And so on … (Be careful, you must carefully change all of the new formulas)

20. Monte Carlo Methods • Focus on the Project: • Finally, we need to modify the formulas in cells N35:S35 • N35 contains (# of customers plus 1) =IF(MAX(E35:E195)=161,"Overflow",MAX(E35:E195)) (new ending cell in column E)

21. Monte Carlo Methods • Focus on the Project: • O35 contains =SUM(J35:J194) (new ending cell in column J) • P35 contains =MAX(J35:J194) (new ending cell in column J)

22. Monte Carlo Methods • Focus on the Project: • Q35 contains =COUNTIF(K35:K194,”yes”) (new ending cell in column K) • R35 contains =SUM(L35:L194) (new ending cell in column L)

23. Monte Carlo Methods • Focus on the Project: • S35 contains =SUM(L35:L194) (new ending cell in column L) • Finally, run the Macro One_ATM • Save the results in a folder (do not change the name of the Excel file Queue Focus.xls)