MGSC 416 Data-driven models for Operations Analytics Problem Set 3: Revenue Management, Optimizing Promotions for Supermarkets
MGSC 416, Winter 2023 Data-driven models for Operations Analytics
Problem Set 3 – Team Assignment
Problem 1. (Revenue Management at Spark Airways)(21pts)
Spark Airways is a small airline that offers passenger air transportation between two major east coast
cities, namely Boston and New York; two major west coast cities, namely San Francisco and Los Angeles; and one major midwest city, namely Chicago. Spark Airways operates a hub in Chicago, at which passengers can change planes to their final destination. Spark Airways Airlines owns and operates two identical Boeing 757 aircraft, each with a capacity of 140 seats. The daily schedule of these aircraft is shown in Figure 1.
Figure 1: The daily westbound flight schedule for Spark Airways
Spark Airways offers both discounted (Q-Class) and unrestricted (Y-Class) fares. Spark Airways wants to focus on their westbound operations. Figure 2 shows the westbound itineraries that Spark Airways offers, their current prices, as well as the expected daily demand for seats for both Q-Class and Y-Class fares for the four Westbound flight legs of Spark Airways. This data is also stored in the csv file ”Spark Airways.csv”.
Spark Airways want to decide the number of passenger seat to reserve for each fight (8 routes portrayed in Figure 2) for each class (Q and Y). We will use the letters Q1, Q2, · · · , Q8 (respectively, Y1, Y2, · · · , Y8)
1
Figure 2: Prices and the expected daily demand for the Q-Class and Y-Class fares for the westbound itineraries of Spark Airways
for the decisions variables related to the 8 routes (in the order they are shown in Figure 2) for the Q-Class (Y-class respectively).
- Formulate the linear program of maximizing Spirit Airways revenue on Mondays clearly stating the decision variables, the objective function and the constraints.(9pts)
- Using Jupyter Notebook, please run the file ’HW3-part1.ipynb’ to load the data for this problem, which is stored in the csv file ”Spark Airways.csv”. Then, using Gurobi, solve the LP problem formulated in the previous question (please assume all variables to be continuous). What is the optimal allocation decisions for each flight and Class? What is the optimal revenue for Spark Airways? (Please submit as well your Jupyter notebook with the appropriate code). (4pts)
3. Analysis of a New Lease Option. Spark Airways is thinking of leasing a small jet from Canadair (with a capacity of 40 seats) to increase the capacity of their flight Chicago-San Fransisco, for a cost of $2.5 million per year. Should Spark Airways accept Canadair’s offer? Why? (Note: Assume that the expected daily demand and prices are the same all year long and the shadow prices don’t change by changing the right hand side of the constraints)(3pts)
4. Managing fare reservation requests. On a Friday, Spark Airways received two simultaneous ticket requests:
(a) Request 1: Two tickets, one for Elon Musk (Y-Class ticket) and another for his assistant (Q-Class ticket). After attending a MIT conference, they are flying back from Boston to San Francisco.
(b) Request 2: One ticket (Q-Class ticket) for Layla Abbot, a student at the university of Chicago, who is flying back to her hometown San Francisco for the weekend.
If Spark Airways was able to accept one request only, which request should that be? Please explain your reasoning.(5pts)
Problem 2. (Questions for the case study: Optimizing Promotions for Supermarkets using Data Analytics) (16pts)
Estimation – Single Item Model
This assignment deals with estimating sales from transactions data1 and promotion optimization. The goal is twofold: (i) understanding what factors affect the demand for items in supermarkets as well as exploring how to estimate demand models from data and (ii) studying one formulation of the promotion optimization problem faced by category managers in supermarkets.
Load the csv file “singleitemSKU88.csv”.
The data consists of three years (156 weeks) of weekly transactions for one particular item (labeled as SKU88). Each row corresponds to a given week for which we report: the week number (from 1 to 156, where 1 corresponds to the first week of January 2011), the price of the item (1 means that the product is at full price, < 1 means that it is on promotion), and the aggregate sales during that week (note that because of data aggregation, sales are not integer numbers). Using R, run the file ’HW3-part2.Rmd’ to load the data and to add the seasonality factors (explained below).
In this question, you will estimate the demand function for SKU88 using R. The demand at time t depends on several observable features, such as seasonality, trend, and price. We consider two specific models.
In each model, we assume that demand has a log-log form (i.e., the logarithm of the demand is linear in the logarithm of the price).
We also assume that the seasonality is monthly (i.e., constant for 4 consecutive weeks and repeated from year to year). We have 13 seasons in a year because there are a total of 52 weeks in a year. In addition, you need to split the dataset into two parts: training set (consists of the first 2 years’ data) and testing set (consists of the last year’s data). Estimate the parameters of the two models below using the training set.
(a) Model 1
log(dt)=a0 +at ·mt +β0log(pt)+γ·t, (0.1)
where dt and pt are the demand and the price during week t respectively and mt is the month index (from 1 to 13, week 1 to 4 is month 1, week 5 to 8 is month 2, etc...). Our goal is to estimate the parameters a0 (the intercept or SKU effect), at (the seasonality factors – a total of 12 different parameters2), γ (a single
parameter that captures the trend effect), and β0 (the price effect). In this model, we have a total of 15 parameters to estimate.
(b) Model 2
log(dt) = a0 + at · mt + β0log(pt) + β1log(pt−1) + β2log(pt−2) + γ · t, (0.2)
where pt−1 and pt−2 are the prices during weeks t − 1 and t − 2 respectively. Our goal is to estimate the parameters a0, at, γ, β0, β1 (effect of last week’s price on current demand) and β2 (effect of the price two weeks ago on current demand). In this model, we have a total of 17 parameters to estimate.
- Conceptually, and according to Model 1 and 2 specifications above, if the price of the product at week t increase by %, by how much do we expect the product’s sales / demand at week t to increase, percentage wise? (3pts)
- For both models, please report in your solutions (i) the summary results of the regressions; and (ii) the values of MAPE on the test set. Which model is better and why? (7pts)
- For Model 2, what do you observe regarding the magnitudes of the estimated parameters β0, β1 and β2? Does this make sense and why? (3pts)
4. Comparing between the two regression models, for which type of products do you think that Model 2 is better? Please explain why. (3pts)
Problem 3. Optimization Formulation (using Mixed-Integer Optimization)(13pts)
Imagine that you are the store manager and need to decide the promotion plan for a particular product in the next 12 weeks. Imagine that you are the store manager and need to decide the promotion plan for two particular products in the next 12 weeks, Product A (full price is given to be pA for Product A) and Product B (full price is given to be pB for Product B). You are told that you can run promotions on this product for at most 4 out of the 12 weeks. Furthermore, the price for product A should always be at most the price of product B. An additional requirement is to space out ALL promotions (for either product A or product B) by at least 3 weeks. In addition, whenever you run a promotion, your supplier requires that you cannot discount more than 30 % of the full price of product A and not more than 50 % of the full price of product B. However, the prices at for product A and product B in every week take all values between the full price and the minimum discounted price.
You have estimated demand for week t to be dt. Assuming that there are no stock-outs, i.e., demand is equal to sales, formulate the promotion optimization problem for maximizing revenue over 12 weeks. Explain the objective function, decision variables, and constraints.
