OME CASE STUDY: HARMAN FOODS, INC.
SECTION B: GROUP 02
PRUDVIRAJ| PRIYANKA | JOEL | KARTHIKA
Problem Statement:
Harmon Foods was experiencing significant challenges with forecasting in the sales of Treat, their
ready-to-eat breakfast cereal. Inaccuracy in forecasting the sales for the Treat created a big problem
that led to costly production changes resulting in lower profitability. The company faces a variation
of 50% to 200% in comparison with its actual sales. The company was also facing inventory
problems, increase in their cost of production, and the profitability of the company became low.
Moreover, the advertisement and the marketing cost is also increased as a result of poor forecasting
techniques adopted by the company.
To avoid related problems, the company must develop a robust sales forecasting technique which
can allow the company in deciphering the actual future sales.
SITUATIONAL ANALYSIS
Incorrect Sales forecast has direct impact on the following
Manufacturing:
The plant manager plans his production based on the forecast provided by the Brand Manager. Any
change in forecast will increase the production cost. All raw material required for production needs
to be shipped in advance as these are long lead items. Also, if the forecast is greater than the actual
requirement this will result in higher demurrage charges. The work forces are highly skilled and
cannot be kept idle hence accurate forecast of sales is required to plan the manufacturing schedule.
Advertising:
The advertising cost was spent on Saturday morning network shows for children. These slots were
highly sought out and booked for a year in advance. Hence any change in the sales of the product
resulted in altering the advertising schedule, impacting the effectiveness of the advertisement.
These changes in schedule of advertisement were expensive and directly affected the profitability of
the business.
Budget and Control:
As the manufacturing schedule was based on the sales forecast provided, deviance in actual sales
affected the reporting of the financials in the organization. Advertising expenses were more than the
budgeted value. Hence the actual budgeting expenses had to be deferred to next fiscal year/quarter
and this resulted in spurious reporting of the financials of the organization.
After talking to the analyst in the Marketing Research, System Analysis, and Operations Research
Department, Mr. Donal Carswell concluded that better forecast were possible. Carswell along with
Robert Haas decided to work on to find a solution for the forecasting problem for company wide
application.
Forecast analysis depends on the following factors:
I. Dependency of Data:
Donald and Robert found that the volume of sales was dependent on the following parameters:
1. Consumer Pack
2. Dealer Allowance
3. Season Index
1. Consumer Packs:
The company offered a 20 cents reduction per package. From the historical data, they concluded
that during canvassing of the product 35%,25%,15%,10%,10% of the product were sold in the first,
second, third, fourth and fifth week respectively. The balance 5% were sold after the canvassing
period. They felt that the historical data were accurate and the same could be used to forecast the
monthly consumer pack shipment.
2. Dealer Allowance:
The sales were also dependent on the dealer allowance. Harmon offered $4-$8 per case discount on
their purchase during allowance’s canvass period. Dealer promoted Treat which in turn increased
the sales of the product i.e average of five week was sold in a single weekend. He realized that these
resulted in inventory build ups and reactions to these build ups were as late as two months after the
initial sale increase.
3. Seasonal Index:
The sale of Treat was seasonal to an extend i.e November and December the sales were relatively
slow as the inventories during this period were cleared off. During summer the sales dropped due to
plant shutdown and sales personnel vacation. They also obtained data from National Association of
Cereal Manufacturers on the seasonal effects on Breakfast cereals shipment which affected the sales
of the product. The sale increased during canvass periods.
II. DATA MODELLING
We created an excel sheet with columns contains dependent variable as sales and independent
variables such as Time Index, Season Index, Consumer Packs and Dealer Allowances.
Based on the historical data, our formula to forecast the sales is:
Sales =
-(85350.3)+(3824.8*SI) +(0.489*CP1) -(0.32*CP2) +(0.082*DA1) -(0.019*DA3)
Where,
SI = Season Index for shipments
CP1 = Actual Consumer Packs
CP2 = Consumer packs lag volume after 1 month (85% attribute to current month and 15% attribute
to previous month)
DA1 = Dealer allowance spending
DA3 = Dealer allowance lag after 2 months (85% spending attribute to current month and 15%
spending attribute to previous month)
CP3 (Consumer packs lag volume after 2 months) and DA2 (Dealer allowance lag after 1 months) is
not considered because of higher P-Values after regression.
The below output shows that the calculated linear regression equation fits the source data very well
as the adjusted R Square value is 91%.
We created an excel sheet with columns contains dependent variable as sales and independent
variables such as Time Index, Season Index, Consumer Packs and Dealer Allowances.
The below output shows that the calculated linear regression equation fits the source data very well
as the adjusted R Square value is 91%.
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.96385055
R Square 0.929007882
Adjusted R Square 0.914062173
Standard Error 35856.94874
Observations 47
EXCEL FILE:
Consumer Packs Dealer Allowance
1 Consumer Pack (CP) = 24 boxes as per sales forecast equation, $ 1 increase in
dealer
= 24 ($0.8) = $19.2 allowance leads to 0.063 cases i.e. (0.082-
1$ contributing to 0.052*cases 0.019) *$ 1
As per sales forecast equation,
As per sales forecast equation,
(0.489-0.32) *CP = 0.16*0.052
cases
= 0.0083 cases
In the above calculation, we have assumed that the price of one pack is 1$.
Conclusion: our promotional strategy is to increase the dealer allowance rather than
increasing consumer packs.
