Monte Carlo Methods A Monte Carlo simulation creates
- Slides: 42
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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: 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: 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: 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 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) 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 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 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 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 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 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 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, 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 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 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 • The solution will appear in cell V 39 (Percent Delayed label)
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 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. 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 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 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, 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) 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, 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 $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 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 $30. 63 + $21 = $51. 63 • This is the cost if 70% of eligible customers used their gift certificate
- Concezio bozzi
- Monte carlo simulation dice roll matlab
- Monte carlo simulation advantages and disadvantages ppt
- Monte carlo simulation alternatives
- 1:77:1
- Monte carlo simulation matlab
- Monte carlo simulation
- Monte carlo simulation freeware
- Monte carlo simulation
- Monte carlo simulation in minitab
- Monte carlo search tree
- Eric veach thesis
- Contoh soal pembangkit bilangan acak
- Inverse monte carlo
- Bushy hair
- Monte carlo localization python
- Contoh simulasi monte carlo
- Simulasi monte carlo
- Quantum monte carlo
- Monte carlo search tree
- Monte carlo localization for mobile robots
- Distribucion binomial excel
- Monte carlo truth
- Villa monte carlo
- Monte carlo radiation transport
- Diagrammatic monte carlo
- Monte carlo vs temporal difference
- The monte carlo
- Monte carlo optimization
- Continuous time monte carlo
- Monte carlo szimuláció példa
- Count of monte carlo
- Monte carlo exercise
- Monte carlo data quality
- Metoda monte carlo algorytm
- Monte carlo tree search tutorial
- Kinetic monte carlo python
- Omonte carlo
- Metoda monte carlo
- Mcmc tutorial
- Ciri distribusi binomial
- Stanislaw ulam
- Monte carlo exercise