3-Statement Modeling

Course Instructor

Creative. Methodical. Meticulous.

Duncan is fascinated with pedagogy and loves

course creation and design. He is an effective
Duncan
communicator with extensive experience teaching
financial modeling, accounting, analysis & valuation.
Prior to teaching, Duncan held senior equity
research positions with top banks & brokerages. He
has solid analytical skills with an Engineering
degree, Master of Finance degree and a CFA
Charter.

Duncan McKeen
Executive Vice President

Corporate Finance Institute®

Learning Objectives

Explore a framework and Analyze a company’s operations Approach each schedule with a
guidelines for effective financial and divide a financial model into consistent layout and structure.
modeling. schedules.

Calculate key outputs within the Finalize and review the financial Create and maintain a library of
model schedules. model, ensuring integrity. schedules or building blocks.

Corporate Finance Institute®

Company Health Check
Regular Health Check-up

What does a regular health check-up involve?

Regular Company
Health Health
Check-up Check-up

Corporate Finance Institute®

Regular Health Check-up

Weight

Heartrate

Regular
Regular
Health
Health
Blood pressure
Check-up
Check-up

Cholesterol

Corporate Finance Institute®

Regular Health Check-up

How are you managing What business are you

cash flow? in?

How do you generate

What assets do you revenue?

own?
How are your costs
structured? Company
How have they been Health
financed? Check-up
Are you profitable?

Describe your capital

Are you cash flow
structure.
positive?

Corporate Finance Institute®

How We Can Address All These Questions

We are going to start Then we are going to Each of these Use these schedules to
by organizing all of construct schedules schedules may show get a more
the company’s to share various different aspects of wholesome view of
figures. details about the health (i.e., financial the health of the
company. statements).
company.

In order for these schedules to be helpful, we need to understand what they are showing and how to use them.

Corporate Finance Institute®

Overview of Financial Statements
What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Income Statement

Revenues

Expenses

Profit or loss

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Income Statement

Revenues

Expenses

Profit or loss

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Income Statement

Revenues

Expenses

Profit or loss

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Cash Flow Statement

Operating

Investing

Financing

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Cash Flow Statement

Operating

Investing

Financing

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Cash Flow Statement

Operating

Investing

Financing

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Balance Sheet

Liabilities

Assets

Equity

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Balance Sheet

Liabilities

Assets

Equity

Corporate Finance Institute®

What Do Financial Statements Show Us
Let’s look at the financial statements to understand exactly what they show us.

Balance Sheet

Liabilities

Assets

Equity

Corporate Finance Institute®

Main Goal of Each Statement
Now that we have done a high-level review of common line items, we want to start thinking about the main goal of
each statement.

Income Statement
Provides a meaningful measure of profitability
Revenues The income statement shows accrued costs and expenses in
the correct period for an accurate picture of profitability.

Expenses

Profit or loss
But how does the company manage cash flow?

Corporate Finance Institute®

Main Goal of Each Statement
Now that we have done a high-level review of common line items, we want to start thinking about the main goal of
each statement.

Cash Flow Statement

Assess the management of cash flow
Operating The cash flow statement tracks the physical flow of cash
both in and out of the corporation.

Investing

Financing

Corporate Finance Institute®

Main Goal of Each Statement
Now that we have done a high-level review of common line items, we want to start thinking about the main goal of
each statement.

Balance Sheet
Review assets and how they are financed

Liabilities The balance sheet will list the corporation’s assets and show
how they have been financed (e.g., debt, equity).
Assets

Equity

Corporate Finance Institute®

Main Goal of Each Statement
Now that we have done a high-level review of common line items, we want to start thinking about the main goal of
each statement.

Income Statement Cash Flow Statement Balance Sheet

Meaningful measure of Assess the management Review assets and how

profitability of cash flow they are financed

Corporate Finance Institute®

Are We Seeing a Period or a Point in Time

Balance
Sheet

Month 1… …Month 12

Income Cash Flow

Statement Statement

Corporate Finance Institute®

Role of Finance Professionals
What Is Our Role as Finance Professionals
There are several steps we are going to be required to do:

Prepare reports to evaluate Add clear schedules to Link the financial statements
business. support them. together.

Confirm that they balance Review the model for Setup for presentation and
correctly. integrity. printing.

Corporate Finance Institute®

Order of Preparing the Statements
There are several steps we are going to be required to do:

Corporate Finance Institute®

Order of Preparing the Statements
There are several steps we are going to be required to do:

Corporate Finance Institute®

Order of Preparing the Statements

1. 2. 3.

Income Statement Cash Flow Statement Balance Sheet

Net Income Cash

… … …
Net Income Ending Cash

Corporate Finance Institute®

Role of Finance Professionals
Financial Modeling Guidelines

Free to download

Over 95 pages

Covers financial modeling principles &

standards

Corporate Finance Institute®

Financial Modeling Guidelines

Corporate Finance Institute®

The Role of Financial Models
There are two very important aspects of financial models.

1. Decision-Making
Financial models are important tools in
Model designers and builders need to
assisting us with complex decisions.
put the audience first.

Decision-makers and stakeholders need

2. Communication
to quickly navigate to key information.
Financial models must be clear and
easy for others to understand.

Corporate Finance Institute®

Reverse Model Design
Preferred Model Design

The health of the

What is the model
company.
used for?

Our goal is to
Why do we need
assess and
it?
communicate the
health of a
company.

Corporate Finance Institute®

Preferred Model Design
We prefer to design in the reverse order and start with the outputs, and we work backward to the inputs.

Inputs Outputs

Corporate Finance Institute®

Preferred Model Design
We prefer to design in the reverse order and start with the outputs, and we work backward to the inputs.

Inputs Drivers Revenue Costs

Operational
Schedules

Income Working
Assets Depreciation
Tax Capital

Equity Debt Statements Outputs

Corporate Finance Institute®

Preferred Model Design
We prefer to design in the reverse order and start with the outputs, and we work backward to the inputs.

Inputs Focuses on audience needs

Prioritizes communication

Outputs Ensures right level of detail

Corporate Finance Institute®

Designing for the Audience
Preferred Model Layout
Next, we prioritize the schedules from a communication perspective.

Cover Page Outputs Inputs Model

Corporate Finance Institute®

Preferred Model Layout

Cover Page

Cover Page

• Sets the tone for the Outputs Inputs Model

model

• Communicates important
model information
Corporate Finance Institute®
Preferred Model Layout

Outputs

Dashboard

Cover Page • Like an executive Inputs Model

summary

• Most important
information right up front
Corporate Finance Institute®
Preferred Model Layout

Inputs

Drivers

Inputs
Cover Page Outputs Model

• Identifies the critical

inputs that drive the model

Corporate Finance Institute®

Preferred Model Layout

Model

Statements

Operational
Cover Page Outputs Inputs

Structure

• This forms the appendix

Corporate Finance Institute®
Preferred Model Layout

Presentation Appendix

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Defining Model Drivers
Model Design: Drivers

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Drivers

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Drivers

Model drivers are volatile and have a significant impact

on model outputs.

We isolate the drivers so that we can test how the model

reacts to them.

Inputs
We need to separate model drivers from other less
Drivers important inputs.

Inputs
Identifying the drivers requires detailed knowledge of
the business.

Corporate Finance Institute®

Overview of Schedules
Model Design: Operational Schedules

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Operational Schedules

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Operational Schedules

Operational schedules give us insight into the

foundational aspects of the business.

It’s very difficult to change operational features of a

business.
Model

Statements
Operational schedules are one of the key aspects that we
need to review.
Operational

Structure

Corporate Finance Institute®

Operational Schedules

Revenue Schedule Working Capital Schedule Asset Schedule

Helps us understand exactly Shows us how the working Helps us to track the PP&E
capital items are moving
how the business generates needed to complete the
(e.g., producing or
its revenue consuming cash) balance sheet

Cost Schedule Depreciation Schedule Income Tax Schedule

Allows us to understand the Enables us to calculate Allows us to break out the

mix of fixed and variable depreciation for the income current and deferred
costs statement incomes taxes

Corporate Finance Institute®

Operational Schedules

Revenue Schedule Working Capital Schedule Asset Schedule

Helps us understand exactly Shows us how the working Helps us to track the PP&E
capital items are moving
how the business generates needed to complete the
(e.g., producing or
its revenue Check out the Operational
consuming balance
cash) Course to learn the
Modeling best sheet
way to build all of these schedules.

Cost Schedule Depreciation Schedule Income Tax Schedule

Allows us to understand the Enables us to calculate Allows us to break out the

mix of fixed and variable depreciation for the income current and deferred
costs statement incomes taxes

Corporate Finance Institute®

Overview of Capital Structure
Model Design: Capital Structure

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Capital Structure

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Capital Structure

It’s very difficult to change operational features of a

business.

Operational schedules are one of the key aspects that we

need to review.
Model

Statements
Operational schedules give us insight into the
foundational aspects of the business.
Operational

Structure

Corporate Finance Institute®

What is a Capital Structure
Capital structure is simply the mix of debt and equity a company is using to finance its business.

100% Equity

The company is not using

Equity any debt to finance its
operations.

Corporate Finance Institute®

What is a Capital Structure
Capital structure is simply the mix of debt and equity a company is using to finance its business.

WACC (Weighted Average Cost of Capital) is a concept we cover in a

lot more
Debtdetail in our DCF Evaluation Modeling course.
Adding Debt

Debt financing is usually a

cheaper form of capital
than equity.

Equity

Corporate Finance Institute®

What is a Capital Structure
Capital structure is simply the mix of debt and equity a company is using to finance its business.

Revolving Line of
Credit

Debt Term Debt

Preferred Shares

Equity Common Shares

Corporate Finance Institute®

Using Corkscrews
Approach to Modeling a Capital Structure

The capital structure can be one Remember to break the It is best to use a consistent and
of the most difficult parts of the formulas down into small logical structure for the capital
model.
components. structure.
We prefer to build operational
schedules first and then add This enables people to more We use corkscrews to track
capital structure. easily understand the model. debt and equity.

Corporate Finance Institute®

Using Corkscrews

Term Debt Schedule

All figures in USD thousands unless stated Year 0 Year 1 Year 2 Year 3 Year 4 Year 5

LONG TERM DEBT

Beginning Balance 20,000 18,000 16,000 14,000 12,000

Increase / (Decrease) (2,000) (2,000) (2,000) (2,000) (2,000)
Ending Balance 20,000 18,000 16,000 14,000 12,000 10,000

Interest Rate 6.0% 6.0% 6.0% 6.0% 6.0%

Interest Expense 1,140 1,020 900 780 660

In many situations, we can pull

this value directly from the
company’s balance sheet.

Corporate Finance Institute®

Optimal Order for Debt & Equity Schedules
We generally will default to the order of seniority for the schedules.

We may deviate from this order if

there is a capital structure with
discretionary debt repayments.
Debt Schedules Equity Schedules

To learn more about discretionary

Include cash to get Equity schedules debt repayments, check out our LBO
interest income and usually include a Modeling course.
then calculate net section for retained
interest expense. earnings as well.

Corporate Finance Institute®

Error Detection & Excel Settings
Model Design: Financial Statements

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Financial Statements

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Financial Statements

We do all our complex calculations in the operational and

capital structure schedules.

This means that our financial statements mostly just

include links to other schedules.
Model

Statements
This systematic approach makes our models easier to link
and balance.
Operational

Structure

Corporate Finance Institute®

Error Detection System

Although it can be frustrating, an

unbalanced balance sheet is useful.

An unbalance signals there is an error

somewhere that needs to be addressed.

Balance Sheet
Balance sheets are useful in that they
Assets = Liabilities + Equity work as an error detection system.

It is never recommended to link financial

statements, so they always balance.

Corporate Finance Institute®

Calculation Options in Excel
Excel can perform iterative or circular calculations.

Corporate Finance Institute®

Calculation Options in Excel
Excel can perform iterative or circular calculations.

Make sure that iterative calculations are enabled.

Corporate Finance Institute®

Calculation Options in Excel
Excel can perform iterative or circular calculations.

Make sure that iterative calculations are enabled.

Set the workbook calculations to automatic.

Corporate Finance Institute®

Revolver Overview
Revolving Line of Credit

A Revolving Line of Credit is Even if a company doesn't Since our model currently
also called: Revolver, Line have a revolver, one should has a negative cash balance,
of Credit, Operating Line, still be included in the we know it needs to get
or Bank Line model cash from the revolver

A revolver is a flexible piece The revolver acts as a lender Cash needs to be paid back
of debt that can be used of last resort if the model as soon as the company is
when it’s needed by a needs cash able. This is called a cash
company sweep mechanism

Corporate Finance Institute®

Overview of Circularity
Circularity

Circularity can be quite a controversial topic in the financial modeling

community.

Excel can actually do two types of calculations: linear calculations and

circular calculations.

In order to do circular calculations, we need to set a particular setting in the

software (see Error Detection & Excel Settings in Financial Statements).

Corporate Finance Institute®

Linear Calculations
Let’s think about linear calculations and use the example of the water cycle to demonstrate.

Precipitation

Reservoir Condensation

Transpiration
Corporate Finance Institute®
Linear Calculations
Let’s think about linear calculations and use the example of the water cycle to demonstrate.

Reservoir Precipitation Condensation Transpiration Reservoir

Corporate Finance Institute®

Linear Calculations
Let’s think about linear calculations and use the example of the water cycle to demonstrate.

Reservoir Precipitation Condensation Transpiration Reservoir

Corporate Finance Institute®

Linear Calculations
Let’s think about linear calculations and use the example of the water cycle to demonstrate.

Reservoir Precipitation Condensation Transpiration Reservoir

Corporate Finance Institute®

Linear Calculations
Let’s think about linear calculations and use the example of the water cycle to demonstrate.

Reservoir Precipitation Condensation Transpiration Reservoir

Corporate Finance Institute®

Linear Calculations

Condensation

Precipitation Transpiration

Reservoirs
Corporate Finance Institute®
Linear Calculations

Condensation

Precipitation Transpiration

Reservoirs
Corporate Finance Institute®
Financial Model Circularity
Linear Calculations
Let’s think about where circularity occurs in a financial model.

Income Statement

Cashflow Statement Net Interest

Cash
Corporate Finance Institute®
Two Circular Loops in This Model

Net Interest

Income Statement Cash Loop

Cashflow Statement

Cash

Corporate Finance Institute®

Two Circular Loops in This Model

Net Interest

Revolver Loop Income Statement Cash Loop

Cashflow Statement

Revolver Cash

Corporate Finance Institute®

Our View & Approach to Circularity

Advantages Our View

Models can be more accurate when Model accuracy is the most important
they are circular in nature. aspect, and risks can be mitigated.

Educate the individuals building the models

Model Obstacles about circularity.
• Circular models can crash if the circularity
is turned on.
Include a circularity switch on all circular
• Model users may not understand financial models.
circularity or how to control it.

• Error messages may pop up for users

depending on calculation settings. Add very clear and prominent footnotes
about Excel settings that are needed.

Corporate Finance Institute®

Importance of Dashboards
Model Design: Financial Statements

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Financial Statements

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Financial Statements

The dashboard is critically important since the audience

will focus on it.

Senior leaders may not load the model in Excel and will
likely focus on the dashboard.
Outputs

Dashboard
We need to make sure that it clearly communicates all
important information.

Corporate Finance Institute®

Using Model Checks
Model Design: Financial Statements

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Financial Statements

Cover Page Outputs Inputs Model

Cover Page Dashboard Drivers Statements

Inputs Operational

Structure

Corporate Finance Institute®

Model Design: Financial Statements

The importance of the cover page should not be

overlooked in a model.

The cover is the first page the audience sees, and it helps
to establish credibility.

Cover Page
It positions the model as a formatted financial
presentation to the audience.
Cover Page

It also instills confidence in the stakeholders by showing a

summary of the model checks.

Corporate Finance Institute®