Financial modeling training
Excel case study
[Link]
1 Instructions
You are considering to acquire a US-based company that specializes in the construction of aeronautical equipment and generates all its revenues in the US and Canada.
The transaction and advisory fee for the acquisition will be 1% of the Equity Value
You will be investing throught Common Shares (CS)
The net financial debt (NFD) of this company is equal to zero prior acquisition
The cash and cash equivalent is equal to 0 prior acquisition
You will partially finance the acquisition through bank debt:
$200 million of Tranche A bank debt that is 100% amortizable over 5 years with an interest rate of 2.5% above the Libor
$70 million of Tranche B bank debt (bullet) with a 5-year maturity and an interest rate of 3.0% above the Libor
Consider a Libor rate of 0.5%
Before the acquisition, the company generated $300 million in revenues and $85 million in EBITDA.
The CEO has informed you that a manager will retire next month, and the company will not hire a new employee to replace him. The manager's annual salary is $200,000.
Additionally, the company incurred $600,000 in marketing costs and $200,000 in headhunting costs in year N. These types of expenses are not expected to occur again in the future.
You will build a business plan with 3 scenarios: management scenario, central scenario, crash scenario
Revenues are expected to increase by 7% per year in the management scenario, 5% per year in the central scenario, and 4% per year in the crash scenario.
Payroll and social security expenses will represent 38%, 40%, and 42% of revenues in each scenario, respectively.
Marketing expenses will represent 5% of revenues in all scenarios.
Other expenses will represent 25% of revenues in all scenarios.
Depreciation and amortization (D&A) will represent 5% of revenues in all scenarios.
For this exercice, consider a 25% corporate tax rate
Working Capital will represent 10% of revenues in all scenarios
Capex will represent 4%, 5%, and 6% of revenues for the management, central, and crash scenarios, respectively.
1 You are planning an iso-multiple investment (same valuation multiple at entrance and exit). Build the model and the IRR sensitivity table.
2 Determine the projected Leverage Ratio for 2025
3 Calculate the investors required output cash flow to reach a target IRR of 20%, without changing any other cash flows. Deduce the last year's EBITDA from this value to achieve the desired IRR.
Financial modeling training
Excel case study
[Link]
1 Scenarios
Selected scenario
Management Scenario 1
Central Scenario 2
Crash Scenario 3
2 Assumptions
Valuation assumptions
EBITDA Multiple at entry
EBITDA Multiple at exit
Transaction fees (% of Eq.V)
Information prior acquisition ($m) Entry
NFD
Cash
Revenue
EBITDA
Debt assumptions Tranche A
Amount of debt ($m)
Margin
Libor
Maturity
Debt assumptions Tranche B
Amount of debt ($m)
Margin
Libor
Maturity
BP assumptions 2023 2024 2025 2026 2027
Financial modeling training
Excel case study
[Link]
1 Valuation and financing
Valuation ($m) Entry Exit
Normative EBITDA
EBITDA multiple
Enterprise Value
NFD
Equity Value
2 Sources & Uses
Uses ($m) Sources ($m) (in %)
Equity Value Equity
Debt to be refinanced Common Shares (CS)
New debt
Tranche A
Transaction and advisory fees Tranche B
TOTAL 0 TOTAL 0 0%
Financial modeling training
Excel case study
[Link]
1 New debt (in $m)
Tranche A 2023 2024 2025 2026 2027 Debt assumptions
Debt interest Amount of debt ($m)
Debt repayment Margin
Outstanding amount at end of the period Libor
Maturity
Tranche B 2023 2024 2025 2026 2027 Debt assumptions
Debt interest Amount of debt ($m)
Debt repayment Margin
Outstanding amount at end of the period Libor
Maturity
TOTAL 2023 2024 2025 2026 2027
Total debt interest
Total debt repayment
Outstanding amount at end of the period
Financial modeling training
Excel case study
[Link]
1 Financial statement ($m)
P&L ($m) Entry 2023 2024 2025 2026 2027
Revenues
Growth in %
Payroll and social security
as a % of revenues
Marketing expenses
as a % of revenues
Other expenses
as a % of revenues
EBITDA
as a % of revenues
D&A
as a % of revenues
EBIT
Debt interest
Exceptional earnings
Earnings before tax
Corporate tax
Corporate tax rate
Net Income
Tax calculation Entry 2023 2024 2025 2026 2027
Earnings before tax
Carrying over
Tax base
Cash flow ($m) Entry 2023 2024 2025 2026 2027
EBITDA
Corporate tax
Change in Working Capital
Operating Cash flow
Capex
as a % of revenues
Transaction fees
Investment Cash flow
Debt interest
Bank debt repayment
Financing Cash flow
Change in cash
Cash at the beginning of the period
Cash at the end of the period
WC calculation Entry 2023 2024 2025 2026 2027
Working Capital
as a % of revenues
4 Normative EBITDA
Normative EBITDA calculation ($m) Entry
Accounting EBITDA
Retirement not renewed
One-off marketing costs
One-off headhunting fees
Normative EBITDA
5 Debt calculation
NFD calculation ($m) Entry 2023 2024 2025 2026 2027
Gross Financial Debt
Cash at the end of the period
NFD
implied xEBITDA
6 IRR and sensitivity
Equity Value (Eq.V) ($m) Entry 2023 2024 2025 2026 2027 Investors expectations
Normative EBITDA
EBITDA multiple
EV
NFD
Equity Value
IRR 12/31/2022 12/31/2023 12/31/2024 12/31/2025 12/31/2026 12/31/2027
Initial investment
Equity exit
Total investor cash flow
Cash flow needed
CoC multiple
IRR
Expected IRR
IRR Exit EBITDA
Exit EBITDA multiple
Financial modeling training
Excel case study
[Link]
1 Transaction comparables
Following a quick search for comparables, you get the following table:
Transac. Date Company Country Activity Acquirer Holding % EV ($m) EBITDA ($m)
2022 B US Aeronautical equipment manufacture PE Fund 50% 580 74
2021 C Canada Aeronautical equipment manufacture PE Fund 30% 570 64
2023 D US Aeronautical equipment trading PE Fund 40% 450 53
2021 E Canada Aeronautical equipment manufacture MBO 100% 750 95
2015 F US Aeronautical equipment manufacture PE Fund 100% 730 100
2022 G US Aeronautical and aerospace equipment manufacture MBO 50% 900 117
2023 H US Aeronautical equipment manufacture Corporate 100% 70 6
2022 I Canada Motor vehicle equipment manufacture PE Fund 30% 1000 126
2021 J US Aeronautical equipment manufacture MBO 40% 390 55
2022 K UK Aeronautical equipment manufacture MBO 50% 518 69
2021 L US Aeronautical equipment manufacture MBO 25% 1350 145
2023 M US Railway equipment manufacture PE Fund 15% 865 98
2022 N US Aeronautical equipment manufacture PE Fund 10% 600 80
2023 O Germany Aeronautical equipment manufacture MBO 50% 565 62
2021 P US Aeronautical equipment trading PE Fund 30% 620 70
2021 Q Canada Aeronautical equipment manufacture Corporate 100% 430 58
2022 R US Aeronautical equipment manufacture PE Fund 50% 680 92
2023 S US Aeronautical equipment manufacture Corporate 100% 970 110
2020 T US Aeronautical equipment manufacture PE Fund 30% 9400 750
Sources:
MergerMarket
Management
Other transaction in the team
Press releases
Market studies