Homework answers / question archive / Class Project (Company – Aetna) The Multiple Regression Model and Strategic Plan Forecast -  This assignment is the class project report to the executives of your assigned company

Class Project (Company – Aetna) The Multiple Regression Model and Strategic Plan Forecast -  This assignment is the class project report to the executives of your assigned company

Statistics

Share With

---

Class Project (Company – Aetna)

The Multiple Regression Model and Strategic Plan Forecast -  This assignment is the class project report to the executives of your assigned company.  You written discussion is critical and should be well organized and grammatically correct.  The best reports are well written and use Minitab results to support the points being made.  This completed assignment is worth up to 25 points and serves as the class project Strategic Plan Forecast.  Late submissions will not be graded.

This assignment is essentially the multiple regression and strategic plan forecast analysis portion of your project and includes the work in assignments that you have done thus far. This means that I expect you to develop a good regression model forecast using more than one significant independent variables (Xs).  That is you must use at least two independent (X) variables to forecast your company revenue (Y) for 8 quarters beyond the end of the Y variable data series.  This means that

1) You will need to develop the best multiple regression model for the company revenue.

2) Then you will predict (forecast) the hold out period of Y with the model using the hold out values (8) for each X variable.  You should not run the X variables separately -- they should all be in the model at the same time (multiple regression).  Single X variable models (simple regression) will not do for this assignment.

3) Then you should comment on the accuracy of the model using RMSE and MAPE and the reliability of the model with model F, Adjusted R-square, t and p statistics, regression ACF LBQ values, DW statistic and the KB coefficient t-value.

4) Finally, you will need to forecast the next 8 quarters of your company revenue past the end of the company revenue data series. 

Ideally, if you made a good choice of variables you should be able to include all three or more X variables in your regression equation.  Be sure to complete each part and write your responses supported by Minitab/excel work.  Remember, that you are writing this to company executives and you must be clear in what you are showing and why.  This assignment should be turned in to me as a Word document.  You should include excel and Minitab tables and graphs in the body of Word document as required.  Be sure to comment on each of the 10 points below. 

1.   Run scatter plots and a correlation matrix on your project variables and comment on their values and significance if you have done this earlier you may use that analysis here. 

2.  Note any seasonality in the company revenue Y data with ACF (autocorrelation analysis of Y).  You may use ACFs that you previously developed.

3.   Determine if any of your variables require transformation.  If they do, calculate the transformed values and show them in an excel Data Appendix inserted at the end of this report.  Create and comment on a scatter plot with a regression line with Y for each transformed X to illustrate the linearity of the XY data relationship.  Create and comment on a correlation matrix table for the Y, X and X transformed values.

4.   Determine if your model requires dummy variables (e.g. for Y variable seasonality or significant events) and include a table of the dummy variable values for regression analysis. Note that if any seasonal dummy variables are used and is significant then all of the seasonal dummy variables may be used.  Use R square and F as primary determinants of the best model.

Note the significance of each slope term in the model.  Rule-- if the coefficient is not significant then you may not use the variable (dummy or continuous) in the model to forecast with the exception of the seasonal dummy variables above.

5.       Investigate your best model using appropriate statistics or graphs to comment on possible:

a.       Autocorrelation (Serial correlation) with the DW statistic

b.      Heteroscedasticity with a residuals versus order plot (look for a megaphone effect) and apply the KB test.

c.       Multicollinearity with the VIF statistic

Comment on your use of the best remedies for any of the problems identified in 6 above and make the appropriate changes to your regression model if required.  Rerun the model and evaluate the fit again including error measures, R adjusted square, F value, slope coefficient significance, DW and VIF.

6.       Evaluate the best multiple regression model accuracy with 2 error measures (RMSE and MAPE) each for the fit and again for the hold out period.  Comment on the acceptability of the error (accuracy) measures relative to your company.

 

7.       Evaluate the best model fit residuals and comment on their randomness using autocorrelation functions (ACFs) , histogram and a normality plot (You should use a four-in-one graphs as well). Comment on the cause of the error -- trend, cycle, seasonality and if it is statistically significant.  You may also use a histogram with fit to illustrate and comment on residual distribution and mean (indicating any model bias).

 

8.       Forecast for the Strategic Plan period using your forecast X values to forecast Y.  You must use your best Minitab Regression model from the analysis above.  After running the best model in Fit Regression go to the Predict menu and place the columns for the forecast X variables values and any dummy variable predictions in the prediction intervals for new observations area.  The forecast must cover the next 8 quarters past the end of your company revenue data series.  (This is the primary objective of the class)

 

9.       Show at time series plot of the strategic plan forecast that you have developed for your company using the forecasts of your selected X variables.

10.    Comment on the business implications for the 8 quarter strategic plan revenue forecast along with any assumptions that you made relative to future values of the independent variables including any dummy variables.

There is also a slide presentation:

Assignment 11 Strategic Plan Forecast Presentation due by midnight Nov. 28. Prepare a 3 slide 5 minute presentation to the executives (President and VPs) your assigned company on the model and variables that you used, the accuracy and reliability of the model and the Strategic Plan Revenue Forecast for the next 8 quarters.  This is worth up to 2 extra credit points.

You may use statistics and plots in the presentation.  Keep the slides focused on conveying the main points of your findings.  The conclusion or last slide showing the plan forecast should include bullet points on the implications of the forecast to your company's business future.

 

Aetna (AET)
Date	Revenue
31-03-1999	17640
30-06-1999	18760
30-09-1999	20380
31-12-1999	22110
31-03-2000	24310
30-06-2000	25080
29-09-2000	26140
29-12-2000	25390
30-03-2001	23930
29-06-2001	23750
28-09-2001	21800
31-12-2001	25190
28-03-2002	24030
28-06-2002	22550
30-09-2002	21200
31-12-2002	19880
31-03-2003	19080
30-06-2003	18480
30-09-2003	18120
31-12-2003	17980
31-03-2004	18330
30-06-2004	18740
30-09-2004	19310
31-12-2004	19900
31-03-2005	20510
30-06-2005	21130
30-09-2005	21790
30-12-2005	22490
31-03-2006	23300
30-06-2006	24050
29-09-2006	24650
29-12-2006	25150
30-03-2007	25610
29-06-2007	26150
28-09-2007	26810
31-12-2007	27600
31-03-2008	28640
30-06-2008	29670
30-09-2008	30340
31-12-2008	30950
31-03-2009	31830
30-06-2009	32670
30-09-2009	33770
31-12-2009	34760
31-03-2010	34770
30-06-2010	34650
30-09-2010	34460
31-12-2010	34250
31-03-2011	34010
30-06-2011	33810
30-09-2011	33750
30-12-2011	33780
30-03-2012	34310
29-06-2012	34800
28-09-2012	35240
31-12-2012	36600
28-03-2013	37220
28-06-2013	39920
30-09-2013	44040
31-12-2013	47290
31-03-2014	51750
30-06-2014	54720
30-09-2014	56410
31-12-2014	58000
31-03-2015	59100
30-06-2015	59830
30-09-2015	60060
31-12-2015	60340
31-03-2016	60940
30-06-2016	61650

 

FRED Graph Observations
Federal Reserve Economic Data
Link: https://research.stlouisfed.org/fred2
Help: https://research.stlouisfed.org/fred2/help-faq
Economic Research Division
Federal Reserve Bank of St. Louis
Real Net Exports of Goods and Services, Billions of Chained 2009 Dollars, Quarterly, Seasonally Adjusted Annual Rate
Q07053USQ027NNBR
Frequency: Quarterly	Not Seasonally Adjusted
observation_date	NETEXC
2000-01-01	-449.0
2000-04-01	-464.9
2000-07-01	-493.5
2000-10-01	-503.8
2001-01-01	-494.0
2001-04-01	-483.8
2001-07-01	-508.7
2001-10-01	-521.8
2002-01-01	-546.0
2002-04-01	-567.7
2002-07-01	-585.1
2002-10-01	-638.4
2003-01-01	-620.3
2003-04-01	-649.7
2003-07-01	-642.1
2003-10-01	-655.4
2004-01-01	-676.1
2004-04-01	-740.0
2004-07-01	-753.0
2004-10-01	-769.9
2005-01-01	-774.1
2005-04-01	-773.5
2005-07-01	-777.7
2005-10-01	-803.9
2006-01-01	-799.0
2006-04-01	-799.1
2006-07-01	-819.7
2006-10-01	-759.3
2007-01-01	-771.0
2007-04-01	-753.7
2007-07-01	-703.2
2007-10-01	-622.6
2008-01-01	-623.7
2008-04-01	-550.4
2008-07-01	-526.9
2008-10-01	-530.3
2009-01-01	-451.3
2009-04-01	-366.3
2009-07-01	-383.6
2009-10-01	-380.4
2010-01-01	-408.8
2010-04-01	-469.7
2010-07-01	-498.4
2010-10-01	-458.1
2011-01-01	-466.2
2011-04-01	-455.2
2011-07-01	-454.3
2011-10-01	-461.7
2012-01-01	-462.7
2012-04-01	-452.7
2012-07-01	-446.8
2012-10-01	-426.0
2013-01-01	-414.4
2013-04-01	-421.1
2013-07-01	-416.1
2013-10-01	-368.1
2014-01-01	-412.0
2014-04-01	-427.5
2014-07-01	-409.4
2014-10-01	-454.0
2015-01-01	-521.2
2015-04-01	-524.9
2015-07-01	-547.1
2015-10-01	-566.6
2016-01-01	-566.3
2016-04-01	-556.3