Get instant access to this case solution for only $19
Harmon Foods Inc Case Solution
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:
-
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.
-
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).
-
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.
-
Give a short interpretation for each of the regression coefficients in your model. Construct 80% confidence intervals for each coefficient.
-
If some extra budget becomes available, would you allocate it to Consumer Packs or to Dealer Allowances?
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
Related Case Solutions
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.