Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please answer in Excel and show formulas. A. The annual demand for lime bags at McCall's Garden Supply for the past 11 months is provided
Please answer in Excel and show formulas.
A. The annual demand for lime bags at McCall's Garden Supply for the past 11 months is provided in the image below.
- Use 2-month, 3-month, and 4-month moving averages to forecast demand in month 12.
- 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.
- 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.
- 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).
B. Daily sales volume for Nilgiris Convenience Store is provided in the image below.
- 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.
- 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?
- Using the trend equation calculate forecasts for days 2 through 10.Calculate the MAPE of this set of forecasts.
- Compare the MAPE of the best in (a) with the MAPE in (c).Which method would you prefer?
C. A retail store sells space heaters. Sales data for four years is provided in the image below.
- Plot the time series.Describe the time series in a few sentences.
- Calculate the seasonal index for each quarter. What are the implications of these indexes?
- Calculate the trend equation for this data. Explain what the slope and intercept mean in this context.
- Using the multiplicative decomposition method calculate forecasts for each quarter of Year 5.