Slides_Higgins_12e_Ch_3.pptxFinancial Forecasting
Chapter Three
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Copyright © 2019 by McGraw-Hill Education. All rights reserved.
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
1
Introduction
From the past (Chapters 1 and 2) to the future. . .
financial forecasting
planning
budgeting
This chapter describes techniques that are part of planning.
Ch. 3 2
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Why financial forecasting?
Much of the language of business forecasting is financial.
A key issue in any plan is determining whether it is financially feasible.
Ensure the consistency of internal goals.
Be prepared for various possible outcomes.
Other reasons?
Ch. 3 3
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Pro Forma Statements
Pro forma financial statements are a prediction of what financial statements will look like in the future.
A major purpose is to estimate the future need for external funding.
Ch. 3 4
External funding required = Total assets – (Liabilities + Owners’ equity)
Why do practitioners call this the “plug”?
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Percent of Sales
Forecast future sales, and tie other items in income statement and balance sheet to the sales forecast
Works well for variable costs, most current assets, and current liabilities
Not generally true for fixed assets
Ch. 3 5
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Steps to Creating Pro Formas
Examine historical data to observe patterns.
Forecast sales.
Forecast items that grow in proportion to sales.
Forecast other financial statement items
Estimate external funding required
Evaluate how to cover the shortfall (or use the surplus)
Ch. 3 6
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Example: R&E Supplies
R&E Supplies, Inc. is a wholesaler of plumbing and electrical supplies.
R&E has been a customer of Suburban National Bank for many years.
Average deposits have been $30K.
Short-term renewable loan has been $50K, with a 5-year maturity.
Ch. 3 7
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
R&E’s Current Situation
In late 2017, R&E asks that the loan amount for 2018 be increased to $500K.
R&E explains that because of growth, AP has gone up and cash balances have gone down.
Suppliers are threatening to go to COD.
Why $500K?
Pro forma financial statements will give better quantitative justification for R&E’s request.
Ch. 3 8
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Step 1: Examine R&E’s Historical Data
Study R&E’s recent financial statements, Table 3.1.
Ch. 3 9
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.1 Income Statements for R&E Supplies 2014–2017
Ch. 3 10
What’s happening with R&E’s profitability?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.1 Balance Sheets for R&E Supplies December 31, 2014–2017
Ch. 3 11
Why is cash declining?
Why are AP increasing?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Step 2: Forecast R&E’s Sales
R&E projects 25% sales growth
This number should be carefully determined with input from many in the organization
Ch. 3 12
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Step 3: Forecast Items that Grow in Proportion to Sales
See historical and forecasted ratios, Table 3.2.
Ch. 3 13
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.2 Historical Financial Ratios for R&E Supplies 2014–2017 and 2018 Forecast
Ch. 3 14
Are these forecasts reasonable?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Step 4: Forecast Other Items Not Closely Linked to Sales
Prepaid expenses = rough guess
Net fixed assets?
capital budget of $43K already approved
$50K depreciation
$280K = $287K (prior year) + $43K – $50K
Bank loan initially set to $0, but only temporarily.
Ch. 3 15
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Additional Items
Current portion (100) of long-term debt is contractual (760 = 660 + 100)
Note assumption that new loans = 0
Retained earnings?
Prior year RE + income statement earnings – dividends
Ch. 3 16
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.3 Pro Forma Income Statement for R&E Supplies, 2018
Ch. 3 17
Why are projected earnings so low relative to 2017?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.3 Pro Forma Balance Sheet for R&E Supplies, December 31, 2018
Ch. 3 18
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Ch. 3 19
You try it. Project pro forma balance sheet items
Given the pro forma income statement, what would be Trapezoid’s projected shareholders’ equity for Dec. 31, 2018? Assume a dividend payout ratio of 25% and no new issues of equity.
What would be Trapezoid’s projected net fixed assets for Dec. 31, 2018? Assume capital expenditures of $200 for 2018, and no sale or disposal of assets.
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
300 + (1-0.25)(100) = $375
1,000 + 200 – 100 = $1,100.
19
Step 5: Estimating the External Funding Required
Income statement measures profitability, and garners most investors’ attention.
The CFO focuses on the balance sheet to estimate funding needs.
External funding required = Assets – Liabilities and Equity
In R&E’s first-stage pro formas, EFR = $1,422,000
Ch. 3 20
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Banker’s Reaction?
EFR = $1.4 million >> $500K!
Not good news about the CFO
Still, AR = $3.6 million, which would provide security.
Ch. 3 21
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Other Issues: Interest Expense
Circular reasoning
Interest this year is based on debt this year.
But interest this year feeds into earnings this year, and therefore into balance sheet retained earnings.
Debt this year, needs to be determined by the gap between assets and liabilities in the balance sheet.
Can try a decent plug, such as basing the interest on the prior year debt
Can iterate, because the two need to be determined simultaneously (spreadsheets can handle this).
Ch. 3 22
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Other Issues: Seasonality
External financing needed is only computed on the date of the balance sheet.
What about points of time in between?
Do a series of these, quarterly, monthly, etc.
Ch. 3 23
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Pro Forma Statements & Financial Planning
The initial financial plan, as embodied within the pro forma, provides the starting point for a discussion about operations.
If the external amount of financing is too large, what kinds of operating changes need to be made, relative to pro forma?
Different level of investment?
Sale of assets?
Different working capital policy?
Cutting costs, with associated impact on revenue?
Ch. 3 24
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
One solution would be a $1.4 million loan from Suburban National Bank.
This might be more than Suburban (or R&E) is comfortable with.
What if loan is limited to $1 million?
Where to shave $400K?
Tighten up AR, so that DSO drops from 51 to 47?
Increase payables period from 59 to 60?
These might lower sales growth (2520%) and increase costs (SG&A 1212.5%) from foregone discounts .
Ch. 3 25
Step 6: Evaluate How to Cover the Shortfall
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.4 Revised Pro Forma Income Statement for R&E Supplies, 2018
Ch. 3 26
How did the revised plan affect profits?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Ch. 3 27
TABLE 3.4 Revised Pro Forma Balance Sheet for R&E Supplies, December 31, 2018
Is everybody happy now?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Why Are Lenders So Conservative?
If expected loan returns are low, lenders cannot accept high risk.
Look at the lending margin (spread) between paying depositors and what the loan pays.
So getting a high ROE requires high financial leverage (like 10-to-1).
Complete default by just a few borrowers can erase a bank’s earnings.
The aggressive lenders have long since gone bankrupt.
Ch. 3 28
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Forecasting with Spreadsheets
Spreadsheets allow for pro forma financial statements to be prepared efficiently.
Specifying assumptions explicitly on the spreadsheet builds flexibility into the model.
Table 3.5 lays out Excel spreadsheet with formulas for the previous example of R&E Supplies.
(The formulas shown in the table would not be visible in an actual spreadsheet.)
Ch. 3 29
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.5 Forecasting with a Computer Spreadsheet: Pro Forma Financial Forecast for R&E Supplies, 2018
Ch. 3 30
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.5 Forecasting with a Computer Spreadsheet: Pro Forma Financial Forecast for R&E Supplies, 2018 (cont.)
Ch. 3 31
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Building the Spreadsheet: Key points
Always create formulas by referring to the cells in the assumptions box.
For example, for sales: = B3+B3*C4
Not: B3+B3*0.25
The spreadsheet can handle the interdependence between interest expense and external funding required.
In Excel, go to File | Options | Formulas
In “Calculation Options” click on “Enable iterative calculation”
Ch. 3 32
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Ch. 3 33
You try it. Write Excel equations for pro forma statements.
Write the equations for cells (a) C4, (b) C5, (c) C9, (d) G15, and (e) G17.
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
a. =B4+C14*B4 b. =C15*C4 c. =C17*(G12+G14+G17) d. =F15+(1-C16)*C12 e. =G9-G16
33
Sensitivity Analysis
“What if” questions:
What if sales growth is only 15%, instead of 25%?
What if COGS is 84% instead of 85%?
Benefit #1: sensitivity analysis produces a range of outcomes.
Benefit #2: sensitivity analysis induces managers to prioritize their assumptions according to importance.
Ch. 3 34
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Scenario Analysis
In practice, forecast variables change together, not one at a time.
Develop a set of scenarios with different co-movements.
Each scenario is built around a story or narrative, such as losing a major customer or facing a new competitor.
Ch. 3 35
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Simulation
Assign probability distributions to each major variable.
Run many pro formas, with the variable values drawn from a Monte Carlo process.
Advantage: many scenarios
Disadvantage: many managers do not think in terms of probabilities, and the planning issues are opaque
Ch. 3 36
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
FIGURE 3.1 Simulating R&E Supplies’ Need for External Funding: Frequency Chart
Ch. 3 37
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
FIGURE 3.1 Distribution Gallery for Sales Growth
Ch. 3 38
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Cash Flow Forecasts
A listing of anticipated sources and uses of cash
Based on same assumptions as the pro forma income statement and balance sheet
EFR = Total uses – Total sources
Easily understood and commonly used
Not as informative as pro forma statements
Ch. 3 39
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.6 Cash Flow Forecast for R&E Supplies, 2018
Ch. 3 40
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Cash Budgets
A listing of projected cash receipts and disbursements over a forecast period
Pro forma statements rely on accrual accounting.
Cash budgets are strictly cash accounting.
Cash budgets require translation from accrual projections to cash projections.
Adjust for timing of collections and payments.
Example: Jill Clair Fashions’ monthly cash budget
2%/10 net 30 days – factoring it in
Ch. 3 41
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
TABLE 3.7 Cash Budget for Jill Clair Fashions 3rd Quarter, 2018
Ch. 3 42
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Ch. 3 43
TABLE 3.7 Cash Budget for Jill Clair Fashions 3rd Quarter, 2018 (cont.)
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Ch. 3 44
TABLE 3.7 Cash Budget for Jill Clair Fashions, 3rd Quarter, 2018 (cont.)
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Higgins, Analysis for Financial Management, 12e
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Bottom Line
The last line in Jill Clair Fashions’ monthly cash budget shows the projected surplus (deficit) for each month.
The budget projects that the treasurer:
Needs to borrow $40,000 in July
Can reduce loan to $10,000 in August
Will have surplus funds of $30,000 in September
Ch. 3 45
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
The Techniques Compared
Pro forma statements, cash flow forecasts, and cash budgets all produce the same results.
Pro forma statements are often best for overall planning purposes.
A cash budget is good for cash management.
Cash flow forecasts lie somewhere between.
Whichever technique is used, it is only part of the overall planning process.
Ch. 3 46
Higgins, Analysis for Financial Management, 12e
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Current assets800Current liabilities400Sales1,000
Net fixed assets1,000Long-term liabilities1,100COGS600
Total assets1,800Shareholders' equity300Operating exp.100
Total liab. & equity1,800Depreciation exp.100
EBIT200
Interest exp.50
Taxes50
Net income100
Trapezoid Corporation
Balance Sheet
actual
Dec. 31, 2017
Income Statement
pro forma
2018
Sheet1
| Trapezoid Corporation | |||||||
| Balance Sheet | Income Statement | ||||||
| actual Dec. 31, 2017 | pro forma 2018 | ||||||
| Current assets | 800 | Current liabilities | 400 | Sales | 1,000 | ||
| Net fixed assets | 1,000 | Long-term liabilities | 1,100 | COGS | 600 | ||
| Total assets | 1,800 | Shareholders' equity | 300 | Operating exp. | 100 | ||
| Total liab. & equity | 1,800 | Depreciation exp. | 100 | ||||
| EBIT | 200 | ||||||
| Interest exp. | 50 | ||||||
| Taxes | 50 | ||||||
| Net income | 100 | ||||||
| 1. What is shareholders' equity, year-end 2003? | |||||||
| 2. What is shareholders' equity, year-end 2004? | |||||||
| Assume that in 2004, dividends paid were 50 and no new equity was paid in. | |||||||
| 3. What is net working capital, year-end 2003? | |||||||
| 4. What are net fixed assets, year-end 2004? | |||||||
| Assume that in 2004, capital expenditures were 200. |
