Description
Understand accounting standards related to prospective information. Discuss uses and applications for financial models, including uses as decision making tools.
Financial Modeling & Forecasting
Jason MacMorran
www.pncpa.com
Presentation Outline
I. Introduction and Learning Objectives
II. Definitions and Standards Overview
III. Uses for Financial Models
IV. Basics of Financial Modeling
V. Basics of Financial Analysis
VI. Sensitivity and Scenario Analysis
VII. Conclusion
Learning Objectives
Understand accounting standards related to prospective
information.
Discuss uses and applications for financial models, including
uses as decision making tools.
Understand basic design and creation of a financial model.
Understand uses for financial analysis and sensitivity analysis.
Definitions and Standards
Overview
Definitions and Standards Overview
The words projection, forecast, pro forma, model, etc., are
often used interchangeably relative to prospective
information.
In an accounting context, they have different meanings.
Generally speaking, the projection, forecast, pro forma or
budget will be the ‘output’, and the financial model will
include the ‘inputs’ and ‘output’.
This section is not intended to be authoritative or a detailed
look at standards, but a general overview of what to look for
and where to find it!
Key Accounting Terms
Forecast – presents an entity’s expected financial position,
results of operations, and cash flows, based on responsible
party’s assumptions reflecting conditions it expects to exist
and actions it expects to take.
Projection – presents an entity’s financial position, results of
operations, and cash flows, based on one or more
hypothetical (what ifs) assumptions provided by a
responsible party.
Hypothetical Assumption - an assumption used to present a
condition or course of action that is not necessarily expected
to occur, but is consistent with the purpose of the projection
(i.e. expansion scenario).
Key Accounting Terms
Financial Analysis – practitioner develops assumptions,
analyzes results, and recommends a course of action.
Partial Presentation – a presentation of prospective financial
information that excludes one or more of the items required
for prospective financial statements.
Responsible Party – person or persons responsible for
assumptions underlying the prospective financial statements.
Types of Presentations
Prospective Information
Prospective Financial Statements
Forecast
Projection
Prospective, but not Financial Statements
Partial presentations
Financial analyses
Not Prospective Information
Pro forma, based on historical amounts
Expired budgets
Uses of Prospective Information
General Use – use by persons with whom the responsible
party is not directly negotiating, for example:
Offering of debt or equity securities under SEC
regulations.
Offering of tax-exempt bonds.
Limited Use – use only by the entity with whom the
responsibly party is negotiating, for example:
Private placement.
Negotiating bank financing.
Merger negotiations.
Internal Use – solely for use by the responsible party.
Uses of Prospective Information
Type of Prospective
Presentation
Appropriate Uses
General Use Limited Use Internal Use
Forecast Yes Yes Yes
Projection No Yes Yes
Partial Presentation No Yes Yes
Financial Analysis No Yes Yes
Types of Engagements
Third-party Use:
Special disclaimer on current year budgets – practitioners
may not be required to apply any procedures if they make
certain disclaimers.
Compilation – assembling prospective statements in
conformity with presentation guidelines and issuing
report.
Agreed-upon procedures – varies by engagement, and
can be very limited or quite extensive.
Examination – evaluating assumptions and presentation
of financial information and issuing report.
Internal Use:
Assembly – no type of assurance, does not require report.
Independence
Independence is required in examination and agreed-upon
procedures engagements, but not in compilation or internal
use engagements because no assurance is expressed.
Exceptions
Litigation and valuation projects often have exceptions to
procedure and presentation rules, so long as they are
properly disclaimed.
Key Finance Terms
Net Present Value – present value of expected future cash
flows minus initial investment.
Internal Rate of Return – discount rate at which investment
has zero net present value.
Resources
Accounting
AICPA Attestation Standards
AICPA Guide for Prospective Financial Information
AICPA Practice Aid 06-2 Preparing Financial Models
PPC’s Guide to Forecasts and Projections
Finance
Brealey & Myers Principles of Corporate Finance
Uses for Financial Models
Background
Financial models should tell a story.
What is the business going to do?
How is it going to do it?
How is it reflected in the financials?
Financial models capture the future operating, investing and
financing activities that determine future profitability,
financial position, and risk.
Financial models should be comprehensive, internally
consistent, and externally reasonable.
Background
Financial models can integrate elements of accounting,
finance, economics, corporate psychology, and business
philosophy.
Accounting based models – compilations for investors /
creditors.
Finance / economics based models – decision making (net
present value, internal rate of return, etc.).
Psychology / Philosophy – In a ‘decision making’ model, how
will competitors react to your planned actions? Does the plan
involve risks you know you’re unwilling to take?
Uses for Financial Models
Financing (debt or equity)
Buy vs. Lease
Valuation
Budgeting
Business Plans
Strategic Plans
Expansion
Merger / Acquisition
Lost Profits
Business Interruption
Litigation Support
Start-ups
Contraction / Closure
Financial Models in Everyday Life
A ‘financial model’ does not have to be complex!
Simple situations call for simple financial models:
Buy vs. lease of vehicle
Impact of change in interest rate on borrowing
Payback period on an investment
Complex situations call for complex financial models:
Merger and acquisition pro-forma and future cost savings
Entering new markets / launching new products
Litigation
Basics of Financial Modeling
Basics of Financial Modeling
Define the need
Basic organization
Sample construction
Other layout considerations
Define the Need
What is the desired goal of the financial model?
Launching a new product? Integrating a potential
acquisition? Refinancing debt?
Who is the expected user?
Management? Investors? Bankers?
What is the expected use?
Internal? External?
The above issues will govern the sophistication and reporting
requirements of a financial model.
Define the Need
Presentation / engagement will depend on purpose and
audience.
May require complete financial statements over a defined time
period.
May require limited information over a defined time period.
May require ‘one-time’ sources and uses of funds.
Complete Financial Statements
Most difficult and time consuming to prepare, but also most
instructive to user:
Balance Sheet – measures future liquidity and leverage
Income Statement – measures future operating results
Statement of Cash Flows – outlines future cash needs for
growth (investment and borrowing) and returns to
investors
Collectively, complete financial statements can help measure
return on investment and potential risks.
Limited Information
Most common, including:
Prepare a twelve month budget
Six months of start-up expenses
Amortize a loan over five years
Do not necessarily have to be financial statements!
Sources and Uses of Funds
Where is money coming
from (bank debt, equity,
etc).
What is it going to be
spent on (equipment,
refinance, operating costs,
etc).
Often seen in refinancing
and bond offerings.
Contribution Cap %
Sources
Cash on Hand 500 $ 0.6%
Bank Revolver 0 0.0%
Senior Bank Note 20,000 23.4%
Subordinated Bank Note 5,000 5.8%
Owner Contribution 10,000 11.7%
New Equity 50,000 58.5%
Total Sources 85,500 $ 100.0%
Uses
Purchase new equipment 10,000 $ 11.7%
Operating costs 25,000 29.2%
Office manager 40,000 46.8%
Refinance Existing Debt 0 0.0%
Working Capital 10,000 11.7%
Cash 500 0.6%
Total Uses 85,500 $ 100.0%
SOURCES AND USES
Time Period
Generally, time period should match the business life cycle.
5 year projection for a 20 year real estate deal may not be
helpful.
20 year projection in a rapidly changing industry may not
be relevant.
How to handle changes in the economy?
For litigation or damage oriented models, time period should
correspond to period of damage.
Basic Organization
Most financial models include the following basic elements:
Identification of the problem to be solved (buy vs. lease,
expansion, new location, etc.)
Key assumptions (sales growth, margins, capital
expenditures, impact of competitors, etc.)
Output / results (financial statements, net present value,
go / no go decision, etc.)
Sample Construction
Problem - Client / Employer wants to expand an existing
product into a new geographic market (new location).
Which question is better:
Will the project be profitable?
Will the project provide an adequate return on investment?
Accounting based financial statements will show profitability.
Net present value / internal rate of return analysis will show
return on investment.
Sample Construction - Scope
‘Problem’ and expected use/users will define scope of financial
model.
In the case of expansion / new location, the following factors
should be considered:
Will be a long-term project
Will likely require significant investment (equity and /or
debt)
Will likely require consideration of competitor reactions
Based on this ‘problem’ and expected use/users, output should
be complete presentation over a long-term, with consideration
of ‘return’ on the investment.
Sample Construction - Assumptions
What are key assumptions?
Will vary by client and industry
May be impacted by current economic environment in
short-term
Critical points:
Assumptions should be defined separately (i.e. a separate
‘tab’ in Excel workbook); often referred to as ‘projection
drivers’
Assumptions should be reasonable and logical
Assumptions should be supported by historical trends,
industry trends, economic data, or other data (will discuss
later)
Bad assumptions = bad decisions!
Sample Construction - Assumptions
Sample Company
Projection Assumptions
Growth Rates
Line Item Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Revenues 5% 5% 5% 5% 5%
Salaries 5% 5% 5% 5% 5%
Benefits 5% 5% 5% 5% 5%
Supplies 5% 5% 5% 5% 5%
Licenses 5% 5% 5% 5% 5%
Utilities 5% 5% 5% 5% 5%
Repairs and maintenance 5% 5% 5% 5% 5%
Insurance 5% 5% 5% 5% 5%
Telephone 5% 5% 5% 5% 5%
Management fees 5% 5% 5% 5% 5%
Miscellaneous 5% 5% 5% 5% 5%
Accounts Receivable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 70 70 70 70 70 70
Accounts receivable turnover 5.21 5.21 5.21 5.21 5.21 5.21
Projected Net Sales $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469
Projected accounts receivable $ 182,793 $ 191,933 $ 201,529 $ 211,606 $ 222,186 $ 233,295
Accounts Payable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 14 14 14 14 14 14
Accounts payable turnover 26.07 26.07 26.07 26.07 26.07 26.07
Projected Operating Expenses, less Int & Depr $ 718,244 $ 751,156 $ 785,714 $ 822,000 $ 860,100 $ 900,105
Projected accounts payable $ 27,549 $ 28,811 $ 30,137 $ 31,529 $ 32,990 $ 34,525
Sample Construction – Income Statement
Easiest place to start:
Core assumptions relate to operations (growth rates,
margins, etc.)
Operations not ‘dependent’ on balance sheet or cash flows
When appropriate, separate fixed and variable costs.
If model is very detailed, consider separate worksheets for
departments, revenues, cost of revenues, operating expenses,
etc.
Separating the model into smaller parts helps to catch errors!
Sample Construction – Income Statement
Sample Company
Projected Income Statements
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Revenues $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469
Operating Expenses
Salaries 286,043 300,345 315,362 331,131 347,687 365,071
Benefits 42,906 45,051 47,304 49,669 52,153 54,760
Supplies 187,585 196,964 206,812 217,153 228,011 239,411
Licenses 8,000 8,400 8,820 9,261 9,724 10,210
Lease 60,000 60,000 60,000 60,000 60,000 60,000
Utilities 12,000 12,600 13,230 13,892 14,586 15,315
Repairs and maintenance 59,710 62,696 65,830 69,122 72,578 76,207
Insurance 28,000 29,400 30,870 32,414 34,034 35,736
Telephone 3,000 3,150 3,308 3,473 3,647 3,829
Interest 3,375 2,758 2,113 1,440 736 -
Management fees 30,000 31,500 33,075 34,729 36,465 38,288
Miscellaneous 1,000 1,050 1,103 1,158 1,216 1,276
Depreciation 140,533 140,533 140,533 150,533 150,533 -
Total Operating Expenses 862,152 894,447 928,360 973,972 1,011,368 900,105
Operating Income 90,983 106,344 122,471 129,400 147,173 316,364
Less: Provision for Income Taxes (22,746) (26,586) (30,618) (32,350) (36,793) (79,091)
Net Income $ 68,237 $ 79,758 $ 91,853 $ 97,050 $ 110,380 $ 237,273
Sample Construction – Balance Sheet
Often more difficult to model, mostly because cash balances
are ‘iterative’ (turn on Excel feature).
Working capital (receivables, inventory, payables) projected
from assumptions, such as days outstanding / turnover ratios.
Capital expenditures need to support expected level of
operations.
Financing (debt / equity) will depend on capital needs, working
capital requirements, etc.
Retained earnings will roll from net income.
Sample Construction – Balance Sheet
Sample Company
Projected Balance Sheets
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
ASSETS
Current Assets
Cash $ 366,367 $ 564,455 $ 773,599 $ 946,853 $ 1,182,298 $ 1,409,996
Accounts receivable 182,793 191,933 201,529 211,606 222,186 233,295
Total Current Assets 549,160 756,387 975,128 1,158,459 1,404,484 1,643,292
Fixed Assets
Fixed assets, at cost 748,450 748,450 748,450 798,450 798,450 798,450
Accumulated depreciation (140,533) (281,066) (421,599) (572,132) (722,665) (722,665)
Total Fixed Assets, net 607,917 467,384 326,851 226,318 75,785 75,785
Total Assets 1,157,077 1,223,771 1,301,979 1,384,777 1,480,269 1,719,077
LIABILITIES & EQUITY
Liabilities
Accounts payable 27,549 28,811 30,137 31,529 32,990 34,525
Notes payable 61,291 46,964 31,993 16,349 (0) (0)
Total Liabilities 88,840 75,776 62,130 47,877 32,990 34,525
Equity
Capital contributions 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000
Retained earnings 68,237 147,996 239,849 336,899 447,279 684,552
Total Equity 1,068,237 1,147,996 1,239,849 1,336,899 1,447,279 1,684,552
Total Liabilities and Equity $ 1,157,077 $ 1,223,771 $ 1,301,979 $ 1,384,777 $ 1,480,269 $ 1,719,077
Sample Construction – Cash Flow
Connect the parts from the Balance Sheet and Income
Statement.
Consider a ‘T=0’ time period for initial investments (capital
expenditure, debt financing, equity financing, etc).
Is there a minimum ‘days cash on hand’ to start with? This
will influence financing requirements.
Cash balances are ‘iterative’ (turn on Excel feature).
Sample Construction – Cash Flow
Sample Company
Projected Cash Flows
T=0 Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Cash Flows from Operating Activities
Net Income $ - $ 68,237 $ 79,758 $ 91,853 $ 97,050 $ 110,380 $ 237,273
Adjust for non-cash items
Depreciation - 140,533 140,533 140,533 150,533 150,533 -
Adjust for changes in:
(Increase) decrease in accounts receivable (174,089) (8,704) (9,140) (9,597) (10,076) (10,580) (11,109)
Increase (decrease) in accounts payable 26,347 1,202 1,262 1,326 1,392 1,461 1,534
Net Cash Provided (Used) by Operations (147,742) 201,268 212,414 224,115 238,899 251,794 227,698
Cash Flows from Investing Activities
Capital expenditures (748,450) - - - (50,000) - -
Net Cash Used in Investing (748,450) - - - (50,000) - -
Cash Flows from Financing Activities
Loan proceeds 75,000 - - - - - -
Member contributions 1,000,000 - - - - - -
Principal payments - (13,709) (14,326) (14,971) (15,645) (16,349) -
Net Cash Provided (Used) by Financing 1,075,000 (13,709) (14,326) (14,971) (15,645) (16,349) -
Net Increase in Cash 178,808 187,559 198,088 209,144 173,254 235,445 227,698
Cash, Beginning of Year - 178,808 366,367 564,455 773,599 946,853 1,182,298
Cash, End of Year $ 178,808 $ 366,367 $ 564,455 $ 773,599 $ 946,853 $ 1,182,298 $ 1,409,996
Sample Construction – Iterative Calculations
Sample Construction – Cash Flows
Most overlooked in financial modeling, but most important.
Statement of Cash Flows shows:
Timing of capital expenditures for growth
Additional borrowing needs
Ability to provide return on investment
Cash flows available to investors (free cash flows) is a core
element in financial decision making, and is essential to a net
present value analysis or an internal rate of return analysis.
Sample Construction – Decision Making
Sample Company
Decision Making
T=0 Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
EBIT
N/A
$ 94,358 $ 109,103 $ 124,584 $ 130,840 $ 147,909 $ 316,364
Less: tax on EBIT (23,590) (27,276) (31,146) (32,710) (36,977) (79,091)
After-tax EBIT 70,769 81,827 93,438 98,130 110,932 237,273
Add: depreciation 140,533 140,533 140,533 150,533 150,533 -
Less: capital expenditures - - - (50,000) - -
Less: working capital requirements (7,502) (7,877) (8,271) (8,685) (9,119) (9,575)
Free Cash Flows to Debt and Equity $ 203,799 $ 214,483 $ 225,700 $ 189,978 $ 252,346 $ 227,698
Free Cash Flows to Debt and Equity $ (1,075,000) $ 203,799 $ 214,483 $ 225,700 $ 189,978 $ 252,346 $ 1,138,490
Present Value (1,075,000) 186,042 163,162 143,080 100,362 111,091 501,203
Net Present Value $ 129,942
Required Rate of Return 20%
Other Layout Consideration
Have a summary tab that provides the ‘answer’ concisely.
Have designated ‘input only’ tabs and clearly delineate
variables.
Link and cross-link worksheets
Use Excel formulas:
If/then
Average and median
Lookup
Forecast
Trend
Basics of Financial Analysis
Why Perform Financial Analysis?
Projection assumptions can be supported by historical financial
analysis.
Basic financial analysis tools include:
Common size financial statements
Ratio analysis
Trend analysis
Industry comparatives
Financial analysis tools and techniques can:
Isolate trends (positive and negative).
Help identify strengths and weaknesses.
Common Size Financial Statements
Income Statement line items as a percentage of revenues:
Identify changes in cost of sales, gross profits, and
operating expense margins over time.
Balance Sheet line items as a percentage of total assets:
Identify changes in (and composition of) current assets and
liabilities, fixed assets, debt, and other balances sheet
items over time.
Are margins and compositions expected to stay the same in
the future?
Why have margins changed? Were the changes expected?
Ratio Analysis
Ratio analysis can assist with understanding and projecting:
Growth
Cost control
Asset turnover
Profitability
Risk
How these ratios have changed (or not) over time.
How do ratios compare to benchmarks?
Integra Information (www.integrainfo.com)
RMA Statement Studies (www.statementstudies.org)
Trade associations
Ratio Analysis
Growth Ratios
Growth in revenues
Growth in expenses
Growth in earnings
Cost Control Ratios
Often common size income statement / margins
Turnover Ratios
Receivable turnover
Inventory turnover
Payable turnover
Total asset turnover
Ratio Analysis - Example
Sample Company
Projection Assumptions
Growth Rates
Line Item Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Revenues 5% 5% 5% 5% 5%
Salaries 5% 5% 5% 5% 5%
Benefits 5% 5% 5% 5% 5%
Supplies 5% 5% 5% 5% 5%
Licenses 5% 5% 5% 5% 5%
Utilities 5% 5% 5% 5% 5%
Repairs and maintenance 5% 5% 5% 5% 5%
Insurance 5% 5% 5% 5% 5%
Telephone 5% 5% 5% 5% 5%
Management fees 5% 5% 5% 5% 5%
Miscellaneous 5% 5% 5% 5% 5%
Accounts Receivable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 70 70 70 70 70 70
Accounts receivable turnover 5.21 5.21 5.21 5.21 5.21 5.21
Projected Net Sales $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469
Projected accounts receivable $ 182,793 $ 191,933 $ 201,529 $ 211,606 $ 222,186 $ 233,295
Accounts Payable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 14 14 14 14 14 14
Accounts payable turnover 26.07 26.07 26.07 26.07 26.07 26.07
Projected Operating Expenses, less Int & Depr $ 718,244 $ 751,156 $ 785,714 $ 822,000 $ 860,100 $ 900,105
Projected accounts payable $ 27,549 $ 28,811 $ 30,137 $ 31,529 $ 32,990 $ 34,525
Ratio Analysis
Profitability Ratios
Return on Assets (ROA)
Return on Equity (ROE)
Return on Investment (ROI)
Risk Ratios
Leverage
Interest coverage
Current ratio
Financial Analysis
Beware the pitfalls:
Ratios can be complicated by accounting methods:
How do comparable companies report inventory,
depreciation, etc.
GAAP allows for different treatments, and different
accounting treatments can skew ratio output.
Ratios are ‘industry dependent’:
CPA firms use different ratios than manufacturing firms
Be cautious of ‘rules of thumb’
Financial analysis tools are diagnostic; they do a better job of raising
questions than providing answers!
Sensitivity and Scenario
Analysis
Sensitivity Analysis
Sensitivity Analysis
How does the projection respond to different ‘shocks’?
Important to know which variables and assumptions are most
influential in your model.
Scenario Analysis
Run multiple scenarios:
Measure outcomes of events with different influences.
Often used to see best case and worst case.
Used to establish a range of cash flows for the company, but
does not necessarily increase confidence in ‘decision’.
Monte Carlo Analysis
Monte Carlo analysis:
Measures outcomes of events with random influences and
assigns probabilities based on frequency.
Can run tens of thousands of potential scenarios in seconds.
Does not give you ‘THE’ answer, but gives confidence in the
range of answers for a set of variables.
Conclusion
Parting Thoughts
Be sure to define the problem and expected use.
Support the assumptions.
Consider alternative scenarios.
Provide the right output for the expected user.
Questions
Contact Information
Jason MacMorran, CPA/ABV, CVA, CFF, MS
Postlethwaite & Netterville, APAC
8550 United Plaza Blvd., Suite 1001
Baton Rouge, LA 70809
225.408.4766
[email protected]
doc_677625208.pdf
Understand accounting standards related to prospective information. Discuss uses and applications for financial models, including uses as decision making tools.
Financial Modeling & Forecasting
Jason MacMorran
www.pncpa.com
Presentation Outline
I. Introduction and Learning Objectives
II. Definitions and Standards Overview
III. Uses for Financial Models
IV. Basics of Financial Modeling
V. Basics of Financial Analysis
VI. Sensitivity and Scenario Analysis
VII. Conclusion
Learning Objectives
Understand accounting standards related to prospective
information.
Discuss uses and applications for financial models, including
uses as decision making tools.
Understand basic design and creation of a financial model.
Understand uses for financial analysis and sensitivity analysis.
Definitions and Standards
Overview
Definitions and Standards Overview
The words projection, forecast, pro forma, model, etc., are
often used interchangeably relative to prospective
information.
In an accounting context, they have different meanings.
Generally speaking, the projection, forecast, pro forma or
budget will be the ‘output’, and the financial model will
include the ‘inputs’ and ‘output’.
This section is not intended to be authoritative or a detailed
look at standards, but a general overview of what to look for
and where to find it!
Key Accounting Terms
Forecast – presents an entity’s expected financial position,
results of operations, and cash flows, based on responsible
party’s assumptions reflecting conditions it expects to exist
and actions it expects to take.
Projection – presents an entity’s financial position, results of
operations, and cash flows, based on one or more
hypothetical (what ifs) assumptions provided by a
responsible party.
Hypothetical Assumption - an assumption used to present a
condition or course of action that is not necessarily expected
to occur, but is consistent with the purpose of the projection
(i.e. expansion scenario).
Key Accounting Terms
Financial Analysis – practitioner develops assumptions,
analyzes results, and recommends a course of action.
Partial Presentation – a presentation of prospective financial
information that excludes one or more of the items required
for prospective financial statements.
Responsible Party – person or persons responsible for
assumptions underlying the prospective financial statements.
Types of Presentations
Prospective Information
Prospective Financial Statements
Forecast
Projection
Prospective, but not Financial Statements
Partial presentations
Financial analyses
Not Prospective Information
Pro forma, based on historical amounts
Expired budgets
Uses of Prospective Information
General Use – use by persons with whom the responsible
party is not directly negotiating, for example:
Offering of debt or equity securities under SEC
regulations.
Offering of tax-exempt bonds.
Limited Use – use only by the entity with whom the
responsibly party is negotiating, for example:
Private placement.
Negotiating bank financing.
Merger negotiations.
Internal Use – solely for use by the responsible party.
Uses of Prospective Information
Type of Prospective
Presentation
Appropriate Uses
General Use Limited Use Internal Use
Forecast Yes Yes Yes
Projection No Yes Yes
Partial Presentation No Yes Yes
Financial Analysis No Yes Yes
Types of Engagements
Third-party Use:
Special disclaimer on current year budgets – practitioners
may not be required to apply any procedures if they make
certain disclaimers.
Compilation – assembling prospective statements in
conformity with presentation guidelines and issuing
report.
Agreed-upon procedures – varies by engagement, and
can be very limited or quite extensive.
Examination – evaluating assumptions and presentation
of financial information and issuing report.
Internal Use:
Assembly – no type of assurance, does not require report.
Independence
Independence is required in examination and agreed-upon
procedures engagements, but not in compilation or internal
use engagements because no assurance is expressed.
Exceptions
Litigation and valuation projects often have exceptions to
procedure and presentation rules, so long as they are
properly disclaimed.
Key Finance Terms
Net Present Value – present value of expected future cash
flows minus initial investment.
Internal Rate of Return – discount rate at which investment
has zero net present value.
Resources
Accounting
AICPA Attestation Standards
AICPA Guide for Prospective Financial Information
AICPA Practice Aid 06-2 Preparing Financial Models
PPC’s Guide to Forecasts and Projections
Finance
Brealey & Myers Principles of Corporate Finance
Uses for Financial Models
Background
Financial models should tell a story.
What is the business going to do?
How is it going to do it?
How is it reflected in the financials?
Financial models capture the future operating, investing and
financing activities that determine future profitability,
financial position, and risk.
Financial models should be comprehensive, internally
consistent, and externally reasonable.
Background
Financial models can integrate elements of accounting,
finance, economics, corporate psychology, and business
philosophy.
Accounting based models – compilations for investors /
creditors.
Finance / economics based models – decision making (net
present value, internal rate of return, etc.).
Psychology / Philosophy – In a ‘decision making’ model, how
will competitors react to your planned actions? Does the plan
involve risks you know you’re unwilling to take?
Uses for Financial Models
Financing (debt or equity)
Buy vs. Lease
Valuation
Budgeting
Business Plans
Strategic Plans
Expansion
Merger / Acquisition
Lost Profits
Business Interruption
Litigation Support
Start-ups
Contraction / Closure
Financial Models in Everyday Life
A ‘financial model’ does not have to be complex!
Simple situations call for simple financial models:
Buy vs. lease of vehicle
Impact of change in interest rate on borrowing
Payback period on an investment
Complex situations call for complex financial models:
Merger and acquisition pro-forma and future cost savings
Entering new markets / launching new products
Litigation
Basics of Financial Modeling
Basics of Financial Modeling
Define the need
Basic organization
Sample construction
Other layout considerations
Define the Need
What is the desired goal of the financial model?
Launching a new product? Integrating a potential
acquisition? Refinancing debt?
Who is the expected user?
Management? Investors? Bankers?
What is the expected use?
Internal? External?
The above issues will govern the sophistication and reporting
requirements of a financial model.
Define the Need
Presentation / engagement will depend on purpose and
audience.
May require complete financial statements over a defined time
period.
May require limited information over a defined time period.
May require ‘one-time’ sources and uses of funds.
Complete Financial Statements
Most difficult and time consuming to prepare, but also most
instructive to user:
Balance Sheet – measures future liquidity and leverage
Income Statement – measures future operating results
Statement of Cash Flows – outlines future cash needs for
growth (investment and borrowing) and returns to
investors
Collectively, complete financial statements can help measure
return on investment and potential risks.
Limited Information
Most common, including:
Prepare a twelve month budget
Six months of start-up expenses
Amortize a loan over five years
Do not necessarily have to be financial statements!
Sources and Uses of Funds
Where is money coming
from (bank debt, equity,
etc).
What is it going to be
spent on (equipment,
refinance, operating costs,
etc).
Often seen in refinancing
and bond offerings.
Contribution Cap %
Sources
Cash on Hand 500 $ 0.6%
Bank Revolver 0 0.0%
Senior Bank Note 20,000 23.4%
Subordinated Bank Note 5,000 5.8%
Owner Contribution 10,000 11.7%
New Equity 50,000 58.5%
Total Sources 85,500 $ 100.0%
Uses
Purchase new equipment 10,000 $ 11.7%
Operating costs 25,000 29.2%
Office manager 40,000 46.8%
Refinance Existing Debt 0 0.0%
Working Capital 10,000 11.7%
Cash 500 0.6%
Total Uses 85,500 $ 100.0%
SOURCES AND USES
Time Period
Generally, time period should match the business life cycle.
5 year projection for a 20 year real estate deal may not be
helpful.
20 year projection in a rapidly changing industry may not
be relevant.
How to handle changes in the economy?
For litigation or damage oriented models, time period should
correspond to period of damage.
Basic Organization
Most financial models include the following basic elements:
Identification of the problem to be solved (buy vs. lease,
expansion, new location, etc.)
Key assumptions (sales growth, margins, capital
expenditures, impact of competitors, etc.)
Output / results (financial statements, net present value,
go / no go decision, etc.)
Sample Construction
Problem - Client / Employer wants to expand an existing
product into a new geographic market (new location).
Which question is better:
Will the project be profitable?
Will the project provide an adequate return on investment?
Accounting based financial statements will show profitability.
Net present value / internal rate of return analysis will show
return on investment.
Sample Construction - Scope
‘Problem’ and expected use/users will define scope of financial
model.
In the case of expansion / new location, the following factors
should be considered:
Will be a long-term project
Will likely require significant investment (equity and /or
debt)
Will likely require consideration of competitor reactions
Based on this ‘problem’ and expected use/users, output should
be complete presentation over a long-term, with consideration
of ‘return’ on the investment.
Sample Construction - Assumptions
What are key assumptions?
Will vary by client and industry
May be impacted by current economic environment in
short-term
Critical points:
Assumptions should be defined separately (i.e. a separate
‘tab’ in Excel workbook); often referred to as ‘projection
drivers’
Assumptions should be reasonable and logical
Assumptions should be supported by historical trends,
industry trends, economic data, or other data (will discuss
later)
Bad assumptions = bad decisions!
Sample Construction - Assumptions
Sample Company
Projection Assumptions
Growth Rates
Line Item Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Revenues 5% 5% 5% 5% 5%
Salaries 5% 5% 5% 5% 5%
Benefits 5% 5% 5% 5% 5%
Supplies 5% 5% 5% 5% 5%
Licenses 5% 5% 5% 5% 5%
Utilities 5% 5% 5% 5% 5%
Repairs and maintenance 5% 5% 5% 5% 5%
Insurance 5% 5% 5% 5% 5%
Telephone 5% 5% 5% 5% 5%
Management fees 5% 5% 5% 5% 5%
Miscellaneous 5% 5% 5% 5% 5%
Accounts Receivable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 70 70 70 70 70 70
Accounts receivable turnover 5.21 5.21 5.21 5.21 5.21 5.21
Projected Net Sales $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469
Projected accounts receivable $ 182,793 $ 191,933 $ 201,529 $ 211,606 $ 222,186 $ 233,295
Accounts Payable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 14 14 14 14 14 14
Accounts payable turnover 26.07 26.07 26.07 26.07 26.07 26.07
Projected Operating Expenses, less Int & Depr $ 718,244 $ 751,156 $ 785,714 $ 822,000 $ 860,100 $ 900,105
Projected accounts payable $ 27,549 $ 28,811 $ 30,137 $ 31,529 $ 32,990 $ 34,525
Sample Construction – Income Statement
Easiest place to start:
Core assumptions relate to operations (growth rates,
margins, etc.)
Operations not ‘dependent’ on balance sheet or cash flows
When appropriate, separate fixed and variable costs.
If model is very detailed, consider separate worksheets for
departments, revenues, cost of revenues, operating expenses,
etc.
Separating the model into smaller parts helps to catch errors!
Sample Construction – Income Statement
Sample Company
Projected Income Statements
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Revenues $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469
Operating Expenses
Salaries 286,043 300,345 315,362 331,131 347,687 365,071
Benefits 42,906 45,051 47,304 49,669 52,153 54,760
Supplies 187,585 196,964 206,812 217,153 228,011 239,411
Licenses 8,000 8,400 8,820 9,261 9,724 10,210
Lease 60,000 60,000 60,000 60,000 60,000 60,000
Utilities 12,000 12,600 13,230 13,892 14,586 15,315
Repairs and maintenance 59,710 62,696 65,830 69,122 72,578 76,207
Insurance 28,000 29,400 30,870 32,414 34,034 35,736
Telephone 3,000 3,150 3,308 3,473 3,647 3,829
Interest 3,375 2,758 2,113 1,440 736 -
Management fees 30,000 31,500 33,075 34,729 36,465 38,288
Miscellaneous 1,000 1,050 1,103 1,158 1,216 1,276
Depreciation 140,533 140,533 140,533 150,533 150,533 -
Total Operating Expenses 862,152 894,447 928,360 973,972 1,011,368 900,105
Operating Income 90,983 106,344 122,471 129,400 147,173 316,364
Less: Provision for Income Taxes (22,746) (26,586) (30,618) (32,350) (36,793) (79,091)
Net Income $ 68,237 $ 79,758 $ 91,853 $ 97,050 $ 110,380 $ 237,273
Sample Construction – Balance Sheet
Often more difficult to model, mostly because cash balances
are ‘iterative’ (turn on Excel feature).
Working capital (receivables, inventory, payables) projected
from assumptions, such as days outstanding / turnover ratios.
Capital expenditures need to support expected level of
operations.
Financing (debt / equity) will depend on capital needs, working
capital requirements, etc.
Retained earnings will roll from net income.
Sample Construction – Balance Sheet
Sample Company
Projected Balance Sheets
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
ASSETS
Current Assets
Cash $ 366,367 $ 564,455 $ 773,599 $ 946,853 $ 1,182,298 $ 1,409,996
Accounts receivable 182,793 191,933 201,529 211,606 222,186 233,295
Total Current Assets 549,160 756,387 975,128 1,158,459 1,404,484 1,643,292
Fixed Assets
Fixed assets, at cost 748,450 748,450 748,450 798,450 798,450 798,450
Accumulated depreciation (140,533) (281,066) (421,599) (572,132) (722,665) (722,665)
Total Fixed Assets, net 607,917 467,384 326,851 226,318 75,785 75,785
Total Assets 1,157,077 1,223,771 1,301,979 1,384,777 1,480,269 1,719,077
LIABILITIES & EQUITY
Liabilities
Accounts payable 27,549 28,811 30,137 31,529 32,990 34,525
Notes payable 61,291 46,964 31,993 16,349 (0) (0)
Total Liabilities 88,840 75,776 62,130 47,877 32,990 34,525
Equity
Capital contributions 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000
Retained earnings 68,237 147,996 239,849 336,899 447,279 684,552
Total Equity 1,068,237 1,147,996 1,239,849 1,336,899 1,447,279 1,684,552
Total Liabilities and Equity $ 1,157,077 $ 1,223,771 $ 1,301,979 $ 1,384,777 $ 1,480,269 $ 1,719,077
Sample Construction – Cash Flow
Connect the parts from the Balance Sheet and Income
Statement.
Consider a ‘T=0’ time period for initial investments (capital
expenditure, debt financing, equity financing, etc).
Is there a minimum ‘days cash on hand’ to start with? This
will influence financing requirements.
Cash balances are ‘iterative’ (turn on Excel feature).
Sample Construction – Cash Flow
Sample Company
Projected Cash Flows
T=0 Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Cash Flows from Operating Activities
Net Income $ - $ 68,237 $ 79,758 $ 91,853 $ 97,050 $ 110,380 $ 237,273
Adjust for non-cash items
Depreciation - 140,533 140,533 140,533 150,533 150,533 -
Adjust for changes in:
(Increase) decrease in accounts receivable (174,089) (8,704) (9,140) (9,597) (10,076) (10,580) (11,109)
Increase (decrease) in accounts payable 26,347 1,202 1,262 1,326 1,392 1,461 1,534
Net Cash Provided (Used) by Operations (147,742) 201,268 212,414 224,115 238,899 251,794 227,698
Cash Flows from Investing Activities
Capital expenditures (748,450) - - - (50,000) - -
Net Cash Used in Investing (748,450) - - - (50,000) - -
Cash Flows from Financing Activities
Loan proceeds 75,000 - - - - - -
Member contributions 1,000,000 - - - - - -
Principal payments - (13,709) (14,326) (14,971) (15,645) (16,349) -
Net Cash Provided (Used) by Financing 1,075,000 (13,709) (14,326) (14,971) (15,645) (16,349) -
Net Increase in Cash 178,808 187,559 198,088 209,144 173,254 235,445 227,698
Cash, Beginning of Year - 178,808 366,367 564,455 773,599 946,853 1,182,298
Cash, End of Year $ 178,808 $ 366,367 $ 564,455 $ 773,599 $ 946,853 $ 1,182,298 $ 1,409,996
Sample Construction – Iterative Calculations
Sample Construction – Cash Flows
Most overlooked in financial modeling, but most important.
Statement of Cash Flows shows:
Timing of capital expenditures for growth
Additional borrowing needs
Ability to provide return on investment
Cash flows available to investors (free cash flows) is a core
element in financial decision making, and is essential to a net
present value analysis or an internal rate of return analysis.
Sample Construction – Decision Making
Sample Company
Decision Making
T=0 Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
EBIT
N/A
$ 94,358 $ 109,103 $ 124,584 $ 130,840 $ 147,909 $ 316,364
Less: tax on EBIT (23,590) (27,276) (31,146) (32,710) (36,977) (79,091)
After-tax EBIT 70,769 81,827 93,438 98,130 110,932 237,273
Add: depreciation 140,533 140,533 140,533 150,533 150,533 -
Less: capital expenditures - - - (50,000) - -
Less: working capital requirements (7,502) (7,877) (8,271) (8,685) (9,119) (9,575)
Free Cash Flows to Debt and Equity $ 203,799 $ 214,483 $ 225,700 $ 189,978 $ 252,346 $ 227,698
Free Cash Flows to Debt and Equity $ (1,075,000) $ 203,799 $ 214,483 $ 225,700 $ 189,978 $ 252,346 $ 1,138,490
Present Value (1,075,000) 186,042 163,162 143,080 100,362 111,091 501,203
Net Present Value $ 129,942
Required Rate of Return 20%
Other Layout Consideration
Have a summary tab that provides the ‘answer’ concisely.
Have designated ‘input only’ tabs and clearly delineate
variables.
Link and cross-link worksheets
Use Excel formulas:
If/then
Average and median
Lookup
Forecast
Trend
Basics of Financial Analysis
Why Perform Financial Analysis?
Projection assumptions can be supported by historical financial
analysis.
Basic financial analysis tools include:
Common size financial statements
Ratio analysis
Trend analysis
Industry comparatives
Financial analysis tools and techniques can:
Isolate trends (positive and negative).
Help identify strengths and weaknesses.
Common Size Financial Statements
Income Statement line items as a percentage of revenues:
Identify changes in cost of sales, gross profits, and
operating expense margins over time.
Balance Sheet line items as a percentage of total assets:
Identify changes in (and composition of) current assets and
liabilities, fixed assets, debt, and other balances sheet
items over time.
Are margins and compositions expected to stay the same in
the future?
Why have margins changed? Were the changes expected?
Ratio Analysis
Ratio analysis can assist with understanding and projecting:
Growth
Cost control
Asset turnover
Profitability
Risk
How these ratios have changed (or not) over time.
How do ratios compare to benchmarks?
Integra Information (www.integrainfo.com)
RMA Statement Studies (www.statementstudies.org)
Trade associations
Ratio Analysis
Growth Ratios
Growth in revenues
Growth in expenses
Growth in earnings
Cost Control Ratios
Often common size income statement / margins
Turnover Ratios
Receivable turnover
Inventory turnover
Payable turnover
Total asset turnover
Ratio Analysis - Example
Sample Company
Projection Assumptions
Growth Rates
Line Item Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Revenues 5% 5% 5% 5% 5%
Salaries 5% 5% 5% 5% 5%
Benefits 5% 5% 5% 5% 5%
Supplies 5% 5% 5% 5% 5%
Licenses 5% 5% 5% 5% 5%
Utilities 5% 5% 5% 5% 5%
Repairs and maintenance 5% 5% 5% 5% 5%
Insurance 5% 5% 5% 5% 5%
Telephone 5% 5% 5% 5% 5%
Management fees 5% 5% 5% 5% 5%
Miscellaneous 5% 5% 5% 5% 5%
Accounts Receivable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 70 70 70 70 70 70
Accounts receivable turnover 5.21 5.21 5.21 5.21 5.21 5.21
Projected Net Sales $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469
Projected accounts receivable $ 182,793 $ 191,933 $ 201,529 $ 211,606 $ 222,186 $ 233,295
Accounts Payable
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal
Assumed days outstanding 14 14 14 14 14 14
Accounts payable turnover 26.07 26.07 26.07 26.07 26.07 26.07
Projected Operating Expenses, less Int & Depr $ 718,244 $ 751,156 $ 785,714 $ 822,000 $ 860,100 $ 900,105
Projected accounts payable $ 27,549 $ 28,811 $ 30,137 $ 31,529 $ 32,990 $ 34,525
Ratio Analysis
Profitability Ratios
Return on Assets (ROA)
Return on Equity (ROE)
Return on Investment (ROI)
Risk Ratios
Leverage
Interest coverage
Current ratio
Financial Analysis
Beware the pitfalls:
Ratios can be complicated by accounting methods:
How do comparable companies report inventory,
depreciation, etc.
GAAP allows for different treatments, and different
accounting treatments can skew ratio output.
Ratios are ‘industry dependent’:
CPA firms use different ratios than manufacturing firms
Be cautious of ‘rules of thumb’
Financial analysis tools are diagnostic; they do a better job of raising
questions than providing answers!
Sensitivity and Scenario
Analysis
Sensitivity Analysis
Sensitivity Analysis
How does the projection respond to different ‘shocks’?
Important to know which variables and assumptions are most
influential in your model.
Scenario Analysis
Run multiple scenarios:
Measure outcomes of events with different influences.
Often used to see best case and worst case.
Used to establish a range of cash flows for the company, but
does not necessarily increase confidence in ‘decision’.
Monte Carlo Analysis
Monte Carlo analysis:
Measures outcomes of events with random influences and
assigns probabilities based on frequency.
Can run tens of thousands of potential scenarios in seconds.
Does not give you ‘THE’ answer, but gives confidence in the
range of answers for a set of variables.
Conclusion
Parting Thoughts
Be sure to define the problem and expected use.
Support the assumptions.
Consider alternative scenarios.
Provide the right output for the expected user.
Questions
Contact Information
Jason MacMorran, CPA/ABV, CVA, CFF, MS
Postlethwaite & Netterville, APAC
8550 United Plaza Blvd., Suite 1001
Baton Rouge, LA 70809
225.408.4766
[email protected]
doc_677625208.pdf