Microsoft Office Tutorials and References
In Depth Information
to change all the values reading Yes to 1, and all the values reading No to 0. See
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
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
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