*This work must be done completely in EXCEL. *__Answer each question on a separate tab.__ Label each tab appropriately. *You can copy and paste the data given into an Excel worksheet.*

South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years.

Don't use plagiarized sources. Get Your Custom Essay on

INFO 564 Module 5 Homework

Just from $13/Page

**Quarter** |
**Year 1** |
**Year 2** |
**Year 3** |
**Year 4** |
**Year 5** |

1 |
20 |
37 |
75 |
92 |
176 |

2 |
100 |
136 |
155 |
202 |
282 |

3 |
175 |
245 |
326 |
384 |
445 |

4 |
13 |
26 |
48 |
82 |
181 |

**Question 1 (10 pts)**

Plot this data with quarters from years 1-5 on the horizontal axis. Which of the following components do you see in this time-series: *randomness, trend, seasonality*?

**Question 2 (15 pts)**

- Suppose the company uses moving 3-quarterly moving averages to make forecasts. What would the forecast be for each quarter starting from Q4 Year 1 all the way through Q4 Year 5? (5 pts)
- Suppose the company uses 3-quarterly
*weighted* moving averages to make forecasts. What would the forecast be for each quarter starting from Q4 Year 1 all the way through Q4 Year 5? Assume the weights are 0.65 (most recent), 0.30, and 0.05 (oldest). (5 pts)
- Using Mean Absolute Percent Error compare the accuracies of the two sets of forecast in (a) and (b). Which method – moving average or weighted moving average, would you use? (5 pts)

**Question 3 (15 pts)**

Suppose the company uses exponential smoothing to make forecasts.

- What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.15? Assume forecast for period Q1 Year 1 was 30 units. (5 pts)
- What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.60? Assume forecast for period Q1 Year 1 was 30 units. (5 pts)
- Based on Mean Absolute Percent Error, which of these two values of alpha would you use? (5 pts)

**Question 4 (20 pts)**

Suppose the manufacturer wants to include adjustments for trend.

- Quantify the trend in the time series using Excel. (6 pts)
- Interpret the slope and intercept of the trendline. (2 pts)
- Use the trendline to make forecasts for periods Q1 Year 1 through Q4 Year 5. (6 pts)
- What is the Mean Absolute Percent Error of these forecasts? (6 pts)

**Question 5 (25 pts)**

Now suppose that the manufacturer wants to include adjustments for trend and seasonality.

- Quantify the seasonality in the time series by calculating seasonality indexes. (10 pts)
- Which of the four quarters shows the largest seasonal effect and how much? (2 pts)
- Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5. (8 pts)
- Calculate the Mean Absolute Percent Error for this set of forecasts. (5 pts)

**Question 6 (5 pts)**

Create a table showing the MAPE for the preferred methods in questions 2 & 3 and the methods in questions 4 and 5. Which of these methods gives you the lowest MAPE?

**Question 7 (10 pts)**

Using the best method identified in Question 6, make forecasts for each quarter of *Year 6*.