Description
Purpose of Assignment
The purpose of this assignment is for students to learn how to apply Operations Forecasting.
Assignment Steps
Resources: Use Microsoft® Excel® to do the forecast.
The instructor will provide information and data for this assignment.
Assignment:
1. Write a brief APA paper to analyze and comment on the forecasting system being used by Fork and Hoe. Suggest changes or improvements that you believe are justified. Include a response to question 6 after you have completed the year 5 forecast.
2. Determine if the 4 years of demand data indicate an ANNUAL trend.
3. Determine if the 4 years of demand data indicates MONTHLY seasonality. If so, illustrate how you make the adjustments to your year 5 forecast.
4. Develop an ANNUAL forecast for bow rakes for year 5. Develop a MONTHLY forecast for each month of year 5. Justify your forecast by showing your work.
5. Include an Excel spread sheet with the paper to illustrate the details of your forecasting method.
6. Determine if your year 5 forecast will correct the problems that plague Fork and Hoe..
Format your assignment consistent with APA guidelines.
Click the Assignment Files tab to submit your assignment.
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Kindly see attached files with the requested forecast and the analysis of the case studyI've included both a word and a pdf version in case you have any problem when trying to read the equations used
Running head: THE FORK AND HOE COMPANY CASE STUDY
The Fork and Hoe Company Case Study
(Name)
(Course)
(Date)
1
THE FORK AND HOE COMPANY CASE STUDY
The Fork and Hoe Company Case Study
Background information
The Fork and Hoe Company is a company selling manual gardening tools. The manager
of the company tries to optimize the company’s sales and overcome the current trend according
to which most of the customers are moving towards automatic gardening machines. In order not
to loose more clients, it is of utmost importance that the company is sure of avoiding stock outs
as much as possible. This is especially critical considering the high competition existing in the
gardening industry.
For this purpose, the company has evaluated the past monthly demands in the last four
years. In order to minimize inventory costs while avoiding stock outs, the company plans the
production of gardening tools according to the forecasted demand for the next year taking into
account the data of the past four years.
Do the demand of the four years indicate an annual trend?
Even while the provided dataset does not directly provide information about an annual
trend in the demand for the bow rake, this can be estimated by calculating the annual demand
over the past four years adding up the monthly demand for the different months as indicated in
table 1. The average monthly demand, on the other hand, has been estimated by dividing the
annual demand by 12.
2
THE FORK AND HOE COMPANY CASE STUDY
3
Table 1. Calculation of the annual demand and yearly average monthly demand
Month
January
February
March
April
May
June
July
August
September
October
November
December
ANNUAL
Average monthly
demand
Year 1
55220
57350
15445
27776
21408
17118
18028
19883
15796
53665
83269
72991
457949
38162
Year 2
39875
64128
47653
43050
39359
10317
45194
46530
22105
41350
46024
41856
487441
40620
Year 3
32180
38600
25020
51300
31790
32100
59832
30740
47800
73890
60202
55200
538654
44888
Year 4
62377
66501
31404
36504
16888
18909
35500
51250
34443
68088
68175
61100
551139
45928
As can be observed from the computed annual demand, it presents an increasing trend
over the four-year period considered. According to the graph presented in figure 1, it seems that
the annual demand increased by approximately 33,000 annually.
Figure 1. Annual demand trend
THE FORK AND HOE COMPANY CASE STUDY
Is there a monthly seasonality in the demand for bow rakes?
Figure 2 represents the monthly demand of bow rakes for the different years. As can be
observed from the graph, the demand during the months October – February seems to be higher
than that during the months March – September. This trend is observed for all the four years
during which the company has recorded the monthly demand of bow rakes.
Figure 2. Seasonal variation in the demand of bow rakes
Taking this into account, an average annual seasonality factor has been calculated by:
•
Estimating the seasonality factor by dividing the monthly demand by the average
monthly demand for each of the twelve months in the four years
•
Evaluating the average of the seasonality factors for each month taking into account the
values computed for each year
As an example, the following equation shows the estimation of the average seasonality
factor for the month of January:
𝑆𝐽𝑎𝑛,𝑦𝑒𝑎𝑟 1 =
55220
= 1.447
38162
4
THE FORK AND HOE COMPANY CASE STUDY
𝑆𝐽𝐴𝑁𝑈𝐴𝑅𝑌 =
𝑆𝐽𝑎𝑛,𝑦𝑒𝑎𝑟 2 =
39875
= 0.982
40620
𝑆𝐽𝑎𝑛,𝑦𝑒𝑎𝑟 3 =
32180
= 0.717
44888
𝑆𝐽𝑎𝑛,𝑦𝑒𝑎𝑟 4 =
62377
= 1.358
45928
5
1.447 + 0.982 + 0.717 + 1.358
= 1.126
4
Table 2 summarizes the results calculated for the seasonality factors for years 1-4 and the
resulting average seasonality factor for each month.
Table 2. Seasonality factors
Month
January
February
March
April
May
June
July
August
September
October
November
December
Year 1
1.447
1.503
0.405
0.728
0.561
0.449
0.472
0.521
0.414
1.406
2.182
1.913
Year 2
0.982
1.579
1.173
1.060
0.969
0.254
1.113
1.145
0.544
1.018
1.133
1.030
Year 3
0.717
0.860
0.557
1.143
0.708
0.715
1.333
0.685
1.065
1.646
1.341
1.230
Year 4
1.358
1.448
0.684
0.795
0.368
0.412
0.773
1.116
0.750
1.482
1.484
1.330
AVERAGE
1.126
1.347
0.705
0.931
0.651
0.457
0.923
0.867
0.693
1.388
1.535
1.376
The computed average seasonality factors confirm the previous observation according to
which demand for bow rakes is higher in the period from October to February and lower from
March to September.
Forecasts of the monthly and annual demand for next year
Figure 1 showed that the demand for bow rakes increased following a nearly linear
model. This result has been used in the forecast of the annual demand for this year. Hence,
THE FORK AND HOE COMPANY CASE STUDY
6
substituting in the regression equation Year = 5, the forecasted annual demand would be of
Demand = 33078 * 5 + 426100 = 591,490 bow rakes.
Taking this value into account, the monthly average demand for next year would be of
591,490/12 = 49,291 bow rakes. This value can then be used in the estimation of the monthly
demand for each of the months by considering the computed average seasonality factors
summarized in table 2. As an example, the monthly forecasted demand for January would be:
𝐹𝑜𝑟𝑒𝑐𝑎𝑠𝑡𝑒𝑑 𝑑𝑒𝑚𝑎𝑛𝑑𝐽𝑎𝑛𝑢𝑎𝑟𝑦,𝑌𝑒𝑎𝑟 5 = 𝑆𝐽𝐴𝑁𝑈𝐴𝑅𝑌 ∗ 𝐴𝑣𝑒𝑟𝑎𝑔𝑒 𝑚𝑜𝑛𝑡ℎ𝑙𝑦 𝑑𝑒𝑚𝑎𝑛𝑑𝑌𝑒𝑎𝑟 5
𝐹𝑜𝑟𝑒𝑐𝑎𝑠𝑡𝑒𝑑 𝑑𝑒𝑚𝑎𝑛𝑑𝐽𝑎𝑛𝑢𝑎𝑟𝑦,𝑌𝑒𝑎𝑟 5 = 1.126 ∗ 49,291 = 55,497
The computed forecasted demands for the different months is summarized in table 3.
Month
Year 1
Year 2
Year 3
Year 4
January
February
March
April
May
June
July
August
September
October
November
December
Annual
55220
57350
15445
27776
214...