YEAR MONTH SALES ADEX MTGRATE HSSTARTS lagsale Trend 90 7 1538 14 10.04 111.2 1 90 8 1360 14 10.1 102.8 1538 2 90 9 1202 68 10.18 93.1 1360 3 90 10 1243 35 10.18 94.2 1202 4 90 11 1076 35 10.01 81.4 1243 5 90 12 691 32 9.67 57.4 1076 6 91 1 1036 94 9.64 52.5 691 7 91 2 891 75 9.37 59.1 1036 8 91 3 1120 76 9.5 73.8 891 9 91 4 1490 73 9.49 99.7 1120 10 91 5 1672 71 9.47 97.7 1490 11 91 6 1517 209 9.62 103.4 1672 12 91 7 1866 95 9.58 103.5 1517 13 91 8 1588 59 9.24 94.7 1866 14 91 9 1430 77 9.01 86.6 1588 15 91 10 1636 94 8.86 101.8 1430 16 91 11 1225 168 8.71 75.6 1636 17 91 12 1027 149 8.5 65.6 1225 18 92 1 1323 100 8.43 71.6 1027 19 92 2 1257 128 8.76 78.8 1323 20 92 3 1915 88 8.94 111.6 1257 21 92 4 1422 124 8.85 107.6 1915 22 92 5 1697 103 8.67 115.2 1422 23 92 6 2053 89 8.51 117.8 1697 24 92 7 1965 156 8.13 106.2 2053 25 92 8 1855 158 7.98 109.9 1965 26 92 9 1911 168 7.92 106 1855 27 92 10 1754 174 8.09 111.8 1911 28 92 11 1559 321 8.31 84.5 1754 29 92 12 1333 206 8.22 78.6 1559 30 93 1 1675 224 8.02 70.5 1333 31 93 2 1360 183 7.68 74.6 1675 32 93 3 1667 281 7.5 95.5 1360 33 93 4 1889 255 7.47 117.8 1667 34 93 5 1906 318 7.47 120.9 1889 35 93 6 2246 235 7.42 128.5 1906 36 93 7 2232 169 7.21 115.3 2246 37 93 8 2300 250 7.11 121.8 2232 38 93 9 2097 231 6.92 118.5 2300 39 93 10 2008 222 6.83 123.2 2097 40 93 11 1931 184 7.16 102.3 2008 41 93 12 1720 177 7.17 98.7 1931 42 94 1 1936 363 7.06 76.2 1720 43 94 2 1847 268 7.15 83.5 1936 44 94 3 2399 229 7.68 134.3 1847 45 94 4 2176 287 8.32 137.6 2399 46

Introduction

This exercise involves the development of a method to forecast future sales. There are two phases to this exercise; phase 1 develops a causal regression model while phase II develops an extrapolative model.

Fanfare International Inc. designs, distribute, and markets ceiling fans and lighting fixtures. The company's product line includes 120 basic models of ceiling fans and 138 compatible fan light kits and table lamps. In the summer of 1994, Fanfare decided it needed to develop forecasts of future sales to help determine future sales force needs, capital expenditures, and so on. The data file named FAN4 contains the following variables:

SALES = total monthly sales in thousands of dollars

ADEX = advertising expense in thousands of dollars

MTGRATE = mortgage rate for 30-year loans (%)

HSSTARTS = housing starts in thousands of units

The data are monthly and cover the period from July 1990 through May 1994.

As a consultant to Fanfare, your job is to find a causal regression model to forecast future sales.

First, create a scatterplot showing sales on the Y-axis and Trend on the X-axis. Explain the how sales rose and fell over time.

Phase I. A causal regression model.

Perform a multiple regression relating Sales to ADEX, MTGRATE, and HSSTARTS.

Answer questions #1 - #5

Perform another multiple regression after removing any variable(s) that don't appear useful to the prediction of sales.

Answer questions #6 - #8

NOTE:

You should note that, to predict future sales, you would need to know future values of Mortgage rate, MTGRATE, and Housing starts, HSSTARTS which would be uncertain projections at best.

Phase II. An extrapolative regression model.

Perform a multiple regression relating Sales to Trend and LagSales.

Answer questions #9 - #11

1. What is the prediction equation for Sales, using all three predictor variables?

2. Now you will perform the "F-test for overall fit. Write out the Ho and Ha for this test for this dataset.

Ho:

Ha:

3. Check the p-value for the F statistic in the ANOVA table in order to perform the "F-test for overall fit. Compare this p-value to an alpha equal to 0.05. What decision to you make? Reject or not reject Ho?

4. What does your decision mean in everyday English?

5. Examine the p-values for each variable's coefficients. Compare these p-values with = 0.05. Do any variables appear to be not useful in the prediction of sales? If so, which one(s)? Use correlation matrix for independent variable to confirm your answer. You can use excel or R to do this.

Perform another multiple regression after removing any variable(s) that do not appear useful to the prediction of sales.

6. What is the prediction equation for Sales in this second analysis of Harris4?

7. Examine the p-values for each variable's coefficients. Compare these p-values with = 0.05. Do any variables appear to be not useful in the prediction of sales? If so, which ones?

8. Check out the Adjusted R² values for each of these two analyses. What are their values?

Adjusted R-Square for first analysis ____________________

Adjusted R-Square for second analysis _________________

NOTE:

Please note that, to predict future sales using model 1, you would need to know future values of Mortgage rate, MTGRATE, and Housing starts, HSSTARTS which would be uncertain projections at best. Now for phase II.

Now Phase II of this exercise.

9. Are the Trend and One-period lag of sales useful as predictors? Check their p-values.

p-value for Trend _______________

p-value for one-period lag of sales __________________

What is the prediction equation for Sales in this lagged regression analysis?

10. Calculate a prediction of Sales for May of 1994. Show work for credit.

11. Check the rise and fall of sales in your scatterplot. Can you think of an equation that uses the trend, lag of sales, and one more variable that is not a lagged variable?

if you can provide a picture of the scatterplot and regressions that would be deeply appreciated.