Loading...
[PDF] Practical, structured design rules for financial modelling
The Standard advocates a philosophy of good financial model design rules founded on the acronym FAST: flexible, appropriate, structured, and transparent
[PDF] Global Financial Modeling Guidelines - PwC Australia
Developed over many years, the following 10 Design Best Practices are practical tips you should apply when developing any financial model These 10 Design Best
[PDF] Financial Modelling Best Practice Guidelines - Access Analytic
The purpose of these guidelines is to provide an overview of some of the best practices that should be considered when building spreadsheets so as to
[PDF] Excel Formatting: Best Practices in Financial Models - Amazon AWS
Properly formatting your Excel models is important because it makes it easier for others to read and understand your analysis and for you to read and
[PDF] FINANCIAL MODELING - IARE
Best Practices when working with Microsoft Excel 1 Save workbooks with backward compatibility in mind If you are not using the latest
[PDF] FINANCIAL MODELING - Simon Benninga
This book was set in Times Roman by Toppan Best-set Premedia Limited Printed and bound in 5 2 How Financial Models Work: Theory and an Initial Example
[PDF] Building Financial Models For Decision Making - ISCA
Definitions of Financial Modeling - Modeling Environment Classification - Spreadsheet Design Principles: - Best Practices in Building Financial Models
[PDF] Financial Modeling and Valuation - WordPresscom
but also to extract the modeling techniques used by analysts and to apply those techniques to any investment Once we have a good understanding of Walmart's
[PDF] Financial Modeling Using Excel and VBA
Developing good financial models requires combining knowledge of finance, mathematics, and Excel and VBA using modeling skill In each of these areas,
[PDF] Global Financial Modeling Guidelines - PwC Australia
Developing best-in-class financial models 3 PwC combines Core best practices for building quality Excel models 12 Project ABC - senior secured note pdf
[PDF] Building financial models with Microsoft Excel - Aggie Horticulture
best efforts in preparing this book, they make no representations or warranties with Financial modeling represents the practice of projecting a business's
[PDF] Financial Modelling Best Practice Guidelines - Access Analytic
Jeff Robson is the Principal Consultant of Access Analytic Solutions, a Perth- based consulting company that provides financial modelling services in model
[PDF] FINANCIAL MODELING - IARE
using excel, analyzing financial statements by using spreadsheet model, excel in project Best Practices when working with Microsoft Excel manual changes to the layout and format of individual chart elements, such as the chart area, plot
[PDF] Financial Modelling Group - assetskpmg
With our financial modelling solutions, we aim to assist you with personalised spreadsheet models We base our models on a common set of best- practice rules that prompt other professionals to Manual entry of loan information
[PDF] Building a 3 Statement Financial Model in Excel - Corporate Finance
banking, or other areas of corporate finance, building financial models is part of the daily Financial Modeling Best Practices •Take raw data (PDF) and
- PDF document for free
![[PDF] Building a 3 Statement Financial Model in Excel - Corporate Finance](https://pdfprof.com/EN_PDFV2/Docs/PDF_2/105650_2lesson_two_building_3_statement_model.pdf.jpg "[PDF] Building a 3 Statement Financial Model in Excel - Corporate Finance")
105650_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