OMBA 5355 Marketing Analytics 2SQL

Java Python OMBA 5355

Marketing Analytics

Assignment 2

§ Grade: 12 Points

§ Submission Format: PDF or DOCX file

Many businesses use sales promotions to increase the demand or visibility of a product or service. These promotions often require increased expenditures (such as advertising) or loss of revenue (such as discounts). Firms must determine the effectiveness of such temporary promotions in generating additional demand. Regression based models using historical time series data are the primary tool for evaluating the incremental impact of short-term promotions.

Suppose you are hired by Kraft to conduct one such analysis. In the file labeled “Kraft.xlsx”, you are provided historical sales data for Kraft cheese along with marketing mix activities. See the sheet “Data”. Specifically, the data includes the following variables:

§ Week: Week #

§ Price Paid: Net Price Paid by Customers, including discounts

§ Shelf Price: Regular Price without discount

§ Feature: Coded as AA to indicate Advertisement in the local newspaper

§ Feature Dummy: Coded as 1 when Feature = AA, coded as 0 otherwise

§ Units Sold = Units sold in that week

Kraft is considering running a promotion of 25% discount along with a Feature AD. However, a potential concern for the management is significant drop in sales the week following the promotion. In the industry jargon this is often referred to as “Trough”. If the trough following the promotion is large enough, the promotion may be rendered ineffective. Your objective is to develop a sales response model and evaluate the effectiveness of the promotion using historical data.

Question 1:  Kraft Promotion Analysis: Linear Regression (5 points)

a. Please create the following variables using the existing variables. Provide a screenshot of the first 10 rows of your data table that includes t OMBA 5355 Marketing Analytics Assignment 2SQL he values of the following variables.

§ % Discount = [(Shelf Price – Price Paid) / (Shelf Price)]

§ Trough = Lag (%Discount) — this variable takes the value of previous week % Discount. It is used to capture the impact of promotion in previous week on sales in current week.

b. Estimate the following regression model. Make sure that the baseline (reference point) is when the Feature Dummy = 0 (i.e., when product is not advertised). Please show the parameter estimates

Regression Model:

c. How would you interpret each parameter, a, b, c, d and e? Which variable is the most important?

d. Please assess the model fit/predictability. Provide relevant metric(s) to assess the model fit and perform. residual diagnostics to test the validity of the model. Make sure to explain the output you get from JMP.

Question 2:  Kraft Promotion Analysis: Semi-log Regression (5 points)

Again, use the “Data” sheet in “Kraft.xlsx”.

a. Apply the log transformation to “Unit Sold”. Run the following regression and show the parameter estimates.

Regression Model:

b. How would you interpret each parameter, a, b, c, d and e?

c. Compare this model with the previous model. Which one fits better? Meaning, which model predicts the y-variable better?

Question 3:  Kraft Promotion Analysis: Trough (2 points)

Open the sheet “Promotion Analysis” in the file “Kraft.xlsx” using Excel. Using the parameter estimates from the semi-log model (regardless of your answer from Question 2c), compute the following and fill up the blanks in the sheet. Make sure to provide the filled-up table in this document.

a.  “Baseline” Sales & Revenues (i.e., units sold in the absence of any promotion)

b. Sales & Revenues for the week of promotion (25% Discount, Feature AD) and Sales & Revenues in the week following promotion