Module 6 Case Problem

Note: Data for this problem is in the Module 6 Case Data file – the data is on two separate tabs. One
tab for data from 2018-2020 and another tab for the data from 2021.
You will be submitting two files at the same time for this assignment – a Word and an Excel file:
As you have done before, rename the word template document as LastNameFirstInitial_HW6.docx and
LastNameFirstInitial_HW6.xlsx and submit these documents through the CANVAS assignment.
The Cullowhee Tools R Us Company is a leading producer of garden tools ranging from wheelbarrows and
hand trucks to shovels and rakes. The tools are sold in four different product lines ranging from the Atlas
products, which are for the toughest job, to Minnie Mouse, which are economy tools for the occasional
user. The market for garden tools is extremely competitive because of the simple design and the large
number of competing manufacturers. In addition, more people are using more power tools reducing the
demand for manual tools.
Garden tools represent a mature industry. Unless new manual tools can be developed, the prospects for
a large increase in sales are poor. Keeping ahead of the competition is a constant battle. No one knows
this better than Joe Greenthumb, the president of Cullowhee Tools R Us.
The types of tools sold today are very similar to the tools sold 30 years ago. The only way to generate new
sales and retain customers is to provide superior customer service and produce a product with high
customer value. Greenthumb has been receiving calls from customers, such as Lowes and Home Depot,
complaining of late shipments.
Greenthumb knows losing customers such as Lowes and Home Depot would be disastrous. He has decided
to ask Sally Fixer, a consultant, to investigate the matter. Greenthumb feels that the main problem they
are facing is that they do not have a very robust method of forecasting future demand. The current
method is very informal and qualitative, like a sales force composite methodology. He has hired Sally
because as a student in WCU’s College of Business, she is very competent in the use of quantitative
techniques.
Greenthumb has suggested that Sally focuses on their bow rake (see Figure 1) because it is a high-volume
product and has been a major source of customer complaints. In Greenthumb’s initial discussions with
Sally, she has mentioned forecasting methods such as, moving averages, weighted moving average,
exponential smoothing, and linear regression, but he is still a little apprehensive of quantitative models.
He has decided to provide Sally with demand history from 2018 through 2020 (see Table 1). He would like
her to evaluate the forecasting techniques (listed below) that she knows based on these 3 years of data
and evaluate them based the MAPE error measurement. Once this has been completed, he would like her
to suggest which method should be used (only one) and apply this method to the 2021 data that he will
provide and comment on the results. He would like her to provide a suggestion for which forecast method
they should use and why she feels it is the correct method based on its performance as well as other
factors.
Figure 1: Bow Rake
Table 1: Demand Data for 2018 – 2020
Table 2: Demand Data for 2021
He would like Sally to include the following table in her report. That table indicates the different
methods to use along with their parameter. He is confident, from previous forecasting projects, that the
MAPE will exceed 20% for all methods. He has provided the results of the Naïve method to show how he
wants the MAPE formatted. She should use all Table 1 data (36 data points – place in a single column in
your forecasting worksheets) to evaluate each forecasting method and complete the table below.
Month 2018 2019 2020
1 28,736 39,875 32,180
2 37,492 64,128 38,600
3 23,576 47,653 25,020
4 27,776 43,050 51,300
5 21,408 39,359 31,790
6 24,876 40,876 32,100
7 27,773 45,194 59,832
8 26,954 46,530 30,740
9 28,974 22,105 47,800
10 53,665 41,350 73,890
11 64,879 46,024 60,202
12 72,991 41,856 55,200
Demand Demand
Month 2021
1 62,377
2 54,269
3 31,404
4 36,504
5 16,888
6 18,909
7 35,500
8 38,479
9 34,443
10 48,972
11 42,378
12 45,697
Table 3 – Historical performance based on 2018-2020 data
METHOD MAPE (%)
Naive 30.38
Two Period Simple Averaging
Three Period Simple Averaging
Four Period Simple Averaging
Exponential Smoothing #1*
α = 0.2
Exponential Smoothing #2*
α = 0.3
Exponential Smoothing #3*
α = 0.4
Linear Regression
Weighted Moving Average #1
Weights:
0.45 (most recent), 0.4,
0.15 (oldest)
Weighted Moving Average #1
Weights:
0.55 (most recent), 0.35,
0.1 (oldest)
Weighted Moving Average #1
Weights:
0.6 (most recent), 0.3,
0.1 (oldest)
*Forecast for period 1-= demand for period 1 for all exponential smoothing models
After completing the table above and selecting the preferred forecasting method, apply that forecasting
method to the 2020 data and submit that Excel file with your assignment. Note: That Excel file should
contain a maximum of two worksheets – one with the 2021 data (optional) and the other worksheet
should be the forecast results/table using the selected method.
Table 4 –Performance based on 2021 data
METHOD MAPE (%)