3. Based on the forecasts you have already developed, use the following spreadsheet to project Idexos free cash flow for 20202024. Will the firms free cash flow steadily increase over this period? Why or why not?
| Free Cash Flow ($000s) | | | | | | |
| | Year | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 |
| 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 | | | | | | |
B D E F G I J K L TABLE 3 SPREADSHEET Idexo's Capital Expenditure Forecast 2019 2020 2021 2022 2023 2024 1 Year 2 Fixed Assets and Capital Investment (5000s) 3 Opening Book Value 4 Capital Investment 5 Depreciation 6 Closing Book Value 60,167 6,500 -6,667 60,000 60,000 5,000 -6,500 58,500 58,500 5,000 -6,350 57,150 57,150 25,000 -8,215 73,935 73,935 12,000 -8,594 77,341 77,341 8,000 -8,534 76,807 TABLE 4 SPREADSHEET Idexo's Debt and Interest Forecast 2019 2020 2021 2022 2023 2024 1 Year 2 Debt and Interest Table (5000s) 3 Outstanding Debt 4 Interest on Term Loan 7.0% 20,000 20,000 -1,400 20,000 -1,400 35,000 -1,400 40,000 -2,450 40,000 -2,800 Year 2019 Working Capital (5000s) Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 2 Inventories 3 Minimum Cash Balance 4 Total Current Assets Liabilities 5 Wages Payable 6 Other Accounts Payable 5 Accounts Payable Net Working Capital 20,000 2,630 5,589 8,219 8,219 36,438 1,726 4,479 6,205 30,233 TABLE 2 SPREADSHEET Idexo's Income Statement and Balance Sheet 2019 2019 120,000 1 Year 2 Income Statement (5000s) 3 Sales 4 Cost of Goods Sold 5 Raw Materials 6 Direct Labor Costs 7 Gross Profit 8 Sales and Marketing 9 Administration 10 EBITA 11 Depreciation 12 EBIT 13 Interest Expense (net) 14 Pretax Income 15 Income Tax 16 Net Income Year 2 Balance Sheet (5000s) 3 Assets 4 Cash and Cash Equivalents 5 Accounts Receivable 6 Inventories 17 Total Current Assets 8 Property, Plant, and Equipment 9 Goodwill 10 Total Assets 11 Liabilities and Stockholders' Equity 12 Accounts Payable 13 Debt 14 Total Liabilities 15 Stockholders' Equity 16 Total Liabilities and Equity 15,000 20,000 8,219 43,219 60,000 1000 104,219 -21,333 -24,000 74,667 -15,000 -18,000 41,667 -6,667 35,000 -1,021 33,979 -4,893 29,086 6,205 20,000 26,205 78,014 104,219 TABLE 2 SPREADSHEET Idexo's Sales and Operating Cost Projections 2022 2023 2024 23,820 17.0% 45.48 25,250 18.0% 46.39 26,765 18.0% 47.32 1 Year 2019 2020 2021 2 Sales Data Growth/Yr 3 Market Size (000s units) 6.0% 20,000 21,200 22,472 4 Market Share* 1.0% 14.0% 15.0% 16.0% 5 Average Sales Price (S/unit) 2.0% 42.86 43.71 44.59 6 7 Operating Expense and Tax Data 8 Gross Margin 62.2% 53.0% 53.0% 9 Sales and Marketing (% sales) 12.5% 16.5% 18.0% 10 Administration (% sales) 15.0% 15.0% 15.0% 11 Tax Rate 14.4% 35.0% 35.0% Market Share growth is expected through 2017 only. Growth is shown in absolute terms. Sheet1 Sheet2 53.0% 19.5% 14.0% 35.0% 53.0% 20.0% 13.0% 35.0% 53.0% 20.0% 13.0% 35.0% oll Lock B D E F G I J K L TABLE 3 SPREADSHEET Idexo's Capital Expenditure Forecast 2019 2020 2021 2022 2023 2024 1 Year 2 Fixed Assets and Capital Investment (5000s) 3 Opening Book Value 4 Capital Investment 5 Depreciation 6 Closing Book Value 60,167 6,500 -6,667 60,000 60,000 5,000 -6,500 58,500 58,500 5,000 -6,350 57,150 57,150 25,000 -8,215 73,935 73,935 12,000 -8,594 77,341 77,341 8,000 -8,534 76,807 TABLE 4 SPREADSHEET Idexo's Debt and Interest Forecast 2019 2020 2021 2022 2023 2024 1 Year 2 Debt and Interest Table (5000s) 3 Outstanding Debt 4 Interest on Term Loan 7.0% 20,000 20,000 -1,400 20,000 -1,400 35,000 -1,400 40,000 -2,450 40,000 -2,800 Year 2019 Working Capital (5000s) Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 2 Inventories 3 Minimum Cash Balance 4 Total Current Assets Liabilities 5 Wages Payable 6 Other Accounts Payable 5 Accounts Payable Net Working Capital 20,000 2,630 5,589 8,219 8,219 36,438 1,726 4,479 6,205 30,233 TABLE 2 SPREADSHEET Idexo's Income Statement and Balance Sheet 2019 2019 120,000 1 Year 2 Income Statement (5000s) 3 Sales 4 Cost of Goods Sold 5 Raw Materials 6 Direct Labor Costs 7 Gross Profit 8 Sales and Marketing 9 Administration 10 EBITA 11 Depreciation 12 EBIT 13 Interest Expense (net) 14 Pretax Income 15 Income Tax 16 Net Income Year 2 Balance Sheet (5000s) 3 Assets 4 Cash and Cash Equivalents 5 Accounts Receivable 6 Inventories 17 Total Current Assets 8 Property, Plant, and Equipment 9 Goodwill 10 Total Assets 11 Liabilities and Stockholders' Equity 12 Accounts Payable 13 Debt 14 Total Liabilities 15 Stockholders' Equity 16 Total Liabilities and Equity 15,000 20,000 8,219 43,219 60,000 1000 104,219 -21,333 -24,000 74,667 -15,000 -18,000 41,667 -6,667 35,000 -1,021 33,979 -4,893 29,086 6,205 20,000 26,205 78,014 104,219 TABLE 2 SPREADSHEET Idexo's Sales and Operating Cost Projections 2022 2023 2024 23,820 17.0% 45.48 25,250 18.0% 46.39 26,765 18.0% 47.32 1 Year 2019 2020 2021 2 Sales Data Growth/Yr 3 Market Size (000s units) 6.0% 20,000 21,200 22,472 4 Market Share* 1.0% 14.0% 15.0% 16.0% 5 Average Sales Price (S/unit) 2.0% 42.86 43.71 44.59 6 7 Operating Expense and Tax Data 8 Gross Margin 62.2% 53.0% 53.0% 9 Sales and Marketing (% sales) 12.5% 16.5% 18.0% 10 Administration (% sales) 15.0% 15.0% 15.0% 11 Tax Rate 14.4% 35.0% 35.0% Market Share growth is expected through 2017 only. Growth is shown in absolute terms. Sheet1 Sheet2 53.0% 19.5% 14.0% 35.0% 53.0% 20.0% 13.0% 35.0% 53.0% 20.0% 13.0% 35.0% oll Lock
