Loading...
Growth Equity Case Study – 2-Hour 3-Statement Modeling Test
We have simplified and consolidated the financial statements to facilitate the modeling process. Part 2: Project the Revenue Expenses
Module 5 – 3-Hour 3-Statement Modeling Test and Debt vs. Equity
Financial Modeling Mastery. – Certification Quiz Questions. Module 5 – 3-Hour 3-Statement Modeling Test and Debt vs. Equity Case Study.
Building a 3 Statement Financial Model in Excel
Types of financial models. Financial. Models. Three. Statement. Model. DCF Model 3. Plan. 4. Integrity. 5. Model Testing. •Use test data to.
toeic-listening-reading-sample-test-updated.pdf
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
Working Paper Series - Banking euro area stress test model
and Wollmershäuser T.: 2012
ECB guide to internal models - Risk-type-specific chapters
3. Margin period of risk and cash flows. 139. 4. Collateral modelling (d) for PD models covering exposures to financial institutions: business model.
Instructions for reporting the validation results of internal models
3. 2. Supplementary validation reporting on credit risk The ECB is conscious of the limitations of statistical tests and the importance of the.
STAMP€: Stress-Test Analytics for Macroprudential Purposes in 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 ...
invest banking/private equity fin-uy 4903 - a nyu tandon school of
Students will build financial models in each class with the Professor. Build a three statement model including revolver and debt schedules.
Supervisory Statement 3/18 'Model risk management principles for
identify manage and control the risks inherent in the use of stress test models. 1.2 This SS is relevant to PRA authorised banks
[PDF] Building a 3 Statement Financial Model in Excel - Corporate Finance
Types of financial models Financial Models Three Statement Model DCF Model 3 Plan 4 Integrity 5 Model Testing •Use test data to ensure the model
[PDF] Financial Modeling - Carnegie Mellon University
Stress test to make sure it works as planned 3) Building Financial Models - Setting a Planning Horizon that, in turn, feed into the 3 main financial statement
[PDF] Building financial models with Microsoft Excel - Aggie Horticulture
Excel also enables users to test assumptions underlying financial models and run sensitivity quired to prepare and submit a set of financial statement projections Valuation, covered in Part Three, is a complex issue—entire books are
[PDF] of 7 FINANCIAL MODELING FNBU4454_L11 SUMMER 2020
Chapter 1: Income Statement; Chapter 3: Depreciation • Introduce: financial statement modeling by reviewing the Income statement and the More Test Prep
[PDF] Lbo Modeling Test Example
Market Failure And The Role Of Government Edu CBA LBO Modeling Test Example Street Of Walls 3 Statement Financial Model Template Wall Street Oasis
- PDF document for free
![[PDF] Building a 3 Statement Financial Model in Excel - Corporate Finance](https://pdfprof.com/EN_PDFV2/Docs/PDF_2/99_2lesson_two_building_3_statement_model.pdf.jpg "[PDF] Building a 3 Statement Financial Model in Excel - Corporate Finance")
99_2lesson_two_building_3_statement_model.pdf Building a 3 Statement
Financial Modelin Excel
corporatefinanceinstitute.com corporatefinanceinstitute.com corporatefinanceinstitute.comWhat is a financial model?
ȊA financial model is a tool
ȇ financial performance into the future based on historical data and assumptions corporatefinanceinstitute.comWhy 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 planningProject Finance
Whether to invest in a
projectCorporate
Transactions
Mergers &
acquisitions, capital raisingInvestment
Decisions
Valuation, equity
research, portfolio management corporatefinanceinstitute.comTypes of financial models
Financial
Models
Three
Statement
ModelDCF Model
Merger
Model
(M&A)Initial
Public
Offering
(IPO) ModelLeveraged
Buyout
(LBO) ModelSum of the
Parts
ModelBudget
ModelForecasting
ModelOption
Pricing
ModelConsolidation
Model corporatefinanceinstitute.comHierarchy 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 statementDiscounted cash flow analysis to value a
businessEstimate changes in the value of a
business in different possible scenariosEvaluate how sensitive an investment is
to changes in driversEvaluate the attractiveness of potential
merger, acquisition or divestitureAnalyze the pro forma impact of raising
debt or equityDetermine how much leverage can be
used to purchase a company corporatefinanceinstitute.comFinancial Modeling Best Practices
corporatefinanceinstitute.comKey structure for model building
Good models clearly separate inputs, processing, and outputs.Inputs
Processing
Outputs
Clearly identified
Should only ever
be entered onceProcessing should
be transparentBroken down into
simple stepsEasy to follow
Quickly accessible
corporatefinanceinstitute.comModeling 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.comModeling best practices
1. Clarify
2. Simplify
What is the
minimum number of inputs and outputs to build a useful model? corporatefinanceinstitute.comModeling best practices
1. Clarify
2. Simplify
3. Plan
Plan how inputs and
outputs will be laid outKeep all inputs in
one place corporatefinanceinstitute.comModeling best practices
1. Clarify
2. Simplify
3. Plan
4. Integrity
Consider using Excel
Ȋ ȋȊȋ
corporatefinanceinstitute.comModeling best practices
1. Clarify
2. Simplify
3. Plan
4. Integrity
5. Model Testing
Use test data to
ensure the model works as expected corporatefinanceinstitute.comModeling best practices
1. Clarify
2. Simplify
3. Plan
4. Integrity
5. Model Testing
corporatefinanceinstitute.comTension: complex vs. simple models
Complex
Models
Simple
Models
High detail
Precise
Hard to model
Prone to error
BasicEasy to follow
Lack of precision
Overly simplified
BestModels
Keep things as simple as
possible while providing enough detail for decision making corporatefinanceinstitute.comModel inputs
Inputs
Processing
Outputs
corporatefinanceinstitute.comModel 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 outputsUse data validation &
conditional formattingUse comments
corporatefinanceinstitute.comModel 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.comModel 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.comModel outputs
Inputs
Processing
Outputs
corporatefinanceinstitute.comModel outputs
Outputs
ObjectivesAchieving objectives
Provide key results to
aid decision-makingEasy to understand
Unambiguous
Make outputs modular
Consider creating a summary
section with only the most important key model outputs corporatefinanceinstitute.comModel structure and layout
Single spreadsheetMulti-spreadsheet
corporatefinanceinstitute.comFinancial 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.comFinancial 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.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers1Historical data corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
4Forecast working capital
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast capital assets (PP&E, capex, depreciation, etc.)
6Forecast capital structure
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comFinancial 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.comModel Setup and Assumptions
corporatefinanceinstitute.comThe 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.comHistorical data
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers1Historical data corporatefinanceinstitute.comAssumptions, drivers and forecasting methods
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comForecasting methods
Top-Down
Analysis
Start with total
addressable market (TAM)Work down from
there based on market share and segments until arriving at revenueBottom-Up
Analysis
Start with most basic
drivers of the business (units)Build up the analysis
all the way to revenueRegression
Analysis
Analyze the
relationship between revenue and other factors using the regression analysis in ExcelYear-over-Year
Growth Rate
Most basic form of
forecastingCalculate the year-
over-year change in revenue corporatefinanceinstitute.comForecast Revenues Down To EBITDA
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
2Assumptions and drivers
1Historical data
corporatefinanceinstitute.comForecasting operating revenues and profits
Income statement
Revenues
Direct operating cost
Indirect operating cost
Depreciation and amortization
Cost of debt
TaxesEBITDA
Net income
corporatefinanceinstitute.comForecasting 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 revenueRegression
corporatefinanceinstitute.comForecasting 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 marginsGross margin20%
Revenue100%
Cost of goods sold(80%)
Gross margin20%
corporatefinanceinstitute.comForecasting gross margin and SG&A expenses
Labor + materials +
Inflation %
Consider factors such
as economies of scale and learning effectsComplex
Models
Simple
ModelsBased on a margin
Easy to model
Cost of goods sold80%
Based on inputs
Per unit
corporatefinanceinstitute.comForecasting 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.comForecast Working Capital and PP&E
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
4Forecast working capital
2Assumptions and drivers
1Historical data
Accounts receivable
Inventories
Accounts payable
corporatefinanceinstitute.comForecasting 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.comHigh 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.comWorking capital equations
Receivable days
Payable days
Inventory days
corporatefinanceinstitute.comWorking capital equations
Accounts
receivable days=Forecast accounts
receivable=Receivable days
Accounts receivable
Sales X365Receivable days
365XSales
Payable days
Inventory days
Payable days
Inventory days
corporatefinanceinstitute.comWorking capital equations
Receivable days
Payable days
Inventory days
Receivable days
Inventory days
Accounts payable
days=Forecast accounts
payable=Accounts payable
Cost of sales
X365Payable days
365XCost of sales
corporatefinanceinstitute.comWorking capital equations
Receivable days
Payable days
Inventory days
Receivable days
Payable days
Inventory days=
Forecast
inventory=Inventory
Cost of sales
X365Inventory days
365XCost of sales
corporatefinanceinstitute.comFinancial modeling steps
Income
Statement
Balance Sheet
Cash Flow
Statement
Supporting
Schedules
Assumptions
and Drivers3Forecast revenues down to EBITDA
4Forecast working capital
5Forecast non-current capital assets
2Assumptions and drivers
1Historical data
PP&E CapexDepreciation
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.comForecasting financial statements
Operating (non-current)
assets / PP&E corporatefinanceinstitute.comForecasting 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. Ifdeprecation 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