Monte Carlo Methods

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

2. 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),"")

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

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

5. 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 G195:L195 down until the last values are one row above the values in columns E and F.

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

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

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

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

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

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

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

13. Monte Carlo Methods • Focus on the Project: • S35 contains =SUM(L35:L194) (new ending cell in column L)

14. Monte Carlo Methods • Focus on the Project: • Note: the new formula in the new cells in column L need to be changed: • Old formula: (cell L194) =IF(ISNUMBER(F194),DCOUNT($I$34:I193,,Y351:Y352),"")

15. Monte Carlo Methods • Focus on the Project: • Cell L194 stays the same, all new cells afterward change • Cell L195 orginially: =IF(ISNUMBER(F194),DCOUNT($I$34:I193,,Y352:Y353),"") • Cell L195 new: =IF(ISNUMBER(F194),DCOUNT($I$34:I193,,Y353:Y354),"")

16. Monte Carlo Methods • Focus on the Project: • Cell L196 orginially: =IF(ISNUMBER(F194),DCOUNT($I$34:I193,,Y353:Y354),"") • Cell L196 new: =IF(ISNUMBER(F194),DCOUNT($I$34:I193,,Y355:Y356),"") • The remaining values in the next L cell should start with the next odd number and finish with the next even number (i.e., Y357:Y358, then Y359:Y360, etc.)

17. Monte Carlo Methods • Focus on the Project: • Now run the simulation One_ATM (Tools, Macro, Macros)

18. Monte Carlo Methods • Focus on the Project: • A summary of possible answers to five of the six claims appears in cells: • P39 (Max waiting time) • S39 (Max number in queue) • U39 (Mean waiting time) • V39 (Percent delayed) • W39 (Mean number in queue)

19. Monte Carlo Methods • Focus on the Project: • The final claim (percent irritated) must be answered and will be addressed later. • Now modify the worksheet 2 ATMs • Note: the following cells must be changed

20. Monte Carlo Methods • Focus on the Project: • Cell B30 (Mean arrival time) • B195:C195 (Allowable customers – orig. 160 customers) • Formulas from cells E194 and F194 need to be copied down

21. Monte Carlo Methods • Focus on the Project: • Formulas from cells G194 through Q194 need to be copied down • You must fix the formulas in columns G, H and K Column G: =IF(ISNUMBER(F194),VLOOKUP(RANDBETWEEN(1,7634),Data!$G$45:Data!$H$7678,2),"") Column H: =IF(ISNUMBER(F194),DCOUNT($J$34:J193,,AG351:AG352),"") Column K: =IF(ISNUMBER(F194),DCOUNT($M$34:M193,,AJ351:AJ352),"")

22. Monte Carlo Methods • Focus on the Project: • Change all formulas in cells S35 through Z35 • In cell S35: =IF(MAX(E35:E195)=161,"Overflow",MAX(E35:E195)) • In cell T35: =SUM(N35:N194)

23. Monte Carlo Methods • Focus on the Project: • In cell U35: =MAX(N35:N194) • In cell V35: =COUNTIF(O35:O194,"yes") • In cell W35: =SUM(P35:P194)

24. Monte Carlo Methods • Focus on the Project: • In cell X35: =MAX(P35:P194) • In cell Y35: =SUM(Q35:Q194) • In cell Z35: =MAX(Q35:Q194)

25. Monte Carlo Methods • Focus on the Project: • Copy down extra cells after AG351:AG352 and AJ351:AJ352 • Fix the formulas in the even cells (354, 356, 358, etc.) • Run the macro Two_ATMs

26. Monte Carlo Methods • Focus on the Project: • A summary of possible answers to five of the six claims appears in cells: • U39 (Max waiting time) • Z39 (Max present) • AB39 (Mean waiting time) • AC39 (Percent delayed) • AD39 (Mean number in queue)

27. Monte Carlo Methods • Focus on the Project: • The final claim (percent irritated) must be answered and will be addressed later. • Now modify the worksheet 3 ATMs • Note: the following cells must be changed

28. Monte Carlo Methods • Focus on the Project: • Cell B30 (Mean arrival time) • B195:C195 (Allowable customers – orig. 160 customers) • Formulas from cells E194 and F194 need to be copied down

29. Monte Carlo Methods • Focus on the Project: • Formulas from cells G194 through T194 need to be copied down • You must fix the formulas in columns G, H, K, and N Column G: =IF(ISNUMBER(F194),VLOOKUP(RANDBETWEEN(1,7634),Data!$G$45:Data!$H$7678,2),"") Column H: =IF(ISNUMBER(F194),DCOUNT($J$34:J193,,AJ351:AJ352),"")

30. Monte Carlo Methods • Focus on the Project: Column K: =IF(ISNUMBER(F194),DCOUNT($M$34:M193,,AM351:AM352),"") Column N: =IF(ISNUMBER(F194),DCOUNT($P$34:P193,,AP351:AP352),"")

31. Monte Carlo Methods • Focus on the Project: • Change all formulas in cells V35 through AC35 • In cell V35: =IF(MAX(E35:E195)=161,"Overflow",MAX(E35:E195)) • In cell W35: =SUM(Q35:Q194)

32. Monte Carlo Methods • Focus on the Project: • In cell X35: =MAX(Q35:Q194) • In cell Y35: =COUNTIF(R35:R194,"yes") • In cell Z35: =SUM(S35:S194)

33. Monte Carlo Methods • Focus on the Project: • In cell AA35: =MAX(S35:S194) • In cell AB35: =SUM(T35:T194) • In cell AC35: =MAX(T35:T194)

34. Monte Carlo Methods • Focus on the Project: • Copy down extra cells after AJ351:AJ352, AM351:AM352, and AP351:AP352 • Fix the formulas in the even cells (354, 356, 358, etc.) • Run the macro Three_ATMs

35. Monte Carlo Methods • Focus on the Project: • A summary of possible answers to five of the six claims appears in cells: • X39 (Max waiting time) • AC39 (Max present) • AE39 (Mean waiting time) • AF39 (Percent delayed) • AG39 (Mean number in queue)

36. Monte Carlo Methods • Focus on the Project: • The final claim (percent irritated) must be answered and will be addressed later. • Now modify the worksheet 3 ATMs Serpentine • Note: the following cells must be changed

37. Monte Carlo Methods • Focus on the Project: • Cell B30 (Mean arrival time) • B195:C195 (Allowable customers – orig. 160 customers) • Formulas from cells E194 and F194 need to be copied down

38. Monte Carlo Methods • Focus on the Project: • Formulas from cells G194 through T194 need to be copied down • You must fix the formulas in columns G, H, K, and N Column G: =IF(ISNUMBER(F194),VLOOKUP(RANDBETWEEN(1,7634),Data!$G$45:Data!$H$7678,2),"") Column H: =IF(ISNUMBER(F194),DCOUNT($J$34:J193,,AJ351:AJ352),"")

39. Monte Carlo Methods • Focus on the Project: Column K: =IF(ISNUMBER(F194),DCOUNT($M$34:M193,,AM351:AM352),"") Column N: =IF(ISNUMBER(F194),DCOUNT($P$34:P193,,AP351:AP352),"")

40. Monte Carlo Methods • Focus on the Project: • Change all formulas in cells V35 through AC35 • In cell V35: =IF(MAX(E35:E195)=161,"Overflow",MAX(E35:E195)) • In cell W35: =SUM(Q35:Q194)

41. Monte Carlo Methods • Focus on the Project: • In cell X35: =MAX(Q35:Q194) • In cell Y35: =COUNTIF(R35:R194,"yes") • In cell Z35: =SUM(S35:S194)

42. Monte Carlo Methods • Focus on the Project: • In cell AA35: =MAX(S35:S194) • In cell AB35: =SUM(T35:T194) • In cell AC35: =MAX(T35:T194)

43. Monte Carlo Methods • Focus on the Project: • Copy down extra cells after AJ351:AJ352, AM351:AM352, and AP351:AP352 • Fix the formulas in the even cells (354, 356, 358, etc.) • Run the macro Three_ATMs_Serpentine

44. Monte Carlo Methods • Focus on the Project: • A summary of possible answers to five of the six claims appears in cells: • X39 (Max waiting time) • AC39 (Max present) • AE39 (Mean waiting time) • AF39 (Percent delayed) • AG39 (Mean number in queue)

45. Monte Carlo Methods • Focus on the Project: • The final claim (percent irritated) must be answered and will be addressed later. • Save this document in one folder (do not change the name from Queue Focus.xls)

46. Monte Carlo Methods • Focus on the Project: • Summary (9 am)

47. Monte Carlo Methods • Focus on the Project: • Once you have saved the file in a folder, create a new folder for your 9 pm data • Save the Queue Focus.xls file in your new folder

48. Monte Carlo Methods • Focus on the Project: • Modify the Queue Focus.xls file for your 9 pm data • This only requires a change of the cell B31 (mean arrival) in the worksheet 1 ATM and a change of the cell B30 (mean arrival) in the worksheets 2 ATMs, 3 ATMs, and 3 ATMs Serpentine

49. Monte Carlo Methods • Focus on the Project: • Summary (9 pm)