Microsoft Office Tutorials and References
In Depth Information
values for the individual quarters and the Yrly Totals. To use a single vertical axis
would make viewing the movement of the series difﬁcult. By selecting any data
observation associated with the Yrly total with a right-click, a menu appears that
permits us to format the data series. One of the options available is to plot the series
on a secondary axis. This feature can be quite useful when viewing data that vary in
magnitude.
3.5.2 Linear Regression
Now let us introduce a tool that is useful in the prediction of future values of the
series. The tool is the forecasting technique linear regression , and although it is
not appropriate for all forecasting situations, it is very commonly used. There are
many sophisticated forecasting techniques that can be used to forecast business and
economic data that may be more appropriate depending on the data that is to be
analyzed. I introduce linear regression because of its common use and instruc-
tive character—understanding the ideas of a linear model can be quite useful in
understanding other more complex models. Just as in our graphical analysis, the
choice of a model should be an intensive and methodical process.
Linear Regression builds a model that predicts future behavior for a dependent
variable based on the assumed linear inﬂuence of one or more independent variables.
The dependent variable is what we attempt to predict or forecast, in this case sales
values for quarters, and the independent variable is what we base our forecast on,
in this case, the year into the future. The concept of a regression formula is relatively
simple: for particular values of an independent variable, we can construct a linear
relationship that permits the prediction of a dependent variable. For our product E
sales data, we will create a regression model for each quarter. So, we will construct
4 regressions. We do this to avoid the need to explicitly consider seasonality in the
linear regression. Our assumption is that there is a linear relationship between the
independent variable, year , and the dependent variable, quarterly sales .
Simple linear regression , which is the approach we will use, can be visualized
on an X–Y coordinate system—a single X represents the independent variable and
Y the dependent variable. Multiple linear regression uses more than one X to predict
Y. Simple regression ﬁnds the linear relationship that best ﬁts the data by choosing
a slope of the regression line, known as the beta (
β
), and a Y intercept (where the
α
line crosses the Y axis) known as the alpha (
). If we examine the individual series
in Exhibit 3.20, it appears that all quarters, except for 4, are a good linear ﬁt with
years. Notice the dip for quarter 4 in year 3. To more closely understand the issue
of a linear ﬁt , I have drawn a linear trend line for the quarter 1 series in Exhibit
3.20—marked Linear (Qtr1) in the legend. As you can see, the ﬁt of the line nicely
tracks the changes in the quarter 1 series. By selecting a series and right clicking, an