Reference no: EM132542623

Background
It is that time of year that managers of all types love to hate: budgeting for the next year. Connie Smith needs forecasts of sales for the next year she can build her budgets around. She would also like some idea of what 2018 could look like, though she realizes the forecasts that far in the future are a bit uncertain. She asks you to develop forecasts for 2017 as a beginning.

Requirements

Using M6A1_Data,

Start Excel. Open the workbook M6A1-Fast Data. Immediately save the workbook with a new name. Use your name and include the assignment name, e.g. Wright-Dawn-M6A1. This will ensure you have a good copy in case you make mistakes. It will also make your instructor happy when grading your work, which is a good thing.

1. Using the Fast 2000 GB Sales data for January 2012 through Dec 2016:

1a. Prepare a scatter plot (with lines instead of data points) with Month as the predictor (x) variable and World Sales as the response (y) variable. Use a basic Excel graph to do this, not a full linear regression using the Data Analysis tool.
How to Do It 1a.1: Excel Scatter Plot
1ai. Add a trend line with the linear regression equation and R2.

How to Do It: Excel Trendline & Equation

1aii. What does the plot communicate about the Fast 2000 GB World sales pattern?
1b. Using the trend line equation from 1a:

1bi. Forecast Fast 2000 GB World Sales for each month from January 2012 through December 2016.
How to Do It 1b.1: Excel Forecast Basics
How to Do It 1b.2: Use Excel to Calculate MAD, MSE, RMSR & MAPE

1c. Using a Moving Average (MA) with k=3 and k=9, develop forecasts for Fast 2000 GB World sales for each month from January 2012 through December 2016. You can either develop the MA using basic Excel functions and formulas in Evans or use the Data Analysis tool pack.
How to do It: Excel Forecasts: Moving Average  1ci. Create a single plot of the actual data and the two MA forecasts.
How to do It: Plot Two Data Sets on One Graph

1cii. Find MAD, MSE, RMSE, and MAPE for the MA forecasts.

1d. Using Exponential Smoothing (ES) with α=0.3 and α=0.9, develop forecasts for Fast 2000 GB World sales for each month from January 2012 through December 2016. You can either develop the ES using basic Excel functions and formulas in Evans or use the Data Analysis tool pack.
How to Do It: Simple Exponential Smoothing 1di. Create a single plot of the actual data and the two ES forecasts.
1dii. Find MAD, MSE, RMSE, and MAPE for two ES forecasts.

1e. Create a summary table of the error metrics for part 1. Which error metric do you think is best?

2. Using the Fast 2000 GB World Sales data:
2a. Develop regression forecasts for seasonality-only for each month Jan 2012 through Dec 2016.

How to do it 2a: Seasonality
2a1. How does the adjusted R2 compare to the R2 of the regression model in #1?
2aii. Create a line plot showing the actual sales and forecast sales for the months Jan 2012 through Dec 2016 using the seasonality-only regression model.
2aiii. Find MAPE for this model.
Note: if the trend component of your data is very strong, the R2 may drop or be very low when you are only regressing the seasonality component. The p-values of the seasonality-only regression coefficients may also be high and not significant. When we add in the trend component, the characteristics of the model will improve.
Important: You must complete part 2 using only basic Excel tools and the Data Analytics, i.e. following the process shown in the Part 2 How-to videos. You may not use the Excel 2016 Forecast sheet or function to do this.

2b. Develop regression forecasts including seasonality and trend for each month Jan 2012 through Dec 2016 for Fast 2000 GB World sales.
How to do it 2b: Seasonality & Trend
2bi. How does the adjusted R2 compare to the R2 of the seasonality only regression model?
2bii. Create a line plot showing the actual and forecast sales from Jan 2012 to Dec 2016 using the seasonality and trend regression model.

2biii. Calculate MAPE for this model's forecasts.

2c. Check to see if any of the dummy variables in the 2b model are not statistically significant. If so, rerun the regression model with the non-significant variables removed and see if R2 and MAPE improve.

2d. Select the best regression model in part 2 (2a, 2b, or 2c) and develop forecast values for the twelve months Jan 2017 to Dec 2017. Use MAPE and R2 to make this decision.
How to do it 2d: Seasonality & Trend Forecast
2di. Prepare a final graph showing the actual data and the forecast values for the 72 months Jan 2012 to Dec 2017.

3. MAPE is useful for comparing forecasts because the measurement scale is eliminated (response variables of different magnitudes and units).

a. Prepare a table (like the format of the table in 1e above) of all the forecast models in this assignment but showing just the MAPE values.

b. What do you conclude about the forecasting ability of the models? Which is best for a short-term forecast? Which for a longer-term forecast?

4. Organize and format your Excel file to make it easy for Connie (and your instructor) to find and understand your work and results. Make sure the tabs are logically named and located. Include an Index tab at the beginning and put hyperlinks to the problem solutions tabs.

Attachment:- Student Instructions.rar