Chapter 5:Financial Forecasting and Estimation

1
Chapter 5Financial Forecasting and Estimation

• 3 forecasting techniques discussed in the
  textbook
• Percent of Sales Method, Trend function, and
  Regression function.
• Percent of Sales Method (Skip P140 148)
• Forecasting the Income statement items
• Items have the consistent relationship with sales
  over time.
• Trend Analysis
• Linear Trend Extrapolation

2
Chapter 5

• Regression
• 1. estimate a relationship among variables we
  are interested in. 2. Forecasting
• Sales vs. interest rate
• Stock return vs. market return
• Spending vs. income
• House prices vs. Attributes of a house

3
Chapter 5 Forecasting

• Where we find the Excel Regression Function??
• Excel 2003 TOOLS gt Data Analysis gt
  Regression
• If necessary, choose Add-ins and check on
  Analysis Toolpak
• Excel 2007 DATA gt Analysis gt Regression
• Click on the Ribbon and choose Excel Option
  and
• check Analysis Toolpak under Add-ins option.
• How to interpret the regression results??

4
Regression

• Regression fitting the best line to a data set
  
• 1. Linear relationship between variables 2.
  Forecasting
• There are two types of regression models
  depending the number of independent
  variables

5
Graph for regression line and data

• For Excel 2007, use Scatter option in Charts
  under Insert Menu. For titles and names, use
  options under Layouts.
• For Excel 2003, use Wizard Chart and pick scatter
  diagram option. Use axis format for titles and
  axis names.

6
Regression Line
7
1. A simple regression

• There is only one independent variable.
• Y a bX, where Y is called an dependent
  variable and X is called an independent or
  explanatory variable
• Example (1) Stock return a b Market return,
  
• b is stock beta
• (2) Sales a b interest
  rates

8
2. Multiple regression

• There are more than one variables.
• Y a bX1 cX2 dX3
• Example Housing price appraisal.
• where Y is housing price and X's are
  
• characteristics of a house such as size,
  
• garage, pool, land size

9
More example

• Wine consumption as a function of age and gender
  type
• Baseball Salary Arbitration
• Salary is a function of players records such
  as RBI, Homeruns, Steals, Hits, .

10
Regression Equations

• Y a bX in general
• IBM returns 0.05 1.3SP500returns
• Sales 145 - 2300 Interest rates
• Wine Cons 0.18 0.06Age - 0.69Gender
• House prices 65363 10802landsize 18.9Sqft
  4640OpenPool . . . . . . . .

11
Forecasting An example

• Sales 145 - 2300 Interest rates
• This is the regression equation estimated given
  the data
• What is the sales units forecast when the
  interest rate is 5?
• Predicted sales 145 - 23005 30 (units)