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). The idea of this case is to compare and use different forecasting methods that would be the best fit for this data set.

Questions:

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)

Develop a 4-quarter weighted moving average for this time series by assigning 40% 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)

Use =0.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)

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

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 Q1 year 6

Forecast for Q2 year 6

Forecast for Q3 year 6

Forecast for Q4 year 6

Use the Trend + Seasonality method to calculate and interpret the seasonal factor for each quarter. (No need to show your calculations, round up your numbers to 2 decimal points)

Interpretation

S1 =

S2 =

S3=

S4=

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 Q1 year 6

Forecast for Q2 year 6

Forecast for Q3 year 6

Forecast for Q4 year 6

P17 B C D E F G H M 1 =INTERCEPT Y-Range, X-Range) =SLOPE Y-Range, X-Range) 2 Quarterly Umbrella Sales (Million Dollars) Forecasted Sales Year Quarter time Sales Error 3 Mean Absolute Error (MAE) (MAPE) TYY13 ((TY-YI)/ ) Squared of Error YA (Y-Y) (Y-YA) Seasonal Index Average value for Average value for each season using Trend + each season / Trend Line =averagelF(Quarter, Average Value of Tt = b0 + b1 Seasonality Criterion, Values) all seasons t YA=T, xs, 4 Q time Year 1 Y: $250 5 5 1 1 6 1 2 2 $306 7 1 3 3 $212 8 1 4 4 S116 9 2 1 5 5236 10 2 2 6 5322 11 2 3 7 $266 12 2 4 8 $204 13 3 1 9 $276 14 3 2 10 15 3 16 17 18 3 4 4 4 4 5 5 5 3 4 1 2 3 4 1 1 2 3 4 11 12 13 14 15 16 17 18 19 $288 $226 $160 $218 $274 $250 $218 $260 $330 5256 S192 21 22 23 24 19 20 25 MAE MAPE MSE 26 27 28 =SLOPE( Y-Range, X-Range) Slope = b; 29 Sheet1 P17 B C D E F G H M 1 =INTERCEPT Y-Range, X-Range) =SLOPE Y-Range, X-Range) 2 Quarterly Umbrella Sales (Million Dollars) Forecasted Sales Year Quarter time Sales Error 3 Mean Absolute Error (MAE) (MAPE) TYY13 ((TY-YI)/ ) Squared of Error YA (Y-Y) (Y-YA) Seasonal Index Average value for Average value for each season using Trend + each season / Trend Line =averagelF(Quarter, Average Value of Tt = b0 + b1 Seasonality Criterion, Values) all seasons t YA=T, xs, 4 Q time Year 1 Y: $250 5 5 1 1 6 1 2 2 $306 7 1 3 3 $212 8 1 4 4 S116 9 2 1 5 5236 10 2 2 6 5322 11 2 3 7 $266 12 2 4 8 $204 13 3 1 9 $276 14 3 2 10 15 3 16 17 18 3 4 4 4 4 5 5 5 3 4 1 2 3 4 1 1 2 3 4 11 12 13 14 15 16 17 18 19 $288 $226 $160 $218 $274 $250 $218 $260 $330 5256 S192 21 22 23 24 19 20 25 MAE MAPE MSE 26 27 28 =SLOPE( Y-Range, X-Range) Slope = b; 29 Sheet1