企业并购财务模型工作底稿-LBOModel

1、1Model 16-01ALBO Model Loan Amortization, 5 Year PeriodBank Loan AmortizationLoan$1,800,000Interest Rate9%Payments$462,766Year 1Year 2Year 3Year 4Year 5Interest162,000134,931105,42673,26538,210Principal300,766327,835357,341389,501424,556Balance1,499,2341,171,398814,058424,5560Insurance Company Loan

2、AmortizationLoan$0Interest Rate16%Payments$0Year 1Year 2Year 3Year 4Year 5Interest00000Principal00000Balance00000Total Loan AmortizationYear 1Year 2Year 3Year 4Year 5Interest162,000134,931105,42673,26538,210Principal300,766327,835357,341389,501424,556Balance1,499,2341,171,398814,058424,5560Beginning

3、Year Balance1,800,0001,499,2341,171,398814,058424,556% Interest9.00%9.00%9.00%9.00%9.00%2Model 16-01BLBO Model Pro-Forma Cash Flows, Years 1 to 5Initial earnings before interest and taxes (EBIT)650,000Growth rate of EBIT0%Tax rate40%Gross plant & equipment2,400,000Depreciation rate (% of gross p

4、lant & equipment)20%Year 1Year 2Year 3Year 4Year 5EBIT650,000650,000650,000650,000650,000- Interest*162,000134,931105,42673,26538,210EBT488,000515,069544,574576,735611,790- Taxes195,200206,028217,830230,694244,716NI292,800309,041326,744346,041367,074+ Depreciation480,000480,000480,000480,000480,

5、000CFBDR*772,800789,041806,744826,041847,074- Principal repaid*300,766327,835357,341389,501424,556Addition to cash472,034461,206449,404436,540422,518* From Model 16-01A* CFBDR: Cash flow before debt repayment.3Model 16-01CLBO Balance Sheet Analysis, Years 1 to 5Equity position of insurance company0M

6、anagement team equity200,000Required rate of return to insurance company0%Year 0Year 1Year 2Year 3Year 4Year 5Cash (from Model 16-01B)*-472,034933,2401,382,6431,819,1832,241,701Other current assets, net1,0001,0001,0001,0001,0001,000Total current assets1,000473,034934,2401,383,6431,820,1832,242,701Pr

7、operty99,00099,00099,00099,00099,00099,000Plant & equipment, gross2,400,0002,400,0002,400,0002,400,0002,400,0002,400,000Less: reserve500,000980,0001,460,0001,940,0002,420,0002,900,000Plant & equipment, net1,900,0001,420,000940,000460,000(20,000)(500,000)Total assets2,000,0001,992,0341,973,24

8、01,942,6431,899,1831,841,701Debt1,800,0001,499,2341,171,398814,058424,556-Equity200,000492,800801,8411,128,5861,474,6271,841,701Total claims2,000,0001,992,0341,973,2391,942,6441,899,1831,841,701% debt90%75%59%42%22%0%Equity AnalysisPrevious equity200,000492,800801,8411,128,5861,474,627Add: net Incom

9、e292,800309,041326,744346,041367,074New equity balance492,800801,8411,128,5861,474,6271,841,701Cash Balance AnalysisNet income292,800309,041326,744346,041367,074Add: depreciation480,000480,000480,000480,000480,000772,800789,041806,744826,041847,074Less: debt repayment300,766327,835357,341389,501424,

10、556472,034461,206449,404436,540422,518Add: Previous cash balance-472,034933,2401,382,6431,819,183Cash account472,034933,2401,382,6431,819,1832,241,701Compounded annual growth rate of book equity55.9%Payment on equity interest to insurance company*0Remaining equity for management1,841,701* Could be u

11、sed for additional investments* (Equity Position of Insurance Company) x (1 + Required Rate of Return)54Model 16-01DChange in Working Capital CalculationYear 0Year 1Year 2Year 3Year 4Year 51. Current Assets (from Model16-01C)1,000473,034934,2401,383,6431,820,1832,242,7012. Current Liabilities*300,76

12、6327,835357,341389,501424,556-3. Working Capital = (1) - (2)(299,766)145,198576,899994,1421,395,6272,242,7014. Change in Working Capital = D(3)444,965431,701417,243401,485847,074* Debt due in one year (from Model 16-01A)6Model 16-01ECapital Cash Flow Model (CCF)EBIT and Interest ExpenseYear 1Year 2Y

13、ear 3Year 4Year 5EBIT650,000650,000650,000650,000650,000Interest (i)162,000134,931105,42673,26538,210Capital Cash FlowsYear 1Year 2Year 3Year 4Year 51. EBIT(1-T)390,000390,000390,000390,000390,0002. + Depreciation480,000480,000480,000480,000480,0003. Capital Expenditures000004. Change in Working Cap

14、ital444,965431,701417,243401,485847,0745. Free Cash Flows Unlevered Firm425,035438,299452,757468,51522,9266. + Interest Tax Shield (iT)64,80053,97242,17029,30615,2847. Capital Cash Flow489,835492,272494,927497,82238,2108. Discounted Capital Cash Flow*433,483385,521343,009305,32320,7399. Steady State Tax Shield (SSTS)010. Terminal value*4,017,000Components of ValueAssumptionsPV Capital Cash Flows1,488,075Risk-Free Rate (Rf)6%PV Terminal Value2,180,267Risk Premium (RP)7%Asset Beta (ba)1.00Total Firm Value3,668,342Expected Asset Return (ka)13.00% Less: Pre-Existing Debt1,800,000Termina