Chapter Five

ADVANCED FINANCIAL
MODELING TECHNIQUES
Chapter Objectives

• Explore in how to • Understand how to • Explore in detail

perform a merger
value banks under analysis on several how to construct
the three valuation targets using comparable a precedent
approaches company data
transactions
analysis
• Understand the components of a Public
Information Book and where to source
each of the components
• Understand the difference between • Create a dynamic scenario
scenario and sensitivity analysis manager in Excel using
Introduction to Bank V aluation
Learning Objectives
 Understand what makes  Discover why enterprise  Value a bank using a
a bank different from a value is meaningless for a Dividend Discount
regular company. bank.
Model.

 Calculate the  Apply multiples and  Learn how to mark a

Residual Income of a regression for bank bank's balance sheet
bank. valuation. to market value.
 How Valuing Banks Is Different
• Valuing a bank or other financial institution is quite different from
valuing a non-bank.
1 2 3

Debt Reinvestment Regulation

It’s difficult to It’s difficult to Banks are very

define what is regulated,
pinpoint
considered which impacts
growth
debt. reinvestment. valuation.
Financial Statement Differences:

Bank vs. Regular C ompany

Financial Statement Differences – Bank vs. Regular
C ompany

A bank earns interest income instead of revenue.

Bank’s Revenue

Debt C lassific ation

L oans = A ssets Deposits = L iabilities

Investments in assets are Liabilities “owed” to another

Reinvestment expected to generate future party that will decrease future
cash flows. cash flows.
 Financial Statement Differences – Bank vs. Regular
C ompany
Reinvestment is necessary for future growth.
Bank’s Revenue

Reg ular C ompany

Bank
• Net working capital • Employees
Debt C lassific ation
• Long-term asset
• Regulatory capital
investments

Reinvestment
 What Does This Mean for Valuation?
• We can’t separate a bank’s operations from how it finances itself, so we
can’t look at enterprise value when valuing banks.

Reg ular C ompany Bank

Capital structure is directly

Debt tied to its operations.

Value of Must instead focus on equity

Enterprise
Company’s value or levered metrics.
Value
Operations Equity
Levered metrics include net income
and book value or equity.

Can still value banks using either

intrinsic or relative valuation.
 Valuation Techniques

Income Approach Market Approach Asset Approach

(Intrinsic Value) (Relative Value) (FMV of Net Assets)
Intrinsic Value – Dividend Discount Model
• Intrinsic valuation means looking at a
company in isolation, focusing on
cash flow generation potential.
• Regular companies use DCF Valuation, but
banks cannot use DCF, even if it is levered.

Income Approach
(Intrinsic Value) • Instead, we focus on the amount of
dividends a bank can pay and value
Dividend Discount
banks using a Dividend Discount
Model (DDM) Model.
Relative Value – Public C ompany C omparables
• Peers are generally easy to find because
these banks’ shares are publicly traded on a
stock exchange.
• We use multiples to find the worth of
the bank we are trying to value (i.e.,
Market Approach Price-to-Book multiple).

(Relative Value) • It is more likely to reflect the mood of

1) Public Company the market and produce a valuation
Comparable that is closer to market price than
2) Precedent Transactions DDM.
 Relative Value – Precedent
Transactions
• Precedent transactions relate to
past mergers and acquisitions.

• This form of valuation includes a

Market Approach (Relative
Value) takeover premium (generally,
 Public Company
more money is paid for a
Comparable controlling position).
 Precedent Transactions
 Asset Approach
• Asset Approach focuses on
determining the fair market value of
individual assets.

• This method is more useful for

banks because banks use mark-to-market
Asset Approach accounting for many of their assets.
(FMV of Net Assets)

• If the market value of assets is greater

Market-based than the market value of liabilities, then
Balance Sheet what’s left over is the equity value.
Intrinsic Valuation
Dividend Discount Model (DDM)
Ex pec ted Dividends

Value of S tock FYear 1 FYear 2 FYear 3 FYear 4 FYear 5

C ost of Equity

Intrinsic Value of a Bank

Banks have higher dividend payout ratios than
Expec ted Dividends regular companies.
Value of S tock =
(C ost of Equity –
Dividend payout ratio is the amount of dividends paid
Dividend G rowth Rate)
out relative to the bank’s net income.
 Two Parts to a Typical DDM Forecast
• We will discount all dividends and the terminal value back to the
present at the cost of equity.
Stag e 1: Stag e 2:

Disc rete Forec ast Terminal Value

Time Period Time Period

•Typically covers a period of • Assumes dividends paid in
5 to 10 years. perpetuity or bank is The further we predict into
acquired. the future, the more prone
our estimates become to
Description Description error.
•Involves calculating •Assumes dividends grow at a
dividends each year based on steady rate or acquired at
projections and regulatory some multiple.
capital.
 DDM with Regulatory C apital C onstraints
• Since banks have strict regulations they must follow, banks are constrained
in how much they can pay in dividends.

Common Equity Tier 1

Ratio (CET1) • Banks are required to maintain a
minimum Common Equity Tier 1 ratio.
C ommon Equity Tier 1

• Banks will usually target a ratio

Risk- Weig hted Assets
above the minimum.

• Learn more about this ratio and its components in

Basel III and Risk Management.
Interactive Exercise
Solution
Compiled Dakito Alemu (PhD)
Compiled Dakito Alemu (PhD)
 Capital Adequacy Ratio (CAR)
►CAR is also known as capital-to-risk weighted assets ratio (CRAR)
►Capital adequacy ratios (CARs) are a measure of the amount of a
bank's core capital expressed as a percentage of its risk-weighted asset.

►Tier one capital is the capital that is permanently and easily available to reduce
losses suffered by a bank without it being required to stop operating.
►A good example of a bank’s tier one capital is its ordinary share capital and RE
►Tier 2 capital is designated as supplementary capital, and is composed of items such as
►revaluation reserves, undisclosed reserves, hybrid instruments (preferred shares) and
subordinated term debt
 Risk Weighted Assets
• Risk weighted assets -mean fund based assets such as cash, loans, investments and other
assets.
• Degrees of credit risk expressed as percentage weights have been assigned by the national
regulator to each such assets.
• Bank "A" has assets totaling 100 Br, consisting of:
• Cash: 10 br
• Government bonds: 15 Br
• Mortgage loans: 20 Br
• Other loans: 50 Br
• Other assets: 5 Br
• Bank "A" has debt of 95 Br, all of which are deposits. By definition, equity is equal to assets
minus debt, or 5 Br.
•Bank A's risk-weighted assets are calculated as follows
Cash 10*0%=0
Government securities 15*0%=0
Mortgage loans 20*50%=10
Other loans 50*100%=50
Other assets 5*100%=5
Total risk
Weighted assets 65
Equity 5
CAR (Equity/RWA) 7.69%
Even though Bank A would appear to have a debt-to-equity ratio of 95:5, or
equity-to-assets of only 5%,
its CAR is substantially higher.
It is considered less risky because some of its assets are less risky than others.
10%
 DDM: Estimating Terminal Value
• First Principles: a bank will grow based on how much it reinvests its profits
and what the return on equity is on those profits.
C alc ulate the Retention Ratio.
• Whatever isn’t paid out to
Retention Ratio = 1 – Dividend Payout Ratio
shareholders is retained in
the business.
Multiply Retention Ratio by c ompany ’s Return on Equity.

N et Inc ome G rowth Rate = Retention Ratio x Return on Equity

Make assumptions about Dividend G row th Rate.

• We can calculate the
Dividend G rowth Rate = N et Inc ome G rowth Rate
terminal value using this
perpetuity growth rate.
DDM: Estimating Terminal Value
Dividends
Equity Value =
(C ost of Equity – G rowth Rate)

[N et Inc ome x (1 + G row th Rate) x Payout Ratio]

This is equivalent to: Equity Value =
(C ost of Equity – G rowth Rate)

Equity Value
Divide both sides by N et Inc ome: = [ (1 + Growth Rate) x Payout Ratio]
N et Inc ome
(Cost of Equity – Growth Rate)

Equity Value
Equivalent to J ustified P/E Multiple: = [ (1 + Growth Rate) x Payout Ratio] = Theoretic al P/E Multiple
N et Inc ome
(Cost of Equity – Growth Rate)
 DDM: Estimating Terminal Value
[N et Inc ome x (1 + G rowth Rate) x Payout Ratio]
Equity Value =
(C ost of Equity – G rowth Rate)

Equity Value
Divide both sides by Book Value: = [Return on Equity x (1 + Growth Rate) x Payout Ratio]
Book Value
(Cost of Equity – Growth Rate)

C alculation for G row th Rate: G row th Rate = (1 – Payout Ratio) x Return on Equity

Rearrang ed G row th Rate: Return on Equity – G rowth Rate = Return on Equity x Payout Ratio

Equity Value Return on Equity – G row th Rate

Equivalent to J ustified P/B Multiple: = = Theoretic al P/B Multiple
Book Value C ost of Equity – G rowth Rate
 DDM Advantages and Disadvantages
Let’s look at the advantag es and disadvantag es of dividend discount models.

A dvantages Disadvantages

Highly intuitive since dividends are a form of Requires a lot of inputs, and the model is only as
income paid directly to equity investors. good as those inputs.

Provides an opportunity to learn about the bank Given all of the inputs, it is easier to “manipulate” a
and regulatory capital requirements. DDM to a desired outcome.

Less prone to market conditions (since it’s an Greater complexity may give an analyst a false
intrinsic valuation, not a relative one). sense of precision.
 Residual Income
• The residual income approach projects residual income over some time
period and discounts it back to the present value.
Bank Income Statement

Income Statement Interest Millions

Ex pected Residual Inc ome Income 1,000
Interest Expense (900) Cost of Debt
Financing
Net Interest Income 100
Present Future Future Future Future Future Non-Interest Income 500
Value Y1 Y1 Y1 Y1 Y1 Non-Interest Expense (450)
Earnings Before Tax 150
Tax (50)
Net Income 100

There is no associated cost of equity

financing that a company recognizes.
Residual Income Advantages and Disadvantages
• Let’s look at the advantages and disadvantages of residual income
valuation.
A dvantages Disadvantages

Useful if the bank is not currently paying This method is less common than DDM and
dividends. Comps.

Can explicitly evaluate whether a bank is Mostly theoretical since we aren’t discounting
creating value by comparing ROE vs. COE. cash flows.

Terminal value is a smaller proportion of value Relies on a bank’s financial statements, which can be
than in the DDM. distorted or manipulated.
Relative Valuation
 Relative Valuation
• Under this methodology, the target bank’s valuation is relative to other
banks or transactions.

Foc us on Equity Multiples:

 Price- to- Earnings

 Price- to- Book Value

 Price- to- Tang ible Book Value

Tang ible Book Value = Book Value of Equity

– Intang ible Assets
Relative Valuation Advantages and Disadvantages
Let’s look at the advantag es and disadvantag es of relative valuation.

A dvantages Disadvantages

Calculating and applying multiples is relatively Sometimes relative valuation can be too
simple and user-friendly. simplistic.

Data is observable since we can directly look at a Banks have many complex value drivers that
public bank’s market capitalization. multiples don’t explicitly account for.

Relative valuation reflec ts market c onditions. Since no banks are exactly alike, relative valuation can
be difficult and subjective.

There are many reasons multiples, banks, and

Precedent transactions are useful for mergers and
transactions may vary.
acquisitions due to the control premium.
A sset A pproac h
 Asset Approach
• Though not common with regular companies, the asset approach is more
applicable for banks as their balance sheets should be closer to market value.

A ssets L iabilities Equity Value

A sset A pproac h A pplic ation

Usually results in a lower valuation than

Start with target’s existing
other methods.
balance sheet
Values individual assets and liabilities
without regard to: Apply various valuation approaches
• Future g rowth to assets & liabilities
• Interconnectedness
Asset Valuation Advantages and Disadvantages
Let’s look at the advantages and disadvantages of the asset approach to valuation.

A dvantages Disadvantages

Can be used for banks that aren’t currently Does not account for potential growth or
profitable but have a relatively large asset base. synergistic impact.

Requires a lot of information that might be

Intuitive and easy- to- understand methodology.
difficult or time consuming to compile.

Necessitates a lot of expertise and judgment to

value different types of assets and liabilities.
M&A Modeling
Merger and Acquisition (M&A)
• A business combination occurs when two companies come together,
either by way of a merger or an acquisition by one company of the other.

Business Combination

Merger Acquisition
When two companies combine When one company proposes to
under mutual agreement to form a offer cash or its shares to acquire
consolidated entity another company
 Mergers and Acquisitions (M&A)
• M&A is a transaction or process of companies buying, selling, or
combining businesses.

Benefits: Potential drawbacks:

• Cost savings •Overpaying

•Large expenses associated
• Revenue enhancements with the investment
• Increase market share •Negative reaction to the
merger or acquisition (hostile
• Enhance financial resources
takeover)
Capital Market Proclamation 1248/2021

Part 10
ACQUISITION AND PROTECTION OF
MINORITY INTERESTS
 Part X: Acquisition & Protection of Minority Interest
Acquisition & Acquisition Offer(Art 83&84)
•“Acquisition offer” means the offer or request to own the majority percentage of a listed
company that enables the offeror, directly or indirectly, to control the board of directors of
the company.
•A person shall not make or pursue an offer in respect of an acquisition of the securities of any company except in accordance with the conditions prescribed by this Proclamation or the directives issued by Authority or any other law.

•The person, wishing to submit an acquisition offer, shall submit copies

of the offer documents to:
a) the Authority,
b) securities exchange, and
c) the issuer of the securities subject to the acquisition offer.
•The offeror shall not take any further steps in the acquisition process before obtaining
the Authority’s approval.
Part X: Acquisition & Protection of Minority Interest, Cont’d…
•Existing shareholders of the company being offered for takeover
or acquisition shall be፡
a) given a reasonable time to consider the proposal;
b) supplied with adequate information to enable them to assess
the merits of the proposal;
c) as far as practicable, given a reasonable and equitable
opportunities to participate in any benefit accruing to the
shareholders under the proposal; and
d) given fair and equitable treatment in relation to the proposal.
10 step acquisition process
10
9 Integration
8 Financing
7 Purchase &
6 Sales Contract
Due
5 Diligence
Negotiation
4 Data & Detailed

3 Approaching Valuation
2 Targets
Searching
1 for Target
Acquisition
Acquisition Criteria • Due Diligence is a comprehensive appraisal of a
Strategy business undertaken by a prospective buyer,
especially to establish its assets and liabilities and
evaluate its commercial potential
 Strategic Vs Financial buyers

Strategic buyers
• Operating businesses
VS Financial buyers
• Private equity (financial sponsor)
• Horizontal or vertical expansions
• Professional investor (non-operator)
• Involves identifying and • Leverage for maximum equity
delivering operating returns
synergies
Acquisition valuation process
Strategic Buyer Scenario:
• Sales growth
• EBIT margin
1. Value the target as stand-
• Operating tax
alone • Working capital requirements
• Capital expenditures
Enterprise value

• Sales (volume & price) • Working capital

• EBIT margin • Vendor relationships
2. Value synergies • Product mix
• Capital expenditures
• Overhead
• Efficiencies
reductions
• Hard (cost savings) and
• Operating tax
• Soft (revenue enhancements) • Tax efficiency
• Tax losses
Best practice acquisition analysis
1 2 3 4 5 6

Soft Transaction
synergies costs
Hard Net Value created
synergies synergies

Stand-alone Consideration
enterprise Stand- (price paid)
value alone value
Issues to consider when structuring a deal
Market Environment

Contract Structuring Antitrust

law Environment rules

Strategic Competing
plan bidders

Accounting Deal Available

rules financing
Hostile vs Public vs
friendly private

Capital
structure

Corporate
Tax
Law

Market
conditions
 Detailed Merger Modeling
• Building a robust merger model is a detailed process
involving several steps:

Step 1: Step 2: Step 3: Step 4: Step 5:

Creating Valuing each Making Modeling the Performing

detailed of the accretion / dilution
forecasts for businesses
acquisition business
assumptions combination and sensitivity
both separately
companies analysis
 M&A Concepts: Merger Model Outputs
• The main outputs of the merger model allow the user to compare the combined
entity with the standalone entities and evaluate the merits of the transaction.
• The most important include pro forma financials, sources and uses of cash, and an
accretion / dilution analysis.

Sources and Uses of Accretion / Dilution

Pro Forma Financials
Cash Analysis

Demonstrate the A summary showing Whether key financial

capitalization and where financing for the
items (EPS, CFPS) have
M&A transaction
relevant financial metrics increased or decreased
comes from and what it
of the acquirer that reflect is used for following the transaction
the M&A transaction
 Scenario Versus Sensitivity
Analysis?

corporatefinanceinstitute.com
 Scenario vs Sensitivity Analysis

Scenario Analysis Sensitivity Analysis

Multiple inputs changed at once One assumption changed at a time

No story about why inputs go up or down

A story (or “scenario”) about the future

Typically represents several business cases Used to determine which assumptions matter
most

Scenarios will be compared and risks A form of risk assessment where drivers are
weighted compared individually
Why Perform Scenario Analysis?

Valuation Business
Planning
Why Perform Scenario Analysis?

• Operating scenarios for the company

• Planning resources (people, capital, etc

Business
Planning

• Corporate and business strategy

Why Perform Scenario Analysis?

• Modeling different views/opinions

Valuation • Different future cases of the world

• Telling a story
Why Perform Sensitivity Analysis?

Valuation
Analysis

Business Model
Planning Testing
Why Perform Sensitivity Analysis?
 • Assess the impact on
valuation as assumptions
change
Valuation
Analysis
 • Macro economic impacts
on value of the business
 • Range of values for the
business under different
scenarios
Why Perform Sensitivity Analysis?

• Cash flow / funding

requirements
Business
Planning
• Hedging and FX
strategies
• • What-if analysis
Why Perform Sensitivity Analysis?

•Test model
functionality
Model
•Ensure drivers / assumptions
Testing are working as intended

• Stress test model

Model Integration

• Build at the end, once

1 2 Create in a
separate area or
model is substantially on a separate
completed sheet

3 4
• Think about the
formula for each
item you want to
sensitize (where to
• Driver must be on same link it)
sheet as the output
Types of Integration

Direct Indirect

• Pick an input/driver you want to sensitize • Pick a formula you want to sensitize
• Pick a range of sensitivity for the input (i.e. +/- 10%) • Create a zero value hardcode cell
• Pick the outputs you want to see the impact on • Pick a range of sensitivity for the input (i.e. +/- 10%)
• Link the table • Pick the outputs you want to see the impact on
• Link the table
Analyzing Results

Which inputs is the model most sensitive to?

Populate a “tornado chart”

Think about expected variability - not all inputs are equal
Communicate results in a clear, effective way
Develop a range of outcomes

Create a “gravity sort table”

Gravity Sort Table

How can you make a table auto-sort the sensitivity analysis results?

Use the Small function in Excel

Rank results from 1 to n

Combine Index and Match functions to output the results

The table will always be sorted in the proper order

Tornado Chart

Easy for people unfamiliar with the model detail to understand

Tornado chart is a great way to illustrate sensitivity analysis

Show inputs from most to least impactful

Indicates what matters most

Great for presentations

• A Tornado diagram is a bar chart that helps project managers with the sensitivity analysis to determine the
impact of various risks on a project.
• This is placed in descending order so that the project manager can take decisions on the high impact items first.
• The topmost bar denotes the significant impact, and the ends of the bars represent a component's low and high
impacts.
2 Excel Practice 2
Presenting Results

• Clear presentation of results is critical

and will set you apart

• Use a combination of tables and charts

• Show a range of values and outcomes

• Discuss the relative variability of

different inputs to assess the biggest
risks (upside and downside)