My Templates - 2
My Templates - 2
Net Profit/Loss
$800.00
$700.00
$600.00
$500.00
$400.00
$300.00
$200.00
$100.00
$-
1 2 3 4 5
$-100.00
Sources of Funds
Sl. No Source Amount Description
1 Jv with VINATABA $ 450.00
2 Convertible Bonds $ 580.00
Sources of Fun
3 Bank Debt $ 370.00
Total $ 1,400.00
Legal Costs
Costs Coningent Funds
Balance Sheet - INR Cr
ASSETS 2024 2025 2026 2027 2028 => Projected
CURRENT ASSETS
Cash ₹ 200.00 ₹ 230.00 ₹ 264.50 ₹ 304.17 ₹ 349.80
Accounts Receivable ₹ 208.00 ₹ 208.42 ₹ 208.83 ₹ 209.25 ₹ 209.67
Inventory ₹ 856.00 ₹ 915.92 ₹ 980.03 ₹ 1,048.64 ₹ 1,122.04
Prepaid Expenses ₹ 646.00 ₹ 646.03 ₹ 646.06 ₹ 646.10 ₹ 646.13
Short-Term Investments ₹ 456.00 ₹ 478.80 ₹ 502.74 ₹ 527.88 ₹ 554.27
TOTAL CURRENT ASSETS ₹ 2,366.00 ₹ 2,479.17 ₹ 2,602.17 ₹ 2,736.04 ₹ 2,881.91
FIXED (LONG-TERM) ASSETS
Long-Term Investments ₹ 200.00 ₹ 236.00 ₹ 278.48 ₹ 328.61 ₹ 387.76
Property, Plant, and Equipment ₹ 34.00 ₹ 56.00 ₹ 78.00 ₹ 90.00 ₹ 120.00
(Less Accumulated Depreciation) ₹ 45.00 ₹ 45.00 ₹ 45.00 ₹ 78.00 ₹ 12.00
Intangible Assets ₹ 54.00 ₹ 54.27 ₹ 54.54 ₹ 54.81 ₹ 55.09
TOTAL FIXED (LONG-TERM) ASSETS ₹ 333.00 ₹ 391.27 ₹ 456.02 ₹ 551.42 ₹ 574.84
OTHER ASSETS
Deferred Income Tax ₹ 300.00 ₹ 310.00 ₹ 320.00 ₹ 330.00 ₹ 340.00
Other ₹ 100.00 ₹ 120.00 ₹ 140.00 ₹ 160.00 ₹ 170.00
TOTAL OTHER ASSETS ₹ 400.00 ₹ 430.00 ₹ 460.00 ₹ 490.00 ₹ 510.00
TOTAL LIABILITIES AND OWNER'S EQUITY ₹ 3,675.00 ₹ 3,885.69 ₹ 4,098.83 ₹ 4,343.95 ₹ 4,595.70
₹ 300,000,000.00
₹ 67,500,000.00
₹ 48,000,000.00
₹ 25,000,000.00
₹ 13,000,000.00
₹ 1,250,000,000.00
₹ 1,964,960,000.00
Compensation and Benefits
Particulars Amount per annum
Employee Salaries ₹ 1,964,960,000.00
Over-time Pay (5% of employee salaries) ₹ 98,248,000.00
Medical Insurance (Average annual premium of Rs. 7500 for a Rs. 3 lakh coverage) (Rs.
₹ 14,895,000.00
7500 x 1986)
Maternal and Infant Insurance (Average annual premium of Rs. 1500 for a Rs. 35,000
₹ 2,979,000.00
coverage)(Rs. 1500 x 1986)
Retirement Funding (10% of employee salaries) ₹ 196,496,000.00
Disability Insurance (2% of employee salaries) ₹ 39,299,200.00
Total ₹ 2,316,877,200.00
Recruitment
HR Budget
Particulars Amount per annum
Compensation and Benefits ₹ 2,316,877,200.00
Training and Development ₹ 35,369,280.00
Recruitment ₹ 42,246,640.00
Employee and Labour Relations ₹ 70,273,600.00
Total ₹ 2,464,766,720.00
Discounted Cash Flow (DCF)
215,000.00
20.6%
12.8%
4.5%
1.50
rket Risk Premium 5.5%
1,046,000.00
6.79%
Leveraged Buyout Model (LBO) - [Company Name]
Assumptions
Sources Uses
Amount xEBITDA % Capital Amount xEBITDA
Bank Debt
Senior Notes
Total Debt
Sponsor Equity
Total Sources
Financials
EBITDA
% sales
EBIT
% sales
Interest
EBT
% sales
Taxes
% tax rate
Net Income
% sales
CapEx
% sales
Debt Schedule
Sr. Notes
Beginning Balance
Interest
Paydown
Ending Balance
Total Debt
Beginning Balance
Interest
Paydown
Ending Balance
IRR
EBITDA at Exit
Exit Multiple
Enterprise Value
Net Debt
Sponsor Equity Value
MOIC
IRR
Cost
6.0%
12.0%
ference
0.0%
% Capital
Year 5
Year 5
Year 5
Year 5
I
Internal Rate of Return (IRR)
($ in millions) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
12/31/21 12/31/22 12/31/23 12/31/24 12/31/25 12/31/26
LBO Returns Schedule IRR MOIC 12/31/21 12/31/22 12/31/23 12/31/24 12/31/25
Exit Year 1 0.0% 1.0x ($100) $100
Exit Year 2 22.5% 1.5x ($100) -- $150
Exit Year 3 26.0% 2.0x ($100) -- -- $200
Exit Year 4 25.7% 2.5x ($100) -- -- -- $250
Exit Year 5 24.6% 3.0x ($100) -- -- -- --
Year 5
$6
300.0%
12/31/26
$300
Debt Schedule - [Company Name]
Loan amount (in USD million) 20,000
Annual Payment Interest Portion Principal Portion Outstanding Balance
Year 1 110 17.72 92.28 -908
Year 2 110 16.08 93.92 -814
Year 3 110 14.42 95.58 -718
Year 4 110 12.72 97.28 -621
Year 5 110 11.00 99.00 -522
Year 6 110 9.25 100.75 -421
Year 7 110 7.46 102.54 -319
Year 8 110 5.64 104.36 -214
Year 9 110 3.80 106.20 -108
Year 10 110 1.91 108.09 -0
Total 1,100 100 1,000
12000
10000
8000
6000
4000
2000
0
XYZ
Column G Column H
Sharpe Ratio Sortino Ratio
Month Returns Excess Return
Expected Portfolio Return (Rp) 25.0% 1 0.89% 0.64%
Risk-Free Rate (Rf) 5.5% 2 0.12% -0.13%
Standard Deviation of Portfolio (σp) 8.0% 3 -0.20% -0.45%
4 1.00% 0.75%
Sharpe Ratio 2.44x 5 -0.02% -0.27%
6 2.00% 1.75%
7 3.00% 2.75%
8 1.00% 0.75%
9 -0.30% -0.55%
10 1.00% 0.75%
11 1.02% 0.77%
12 -1.00% -1.25%
no Ratio
Negative Excess Returns
0.00% Min Acc Return 0.25%
-0.13% Downside Risk 0.0042399882075
-0.45% Av Excess Return 0.46%
0.00% Sortino Ratio 1.0829432635004
-0.27%
0.00%
0.00%
0.00%
-0.55%
0.00%
0.00%
-1.25%
Accretion / Dilution Analysis
Acquirer Target
Acquirer
Current share price $750.00
Diluted shares outstanding 310,000,000
Earnings per share (EPS) forecast $25.00
Acquirer shares issued in transaction
Target
Pre-deal share price $25.00
Offer price per share $40.00
% Offer Premium 62.5%
Shares outstanding 315,000,000
Offer value $12,600,000,000
Earnings per share (EPS) forecast $2.00
Sensitivity analysis
Accretion / dilution sensitivity at various % consideration and $ offer price per share assumptions
% stock:
$0.00 0%
Offer price: $30.00 $0.00
$40.00 $0.00
$70.00 $0.00
Pro Deal Assumptions
Forma (Year 1)
Deal date:
Form of consideration <--Assume cash portion financed entirely by new acquirer debt for simplicity of this exercise.
% Stock 50.0%
% Cash 50.0%
Deal debt:
Acquirer new borrowing $6,300,000,000
Term of loan 5 years
Interest rate on new debt 5.0%
Financing fees $2,000,000
Annual financing fee amortization $400,000
Synergies: $100,000,000
Asset write-ups:
Book value of target assets $20,000,000
Fair market value of target assets $25,000,000
Asset write-up $5,000,000
Useful life 10 years
Incremental D&A expense $500,000
Advertising SUBTOTAL ₹ -
Online
Print
Outdoor
Radio
Television
Web SUBTOTAL ₹ -
Development
Pay-Per-Click Marketing
SEO
Other SUBTOTAL ₹ -
Premiums
Corporate Branding
Business Cards
Signage