Microsoft Office Tutorials and References

In Depth Information

**Multi-Regression**

to change all the values reading Yes to 1, and all the values reading No to 0. See

Figure 14.6.

You can find the data on the sheet named Balcony Replaced.

Now you are ready to use the Regression of the Analysis ToolPak on this

extended data. This time you will request on the menu to calculate the Residuals. It

will result in calculating the theoretical Y forecasted (Price.) You will be able compare

it to the observed data with the theoretical (calculated) values and the Residuals

(differences). See Figure 14.7 for the data and the Regression menu.

Running the regression on the extended data you will get much better results.

This time you did take into consideration more factors/variables. In addition to

Size and Bathrooms you are adding Location (City), Corner, and Balcony. See

Figure 14.8.

The first thing to notice on the new results is R
2
in cell B5
. This time R
2
is almost

1.0, the highest and best possible value. You were able to achieve such a high R
2

because you have the correct variables.

Note that all the p-values in cells E18:E22 are 0.00. Theses have to be less than 5

percent to be included in the list of acceptable variables.

The Standard Error in cell B7 is reduced to 5.0697. In other words 95 percent of

the time using our model predicted results would be within

þ

1.96

×

5.0697 of our

þ

forecast or within

$9,936.61. It will be an accurate forecast.

FIGURE 14.7
Calculating the Regression for the Complete Example