1 Instructions Respon Create an 'Inputs' section with cells for the following inputs (you will fill in the values for these inputs later): Plane Capacity Tickets Sold - Show-up rate - Price per ticket Voucher Intercept 2 Voucher Slope IN/A Fill in the follwing input values: - Plane capacity: 240 - Tickets sold: 260 Show-up rate: 95% IN/A The airline must offer travel vouchers to overbooked passengers to conving them to move to other flights - and this cuts into the airline's revenue. These vouchers each become more expensive as the number of overbooked passengers increases (since the airline has to entice more passengers to accept them). Voucher Intercept and Voucher Slope are the intercept and slope for a regression model that predicts the cost of each travel voucher based on the number of overbooked passengers. Use the data on the Voucher Data sheet to estimate the intercept and slope of a regression predicting Voucher Cost from Number of Overbooked Passengers (you can use the Analysis Toolpak or the SLOPE / INTERCEPT functions). Type these values into the 4 Voucher Slope and Voucher Intercept input cells. N/A Tickets for some flight are, by random chance, more expensive than tickets for other flights. So, we will treat the average price per ticket of this flight as a normal distribution with a mean of $200 and standard deviation of $10. Use the appropriate formula to input a randomly sampled 5 Price per Ticket into this input cell. N/A Create your Model and Output sections to compute the airline's total revenue based on your inputs. One of the quantities you should include in your Model is Number of Passengers Present (i.e., the number of passengers that show up for the flight). Do not just use 95% 240 for this value - instead, use a binomial distribution to simulate the number of passengers that might show up for any one given flight. Here are some other quantities you might want to compute in your Model section: - Number of Overbooked Passengers (be sure to use a MAX() function to ensure this isn't negative) - Cost per voucher (this will depend on Number of Overbooked Passengers and the regression parameters you computed from the Voucher Data) - Total Voucher Cost: This will depend on Cost per Voucher and Number of Overbooked Passengers Spreadsheet Model Results Voucher Data Instructions Ready Type here to search T32 D A G Tickets Sold 255 245 250 260 265 270 2 3 Mean 4 SD 5 10th Percentile 6.90th Percentile 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 1. Number of Overbooked Passengers Voucher Cost 8 178 2 co do 18 292 UNUN 4 11 217 5 101 199 6 245 12 17 7 153 8 13 14 255 237 309 10 18 11 7 170 296 12 13 17 17 27 303 14 344 15 16 17 18 19 179 323 223 288 331 167 203 296 8 ! ! ! ! ! ! 3 ) ANNAN MASARAR ALAMAN 13 17 255 266 266 272 241 311 309 312 15 20 289 29 30 31 32 33 34 35 36 37 38 20 29 13 278 213 304 376 218 211 122 Spreadsheet Model Results Voucher Data instructions Ready A D m 43 44 45 46 NEAUNA 999 8 51 58 988 16 17 17 19 62 63 64 67 68 69 71 72 73 74 75 17 18 Results Spreadsheet Model Instructions Voucher Data Ready 37 A B D E F G H In this exercise, you will revisit the 'airline overbooking problem' that we addressed in class. You will use linear regression to predict the cost of each travel voucher based on the number of overbooked passengers, build a Monte Carlo model to simulate the airline's revenue, and use descriptive statistics to interpret the results. Follow the instructions on the 'Instructions' tab and type your responses in the 'Response' column. Then, save this 1 file and upload it to ilearn. 2 3 Inputs Model Output 4 Plane Capacity 2401 5 Tickets Sold 260 6 Show up rate 95 7 Price per ticket 8 Voucher intercept 9 Voucher slope 10 11 12 13 14 15 16 17 18 19 20 Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Used- Time - Reference Trig: Functions Manager Create from Selection Remove Arrows Function Library Defined Names F F3 B A Create your Model and Output sections to compute the airline's total revenue based on your inputs, One of the quantities you should include in your Modelis Number of Passengers Present fi.e., the number of passengers that show up for the flight). Do not just use 95% 240 for this value - instead, use a binomial distribution to simulate the number of passengers that might show up for any one given flight. Here are some other quantities you might want to compute in your Model section: - Number of Overbooked Passengers (be sure to use a MAX() function to ensure this isn't negative) - Cost per voucher (this will depend on Number of Overbooked Passengers and the regression parameters you computed from the Voucher Data) Total Voucher Cost: This will depend on Cost per Voucher and Number of Overbooked Passengers 6 - Ticket Revenue: This will depend on the Number of Tickets sold and the Price per Ticket N/A Run a Monte Carlo simulation of Total Revenue using 200 trials. Do this with a two-way data table that lists the Trial Number down the left-hand column (numbered 1-200), and different 7 values for Number of Tickets Sold across the top row (use 245, 250, 255, 260, 265, 270). N/A Head over to the Results worksheet. Fill in the descriptive statistics (Mean, SD, 10th Percentile, 8 90th Percentile) for each column from your two-way data table. The pricing team is considering one of three strategies for ticket sales: Selling 245 tickets, selling 255 tickets, or selling 270 tickets. Based on your results, which of these options will yield the 9 highest (average) revenue? Let's examine the distributions of possible Total Revenues for one of our pricing scenarios, Create a Pivot Table for the 260 tickets' column of your data table (you can just highlight the 260' header, and all 200 revenue values below it, then go to Insert --> Pivot Table), Place this 10 Pivot Table on a new sheet, N/A Click on your Pivot Table placeholder to display the Field Settings List on the right hand side of the screen. Drag your 260 field into the "Rows' box, then right click on any of the values in your Pivot Table to "Group" them. Group your revenue values starting at o, ending at 100,000 by 11 Increments of 1,000. N/A in the Pivot Table Field Settings pane, drag your 260 column to the 'Values' box, and change its 12 summary function to "Count" (it should default to this, but it may default to "Sum" instead). IN/A Your Pivot Table now shows the number of simulations in which Total Revenue falls into each category (e.g. $50,000-$50,999, SS1,000-$51,999), Highlight the entire Pivot Table and add a column chart, which will act as a histogram that visualizes our distribution of Total Revenues. 13. Paste the histogram to the right. 14 N/A