This question builds on your work from B-1. Competition is fierce for the Chicago to Atlanta route, and the airline realizes that it may need to reduce the current ticket price. They ask you to provide projections for number of reservations, number of denied passenger check ins (due to overbooking), penalties, sales, net income, and profit margin for this route at different ticket prices. The average number of reservations for this route is 184 when the ticket price is $420, use those figures as a base for your calculations. The airline conducted a price sensitivity analysis and determined that for every $20 increase in ticket price, demand will decrease by 1 ticket reservation. Similarly, ticket sales will increase by 1 reservation for every $20 decrease in ticket price. Using an appropriate data table, calculate the number of reservations, number of passengers denied check ins (due to overbooking), total penalties, total sales, net income, and profit margin (as a percentage) for ticket prices ranging from $100 to $500, in $20 increments. Present this in a worksheet titled “Part B-2” (Hint: you can copy your model from “Part B-1” and adjust the formulas if needed). Create a graph that visualizes the effects that changing prices will have on profitability. The graph should only include sales, net income, and profit margin at the different ticket prices. Include an analysis of these results in your memo to management, highlighting the ticket prices that would give the airline the maximum profitability. Support your analysis in your memo with a meaningful graph/visualization.
This question builds on your work from B-1. Competition is fierce for the Chicago to Atlanta route, and the airline realizes that it may need to reduce the current ticket price. They ask you to provide projections for number of reservations, number of denied passenger check ins (due to overbooking), penalties, sales, net income, and profit margin for this route at different ticket prices. The average number of reservations for this route is 184 when the ticket price is $420, use those figures as a base for your calculations. The airline conducted a price sensitivity analysis and determined that for every $20 increase in ticket price, demand will decrease by 1 ticket reservation. Similarly, ticket sales will increase by 1 reservation for every $20 decrease in ticket price. Using an appropriate data table, calculate the number of reservations, number of passengers denied check ins (due to overbooking), total penalties, total sales, net income, and profit margin (as a percentage) for ticket prices ranging from $100 to $500, in $20 increments. Present this in a worksheet titled “Part B-2” (Hint: you can copy your model from “Part B-1” and adjust the formulas if needed). Create a graph that visualizes the effects that changing prices will have on profitability. The graph should only include sales, net income, and profit margin at the different ticket prices. Include an analysis of these results in your memo to management, highlighting the ticket prices that would give the airline the maximum profitability. Support your analysis in your memo with a meaningful graph/visualization.
Step by step
Solved in 2 steps with 1 images
