Monte Carlo MethodsSlide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26Slide 27Slide 28Slide 29Slide 30Slide 31Slide 32Slide 33Slide 34Slide 35Slide 36Slide 37Slide 38Slide 39Slide 40Slide 41Slide 42Slide 43Slide 44Slide 45Slide 46Slide 47Slide 48Slide 49Monte 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.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),"")Monte Carlo Methods•Focus on the Project:•Change the numbers indicated to match your data•Copy your new formula into cells G36:G194Monte 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)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.Monte Carlo Methods•Focus on the Project:•The finished columns E through L should look like:•Note: columnsE and F have oneextra cellMonte 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 timesMonte 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)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)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)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)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)Monte Carlo Methods•Focus on the Project:•S35 contains =SUM(L35:L194) (new ending cell in column L)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),"")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),"")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.)Monte Carlo Methods•Focus on the Project:•Now run the simulation One_ATM (Tools, Macro, Macros)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)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 changedMonte 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 downMonte 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 KColumn 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),"")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)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)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)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_ATMsMonte 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)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 changedMonte 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 downMonte 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 NColumn G:=IF(ISNUMBER(F194),VLOOKUP(RANDBETWEEN(1,7634),Data!$G$45:Data!$H$7678,2),"")Column
View Full Document
Unlocking...