Contents

01 Introduction 1

02 Key Structure for Model Building 2

03 Modeling Best Practices 3

04 Complex vs. Simple Models 5

05 Model Inputs 5

06 Model Processing 6

07 Model Outputs 6

08 Structure and Layout 7

09 Financial Forecasting Framework 9

10 Forecasting Methods 10

11 Forecast revenues down to EBITDA 11

12 Forecast Working Capital and PP&E 13

13 Forecast Capital Structure 18

14 Complete Cash Flow Statement 20

15 Review and Audit 25

[Link]
 Integrated financial statement model
Module 2

01 Introduction

What Is a Financial Model

A financial model is a tool built in a spreadsheet that’s used to

forecast a business’s financial performance into the future and make
business decisions.

Uses of financial model :

Corporate Decisions Company performance, strategic planning

Project Finance Whether to invest in a project

Corporate Transactions Mergers & acquisitions, raising capital

Investment Decisions Valuation, equity research, portfolio

management

Types of Financial Models :

3-Statement Model or integrated FS model

DCF Model Budget Model

Merger Model (M&A) Forecasting Model

Capital Raises Model Option Pricing Model

Leverage Buyout Model

Sum of the Parts Model

Consolidation Model

(1) [Link]
 Integrated financial statement model
Module 2

01 Introduction

02 Key Structure for Model Building

Inputs Processing Outputs

Assumptions Calculations Graphs & Charts

Clearly identified Processing should Quickly accessible
be transparent
Should only ever Broken down Easily updated and
be entered once into simple steps exported

Easy to follow

(2) [Link]
 Integrated financial statement model
Module 2

03 Modeling Best Practices

Sign Convention :

Depending on analyst preferences, models may be built in several

different ways.

(3) [Link]
 Integrated financial statement model
Module 2

03 Modeling Best Practices

Advantages include making the model easier to follow and being able
to use the SUM function.

Many companies do not report using this convention.

It may cause some confusion doing conversions while building the
supporting schedules.
Depending on analyst preferences, models may be built in several
different ways :

We need to be careful when entering and interpreting data based on

both how the company reports and how we build the model

(4) [Link]
 Integrated financial statement model
Module 2

04 Complex vs. Simple Models

Remember that while it is tempting to make your model complex, it is

important not to overcomplicate the model with too much detail.

05 Model Inputs

(5) [Link]
 Integrated financial statement model
Module 2

06 Model Processing

Do you try to put all your calculations into as few cells are possible?
Do you ever hide sections of the model?

07 Model Outputs

(6) [Link]
 Integrated financial statement model
Module 2

08 Structure and Layout

There are generally two ways to set up your financial model – multi-
spreadsheet and single-spreadsheet.

Structure and Layout :

> Single-Spreadsheet Approach.

> Easy to link and formula build.
> Simplifies organization of larger models.

(7) [Link]
 Integrated financial statement model
Module 2

08 Structure and Layout

Cleanest Most organized Easiest to use

(8) [Link]
 Integrated financial statement model
Module 2

09 Financial Forecasting Framework

Financial Forecasting Framework :

Assumptions & Drivers :

Historical ratios and figures that drive the forecast.

> Step in Financial Forecasting Approach :
1. Historical data
[Link] and drivers

Income Statement
Summarizes the company’s profit and loss.
> Step in Financial Forecasting Approach :
3. Forecast revenues down to EBITDA

Balance Sheet
Displays the company’s assets, liabilities, and shareholders’
equity.
> Step in Financial Forecasting Approach :
4. Forecast working capital
5. Forecast capital assets (PP&E, Capex, depreciation, etc.)

Cash Flow Statement

Reports the cash generated and spent by a company.
> Step in Financial Forecasting Approach :
7. Complete cash flow statement

(9) [Link]
 Integrated financial statement model
Module 2

09 Financial Forecasting Framework

Supporting Schedules
Breaks down longer calculations such as PP&E and debt
schedule.
> Step in Financial Forecasting Approach :
6. Forecast capital structure

10 Forecasting Methods

Four types of forecasting methods.

(10) [Link]
 Integrated financial statement model
Module 2

11 Forecast revenues down to EBITDA

1. We are going to forecast revenues, direct operating expenses, and

indirect operating expenses to get to EBITDA.

2. Then we will work on the balance sheet and supporting schedules

so we can forecast depreciation and interest expense.

(11) [Link]
 Integrated financial statement model
Module 2

Forecasting Revenues :

Forecasting Gross Margin and SG&A Expenses :

There is not a major difference in setting cost of goods sold as the

target or gross profit as the target.
Use historical figures or trends to
forecast future margins.
Consider factors such as economies
of scale and learning effects for
labor, materials, and inflation
percentage.

(12) [Link]
 Integrated financial statement model
Module 2

These indirect costs often include things like marketing, sales, and
general and administrative expenses.

You may have a separate schedule that builds the indirect cost with a
lot of detail, a hybrid of fixed and variable; some components are a
percentage of revenue, and some components are fixed dollar costs.

12 Forecast Working Capital and PP&E

We are now going to look at the current assets and current liabilities
of the business that are required to support revenues and expenses.

(13) [Link]
 Integrated financial statement model
Module 2

12 Forecast Working Capital and PP&E :

Forecasting Methods

(14) [Link]
 Integrated financial statement model
Module 2

12 Forecast Working Capital and PP&E

Working Capital Equations – Accounts Receivable

Working Capital Equations – Accounts Payable

(15) [Link]
 Integrated financial statement model
Module 2

12 Forecast Working Capital and PP&E

Working Capital Equations – Inventory Days

Now let’s forecast non-current capital assets for the business, which
includes several accounts, such as property, plant, and equipment
(PP&E).

(16) [Link]
 Integrated financial statement model
Module 2

12 Forecast Working Capital and PP&E

(17) [Link]
 Integrated financial statement model
Module 2

13 Forecast Capital Structure

The financing structure affects both the balance sheet and the
income statement (i.e., interest).

Do we want to model the status quo for this company, or do we want

to model a different capital structure in the future?

(18) [Link]
 Integrated financial statement model
Module 2

13 Forecast Capital Structure

The financing structure affects both the balance sheet and the
income statement (i.e., interest).

(19) [Link]
 Integrated financial statement model
Module 2

13 Forecast Capital Structure

The financing structure affects both the balance sheet and the
income statement (i.e., interest).

14 Complete Cash Flow Statement

A cash flow forecast can be derived from the balance sheet and
income statement.

(20) [Link]
 Integrated financial statement model
Module 2

14 Complete Cash Flow Statement

Let’s review cash flow from operating activities.

(21) [Link]
 Integrated financial statement model
Module 2

14 Complete Cash Flow Statement

(22) [Link]
 Integrated financial statement model
Module 2

14 Complete Cash Flow Statement

Cash Flows From Investing Activities

We're going to use the information about specific fixed assets to

derive this cash flow section.

(23) [Link]
 Integrated financial statement model
Module 2

14 Complete Cash Flow Statement

Cash Flows From Financing Activities

We're going to use the balance sheet and supporting schedules to

complete this part.

Forecasting Financial Statements

(24) [Link]
 Integrated financial statement model
Module 2

15 Review and Audit

Review and Audit

(25) [Link]