Microsoft Office Tutorials and References
In Depth Information
Exhibit 3.22 Dialogue box for regression analysis of product E, quarter 1
fact of life and must be dealt with, even if it means basing predictions on very little
data, and assuredly, 6 data points are an extremely small number of data observa-
tions. In this case, it is also a matter of using what I would refer to as a baby problem
to demonstrate the concept. So, how do we perform the regression analysis?
As with the other tools in Data Analysis , a dialogue box, shown in Exhibit 3.22
will appear and query you as to the data ranges that you wish to use for the analy-
sis: the dependent variable will be the Input Y Range and the independent variable
will be the Input X Range . The data range for Y is the set of 6 values (C3:C8) of
observed quarterly sales data. The X values are the numbers 1–6 (B3:B8) represent-
ing the years for the quarterly data. Thus, regression will determine an alpha and
beta that when incorporated into a predictive formula (Y
) will result in
the best model available for some criteria. This does not mean that you are guaran-
teed a regression that is a good ﬁt—it could be good, bad, or anything in between.
Once alpha and beta have been determined, they can then be used to create a pre-
dictive model. The resulting regression statistics and regression details are shown in
Exhibit 3.23.
The regression statistics that are returned judge the ﬁt, good or bad, of a
regression line to the dependent variable values of quarterly sales. The R-square
(coefﬁcient of determination) shown in the Regression Statistics of Exhibit 3.23
=
bX+
α
Search JabSto ::

Custom Search