Problem-1 (40 pts)
The monthly demand for lime bags at McCall’s Garden Supply for the past 11 months is provided in the tab McCalls Garden Supply.
- Use 2-month, 3-month, and 4-month moving averages to forecast demand in month 12. (5 pts)
- Forecast demand with a 3-month weighted moving average in which demand in the most recent month is given a weight of 3 and demands in the other two months are each given a weight of 1. (5 pts)
- Forecast demand for month 12 by using exponential smoothing with a smoothing constant of 0.7. Assume that the forecast for month 1 is 6,000 bags to begin the procedure. (10 pts)
- Which of the methods analyzed here would you use for month 12 and beyond? Answer by making forecasts for months 5-11 using each of the above methods and then calculating the mean absolute deviation (MAD). (20 pts)
Problem-2 (30 pts)
Daily sales volume for Nilgiris Convenience Store is provided in the tab Niligiris Convenience.
- Starting Day 11, the store wants to use exponential smoothing to forecast the next day’s sales volume. But what value of α should it use? It is considering three values: 0.2, 0.5, and 0.9. Select the appropriate value of α by calculating forecasts for days 2 through 10 with each of these values and calculating the Mean Absolute Percent Error (MAPE). Assume in each case that the forecast for Day 1 was $300. (15 pts)
- The store sees an increasing trend in the data and decides to use a linear trend for forecasting. What is the trend equation for this data? (5 pts)
- Using the trend equation calculate forecasts for days 2 through 10. Calculate the MAPE of this set of forecasts. (8 pts)
- Compare the MAPE of the best α in (a) with the MAPE in (c). Which method would you prefer? (2 pts)
Problem-3 (30 pts)
A retail store sells space heaters. Sales data for four years is provided in the tab Space Heaters.
- Plot the time series. Describe the time series in a few sentences. (4 pts)
- Calculate the seasonal index for each quarter. What are the implications of these indexes? (8 pts)
- Calculate the trend equation for this data. Explain what the slope and intercept mean in this context. (8 pts)
- Using the multiplicative decomposition method calculate forecasts for each quarter of Year 5. (10 pts)
Instructions
- All work must be done in Excel.
- Each problem must be done on a separate sheet. Label the sheet appropriately.
- Clearly indicate which sub-question is being answered
- Show formulas for all calculations. Points will be taken off for missing formulas.
- Clearly highlight your final answers.
