Trend Projection Model

1
Trend Projection Model
b1
Yi
b0
ltlt The X variable in a Trend Projection model is
the time period t
2
Trend Projection in Excel

• Two techniques
• On the Chart, select the time series and then
  right-click to ADD TRENDLINE. Pick the linear
  trendline option and display the equation and
  R-square
• or
• On a Worksheet that contains the time series,
  select the TOOLS menu in Excel 2003 or the DATA
  tab in Excel 2007 open the DATA ANALYSIS
  submenu, and select the REGRESSION option in the
  dialog box.
• See snippits in Blackboard to add-in the Data
  Analysis submenu if it does not show up as an
  option in Excel on your PC at home.

3
Excels Regression Option
4
Class Exercise Trend Projection Output for
Trend.xls
5
Trend Projection Model
From Excel Printout
6
The Coefficient of Determination

• r2 is a number between 0 and 100
• Measures the proportion of variation in Y that
  is explained by the independent variable X in
  the regression model
• In trend projections, you can interpret it as the
  proportion of variation in Y that is explained
  by the presence of linear trend.

7
Simple Linear Regression Example
You want to examine the linear dependency of the
annual sales of produce stores on their size in
square footage. Sample data for seven stores were
obtained. Find the equation of the straight line
that fits the data best.
Annual Store Square Sales
Feet (1000) 1 1,726 3,681 2
1,542 3,395 3 2,816 6,653
4 5,555 9,543 5 1,292 3,318
6 2,208 5,563 7 1,313 3,760
8
Which is the dependent Y variable?

1. The Store Number
2. The Square Footage of the Store
3. The Annual Sales of the Store

9
Which is the independent X variable?

1. The Store Number
2. The Square Footage of the Store
3. The Annual Sales of the Store

10
Scatter Diagram Example
Excel Output
11
Equation for the Sample Regression Line Example
From Excel Printout
12
Graph of the Sample Regression Line Example
Yi 1636.415 1.487Xi
?
13
Interpretation of Results Example
The slope of 1.487 means that for each increase
of one unit in X, we predict the average of Y to
increase by an estimated 1.487 units.
The model estimates that for each increase of one
square foot in the size of the store, the
expected annual sales are predicted to increase
by 1487.
14
Restaurant Sales Exercise (Regress.xls)

• The manager wants to forecast restaurant sales
  for quarter 11
• On the Trend Projection worksheet, generate the
  Trend Projection output starting in cell A17 and
  forecast restaurant sales for quarters 1-10
• On the Regression worksheet, perform a regression
  of restaurant sales on student population. Write
  the regression output starting in cell A17 and
  forecast restaurant sales for quarters 1-10

15
Trend Projection Output
16
Regression on Student Population Output