Case #$1$ Forecasting Methods

$$

California Umbrella Corporation is a premier custom umbrella manufacturer and supplier since $2001.$ Quarterly sales $($in Millions of $$)$ for the last $5$ years is reported in Excel file $($Quarterly Umbrella Sales$).($Quarter $1=$Fall, Quarter $2=$Winter, Quarter $3=$Spring,$$ Quarter $4=$Summer$)$

The idea of this case is to compare and use different forecasting methods that would be the best fit for this data set.

Answer the following questions, on the space that is provided for each section. Use Excel for calculations, but no need show your calculations. Upload your answer using the $$Turnitin Assignment$$ link for the computer projects.

$$

Questions:

a$.$ Develop a $4-$quarter moving average for this time series. Forecast the sale for the first quarter of year $6,$ then calculate the MSE and the MAPE for the model. $($No need to show your calculations, just write down the answers on appropriate boxes below, round up your numbers to $2$ decimal points$)$

$$

Forecast for first quarter of year $6.$

$$

Mean Squared Error $($MSE$)$

$$

Mean Absolute Percentage Error $($MAPE$)$

$$

$$

$$

b$.$ Develop a $4-$quarter weighted moving average for this time series by assigning $20\%$ weight for the most recent value and an equal weight for preceding time periods.

Forecast for first quarter of year $6.$

$$

Mean Squared Error $($MSE$)$

$$

Mean Absolute Percentage Error $($MAPE$)$

$$

$$

$$

c$.$ As a follow up of your answer to part b$,$ calculate the optimal $$ for $4-$quarter weighted moving average, use the optimal $$ and calculate the following:.

Optimal $=$

$$

Forecast for first quarter of year $6.$

$$

Mean Absolute Percentage Error $($MAPE$)$ for the optimal

$$

$$

$$

d$.$ Use $=0\mathrm{.}2$ for the exponential method and calculate the following:.

Forecast for first quarter of year $6.$

$$

Mean Squared Error $($MSE$)$

$$

Mean Absolute Percentage Error $($MAPE$)$

$$

$$

$$

$$

e$.$ As a follow up of your answer to part d$,$ calculate the optimal $$for the exponential method $,$ use the optimal $$and calculate the following:.

Optimal $=$

$$

Forecast for first quarter of year $6.$

$$

Mean Squared Error for the optimal $-($MSE$)$

$$

$$

f$.$ Now, using the MSE method, compare the above models and decide which method provides the most accurate forecast. Why? Explain. Does MAPE method confirm your recommendation based on MSE?

$$

$$

$$

$$

$$

$$

$$

$$

$$

g$.$ Use the Trend Line Method and make a forecast for the next $4$ quarters.$($No need to show your calculations, round up your numbers to $2$ decimal points$)$

Equation for the Trend Line

$$

$$

$$

Forecast for Q$1$ year $6$

$$

Forecast for Q$2$ year $6$

$$

Forecast for Q$3$ year $6$

$$

Forecast for Q$4$ year $6$

$$

$$

h$.$ Use the Trend $+$ Seasonality method to calculate and interpret the seasonal factor for each quarter.$($Quarter $1=$Fall, Quarter $2=$Winter, Quarter $3=$Spring,$$ Quarter $4=$Summer$).$

No need to show your calculations, round up your numbers to $2$ decimal points$)$

$$

Interpretation

S$1=$

$$

S$2=$

$$

S$3=$

$$

S$4=$

$$

$$

$$

i$.$ Use the Trend $+$ Seasonality method and make a forecast for the next $4$ quarters.$($No need to show your calculations, round up your numbers to $2$ decimal points$)$

Forecast for Q$1$ year $6$

$$

Forecast for Q$2$ year $6$

$$

Forecast for Q$3$ year $6$

$$

Forecast for Q$4$ year $6$

$$

$$

j$.$ Calculate the MAPE for parts f and h $($ the Trend Line Method$$ and the Trend $+$ Seasonality method$)$ which method should be used and why?$($No need to show your calculations, round up your numbers to $2$ decimal points$)$

$$

Mean Absolute Percentage Error $($MAPE$)$ for the Trend Line Method$$

$$

Mean Absolute Percentage Error $($MAPE$)$ for the Trend $+$ Seasonality Method$$

$$

Mean Absolute Percentage Error $($MSE$)$ for the Trend Line Method$$

$$

Mean Absolute Percentage Error $($MSE$)$ for the Trend $+$ Seasonality Method