FINANCIAL FORECASTING

1
CHAPTER 5

• FINANCIAL FORECASTING

2
FINANCIAL FORECASTING

• Percent of Sales Method
• Linear Trend Extrapolation
• Regression Analysis

3
PERCENT OF SALES METHOD

• A. PERCENT OF SALES METHOD
• Simplest forecasting method
• Forecasting the income statement and balance
  sheet items as percentages of sales forecast
• Sales forecast is assumed to be given

4
PERCENT OF SALES METHOD

• 1. Forecasting Income Statement
• 2. Forecasting Assets on Balance Sheet
• 3. Forecasting Liabilities on Balance Sheet
• 4. Discretionary Financing

5
PERCENT OF SALES METHOD

• 1. Forecasting Income Statement
• - Use Common-Size Income Statement
• - Determine items that will change with sales
• i. Cost of Goods Sold
• ii. Selling and GA Expenses (maybe)
• - Assume that the sales forecast is given

6
PERCENT OF SALES METHOD

• 2. Forecasting Assets on Balance Sheet
• We can not use common-size balance sheet for
  forecasting assets
• Decide on the Assets that may change with Sales
• Cash Balance
• Accounts Receivable
• Inventory
• Plant and Equipment
• Accumulated Depreciation

7
PERCENT OF SALES METHOD

• 3. Forecasting Liabilities on Balance Sheet
• We need to categorize liabilities into two
  groups
• a) Spontaneous Sources of Financing
• Arise in ordinary course of business, change
  with sales
• Example Accounts Payable, Other Current
  Liabilities
• b) Discretionary Sources of Financing
• These sources of financing requires great
  effort. Involve upper-level management decisions.
  Do not change with sales.
• Example Bonds, Bank loans, Common and Preferred
  Stock

8
PERCENT OF SALES METHOD

• Accounts Payable and Other Current Liabilities
• Change with sales
• Retained Earnings
• Previous year level Additions in this year
  (from Income Statement
• Other Items of Liability Section
• Assume same level as previous year

9
PERCENT OF SALES METHOD

• 4. Discretionary Financing
• Balance sheet plug
• Total Assets Total Liability and Owners Equity
• A negative value forecasts a surplus of
  discretionary financing
• A positive value, forecasts a deficit of
  discretionary financing, and means that more
  discretionary funds will be needed.

10
LINEAR TREND EXTRAPOLATION

• B. LINEAR TREND EXTRAPOLATION
• In the percent of sales method, we assumed that
  you are given the sales forecast.
• Assume that you are not given the sales forecast
  but you have to do it yourself
• TREND function of Excel
• TREND(Known_Ys, Known_Xs, New_Xs, Constant)
• Y is the variable we want to forecast (dependent
  variable)(in our example it is Sales)
• X is the variable we use to forecast Y
  (independent variable) (in our example, it is
  Years)
• New_X is the new variable value to forecast Y
• Constant is a TRUE/FALSE variable. If you want an
  intercept, write True, else write False.

11
Adding a Trendline to the Chart

• Double Click the X-Y scatter chart, click on the
  plot and right click the mouse
• Choose Insert Trendline from the menu
• Displaying the Trend Equation
• - Right click the mouse on the trendline, choose
  Format Trendline, go to Options tab select
  Display Equation on the Chart -

12
REGRESSION ANALYSIS

• C. REGRESSION ANALYSIS
• Regression analysis is the method used to fit the
  best line to a data set
• The best line is the line that minimizes the sum
  of squared errors. The errors are the difference
  between the actual data point and the one
  predicted by the model.

13
REGRESSION ANALYSIS

• Example
• Suppose you want to buy a Yahoo stock, and you
  want to know how the stock price moves with the
  market
• You want to explain the return on Yahoo stock by
  the return on SP 500 index

14
REGRESSION ANALYSIS

• First, you should collect data on the returns of
  Yahoo and SP 500.
• Enter these data on Excel (Most probably, the
  data you found will be price data)
• Find the returns
• Return (Price Now) / (Price one period ago) -
  1
• Note after finding the first returns of SP 500,
  and Yahoo, drag the formulas to other cells

15
REGRESSION ANALYSIS

• Select the return range of SP 500 and Yahoo, go
  to Chart Wizard, and create a Scatter Plot,
  choose Use 1st Column as X data, so SP 500
  returns will be on the X-axis, and Yahoo returns
  will be on the Y axis

16
REGRESSION ANALYSIS

• Analyzing the relation between the returns of
  Yahoo and SP 500 from the Scatter diagram is a
  little difficult. You can possibly detect a vague
  positive relation between the Yahoo and SP 500
  returns.
• You can add a trend line in the chart to help you
  see the linear relation.

17
REGRESSION ANALYSIS

• Regression Equation
• You want to explain the returns of Yahoo in
  relation to the returns of SP 500. Similar to
  the Linear Trend Equation the equation is as
  follows
• Yahoo Return a b(SP500 return) e

18
REGRESSION ANALYSIS

• Here Yahoo returns is the dependent variable that
  we want to explain
• SP500 returns is the independent variable we use
  to explain Yahoo returns
• a is the intercept of the regression equation
• b is the slope of regression equation
• e is the error term Error between the actual
  data and the fitted data

19
REGRESSION ANALYSIS

• Regression Analysis Using Excel
• - Go to Tools Menu, click on Data Analysis
• - Select Regression
• - When the regression dialog box appears,
• Enter the range which covers Yahoo Returns in
  the Input Y Range (Y is our dependent variable)
• Enter the range which covers SP500 Returns in
  the Input X Range (X is our dependent variable)

20
REGRESSION ANALYSIS

• - If labels are included in the entered range in
  X and Y values, check the Label box
• - Tell Excel to form a sheet for the regression
  results. To do this
• Select New Worksheet Ply, and in the right box
  enter a name for sheet e.g. Yahoo vs. SP500
• -Press OK

21
REGRESSION ANALYSIS

• - Regression results will appear in the Yahoo
  vs. SP500 sheet
• - Look at the coefficients
• Intercept is
• Slope is SP500
• So,
• Yahoo Return ______ _______SP500 return

22
REGRESSION ANALYSIS

• - Interpretation of Regression Outputs
• P-value The probability of observing a
  co-efficient at least this magnitude if there
  were no relations between the dependent variable
  and the independent variables we observe.
• Would a small p-value be a strong or weak
  evidence of a relation?
• R-square How much of the variation of the
  dependent variable is explained by the regression
  equation?
• A larger R-square indicates better ability for
  the independent variables to explain the
  variations in Y.

23
REGRESSION ANALYSIS

• Typically, there are two steps involved in
  regression forecast.
• Model estimation
• Predictions with estimated parameters and new
  values of the independent variables