ties: Simple moving average, Weighted moving average, Simple Exponential Smoothing, Regression. • Choose two of the above methods and calculate forecasts for Year 11 o If you use Weighted moving average, use .5, .3, .2 for the weights (.5 for most recent and so on) o If you use Exponential Smoothing, begin with the assumption the Forecast for Year 9 was 1500 ▪ Use .3 for your alpha if you use this method. • Present your forecast results • Explain why each of your chosen methods is appropriate for the data and time frame given • Include MAD, MSE and MAPE
Problem 1
• All analysis and calculations and report must be done in a single (ONE) Excel file.
• Put your name at the top of the worksheet.
• Make Excel do all of the calculations. (Instructor must be able to see your cell-reference formulas.)
• Include report/answers below the forecasting calculations.
o Make sure answers are clear, complete and easy to find.
o Your report must include:
a. Presentation of forecasts
b. Explanation of why you chose each of the methods
1. Tom Simpson, Director of the Chamber of Commerce for Exeter township is investigating the past ten years of
tourist visits to the area. The following data has been gathered on number of tourists who signed into the local
information center.
Year Number of tourists
1 700
2 248
3 633
4 458
5 1410
6 1588
7 1629
8 1301
9 1455
10 1989
Tom is interested in implementing a forecasting system and is investigating the following forecasting methods as
possibilities:
Simple moving average, Weighted moving average, Simple Exponential Smoothing, Regression.
• Choose two of the above methods and calculate forecasts for Year 11
o If you use Weighted moving average, use .5, .3, .2 for the weights (.5 for most recent and so on)
o If you use Exponential Smoothing, begin with the assumption the Forecast for Year 9 was 1500
▪ Use .3 for your alpha if you use this method.
• Present your forecast results
• Explain why each of your chosen methods is appropriate for the data and time frame given
• Include MAD, MSE and MAPE
![1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
25
26
26
27
28
35
36
37
38
39
Year
2
Year
4
5
6
9
10
11
Number of Tourists
2
3
3
A
4
F
Explain why forecast 1 is appropriate for the data and time frame given:
5
5
b
7
Z
700
248
633
458
1410
1588
1629
1301
1455
1989
Number of Tourists
700
248
633
458
120
8
9
10
11
1410
1410
1588
1629
Forecast 1
1301
1455
1989
Error
Forecast 2
Error Error
Error
MAD
Error] Error
MAD
Explain why forecast 2 is appropriate for the data and time frame given:
Error 1%
MSE MAPE
Error %
MSE MAPE
Values for Weights, Alpha, Intercept and Slope are
copied from instructions into cells below. You must
use these cells as a reference in your formulas
based on the method(s) chosen. If using Simple
Moving Average, use these weights. If using Simple
Exponential Smoothing use this Alpha. If using
Regression use this Intercept and Slope.
Weights
0.2
0.3
0.5
Alpha
0.3
Intercept
238.4
Slope
164.13
In Green Cells, write the name of the forecast type:
Simple moving average, Weighted moving average,
Simple Exponent Smoothing, Regression
Light blue cells should have a formula written that
references other cells where appropriate (some may
be blank depending on forecast type chosen)
Yellow cells contain your final answers for Year 11
forecast, MAD, MSE, and MAPE
Q](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2Fe52ee693-9757-4e68-b9ed-a0f596aef9de%2F159ae655-d3d3-4254-8413-fff0a8f22358%2Fc68poeh_processed.png&w=3840&q=75)
Step by step
Solved in 4 steps with 4 images
