Excel HW

1. Sales data for two years are as follows. Data are aggregated with two months of sales (in 1,000 units) in each “period.”

a) Plot the data.

b) Fit a linear regression model to all the sales data.

c) In addition to the regression model, determine multiplicative seasonal index factors. A full cycle is assumed to be a full year.

d) Using the results from parts b) and c), prepare a forecast for the next year.

2. Zeus Computer Chips Inc. used to have major contracts to produce the Centrino-type chips. Here is demand over the past 12 quarters:

Fit all the data above by a linear regression model with an additive form (using dummy variables) to forecast the four quarters of 2019.

3. The demand manager of Maverick Jeans is responsible for ensuring sufficient warehouse space for the finished jeans that come from the production plants. In order to estimate the space requirements the demand manager is evaluating moving-average forecasts. The demand (in 1,000 case units) for the last fiscal year is shown below.

a) Use a three-month moving average to estimate the month-in-advance forecast of demand for months 4–12 and generate a forecast for the first month of next year. Calculate mean absolute deviation (MAD).

b) Use an exponential smoothing method with a starting forecast of 21 for month 1 and a smoothing constant α = 0.5 to calculate month-in-advance forecasts for months 4–12 and forecast for the first month of next year. Calculate the MAD.

c) Compare the MAD for the forecasting methods in parts a) and b). Based on these error calculations, which of the two forecast methods would you recommend?