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

option to
Add Trendline
appears.

Before we move on with the analysis, let me caution that creating a regression

model from only 6 data points is quite dangerous. Yet, data limitations are often a

Search JabSto ::

Custom Search