Swiftcar Rental Company is a local rental car company that serves the Buffalo-Niagara area. The management team is considering to incorporate the network management framework into its booking system. To begin with, the management team attempts to identify the optimal booking schedule for weekends (i. e., from Friday to Sunday) to maximize its revenue performance. In total, Swiftcar Rental Company has 100 full-size sedans, and all sedans are available for those three days (i. e., the capacity for Friday, Saturday, and Sunday will be 100 for each and every day.) The rental prices for Friday, Saturday, and Sunday are $89, $69, and $59 respectively. If a consumer asks for two-consecutive-day booking, then this customers will receive a 10% discount on the total price. For three-consecutive-day booking, the discount will be 30% over the total price. Particularly, demands and prices for all booking periods areas shown in the following table: Booking Period Price Demand
Friday Only 89 35
Saturday Only 69 12
Sunday Only 59 8
Friday-Saturday 142 55
Saturday-Sunday 115 35
Friday-Sunday Price 152 107
a. Write down the optimization Model for this Swiftcar Rental Company including decision variables, objective function, and constraints.
b. Based on the Optimization Model, please build an Excel model and provide one printout of your formula-view snapshot for your Excel model. • To get a formula-view of your spreadsheet, you need to go to Excel’s "Formula" tab and click "Show Formulas"; alternatively, you can press CTRL + ` (grave accent) to show your formulas. • To check the correctness of your Excel model, you can key in an arbitrary (i. e., nonoptimal) booking schedule: 15 bookings for Friday Only, 3 for Saturday Only, 8 for Sunday Only, 5 for Friday-Saturday, 12 for Saturday-Sunday, and 80 for Friday-Sunday. Under this arbitrary booking schedule, Swiftcar Rental Company will exhaust its capacity for all three days (i. e., the number of bookings for each day will be equal to the company’s capacity—100 full-size sedans). The revenue generated under this booking schedule is $16,264.
c. Use the Excel Solver to solve this problem and report the optimal booking schedule and the corresponding revenue performance.
d. Swiftcar Rental Company is considering a marketing campaign to increase the demand for Sunday Only booking requests by 30 (i. e. from 8 booking requests to 38 booking requests). It has been estimated that such campaign will cost $1,500. Should Swiftcar Rental Company implement such marketing campaign? Please show your analysis. • Hint: generate and use Sensitivity Report in the Excel solver.