4. Recall that Idexo plans to maintain only the minimal necessary cash and pay out all excess cash as dividends.

1. Suppose that at the very end of 2019 Ferris plans to use all excess cash to pay an immediate dividend. How much cash can the firm pay out at this time? Compute a new 2019 balance sheet reflecting this dividend using the spreadsheet.
2. Forecast the cash available to pay dividends in future yearsthe firms free cash flow to equityby adding any new borrowing and subtracting after-tax interest expenses from free cash flow each year. Will Idexo have sufficient cash to pay dividends in all years? Explain.
3. Using your forecast of the firms dividends, construct a pro forma balance sheet for Idexo over the next five years.

2019* 2020 2021 2022 2023 2024 Year BALANCE SHEET (5000s) Assets 1 Cash & Cash Equivalents 2 Accounts Receivable 3 Inventories 4 Total Current Assets 5 Property, Plant and Equipment 6 Goodwill 7 Total Assets Liabilities 8 Accounts Payable 9 Debt 10 Total Liabilities Stockholders' Equity 11 Starting Stockholders' Equity 12 Net Income 13 Dividends 14 Capital Contributions 15 Stockholders' Equity 16 Total Liabilities & Equity 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 2019* 2020 2021 2022 2023 2024 Year BALANCE SHEET (5000s) Assets 1 Cash & Cash Equivalents 2 Accounts Receivable 3 Inventories 4 Total Current Assets 5 Property, Plant and Equipment 6 Goodwill 7 Total Assets Liabilities 8 Accounts Payable 9 Debt 10 Total Liabilities Stockholders' Equity 11 Starting Stockholders' Equity 12 Net Income 13 Dividends 14 Capital Contributions 15 Stockholders' Equity 16 Total Liabilities & Equity 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