Reproduce in Excel table 7.15.
All numbers are formulae (so link table 7.15 to the previous tables)
except the EBITDA multiple (9.1x) which is input.
2026 EBITDA from Table 7.7 Pro Forma Income Statement, etc.
Table 7.4 Spreadsheet Kappa Capital Expenditure Assumptions 2022 2023 2024 2025 2026 Year 2021 Fixed Assets and Capital Investment ($ 000) 1 Opening Book Value 50,000 2 Capital Investment 5,000 3 Depreciation (5,500) 4 Closing Book Value 49,500 49,500 5,000 (5,450) 49,050 49,050 5,000 (5,405) 48,645 48,645 20,000 (6,865) 61,781 61,781 15,000 (7,678) 69,102 69,102 8,000 (7,710) 69,392 Table 7.10 Spreadsheet Kappa's Free Cash Flow Forecast 2021 2022 2023 2024 2025 2026 Year Free Cash Flow ($ 000) 1 Net Income 2 Plus: After-Tax Interest Expense 3 Unlevered Net Income 4 Plus: Depreciation Less: Increases in NWC 6 Less: Capital Expenditures 7 Free Cash Flow of Firm 8 Plus: Net Borrowing 9 Less: After-Tax Interest Expense 10 Free Cash Flow to Equity 5,193 4,420 9,613 5,450 3,412 (5,000) 13,475 6,247 6,960 8,382 10,545 4,420 4,420 5,083 5,304 10,667 11,380 13,465 15,849 5,405 6,865 7,678 7,710 (3,663) (4,089) (4,685) (5,231) (5,000) (20,000) (15,000) (8,000) 7,409 (5,845) 1,458 10,328 15,000 5,000 (4,420) (4,420) (5,083) (5,304) 2,989 4,735 1,375 5,024 - (4,420) 9,055 Table 7.15 Spreadsheet Continuation Value Estimate for Kappa Continuation Value: Multiples Approach ($ 000) 1 EBITDA in 2021 32,094 2 EBITDA multiple 9.1 x 3 Continuation Enterprise Value 292,052 4 Debt (120,000) 5 Continuation Equity Value 172,052 Common Multiples EV/Sales P/E (levered) P/E (unlevered) 1.8X 16.3x 18.4 x Estimated 2021 Income Statement and Balance Sheet Data for Kappa Corporation Year 2021 Year 2021 Income Statement ($ 000) Balance Sheet ($ 000) 1 Sales 75,000 Assets 2 Cost of Goods Sold 1 Cash and Equivalents 12,664 3 Raw Materials (16,000) 2 Accounts Receivable 18,493 4 Direct Labor Costs (18,000) 3 Inventories 6,165 5 Gross Profit 41,000 4 Total Current Assets 37,322 6 Sales and Marketing (11,250) 5 Property, Plant, and Equipment 49,500 7 Administrative (13,500) 6 Goodwill 8 EBITDA 16,250 7 Total Assets 86,822 9 Depreciation (5,500) Liabilities and Stockholder's Equity 10 EBIT 10,750 8 Accounts Payable 4,654 11 Interest Expense (net) (75) 9 Debt 4,500 12 Pre-tax Income 10,675 10 Total Liabilities 9,154 13 Income Tax (3,736) 11 Stockholder's Equity 77,668 14 Net Income 6,939 12 Total Liabilities and Equity 86,822 2021 2022 2023 2024 2025 2026 18493 16970 22709 7.9 Year Working Capital Assests Account Recievable Raw Materials Finished Goods Minimum Cash Balance 14525 -1534 -4967 19689 -2039 1922 -1775 -2329 26059 -2646 -9138 4147 -5838 -6815 -7911 6165 7262 8485 9845 11355 13030 30822 28288 33067 38388 44304 50872 1294 1433 1695 1941 2211 2570 4953 5938 Total Current Assets Liabilities Wages Payable Other Accounts Payable Total Liabilities Net Working Capital Net Working Capital (5-8) Increase in Net Working Capital 3360 4654 4099 5532 6900 9110 7878 10448 6648 7879 26168 22756 26419 30509 35194 40425 -3412 3663 4089 4685 5231 Table 7.7 Spreadsheet Pro Forma Income Statement for Kappa, 2021-2026 Year 2021 2022 2023 2024 2025 2026 75,000 88,358 103,234 119,777 138,149 158,526 Income Statement ($ 000) 1 Sales 2 Cost of Goods Sold 3 Raw Materials 4 Direct Labor Costs 5 Gross Profit 6 Sales and Marketing 7 Administrative 8 EBITDA 9 Depreciation 10 EBIT 11 Interest Expense (net) 12 Pretax Income 13 Income Tax 14 Net Income (16,000) (18,665) (21,593) (24,808) (28,333) (32,193) (18,000) (21,622) (25,757) (30,471) (35,834) (41,925) 41,000 48,071 55,883 64,498 73,982 84,407 (11,250) (14,579) (18,582) (23,356) (27,630) (31,705) (13,500) (13,254) (15,485) (16,769) (17,959) (20,608) 16,250 20,238 21,816 24,373 28,393 32,094 (5,500) (5,450) (5,405) (6,865) (7,678) (7710) 10,750 14,788 16,411 17,508 20,715 24,383 (75) (6,800) (6,800) (6,800) (7,820) (8,160) 10,675 7,988 9,611 10,708 12,895 16,223 (3,736) (2,796) (3,364) (3,748) (4,513) (5,678) 6,939 5,193 6,247 6,960 8,382 10,545 Table 7.9 Spreadsheet Kappa's Net Working Capital Forecast 2021 2022 2023 2024 2025 2026 Year Working Capital ($ 000) Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 4 Minimum Cash Balance 5 Total Current Assets Liabilities 6 Wages Payable 7 Other Accounts Payable 8 Total Current Liabilities Net Working Capital 9 Net Working Capital (5 - 8) 10 Increase in Net Working Capital 18,493 14,525 1,973 1,534 4,192 4,967 6,164 7,262 30,822 28,288 16,970 1,775 5,838 8,485 33,067 19,689 2,039 6,815 9,845 38,388 22,709 26,059 2,329 2,646 7,911 9,138 11,355 13,030 44,304 50,872 1,294 3,360 4,654 1,433 4,099 5,532 1,695 4,953 6,648 1,941 5,938 7,879 2,211 6,900 9,110 2,570 7,878 10,448 26,168 22,756 (3,412) 26,419 3,663 30,509 4,089 35,194 4,685 40,425 5,231 Table 7.8 Spreadsheet Kappa's Working Capital Requirements Year 2021 >2021 Working Capital Days Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 4 Minimum Cash Balance Liabilities 5 Wages Payable 6 Other Accounts Payable Based on: Sales Revenue Raw Materials Costs Raw Materials + Labor Costs Sales Revenue Days 90 45 45 30 Days 60 30 45 30 & a a 15 Direct Labor + Admin Costs Raw Materials + Sales and Marketing 15 45 Table 7.5 Spreadsheet Kappa's Planned Debt and Interest Payments 2021 2022 2023 2024 2025 2026 Year Debt and Interest Table ($ 000) 1 Outstanding Debt 2 Interest on Term Loan 6.80% 100,000 100,000 100,000 115,000 120,000 120,000 (6,800) (6,800) (6,800) (7,820) (8,160) Table 7.3 Spreadsheet Kappa Sales and Operating Cost Assumptions 2021 2022 2023 2024 2025 2026 10,000 10.0% 75.00 10,500 11.0% 76.50 11,025 12.0% 78.03 11,576 13.0% 79.59 12,155 14.0% 81.18 12,763 15.0% 82.81 Year Sales Data Growth/Year Market Size (000 units) 5.0% 2 Market Share 1.0% 3 Average Sales Price ($/unit) 2.0% Cost of Goods Data 4 Raw Materials ($/unit) 1.0% 5 Direct Labor Costs ($/unit) 4.0% Operating Expense and Tax Data 6 Sales and Marketing (% sales) 7 Administrative (% sales) 8 Tax Rate 16.00 18.00 16.16 18.72 16.32 19.47 16.48 20.25 16.65 21.06 16.82 21.90 15.0% 18.0% 35.0% 16.5% 15.0% 35.0% 18.0% 15.0% 35.0% 19.5% 14.0% 35.0% 20.0% 13.0% 35.0% 20.0% 13.0% 35.0% Table 7.4 Spreadsheet Kappa Capital Expenditure Assumptions 2022 2023 2024 2025 2026 Year 2021 Fixed Assets and Capital Investment ($ 000) 1 Opening Book Value 50,000 2 Capital Investment 5,000 3 Depreciation (5,500) 4 Closing Book Value 49,500 49,500 5,000 (5,450) 49,050 49,050 5,000 (5,405) 48,645 48,645 20,000 (6,865) 61,781 61,781 15,000 (7,678) 69,102 69,102 8,000 (7,710) 69,392 Table 7.10 Spreadsheet Kappa's Free Cash Flow Forecast 2021 2022 2023 2024 2025 2026 Year Free Cash Flow ($ 000) 1 Net Income 2 Plus: After-Tax Interest Expense 3 Unlevered Net Income 4 Plus: Depreciation Less: Increases in NWC 6 Less: Capital Expenditures 7 Free Cash Flow of Firm 8 Plus: Net Borrowing 9 Less: After-Tax Interest Expense 10 Free Cash Flow to Equity 5,193 4,420 9,613 5,450 3,412 (5,000) 13,475 6,247 6,960 8,382 10,545 4,420 4,420 5,083 5,304 10,667 11,380 13,465 15,849 5,405 6,865 7,678 7,710 (3,663) (4,089) (4,685) (5,231) (5,000) (20,000) (15,000) (8,000) 7,409 (5,845) 1,458 10,328 15,000 5,000 (4,420) (4,420) (5,083) (5,304) 2,989 4,735 1,375 5,024 - (4,420) 9,055 Table 7.15 Spreadsheet Continuation Value Estimate for Kappa Continuation Value: Multiples Approach ($ 000) 1 EBITDA in 2021 32,094 2 EBITDA multiple 9.1 x 3 Continuation Enterprise Value 292,052 4 Debt (120,000) 5 Continuation Equity Value 172,052 Common Multiples EV/Sales P/E (levered) P/E (unlevered) 1.8X 16.3x 18.4 x Estimated 2021 Income Statement and Balance Sheet Data for Kappa Corporation Year 2021 Year 2021 Income Statement ($ 000) Balance Sheet ($ 000) 1 Sales 75,000 Assets 2 Cost of Goods Sold 1 Cash and Equivalents 12,664 3 Raw Materials (16,000) 2 Accounts Receivable 18,493 4 Direct Labor Costs (18,000) 3 Inventories 6,165 5 Gross Profit 41,000 4 Total Current Assets 37,322 6 Sales and Marketing (11,250) 5 Property, Plant, and Equipment 49,500 7 Administrative (13,500) 6 Goodwill 8 EBITDA 16,250 7 Total Assets 86,822 9 Depreciation (5,500) Liabilities and Stockholder's Equity 10 EBIT 10,750 8 Accounts Payable 4,654 11 Interest Expense (net) (75) 9 Debt 4,500 12 Pre-tax Income 10,675 10 Total Liabilities 9,154 13 Income Tax (3,736) 11 Stockholder's Equity 77,668 14 Net Income 6,939 12 Total Liabilities and Equity 86,822 2021 2022 2023 2024 2025 2026 18493 16970 22709 7.9 Year Working Capital Assests Account Recievable Raw Materials Finished Goods Minimum Cash Balance 14525 -1534 -4967 19689 -2039 1922 -1775 -2329 26059 -2646 -9138 4147 -5838 -6815 -7911 6165 7262 8485 9845 11355 13030 30822 28288 33067 38388 44304 50872 1294 1433 1695 1941 2211 2570 4953 5938 Total Current Assets Liabilities Wages Payable Other Accounts Payable Total Liabilities Net Working Capital Net Working Capital (5-8) Increase in Net Working Capital 3360 4654 4099 5532 6900 9110 7878 10448 6648 7879 26168 22756 26419 30509 35194 40425 -3412 3663 4089 4685 5231 Table 7.7 Spreadsheet Pro Forma Income Statement for Kappa, 2021-2026 Year 2021 2022 2023 2024 2025 2026 75,000 88,358 103,234 119,777 138,149 158,526 Income Statement ($ 000) 1 Sales 2 Cost of Goods Sold 3 Raw Materials 4 Direct Labor Costs 5 Gross Profit 6 Sales and Marketing 7 Administrative 8 EBITDA 9 Depreciation 10 EBIT 11 Interest Expense (net) 12 Pretax Income 13 Income Tax 14 Net Income (16,000) (18,665) (21,593) (24,808) (28,333) (32,193) (18,000) (21,622) (25,757) (30,471) (35,834) (41,925) 41,000 48,071 55,883 64,498 73,982 84,407 (11,250) (14,579) (18,582) (23,356) (27,630) (31,705) (13,500) (13,254) (15,485) (16,769) (17,959) (20,608) 16,250 20,238 21,816 24,373 28,393 32,094 (5,500) (5,450) (5,405) (6,865) (7,678) (7710) 10,750 14,788 16,411 17,508 20,715 24,383 (75) (6,800) (6,800) (6,800) (7,820) (8,160) 10,675 7,988 9,611 10,708 12,895 16,223 (3,736) (2,796) (3,364) (3,748) (4,513) (5,678) 6,939 5,193 6,247 6,960 8,382 10,545 Table 7.9 Spreadsheet Kappa's Net Working Capital Forecast 2021 2022 2023 2024 2025 2026 Year Working Capital ($ 000) Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 4 Minimum Cash Balance 5 Total Current Assets Liabilities 6 Wages Payable 7 Other Accounts Payable 8 Total Current Liabilities Net Working Capital 9 Net Working Capital (5 - 8) 10 Increase in Net Working Capital 18,493 14,525 1,973 1,534 4,192 4,967 6,164 7,262 30,822 28,288 16,970 1,775 5,838 8,485 33,067 19,689 2,039 6,815 9,845 38,388 22,709 26,059 2,329 2,646 7,911 9,138 11,355 13,030 44,304 50,872 1,294 3,360 4,654 1,433 4,099 5,532 1,695 4,953 6,648 1,941 5,938 7,879 2,211 6,900 9,110 2,570 7,878 10,448 26,168 22,756 (3,412) 26,419 3,663 30,509 4,089 35,194 4,685 40,425 5,231 Table 7.8 Spreadsheet Kappa's Working Capital Requirements Year 2021 >2021 Working Capital Days Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 4 Minimum Cash Balance Liabilities 5 Wages Payable 6 Other Accounts Payable Based on: Sales Revenue Raw Materials Costs Raw Materials + Labor Costs Sales Revenue Days 90 45 45 30 Days 60 30 45 30 & a a 15 Direct Labor + Admin Costs Raw Materials + Sales and Marketing 15 45 Table 7.5 Spreadsheet Kappa's Planned Debt and Interest Payments 2021 2022 2023 2024 2025 2026 Year Debt and Interest Table ($ 000) 1 Outstanding Debt 2 Interest on Term Loan 6.80% 100,000 100,000 100,000 115,000 120,000 120,000 (6,800) (6,800) (6,800) (7,820) (8,160) Table 7.3 Spreadsheet Kappa Sales and Operating Cost Assumptions 2021 2022 2023 2024 2025 2026 10,000 10.0% 75.00 10,500 11.0% 76.50 11,025 12.0% 78.03 11,576 13.0% 79.59 12,155 14.0% 81.18 12,763 15.0% 82.81 Year Sales Data Growth/Year Market Size (000 units) 5.0% 2 Market Share 1.0% 3 Average Sales Price ($/unit) 2.0% Cost of Goods Data 4 Raw Materials ($/unit) 1.0% 5 Direct Labor Costs ($/unit) 4.0% Operating Expense and Tax Data 6 Sales and Marketing (% sales) 7 Administrative (% sales) 8 Tax Rate 16.00 18.00 16.16 18.72 16.32 19.47 16.48 20.25 16.65 21.06 16.82 21.90 15.0% 18.0% 35.0% 16.5% 15.0% 35.0% 18.0% 15.0% 35.0% 19.5% 14.0% 35.0% 20.0% 13.0% 35.0% 20.0% 13.0% 35.0%
