Get instant access to this case solution for only $19

Harmon Foods Inc Case Solution

Solution Id Length Case Author Case Publisher
749 1485 Words (8 Pages) William B. Whiston Harvard Business School : 171248
This solution includes: A Word File A Word File and An Excel File An Excel File

The sales manager at Harmon Foods Inc. faces difficulty in predicting the sales for its premium cereal brand ‘treat’. There is a need for the company to produce accurate forecasts. Inaccurate forecast can lead to possible loss of advertising budget. Both the brand and sales manager have delved into the factors affecting sales. Two most important factors are the promotional offers namely consumer packs and dealer allowances. Besides these offers, managers think that time trends, seasonal factors and the possible lags (loss in the current month sales due to high past month sales) are the relevant factors to be considered. The best way to predict sales is to develop a regression model and analyze different coefficients to find out their relevance with sales.

Following questions are answered in this case study solution:

  1. Build the regression model, starting with the independent variables in the file “harmon.xls”. Please include the regression output for your model in your report.

  2. Argue that your model is good.

    a. How does it compare with other possible models (if you include/remove some independent variables, etc.)?

    b. Check whether the regression assumptions are satisfied (briefly summarize your conclusions from residual analysis).

  3. For the next month, January 1988, the planned amount of Consumer Packs is 100 000 cases and Dealer Allowances are set at 500 000$. Use your model to predict sales in January 1988. Please report a point estimate and a 95% prediction interval.

  4. Give a short interpretation for each of the regression coefficients in your model. Construct 80% confidence intervals for each coefficient.

  5. If some extra budget becomes available, would you allocate it to Consumer Packs or to Dealer Allowances?

41630910352.png
51630910352.png
61630910352.png
21630910352.png
71630910351.png
31630910351.png
11630910351.png

Harmon Foods Inc Case Analysis

1. Build the regression model, starting with the independent variables in the file “harmon.xls”. Please include the regression output for your model in your report.

In the case of Harmon foods Inc., the desired variable that changes and/or depends on other variables is monthly sales. Hence, the dependent variable is sales. The other factors that influence the dependent variable include:

  • Consumer allowance,

  • Dealers allowances,

  • Seasonal effects,

  • Time trend (monthly)

  • Consumer lag T-1

  • Consumer lag T-2

  • Dealer Lag T-1

  • Dealer Lag t-2

All these variables are independent, and sales figure depends on all of them. As sales depend on more than one variable; thus, the output will be a multiple regression model consisting of 8 independent and 1 dependent variables. The equation for these variables can be written as:

Case Shipments = -76436.43 + 979.83T + 3858S + 0.0867C - 0.431C (t-1) - 0.00258C (t-2) + 0.0700D + 0.00467D (t-1) – 0.0175D (t-2)

-76436.43 = Y- intercept

T = Time since December 1983

S = Seasonal Factor

C = Consumer allowance current month

C (t-1) = Consumer allowance Last month

C (t-2) Consumer allowance 2nd last month

D = Dealer Allowances Current month

D (t-1) = Dealer allowance last month

D (t-2) Dealer Allowance 2nd last month

For the purpose of regression, consumer allowances are taken rather than consumer cases. Also, it is assumed that each case contains 24 coupons and every coupon has a valued discount of 20 cents as pointed out in the case.

The following figures show the regression results.

Regression Statistics

Multiple R

0.964282154

R Square

0.929840072

Adjusted R Square

0.915448292

Standard Error

35233.46472

Observations

48

 

ANOVA

 

df

SS

MS

F

Significance F

Regression

8

6.41644E+11

80205555158

64.60910799

4.23632E-20

Residual

39

48414484407

1241397036

 

 

Total

47

6.90059E+11

 

 

 
 

 

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Lower 95.0%

Upper 95.0%

Intercept

-76436.43999

42002.64302

-1.819800719

0.076471364

-161394.8039

8521.923881

-161395

8521.924

Time

979.8340369

461.3286893

2.12393909

0.040071253

46.70869444

1912.959379

46.70869

1912.959

Con Allowance

0.086794263

0.009543823

9.09428673

3.50605E-11

0.067490059

0.106098468

0.06749

0.106098

Dealer Allowance

0.070070148

0.006717488

10.43100506

7.64178E-13

0.056482747

0.083657549

0.056483

0.083658

Seasonal

3858.000456

409.9075351

9.411879814

1.38377E-11

3028.884214

4687.116697

3028.884

4687.117

ConsumerT-1

-0.043162499

0.008745228

-4.93554886

1.53458E-05

-0.060851392

-0.025473607

-0.06085

-0.02547

Consumer  T-2

-0.002585987

0.008668907

-0.298305996

0.767053217

-0.020120506

0.014948532

-0.02012

0.014949

Dealer T-1

0.004673377

0.006601023

0.707977588

0.483169159

-0.008678453

0.018025206

-0.00868

0.018025

Dealer T-2

-0.017549613

0.00642926

-2.729647284

0.009464433

-0.030554019

-0.004545207

-0.03055

-0.00455

Source: Excel File

2. Argue that your model is good.

a. How does it compare with other possible models (if you include/remove some independent variables, etc.)?
b. Check whether the regression assumptions are satisfied (briefly summarize your conclusions from residual analysis).

The model discussed above contains 8 different independent variables. It is mentioned in the case that due to allowances in the past months and inventory buildup, the sales for consequent month is affected negatively. Also, as only two months significance is described in the case so lags for last two months is included. The equation also includes time trend as pointed out in case. The model is complete in terms of related variables and their significance to sales. The following figure shows the baseline case regression analysis, which excludes lags on the part of both the consumer and the dealer allowances.

Regression Statistics

Multiple R

0.931655956

R Square

0.867982821

Adjusted R Square

0.855702153

Standard Error

46028.20995

Observations

48

 

ANOVA

 

df

SS

MS

F

Significance F

Regression

4

5.98959E+11

1.4974E+11

70.6787964

2.43879E-18

Residual

43

91099632790

2118596111

 

 

Total

47

6.90059E+11

 

 

 

Get instant access to this case solution for only $19

Get Instant Access to This Case Solution for Only $19

Standard Price

$25

Save $6 on your purchase

-$6

Amount to Pay

$19

Different Requirements? Order a Custom Solution

Calculate the Price

Approximately ~ 1 page(s)

Total Price

$0

Get More Out of This

Our essay writing services are the best in the world. If you are in search of a professional essay writer, place your order on our website.

Essay Writing Service
whatsapp chat icon

Hi there !

We are here to help. Chat with us on WhatsApp for any queries.

close icon