Chapter 1 – Financial Modelling

Introduction

Financial modelling is a representation in numbers of a company's operations in the past,

present, and the forecasted future. Such models are intended to be used as decision-making
tools. Company executives might use them to estimate the costs and project the profits of a
proposed new project. Financial modelling is the process of creating a summary of a
company's expenses and earnings in the form of a spreadsheet that can be used to calculate
the impact of a future event or decision.

Objectives of Financial Modelling

 To create a summary of a company's expenses and earning.

 Financial analysts most often use it to analyse and anticipate how a
company's stock performance might be affected by future events or executive decisions.
 To estimate the valuation of a business or to compare companies to their industry
competitors.
 To estimate the valuation of a business or to compare businesses to their peers in the
industry.

 Financial modelling combines accounting, finance, and business metrics to create a

forecast of a company’s future results.
 The main goal of financial modelling is to accurately project a company’s future
financial performance.
 Modelling can be useful for valuing companies, determining whether a company
should raise capital or grow the business organically or through acquisitions.

Reason for Financial Modelling (Usage)

There are many types of financial models with a wide range of uses. The output of a financial
model is used for decision-making and performing financial analysis, whether inside or outside
of the company. Financial models are used to make decisions about:

 Raising capital (debt and/or equity)

 Making acquisitions (businesses and/or assets)
 Growing the business organically (e.g., opening new stores, entering new markets, etc.)
 Selling or divesting assets and business units
 Budgeting and forecasting (planning for the years ahead)
 Capital allocation (priority of which projects to invest in)
 Valuing a business
 Financial statement analysis/ratio analysis
 Management accounting

Who builds financial models?

There are many different types of professionals who build financial models. The most common
types of career tracks are

 Investment banking,
 Equity research,
 Corporate development,
 FP&A, and accounting (due diligence, transaction advisory, valuations, etc.).

Benefits of Financial Modelling

1
1. A financial model is developed after having a deep insight into the business. The analysts
understand how a business operates and what the different factors that could impact such
business are.
2. To understand how a business is performing, it is important to do the variance analysis.
Financial models help in carrying out the variance analysis by comparing the actual results of
the business against the budgets.
3. Financial models provide clarity on the expected cash inflows and outflows. A business can
get to know the net cash flows that it would be required to arrange to run its affairs.
4. Companies that wish to know their worth can use financial models. A financial model helps
in determining free cash flows that are expected to accrue to a business at different points of
time which further helps in reaching the fair value of a business.
5. Since a financial model helps in carrying out due diligence by suggesting the financial impact
of a particular activity, thus, it helps the businesses in minimizing the overall risk in a business.
6. Businesses may take months to get answers to certain financial questions and to determine
the impact of a certain decision.
7. Financial models build financial budgets and forecasts based on business data and thus, tend
to be accurate. Businesses can use these budgets and forecasts for their business activities so
that their activities remain structured and within the defined structure.
8. The models also help in carrying out a cost-benefit analysis of new projects. Businesses can
use financial models to understand as investment shall be made in which areas and projects for
better profitability and growth.

Financial Modelling Skills

The most important financial modelling skills are:

1. A solid understanding of accounting

In order to build a financial model, it’s important to possess a solid understanding

of accounting fundamentals.

2. Strong Excel skills

In order to build a financial model, it’s important to possess a solid understanding

of accounting fundamentals.
3. Knowing how to link the 3 financial statements

Another skill that’s very important is being able to link the 3 financial statements. This means
taking historical financial statements (income statement, balance sheet, and cash flow
statement)

4. Understanding how to build a forecast

Being good at forecasting is definitely both an art and a science. An analyst can use
regression analysis to predict future results based on historical results.

5. A logical framework for problem-solving

A good financial analyst has the ability to think logically and in a very organized manner.

6. Attention to detail

This is an absolutely essential skill for financial modelling. Given the vast amount of
information and the intricate nature of a complex model, if you don’t have attention to detail,
you’re not likely to be great at financial modelling.

7. Ability to distil large amounts of data into a simple format

One of the hallmarks of someone with great financial modelling skills is their ability to distil
large amounts of complex information into a simple format.

8. An eye for design and aesthetics

One of the least discussed, yet most important, financial modelling skills is having an eye for
design and aesthetics.

9. Clear presentation skills

A great financial modeler will also be able to create an effective PowerPoint Presentation and
Pitchbook to communicate the results of the model to managers, executives, or clients.

10. The ability to easily zoom in on details, and zoom out to high-level strategy
Think about financial modelling as a systematic way to understand the company. Here is what
the name, ‘Integrated Financial Modelling’, means –
Financial = Indicates that we are working with the company’s financial statements
Modelling = Indicates that we are laying down a company’s financials systematically,
connecting these financial statements and subjecting the same to a bunch of equations. The
entire thing tied together is called a model, a model with specific input (financial statements)
and a specific output (valuations).
Integrated = Implies that all the numbers are interconnected, and no part of the financial model
is isolated. You will understand this better as we progress through building the financial model.
The end objective of any financial model is to help you build a perspective of valuation. The
final output of the financial models is the company’s share price after factoring in everything
that matters. You take the share price from the model, compare the share price against the
market share price, and figure if the stock is fairly valued, undervalued, or overvalued.

Tools of the craft

Let me break the ‘not so good news’ first – to learn, build, and benefit from a financial model;
it is mandatory to have some background knowledge about the following –

o How to read an annual report

o How to read the financial statements of the company – Balance Sheet, P&L,
Cash Flow

o It would be best if you were comfortable working with MS Excel or any other
software similar to MS Excel

The good part is that you can learn how to read the annual report, Balance sheet, P&L, and
Cash flow in the fundamental analysis module.
 Chapter 2 – Building Financial Modelling
How can you learn financial modelling?

The best way to learn financial modelling is to practice. It takes years of experience to become
an expert at building a financial model, and you really have to learn by doing. Reading equity
research reports can be a helpful way to practice, as it gives you something to compare your
results to. One of the best ways to practice is to take a mature company’s historical financials,
build a fat-line model into the future, and calculate the net present value per share. This should
compare closely to the current share price or the target prices of equity research reports.

What are financial modelling best practices?

1. Excel tips and tricks it’s very important to follow best practices in Excel when building a
financial model.

• Limit or eliminate the use of your mouse (keyboard shortcuts are much faster)

• Use a blue font for hard-codes and inputs (formulas can stay black)

• Keep formulas simple and break down complex calculations into steps

• Ensure you know how to use the most important Excel formulas and functions

• Use INDEX and MATCH instead of VLOOKUP to query data

• Use the CHOOSE function to build scenarios

2. Formatting it’s important to clearly distinguish between inputs (assumptions) in a financial

model and output (calculations). This is typically achieved through formatting conventions,
such as making inputs blue and formulas black. You can also use other conventions like
shading cells or using borders.

3. Model layout and design it’s critical to structure a financial model in a logical, easy-to-follow
design. This typically means building the whole model on one worksheet and using grouping
to create different sections. This way, it’s easy to expand or contract the model and move
around it easily.

The main sections to include in a financial model (from top to bottom) are:

1. Assumptions and drivers

2. Income statement

3. Balance sheet

4. Cash flow statement

5. Supporting schedules

6. Valuation

7. Sensitivity analysis

8. Charts and graphs

1. Historical results and assumptions every financial model starts with a company’s historical
results. You begin building the financial model by pulling three years of financial statements
and inputting them into Excel. Next, you reverse engineer the assumptions for the historical
period by calculating items such as revenue growth rate, gross margins, variable costs, fixed
costs, AR days, inventory days, and AP days, to name a few. From there you can fill in the
assumptions for the forecast period as hard-codes.

2. Start the income statement with the forecast assumptions in place, you can calculate the top
of the income statement with revenue, COGS, gross profit, and operating expenses down to
EBITDA. You will have to wait to calculate depreciation, amortization, interest, and taxes.

3. Start the balance sheet with the top of the income statement in place, you can start to fill in
the balance sheet. Begin by calculating accounts receivable and inventory, which are both
functions of revenue and COGS, as well as the AR days and inventory day’s assumptions.
Next, fill in accounts payable which is a function of COGS and AP days.

4. Build the supporting schedules before completing the income statement and the balance
sheet, you have to create a schedule for capital assets like PP&E, as well as for debt and interest.
The PP&E schedule will pull from the historical period and add capital expenditures and
subtract depreciation. As for the debt schedule, it will also pull from the historical period and
add increases in debt and subtract repayments. Interest will be based on the average debt
balance.

5. Complete the income statement and balance sheet the information from the supporting
schedules completes the income statement and balance sheet. On the income statement, link
depreciation to the PP&E schedule and interest to the debt schedule. From there you can
calculate earnings before tax, taxes and net income. On the balance sheet, link the closing
PP&E balance and closing debt balance from the schedules. Shareholder’s equity can be
completed by pulling forward last year’s closing balance, adding net income and capital raised
and subtracting dividends or shares repurchased.

6. Build the cash flow statement with the income statement and balance sheet complete, you
can build the cash flow statement with the reconciliation method. Start with net income, add
back depreciation and adjust for changes in non-cash working capital, which results in cash
from operations. Cash used in investing is a function of capital expenditures in the PP&E
schedule, and cash from financing is a function of the assumptions that were laid out about
raising debt and equity.

7. Perform the DCF analysis when the three statement model is completed, it’s time to calculate
free cash flow and perform the business valuation. The free cash flow of the business is
discounted back to today at the firm’s cost of capital (its opportunity cost, or required rate of
return). We offer a full suite of courses that teach all of the above steps with examples,
templates, and step-by-step instruction. Read more about how to build a DCF model.

8. Add sensitivity analysis and scenarios Once the DCF analysis and valuation sections are
complete, it’s time to incorporate sensitivity analysis and scenarios into the model. The point
of this analysis is to determine how much the value of the company (or some other metric) will
be impacted by changes in underlying assumptions.

9. Build charts and graphs Clear communication of results is something that really separates
well from great financial analysts. The most effective way to show the results of a financial
model is through charts and graphs, which we cover in detail in our advanced Excel course as
well as many of the individual financial modelling courses. Most executives don’t have the
time or patience to look at the inner workings of the model, so charts are much more effective.

10. Stress test and audit the model when the model is done, your work is not yet over. Next,
it’s time to start stress-testing extreme scenarios to see if the model behaves as expected. It’s
also important to use the auditing tools covered in our financial modelling fundamentals course
to make sure it’s accurate and the Excel formulas are all working properly.

Types of Financial Models

A list of the 10 most common types of financial models:

1. Three Statement Model

This is the most basic setup for financial modelling. As the name implies, the three statements
(income statement, balance sheet and cash flow) are all dynamically linked with formulas in
this model.

2. Discounted Cash Flow (DCF) Model

This model builds on the three statement model to value the business based on the net present
value of the business’ future cash flow. These types of financial models are used in equity
research and other areas of capital markets.

3. Merger Model (M&A)

This is a more advanced model used to evaluate the accretion / dilution pro forma of a merger
or acquisition. Level of complexity can vary widely. This model is most commonly used in
investment banking and/or corporate development.

4. Initial Public Offering (IPO) Model

Bankers and corporate development professionals will also build IPO models in Excel to value
their business in advance of going public. This includes “an IPO discount” to ensure the stock
trades well in the secondary market.

5. Leveraged Buyout (LBO) Model

A leveraged buyout typically requires modelling complicated debt schedules and is an

advanced form of financial modelling.

6. Sum of the Parts Model

This type of model is based on several DCF models added together, as well as other
components of the business that might not be suitable for a DCF.

7. Consolidation Model

Multiple business units added into one model. Typically, each business unit is its own tab with
consolidation tab that simply sums up the other business units.

8. Budget Model
Used in financial planning & analysis to get the budget for the coming year(s). Budget models
are typically designed to be based on monthly figures.

9. Forecasting Model

Also used in FP&A to build a forecast that compares to the budget model. Sometimes the
budget and forecast models are just one.

10. Option Pricing Model

The two main types are binomial tree and Black-Sholes. These models are based purely on
mathematical models rather than subjective criteria.

Problems on the following:

CAPM / Calculation of Re

DCF/FCF Model.

WACC/Discounting factor.

Gordon’s formula.

Dividend Discount Model.