Monte Carlo Methods A Monte Carlo simulation creates

  • Slides: 42
Download presentation
Monte Carlo Methods • A Monte Carlo simulation creates samples from a known distribution

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: X f. X(x) 0 0. 10 1 0. 90

Monte Carlo Methods • If you tossed the coin, the expected value would be

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 • The average of the sample is 0. 7 (close, but not the same as the expected average)

Monte Carlo Methods • Another type of simulation can be run using the RAND

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

Monte Carlo Methods • The outputs will include as many decimal places as Excel

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

Monte Carlo Methods • The IF function in Excel determines a value based upon

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

Monte Carlo Methods • Ex. The situation where heads occurs 90% of the time

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

Monte Carlo Methods • If we have a variable with a known distribution, we

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.

Monte Carlo Methods • For example, if we have an exponential function with a

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( )

Monte Carlo Methods • Focus on the Project: • Enter mean time between arrivals

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

Monte Carlo Methods • Focus on the Project: • The formula in cell G

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

Monte Carlo Methods • Focus on the Project: • Change the numbers indicated to

Monte Carlo Methods • Focus on the Project: • Change the numbers indicated to match your data • Copy your new formula into cells G 36: G 194

Monte Carlo Methods • Focus on the Project: • Note that my simulation (from

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

Monte Carlo Methods • Focus on the Project: • Drag the information in cells

Monte Carlo Methods • Focus on the Project: • Drag the information in cells E 195: F 195 down until the last values are at the same row as the values in columns B and C. • Drag the information in cells G 194: L 195 down until the last values are one row above the values in columns E and F.

Monte Carlo Methods • Focus on the Project: • The finished columns E through

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

Monte Carlo Methods • • Focus on the Project: The formula in column L

Monte Carlo Methods • • Focus on the Project: The formula in column L should be written in the following way: In cell L 36 write: =IF(ISNUMBER(F 36), COUNTIF($I$35: I 35, ">"&F 36 ), "") This formula can then be filled down column L without any additional editing.

Monte Carlo Methods • Focus on the Project: • Finally, we need to modify

Monte Carlo Methods • Focus on the Project: • Finally, we need to modify the formulas in cells N 35: S 35 • N 35 contains (# of customers plus 1) =IF(MAX(E 35: E 195)=161, "Overflow", MAX(E 35: E 195)) (new ending cell in column E)

Monte Carlo Methods • Focus on the Project: • O 35 contains =SUM(J 35:

Monte Carlo Methods • Focus on the Project: • O 35 contains =SUM(J 35: J 194) (new ending cell in column J) • P 35 contains =MAX(J 35: J 194) (new ending cell in column J)

Monte Carlo Methods • Focus on the Project: • Q 35 contains =COUNTIF(K 35:

Monte Carlo Methods • Focus on the Project: • Q 35 contains =COUNTIF(K 35: K 194, ”yes”) (new ending cell in column K) • R 35 contains =SUM(L 35: L 194) (new ending cell in column L)

Monte Carlo Methods • Focus on the Project: • S 35 contains =SUM(L 35:

Monte Carlo Methods • Focus on the Project: • S 35 contains =SUM(L 35: L 194) (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)

Monte Carlo Methods • Focus on the Project: • A summary of possible answers

Monte Carlo Methods • Focus on the Project: • A summary of possible answers to five of the six claims appears in cells: • P 39 (Max waiting time) • S 39 (Max number in queue) • U 39 (Mean waiting time) • V 39 (Percent delayed) • W 39 (Mean number in queue)

Monte Carlo Methods • Focus on the Project: • The final claim (percent irritated)

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)

Monte Carlo Methods • Focus on the Project: • Summary (9 am) Mean Wait

Monte Carlo Methods • Focus on the Project: • Summary (9 am) Mean Wait Time Max Wait Time Percent Delayed Mean # in Queue Max # in Queue Percent Irritated 1 ATM 43. 73 135. 60 93. 4% 37. 6 112 TBD 2 ATMs 7. 95 47. 94 60. 7% 7. 1 67 TBD 3 ATMs 0. 91 15. 05 1. 6% 1. 0 29 TBD 3 ATMs Serp 0. 80 12. 15 1. 0% 2. 3 31 TBD Claims 1. 2 9. 5 3. 9% 8. 5 30 3. 0%

Monte Carlo Methods • Focus on the Project: • Once you have saved the

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

Monte Carlo Methods • Focus on the Project: • Modify the Queue Focus. xls

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 B 31 (mean arrival) in the worksheet 1 ATM.

Monte Carlo Methods • Focus on the Project: • Summary (9 pm) Mean Wait

Monte Carlo Methods • Focus on the Project: • Summary (9 pm) Mean Wait Time Max Wait Time Percent Delayed Mean # in Queue Max # in Queue Percent Irritated 1 ATM 0. 79 12. 03 1. 5% 0. 9 10 TBD 2 ATMs 0. 08 5. 98 0. 0% 0. 5 6 TBD 3 ATMs 0. 01 7. 60 0. 0% 0. 0 6 TBD 3 ATMs Serp 0. 01 1. 91 0. 0% 0. 0 6 TBD Claims 1. 2 9. 5 3. 9% 8. 5 30 3. 0%

Monte Carlo Methods • Focus on the Project: • Now that there are two

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

Monte Carlo Methods • Focus on the Project: • For the 9 am irritated

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

Monte Carlo Methods • Focus on the Project: • In the sheet 1 ATM,

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

Monte Carlo Methods • Focus on the Project: • Cell K 35 contains the

Monte Carlo Methods • Focus on the Project: • Cell K 35 contains the following formula: =IF(ISNUMBER(F 35), IF(J 35>$D$31, "yes", "no"), "") • Change J 35 to L 35 and copy the formula to the end of the table

Monte Carlo Methods • Focus on the Project: • In addition, the value in

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

Monte Carlo Methods • Focus on the Project: • Run the macro One_ATM •

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

Monte Carlo Methods • Focus on the Project: • A summary of the findings

Monte Carlo Methods • Focus on the Project: • A summary of the findings appears below: Percent Irritated 1 ATM 95. 1% 2 ATMs 75. 4% 3 ATMs 2. 7% 3 ATMs Serp 2. 5% Claim is that at most 3. 0% will be irritated (3 ATMs)

Monte Carlo Methods • Focus on the Project: • Summary (9 am) Mean Wait

Monte Carlo Methods • Focus on the Project: • Summary (9 am) Mean Wait Time Max Wait Time Percent Delayed Mean # in Queue Max # in Queue Percent Irritated 1 ATM 43. 73 135. 60 93. 4% 37. 6 112 95. 1% 2 ATMs 7. 95 47. 94 60. 7% 7. 1 67 75. 4% 3 ATMs 0. 91 15. 05 1. 6% 1. 0 29 2. 7% 3 ATMs Serp 0. 80 12. 15 1. 0% 2. 3 31 2. 5% Claims 1. 2 9. 5 3. 9% 8. 5 30 3. 0%

Monte Carlo Methods • Focus on the Project: • Save the new Queue Focus.

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

Monte Carlo Methods • Focus on the Project: • Summary (9 pm) Mean Wait

Monte Carlo Methods • Focus on the Project: • Summary (9 pm) Mean Wait Time Max Wait Time Percent Delayed Mean # in Queue Max # in Queue Percent Irritated 1 ATM 0. 79 12. 03 1. 5% 0. 9 10 3. 2% 2 ATMs 0. 08 5. 98 0. 0% 0. 5 6 0. 0% 3 ATMs 0. 01 7. 60 0. 0% 0. 0 6 0. 0% 3 ATMs Serp 0. 01 1. 91 0. 0% 0. 0 6 0. 0% Claims 1. 2 9. 5 3. 9% 8. 5 30 3. 0%

Monte Carlo Methods • Focus on the Project: • Final steps are to determine

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

Monte Carlo Methods • Focus on the Project: • During the 9 am hour,

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

Monte Carlo Methods • Focus on the Project: • It is estimated (from simulation)

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 hour delayed customers per

Monte Carlo Methods • Focus on the Project: • According to my sample data,

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

Monte Carlo Methods • Focus on the Project: • The total cost would be

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

Monte Carlo Methods • Focus on the Project: • However, some customers will not

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

Monte Carlo Methods • Focus on the Project: • The overall cost would be

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