We have simplified and consolidated the financial statements to facilitate the modeling process. Part 2: Project the Revenue Expenses
Financial Modeling Mastery. – Certification Quiz Questions. Module 5 – 3-Hour 3-Statement Modeling Test and Debt vs. Equity Case Study.
Types of financial models. Financial. Models. Three. Statement. Model. DCF Model 3. Plan. 4. Integrity. 5. Model Testing. •Use test data to.
Directions: You will hear a question or statement and three responses spoken in English. They will not be printed in your test book and will be spoken only
and Wollmershäuser T.: 2012
3. Margin period of risk and cash flows. 139. 4. Collateral modelling (d) for PD models covering exposures to financial institutions: business model.
3. 2. Supplementary validation reporting on credit risk The ECB is conscious of the limitations of statistical tests and the importance of the.
11 mai 2017 3. Conclusion. 68. Chapter 6 Top-down modelling for market risk ... Financial sector stress tests have proved to be an important tool for ...
Students will build financial models in each class with the Professor. Build a three statement model including revolver and debt schedules.
identify manage and control the risks inherent in the use of stress test models. 1.2 This SS is relevant to PRA authorised banks
![Building a 3 Statement Financial Model in Excel Building a 3 Statement Financial Model in Excel](https://pdfprof.com/EN_PDFV2/Docs/PDF_2/99_2lesson_two_building_3_statement_model.pdf.jpg)
99_2lesson_two_building_3_statement_model.pdf
Building a 3 Statement
Financial Modelin Excel
corporatefinanceinstitute.com corporatefinanceinstitute.com corporatefinanceinstitute.com
What is a financial model?
ȊA financial model is a tool
ȇ financial performance into the future based on historical data and assumptions corporatefinanceinstitute.com
Why do we build financial models?
For anyone pursuing a career in corporate development, investment banking, FP&A, equity research, commercial
banking, or other areas of corporate finance, building financial models is part of the daily routine.
Corporate
Decisions
Company performance,
strategic planning
Project Finance
Whether to invest in a
project
Corporate
Transactions
Mergers &
acquisitions, capital raising
Investment
Decisions
Valuation, equity
research, portfolio management corporatefinanceinstitute.com
Types of financial models
Financial
Models
Three
Statement
Model
DCF Model
Merger
Model
(M&A)
Initial
Public
Offering
(IPO) Model
Leveraged
Buyout
(LBO) Model
Sum of the
Parts
Model
Budget
Model
Forecasting
Model
Option
Pricing
Model
Consolidation
Model corporatefinanceinstitute.com
Hierarchy of financial modeling
Three Statement Model
DCF Analysis
Scenario Analysis
Sensitivity Analysis
M&A Analysis
LBO Analysis
Capital Raising
Income statement, balance sheet, cash
flow statement
Discounted cash flow analysis to value a
business
Estimate changes in the value of a
business in different possible scenarios
Evaluate how sensitive an investment is
to changes in drivers
Evaluate the attractiveness of potential
merger, acquisition or divestiture
Analyze the pro forma impact of raising
debt or equity
Determine how much leverage can be
used to purchase a company corporatefinanceinstitute.com
Financial Modeling Best Practices
corporatefinanceinstitute.com
Key structure for model building
Good models clearly separate inputs, processing, and outputs.
Inputs
Processing
Outputs
Clearly identified
Should only ever
be entered once
Processing should
be transparent
Broken down into
simple steps
Easy to follow
Quickly accessible
corporatefinanceinstitute.com
Modeling best practices
1. Clarify
What problem is the model
meant to solve?
Who is the end user?
What are users supposed
to do with the model? corporatefinanceinstitute.com
Modeling best practices
1. Clarify
2. Simplify
What is the
minimum number of inputs and outputs to build a useful model? corporatefinanceinstitute.com
Modeling best practices
1. Clarify
2. Simplify
3. Plan
Plan how inputs and
outputs will be laid out
Keep all inputs in
one place corporatefinanceinstitute.com
Modeling best practices
1. Clarify
2. Simplify
3. Plan
4. Integrity
Consider using Excel
Ȋ ȋ
Ȋȋ
corporatefinanceinstitute.com
Modeling best practices
1. Clarify
2. Simplify
3. Plan
4. Integrity
5. Model Testing
Use test data to
ensure the model works as expected corporatefinanceinstitute.com
Modeling best practices
1. Clarify
2. Simplify
3. Plan
4. Integrity
5. Model Testing
corporatefinanceinstitute.com
Tension: complex vs. simple models
Complex
Models
Simple
Models
High detail
Precise
Hard to model
Prone to error
Basic
Easy to follow
Lack of precision
Overly simplified
Best
Models
Keep things as simple as
possible while providing enough detail for decision making corporatefinanceinstitute.com
Model inputs
Inputs
Processing
Outputs
corporatefinanceinstitute.com
Model inputs
Inputs
ObjectivesAchieving objectives
Accurate
Reasonable data ranges
Easy to use
Easy to understand
Easy to update data
Enter each data once
Use colorto differentiate
inputs and outputs
Use data validation &
conditional formatting
Use comments
corporatefinanceinstitute.com
Model processing
Inputs
Processing
Outputs
Do you try to put all your processing calculations into as few cells as possible?
Do you hide your processing cells or worksheets?
corporatefinanceinstitute.com
Model processing
Processing
ObjectivesAchieving objectives
Easy to maintain
Accurate processing
Transparency
Break down complex calculations
Use comments and annotations
Use formatting
Calculate final figures which will
go onto the output reports corporatefinanceinstitute.com
Model outputs
Inputs
Processing
Outputs
corporatefinanceinstitute.com
Model outputs
Outputs
ObjectivesAchieving objectives
Provide key results to
aid decision-making
Easy to understand
Unambiguous
Make outputs modular
Consider creating a summary
section with only the most important key model outputs corporatefinanceinstitute.com
Model structure and layout
Single spreadsheetMulti-spreadsheet
corporatefinanceinstitute.com
Financial forecasting framework
Assumptions & drivers
Income statement
Balance sheet
Cash flow statement
Supporting schedules
Historical ratios and figures which drive the forecast ȇ ȇ ȇ
Reports the cash generated and spent by a company
Breaks down longer calculations such as PP&E and debt schedule corporatefinanceinstitute.com
Financial forecasting approach
HistoricalForecast
Assumptions & drivers
Income statement
Balance sheet
Cash flow statement
Supporting schedules
B A A A A C D D D D corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers1Historical data corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
4Forecast working capital
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
6Forecast capital structure
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
6Forecast capital structure
Supporting
Schedules
Complete cash flow statement7
2Assumptions and drivers
Assumptions
and Drivers1Historical data corporatefinanceinstitute.com
Model Setup and Assumptions
corporatefinanceinstitute.com
The case
Your boss has just emailed you about about something the executive team would like to look at ASAP You need to create a financial forecast for a business, with limited information You only have a set of historical financial statements ȇ well as a template model from a colleague You must link the historical financial statements and create a well built 5-year forecast as fast as possible corporatefinanceinstitute.com
Historical data
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers1Historical data corporatefinanceinstitute.com
Assumptions, drivers and forecasting methods
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Forecasting methods
Top-Down
Analysis
Start with total
addressable market (TAM)
Work down from
there based on market share and segments until arriving at revenue
Bottom-Up
Analysis
Start with most basic
drivers of the business (units)
Build up the analysis
all the way to revenue
Regression
Analysis
Analyze the
relationship between revenue and other factors using the regression analysis in Excel
Year-over-Year
Growth Rate
Most basic form of
forecasting
Calculate the year-
over-year change in revenue corporatefinanceinstitute.com
Forecast Revenues Down To EBITDA
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Forecasting operating revenues and profits
Income statement
Revenues
Direct operating cost
Indirect operating cost
Depreciation and amortization
Cost of debt
Taxes
EBITDA
Net income
corporatefinanceinstitute.com
Forecasting revenues
Complex
Models
Simple
Models
Quick and simple
Use historic figures and trends to
predict future growth (e.g. last year plus 5%)
First principles
Retail (bottom up)Ȃforecast # of
stores, size, and derive revenue per sq. ft.
Telco(top down) ȂForecast market size
and use current market share and competitor analysis to predict revenue
Regression
corporatefinanceinstitute.com
Forecasting gross margin and SG&A expenses
Revenue100%
Cost of goods sold80%
Gross margin20%
SG&A5%
Operating margin15%
Use historic figures or
trends to forecast future margins
Gross margin20%
Revenue100%
Cost of goods sold(80%)
Gross margin20%
corporatefinanceinstitute.com
Forecasting gross margin and SG&A expenses
Labor + materials +
Inflation %
Consider factors such
as economies of scale and learning effects
Complex
Models
Simple
ModelsBased on a margin
Easy to model
Cost of goods sold80%
Based on inputs
Per unit
corporatefinanceinstitute.com
Forecasting gross margin and SG&A expenses
Revenue100%
Cost of goods sold80%
Gross margin20%
SG&A(5%)
Operating margins15%
Indirect5% or $xx
Forecasted as a
percentage of revenue or as a fixed cost (plus an inflator)
Often includes
marketing, sales, general and administrative expenses corporatefinanceinstitute.com
Forecast Working Capital and PP&E
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
4Forecast working capital
2Assumptions and drivers
1Historical data
Accounts receivable
Inventories
Accounts payable
corporatefinanceinstitute.com
Forecasting financial statements
Having forecast the revenues and costs of an operation, the next step is to considerthe working capital required to generate them.
Assetsȇ
Current assetsCurrent liabilities
CashAccounts payable
Accounts receivableOther current liabilities
InventoryLong term liabilities
Non-current assetsȇ
Operating (non-current) assetsCommon shares
Retained earnings
Accounts receivable
Inventory
Accounts payable
Other current liabilities
corporatefinanceinstitute.com
High complexityLow complexity
Forecasting working capital
Moderate approach
Ȋȋ
Receivable days
Inventory days
Payable days
Historical trends
A % of sales based on trends
Detailed approach
Account/client detail
Inventory management
detail corporatefinanceinstitute.com
Working capital equations
Receivable days
Payable days
Inventory days
corporatefinanceinstitute.com
Working capital equations
Accounts
receivable days=
Forecast accounts
receivable=
Receivable days
Accounts receivable
Sales X365
Receivable days
365
XSales
Payable days
Inventory days
Payable days
Inventory days
corporatefinanceinstitute.com
Working capital equations
Receivable days
Payable days
Inventory days
Receivable days
Inventory days
Accounts payable
days=
Forecast accounts
payable=
Accounts payable
Cost of sales
X365
Payable days
365
XCost of sales
corporatefinanceinstitute.com
Working capital equations
Receivable days
Payable days
Inventory days
Receivable days
Payable days
Inventory days=
Forecast
inventory=
Inventory
Cost of sales
X365
Inventory days
365
XCost of sales
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast non-current capital assets
2Assumptions and drivers
1Historical data
PP&E Capex
Depreciation
Intangibles
Assetsȇ
Current assetsCurrent liabilities
CashAccounts payable
Accounts receivableOther current liabilities
InventoryLong term liabilities
Non-current assetsȇ
Operating (non-current) assetsCommon shares
Retained earnings
corporatefinanceinstitute.com
Forecasting financial statements
Operating (non-current)
assets / PP&E corporatefinanceinstitute.com
Forecasting property, plant and equipment (PP&E)
First principles approach
Forecast property, plant, and equipment requirement directly (e.g. store expansion) Forecast depreciation/amortization based on stated depreciation/amortization policies. If
deprecation policies are not available, divide gross assets by the depreciation expense to get average
asset life.
Ȋȋ
Forecast depreciation & amortization as a percentage of opening PP&E balance or percentage of revenue
Forecast PP&E balance based on a capital asset turnover ratio
High complexityLow complexity
corporatefinanceinstitute.com
Forecasting property, plant and equipment (PP&E)
Capital Asset Turnover Ratio
Sales / PP&E (end of period)
or
Sales / PP&E (average)
corporatefinanceinstitute.com
Forecast Capital Structure
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers
3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
6Forecast capital structure
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.com
Forecasting financial statements
The financing structure affects both the balance sheet and the income statement (i.e. interest)
Assetsȇ
Current assetsCurrent liabilities
CashAccounts payable
Accounts receivableIncome taxes payable
InventoryNon-current liabilities
Long term debt
Non-current assetsȇ
Operating (non-current) assetsCommon shares
Retained earnings
Cash
Long term debt
Common shares
Retained earnings
corporatefinanceinstitute.com
Forecasting financial statements
Other Current Liabilities
Long term Liabilities
Common SharesRetained Earnings
Cash corporatefinanceinstitute.com Approaches to modeling capital structure (debt/equity)
Do we want to model the status quo, or do we
want to model a different capital structure? ?
Debt & Equity $ Values
Held Constant
Debt/Equity x Ratio Held
Constant
Debt/Equity Change Over
Time Based on Cash Flow
corporatefinanceinstitute.com
Forecasting the capital structure
What should be the split between equity and debt financing?
Leverage ratios
DebtEquity
Coverage ratios
EBITInterest
Expense
Financial covenants dictate maximum leverage and minimum coverage ȇ ȇ corporatefinanceinstitute.com
Compounding vs non compoundinginterest
Is the debt (and interest expense) compounding or not? ?
IF YES
There will be a circular
reference in the model IF NO
There will not be a circular
reference in the model corporatefinanceinstitute.com
Circular references
Opening Debt Balance
+ Interest = Closing Debt Balance +/-
Additions/Repayments
corporatefinanceinstitute.com
Complete Cash Flow Statement
corporatefinanceinstitute.com
Financial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
6Forecast capital structure
Supporting
Schedules
Complete cash flow statement7
2Assumptions and drivers
Assumptions
and Drivers1Historical data corporatefinanceinstitute.com
Forecasting financial statements
Operating activities (e.g. revenues, operating expenses) Investing activities (e.g. sale/purchase of assets) Financing activities (e.g. issuing shares, raising debt)
Net Cash Movement
A cash flow forecast can be derived from the balance sheet and income statement corporatefinanceinstitute.com
Cash flows from operating activities
Net income100
Depreciation20
Other non-cash items-
Trade and other receivables(10)
Inventory(20)
Trade and other payables45
15
Cash from operating activities135
From incomestatement
From balancesheet
Changes in operating assets
and liabilities corporatefinanceinstitute.com
Cash flows from investing activities
Capital expenditures (additions to PP&E)(120)
Proceeds from disposals of fixed assets10
Payments for investment in businesses0
Proceeds from disposals of businesses0
From specific fixed asset forecasts
Cash from investing activities(110)
CAPEX corporatefinanceinstitute.com
Cash flows from financing activities
Issuance of common stock100
Dividends paid in the year(5)
Increase/(decrease) in long-term debt15
Increase/(decrease) in short-term debt(10)
From balance sheet and supporting
schedules
Cash from financing activities100
corporatefinanceinstitute.com
Review and Audit
corporatefinanceinstitute.com
Auditing techniques
Error Messages
and Alerts
Sanity Checks
(Assumptions and Drivers)
Trace
Precedents and
Dependents
Excel Settings
GoToSpecial
View Formulas
corporatefinanceinstitute.com
Summary
corporatefinanceinstitute.com
Final thoughts
ȊA financial model is a tool
that relies on a set of assumptions corporatefinanceinstitute.com
A modular approach to building models
Income Statement
Balance Sheet
Cash Flow Statement
Supporting Schedules
Assumptions and Drivers
Discounted Cash Flow (DCF) Analysis
Sensitivity Analysis
LBO or M&A
corporatefinanceinstitute.com
DCF models, sensitivity, M&A, LBO, and more
Three Statement Model
DCF Analysis
Scenario Analysis
Sensitivity Analysis
M&A Analysis
LBO Analysis
Capital Raising
Business Valuation Modeling
Course
Advanced Financial Modeling
(AMZN Case Study) Course
Scenario & Sensitivity
Analysis in Excel Course
Leveraged Buyout LBO
Modeling Course
Mergers & Acquisitions (M&A)
Modeling Course
corporatefinanceinstitute.com
Additional Case Study
corporatefinanceinstitute.com
Overview
Practice your skills
Solution and demonstration
Ȋȋ
ȇ
Take raw data (PDF) and
blank Excel model provided
Build the three financial
statements from scratch, including the historical results and a 5-yr forecast
Highlight the main steps to
building the three statement model
Step-by-step demonstration
Add commentary about
why things are modeled as they are corporatefinanceinstitute.com
Steps for building the three statement model
1. Copy and paste raw
data into the blank model
2. Format and link
historical subtotals
3. Calculate historical
ratios
4. Make assumptions
based on the guidance provided
5. Start the income
statement
6. Start the balance
sheet
7. Build supporting
schedules
8. Build the cash flow
statement and complete the I/S and B/S
9. Create charts,
graphs, and outputs corporatefinanceinstitute.com
1.Download the Case Study ȂFinancial Statements
& Future Prospectsfor raw data and assumptions
2.Open the CaseStudy ȂThree Statement Model Ȃ
Blank and build a three statement model
You can find these links on the attachment tab
Practice your skills
corporatefinanceinstitute.com
Case study wrap up
corporatefinanceinstitute.com
Business Valuation
Modeling
DCF Analysis
Business valuation modeling course
corporatefinanceinstitute.com
Conclusion
01.
Understand the key
structure for building a financial model in Excel04.Make sure the three statements are properly linked
02.Followmodeling best practices
Key messages from this session:
03.Build a model step by step
as outlined in this course
05.Practice your skills
FMVA® Certification
corporatefinanceinstitute.com