FIGURE 13.22 A spreadsheet model for applying computer simulation to the Trans- continental Airlines overbooking problem

Business, Finance, Economics, Accounting, Operations Management, Computer Science, Electrical Engineering, Mechanical Engineering, Civil Engineering, Chemical Engineering, Algebra, Precalculus, Statistics and Probabilty, Advanced Math, Physics, Chemistry, Biology, Nursing, Psychology, Certifications, Tests, Prep, and more.
Post Reply
answerhappygod
Site Admin
Posts: 899603
Joined: Mon Aug 02, 2021 8:13 am

FIGURE 13.22 A spreadsheet model for applying computer simulation to the Trans- continental Airlines overbooking problem

Post by answerhappygod »

Figure 13 22 A Spreadsheet Model For Applying Computer Simulation To The Trans Continental Airlines Overbooking Problem 1
Figure 13 22 A Spreadsheet Model For Applying Computer Simulation To The Trans Continental Airlines Overbooking Problem 1 (422.62 KiB) Viewed 11 times
Figure 13 22 A Spreadsheet Model For Applying Computer Simulation To The Trans Continental Airlines Overbooking Problem 2
Figure 13 22 A Spreadsheet Model For Applying Computer Simulation To The Trans Continental Airlines Overbooking Problem 2 (518.4 KiB) Viewed 11 times
FIGURE 13.22 A spreadsheet model for applying computer simulation to the Trans- continental Airlines overbooking problem. The uncertain variable cells are Simulated- TicketDemand (C10) and NumberThatShow (C17). The results cells are Profit (F23), Number- OfFilledSeats (C20), and NumberDenied Boarding (C21). The statistic cells are MeanFilledSeats (C23), MeanDenied- Boarding (C24), and MeanProfit (C25). The decision variable is Reservations To-Accept (C13). A B C E Transcontinental Airlines overbooking Data Available Seats 150 Fixed Cost $30,000 Avg. Fare / Seat $300 7 Cost of Bumping $450 8 9 Mean 195 10 Ticket Demand 179.74 11 Demand (rounded) 180 12 13 Reservations to Accept 190 14 15 16 Tickets Purchased 180 17 Number That Show 153 18 19 20 Number of Filled Seats 150 Ticket Revenue 21 Number Denied Boarding 3 Bumping Cost 22 Fixed Cost 23 142.275 Profit Mean Filled Seats Mean Denied Boarding 24 2.038 25 Mean Profit $11,765 B C Range Name Cell 11 Demand (rounded) =ROUND(Simulated TicketDemand,0) AvailableSeats C4 C6 AverageFare Bumping Cost CostOfBumping E Tickets F21 C7 15 C11 16 Purchased Demand FixedCost C5 =MIN(Demand,Reservations To Accept) MeanDenied Boarding C24 MeanFilled Seats C23 MeanProfit C25 NumberDenied Boarding C21 NumberOfFilledSeats C20 NumberThatShow C17 F17 Probability ToShowUp Profit F23 ReservationsToAccept C13 SimulatedTicketDemand C10 TicketRevenue F20 TicketsPurchased E17 B с 20 Number of Filled Seats =MIN(AvailableSeats,NumberThatShow) + PsiOutput() Number Denied Boarding =MAX(0,NumberThatShow - AvailableSeats) + PsiOutput() 21 22 23 Mean Filled Seats -PsiMean(C20) Mean Denied Boarding =PsiMean(C21) 24 25 Mean Profit=PsiMean(F23) E F 20 21 Ticket Revenue =AverageFare*NumberOfFilledSeats Bumping Cost CostOfBumping NumberDeniedBoarding Fixed Cost =FixedCost 22 23 Profit-TicketRevenue - BumpingCost - FixedCost + PsiOutput() 1 2 3 4 5 6 17 D Normal Binomial F Standard Dev. 30 Probability to Show Up 80% $45,000 $1,350 $30,000 $13,650
A B C D E 1 Simulation of Think-Big Development Co. Problem 2 3 Project Simulated 4 Cash Flow Hotel Project: (Smillions) -80 Construction Costs: Year () Year 1 -79.057 Year 2 -80.343 Year 3 -73.063 Revenue per Share Year 4 14.059 Year 5 29.746 Year 6 81.373 Selling Price per Share Year 7 395.247 Shopping Center Project Construction Costs: Year 0 -90 Year 1 -42.329 Year 2 -15.124 Year 3 -54.653 Revenue per Share Year 4 21.923 Year 5 10.122 Year 6 14.780 Selling Price per Share Year 7 494.378 Think-Big's Simulated Cash Flow ($millions) Year ( -24.999 Year 1 -18.594 Year 2 -15.239 Year 3 -19.221 Year 4 5.194 Year 5 6.235 Year 6 15.364 Year 7 130,029 Net Present Value (Smillions) 13.879 MeanNPV (Smillions) 18.120 D 25 Think Big's Simulated Cash Flow 26 27 (Smillions) 28 29 20 30 31 Year 0 Hotel Share D6+ShoppingCenterShare D16 Year !=HotelShare D7+ShoppingCenterShare D17 Year 2=HotelShare D8+Shopping CenterShare D18 Year 3-HotelShare D9+Shopping CenterShare" D19 Year 4-HotelShare D10+Shopping Center Share D20) Year 5-HotelShare D11+Shopping Center Share D21 Year 6 HotelShare D12+Shopping Center Share D22 Year 7-HotelShore D13+Shopping Center Share D23 32 33 34 JA 35 26 36 37 Net Present Value (Smillions) CashFlow Year0+NPV CostOfCapital,CashFlow Year ITo7)+PsiOutput 38 39 Mean NPV (Smillions) PsiMean(D37) 5 6 7 8. 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 C G 5 Normal -80 Normal -80 10 15 20 Normal -70 Normal 30 Normal 40 20 20 Normal 50 Uniform 200 844 Normal -50 3 Normal -20 Normal -60 5 10 15 15 Normal 15 Normal 25 Normal 40 15 Uniform 160 615 Hotel Shopping Center Cost of Capital F H C D Project Simulated Cash Flow (Smillions) Year 0-80 Year PsiNormal (F7,G7) Year 2 PsiNormal (F8,G8) Year 3-PsiNormal (F9,09) Year 4-PsiNormal(F10,G10) Year 3-PsiNormal FII.GID) Year 6-PsiNormal(F12,G12) Year 7-PsiUniform(F13,G13) Year 0-90 Year 1-PsiNormal(F17,G17) Year 2-PsiNormal (F18.G18) Year 3-PsiNormal(F19.G19) Year 4-PsiNormal(F20,G20) Year 5-PsiNormal(F21,G21) Year 6-PsiNormal (F22,G22) Year 7-PsiUniform(F23, G23) 3 4 5 6 7 (mean, st. dev.) 8 (mean, st. dev.) (mean, st. dev.) 9 10 11 12 (mean, st. dev.) (mean, st. dev.) 13 14 (mean, st. dev.) (lower upper) 15 16 17 18 19 20 (mean, st. dev) 21 (mean, st. dev.) 22 (mean, st. dev.) 23 (mean, st. dev.) (mean, st. dev.) (mean, st. dev.) (lower.upper) Share 16.50% 13.11% 10% Range Name Cells. CashFlow Year D28 Cashflow Year To7 D29:D35 CostOlCapital H31 HotelShare 1128 MeinNPV D39 NetPresentValue 1337 Shopping CenterShare H24 B
FIGURE 13.23 A normal distribution with parameters F7 (=-80) and G7 (-5) is being entered into the first uncertain variable cell D7 in the spreadsheet model in Figure 13.22. $D$7 D CD 0 PDF CDF Reverse CDF Parameters -71.776 Uncertain Variable Address 15.00%| $D$7 -PsiNormal(F7,G7) Formula Distribution Normal Alternate Form No Parameters mean F7 stdev Address The address of the uncertain variable. Save Cancel 0.08 0.06 0.04 0.02 0 -105.000 -88.224 -95.000 -100.000 190.00% -85.000 -90.000. -75.000 -70.000 -80.000 15.00% H -65.000 -55.000 -60.000 0 X ➤A
FIGURE 13.24 The frequency chart and statistics table that summarize the results of running the simulation model in Figure 13.22 for the Think- Big Development Co. financial risk analysis problem. The Likelihood box in the statistics table reveals that 81 percent of the trials resulted in a positive net present value. NetPresentvalue Frequency Cumulative Frequency | Reverse Cumulative Frequency Sensitivity Scatter Plots Statistics Simulation Results - NetPresentValue Chart Statistics Lower Cutoff Likelihood 0 0.000 81% 19.00% 181.00% Upper Cutoff 0.12 120 Statistics 18.120 Mean 0.10+ 100 Standard Deviation 18.960 Variance 359.488 80 0.08 Skewness -0.0243537 Kurtosis -0.69472 0.06 60 Mode 18.307 Minimum -28.155 0.04 40 Maximum 62.122 0.02 20 Range 90.277 Likelihood 0.00 0 The probability that this uncertain function is between the specified lower and upper cutoffs. -30.000 -10.000 10.000 30.000 50.000 70.000 -20.000 0.000 20.000 40.000 60.000 Save Cancel Relative Probability Frequency ▸
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!
Top
Post Reply

Return to “Archived Questions & Answers”