Monte Carlo Methods

1. Monte Carlo Methods • Focus on the Project: • Now that there are two folders (9am and 9pm) that contain a Queue Focus.xls file, we will create two more folders • One folder should contain the Queue Focus.xls file using 9am data and one should have the Queue Focus.xls file using the 9pm data (we can call these folders “9am irritated” and “9pm irritated”

2. Monte Carlo Methods • Focus on the Project: • For the 9am irritated claim, we will open the third copy of Queue Focus.xls (in 9am irritated folder) • We will find the percent irritated value for each of the four ATM models

3. Monte Carlo Methods • Focus on the Project: • In the sheet 1 ATM, column K contains Delayed information • This will be changed to find Irritated information

4. Monte Carlo Methods • Focus on the Project: • Cell K35 contains the following formula: =IF(ISNUMBER(F35),IF(J35>$D$31,"yes","no"),"") • Change J35 to L35 and copy the formula to the end of the table

5. Monte Carlo Methods • Focus on the Project: • In addition, the value in cell D31 must be changed from 5 to 3 • Recall, a customer is irritated if there are more than 3 customers waiting to use an ATM

6. Monte Carlo Methods • Focus on the Project: • Run the macro One_ATM • The solution will appear in cell V39 (Percent Delayed label)

7. Monte Carlo Methods • Focus on the Project: • In the sheet 2 ATMs, column O contains Delayed information • This will be changed to find Irritated information

8. Monte Carlo Methods • Focus on the Project: • Cell O35 contains the following formula: =IF(ISNUMBER(F35),IF(N35>$D$30,"yes","no"),"") • Change N35 to P35 and copy the formula to the end of the table

9. Monte Carlo Methods • Focus on the Project: • In addition, the value in cell D30 must be changed from 5 to 3 • Recall, a customer is irritated if there are more than 3 customers waiting to use an ATM

10. Monte Carlo Methods • Focus on the Project: • Run the macro Two_ATMs • The solution will appear in cell AC39 (Percent Delayed label)

11. Monte Carlo Methods • Focus on the Project: • In the sheet 3 ATMs, column R contains Delayed information • This will be changed to find Irritated information

12. Monte Carlo Methods • Focus on the Project: • Cell R35 contains the following formula: =IF(ISNUMBER(F35),IF(Q35>$D$30,"yes","no"),"") • Change Q35 to S35 and copy the formula to the end of the table

13. Monte Carlo Methods • Focus on the Project: • In addition, the value in cell D30 must be changed from 5 to 3 • Recall, a customer is irritated if there are more than 3 customers waiting to use an ATM

14. Monte Carlo Methods • Focus on the Project: • Run the macro Three_ATMs • The solution will appear in cell AF39 (Percent Delayed label)

15. Monte Carlo Methods • Focus on the Project: • In the sheet 3 ATMs Serpentine, column R contains Delayed information • This will be changed to find Irritated information

16. Monte Carlo Methods • Focus on the Project: • Cell R35 contains the following formula: =IF(ISNUMBER(F35),IF(Q35>$D$30,"yes","no"),"") • Change Q35 to S35 and copy the formula to the end of the table

17. Monte Carlo Methods • Focus on the Project: • In addition, the value in cell D30 must be changed from 5 to 9 • We use 9 since 3 ATM’s with a serpentine queue should move 3 times faster than a single queue behind a single ATM • Recall, a customer is irritated if there are more than 3 customers waiting to use an ATM

18. Monte Carlo Methods • Focus on the Project: • Run the macro Three_ATMs_Serpentine • The solution will appear in cell AF39 (Percent Delayed label)

19. Monte Carlo Methods • Focus on the Project: • A summary of the findings appears below: Claim is that at most 3.0% will be irritated (3 ATMs)

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

21. Monte Carlo Methods • Focus on the Project: • Save the new Queue Focus.xls file in your 9am irritated folder • Do all of the same calculations for 9pm irritated

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

23. Monte Carlo Methods • Focus on the Project: • Final steps are to determine the cost of the gift certificate program as is and to determine the cost if 70% of customers who are eligible use the gift certificates

24. Monte Carlo Methods • Focus on the Project: • During the 9 am hour, the mean time between arrivals was 0.48 (for my data) • This gives customers per hour

25. Monte Carlo Methods • Focus on the Project: • It is estimated (from simulation) that 1.0% of customers will be delayed using 3 ATMs Serpentine • This gives us delayed customers per hour

26. Monte Carlo Methods • Focus on the Project: • According to my sample data, the gift certificates are worth $35 • Each hour would cost in gift certificates • The maintenance cost is $21 per hour

27. Monte Carlo Methods • Focus on the Project: • The total cost would be $43.75 + $21 = $64.75 • This is the cost if all customers who are eligible for gift certificates actually use them

28. Monte Carlo Methods • Focus on the Project: • However, some customers will not use the gift certificate program • If 70% of those eligible to use the gift certificates actually use them, then the hourly cost of gift certificates changes from $43.75 to $43.75(0.70) = $30.63

29. Monte Carlo Methods • Focus on the Project: • The overall cost would be $30.63 + $21 = $51.63 • This is the cost if 70% of eligible customers used their gift certificate