INFO 564 Module 5 Homework

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.

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)

1. 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)
2. 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)
3. 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.

1. 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)
2. 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)
3. 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.

1. Quantify the trend in the time series using Excel. (6 pts)
2. Interpret the slope and intercept of the trendline. (2 pts)
3. Use the trendline to make forecasts for periods Q1 Year 1 through Q4 Year 5. (6 pts)
4. 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.

1. Quantify the seasonality in the time series by calculating seasonality indexes. (10 pts)
2. Which of the four quarters shows the largest seasonal effect and how much? (2 pts)
3. Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5. (8 pts)
4. 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.