Question
All numbers are formulae. Capital Expenditures are Capital Investments from table 7.4 The after-tax interest expense is calculated from table 7.3 (tax rate) and table
All numbers are formulae.
Capital Expenditures are Capital Investments from table 7.4
The after-tax interest expense is calculated from table 7.3 (tax rate) and table 7.5 for Interest on Debt as:
After Tax Interest Expense = (1 - Tax Rate) * (Interest on Debt - Interest on Excess Cash)
We assume that Interest on Excess Cash = 0 for all years.
The net borrowing is calculated from table 7.5 for Net Debt as:
Net Borrowing in Year t = Net Debt in Year t - Net Debt in Year (t - 1)
Please do the above calculations!
The pictures of the Tables are attached
Table 7.3 Spreadsheet Kappa Sales and Operating Cost Assumptions 2021 2022 2023 2024 2025 2026 10,000 10,500 10.0% 11.0% 75.00 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 1 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.82 16.00 18.00 16.16 18.72 16.32 19.47 16.48 20.25 16.65 21.06 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 2023 2024 2025 2026 Year 2021 2022 Fixed Assets and Capital Investment ($ 000) Opening Book Value 50,000 49,500 2. Capital Investment 5,000 5,000 3 Depreciation (5,500) (5,450) 4 Closing Book Value 49,500 49,050 49,050 5,000 (5,405) 48,645 48,645 20,000 (6,865) 61,781 61,781 69,102 15,000 8,000 (7,678) (7,710) 69,102 69,392 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.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 5 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,055Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started