Question

Year	Quarter	Units
1	Q1	20
1	Q2	100
1	Q3	175
1	Q4	13
2	Q1	37
2	Q2	136
2	Q3	245
2	Q4	26
3	Q1	75
3	Q2	155
3	Q3	326
3	Q4	48
4	Q1	92
4	Q2	202
4	Q3	384
4	Q4	82
5	Q1	176
5	Q2	282
5	Q3	445
5	Q4	181

Using this data:

a. Plot this data on a line chart with quarters from years 1-5 on the horizontal axis. (2.5 pts) Make sure to display the data labels. (2.5 pts) What does this data tell you? (5 pts) Line Chart should be on the same worksheet as the data.

a. Assume the company uses 3-quarterly moving averages to make forecasts. Make forecasts for each quarter starting Q4 Year 1 all the way through Q4 Year 5. (5 pts) b. Suppose the company uses 3-quarterly weighted moving averages to make forecasts. Make quarterly forecasts starting with Q4 Year 1 all the way through Q4 Year 5. Assume the company uses quarterly moving averages with weights 0.6 (most recent), 0.3 (next), and 0.1 (oldest). (5 pts) c. Using Mean Absolute Percent Error compare the accuracies of the two sets of forecast. Explain which one is better – 3 quarter moving average or 3 quarter weighted moving average? (15 pts) d. Develop a Line Chart to showcase your results comparing the two forecast models used. (5 pts)

Again ignore any trend or seasonality in the data. Suppose the company uses exponential smoothing to make forecasts. a. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.3. Assume that the forecast for Q1 Year 1 was 25 units. (5 pts) b. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.8. Assume that the forecast for Q2 Year 1 was 25 units. (5 pts) c. Compare the accuracies of the forecasts in (a) and (b) using Mean Absolute Percent Error. Which value of alpha gives us the better forecasts? (15 pts) d. Develop a Line Chart to showcase your results comparing the two forecast models used. (5 pts)

Now make adjustments for trend and seasonality. a. Quantify the trend in the time series. (5 pts) Explain in writing what the trend equation tells you. (5 pts) b. Quantify the seasonality in the time series by calculating seasonality indexes. (5 pts) What do these indexes tell you? (5 pts) c. Using the trend and the seasonality information from (a) and (b) to make forecasts from Q1 through Q4 for Year 6. (5 pts)

a. Compare your preferred method in Question 2, and your preferred method in Question 3. Explain why you would choose one method over the other on the basis of MAPE. (5 pts)

Answer 1