1. Based on the forecasts above, use the spreadsheet below to construct a pro forma income statement for Idexo over the next five years. What is the annual growth rate of the firm's net income over this period?

2. Use the spreadsheet below to project Idexo's working capital needs over the next five years. Why is the increase in net working capital negative in 2011? Why does the increase in net working capital decline from 2014 to 2015?

3. Based on the forecasts you have already developed, use the spreadsheet below to project Idexo's free cash flow for 2011 to 2015. Will the firm's free cash flow steadily increase over this period? Why or why not?

4. (Optional) Recall that Idexo plans to maintain only the minimal necessary cash and pay out all excess cash as dividends.
a. Suppose that, at the very end of 2010, 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 2010 balance sheet reflecting this dividend using the spreadsheet below.
b. Forecast the cash available to pay dividends in future years-the firm's free cash flow to equity-by 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.
c. Using your forecast of the firm's dividends, construct a pro forma balance sheet for Idexo over the next five years.

5. In late 2010, soon after Ferris's return as CEO, the firm receives an unsolicited offer of $210 million for its outstanding equity. If Ferris accepts the offer, the deal would close at the end of 2010. Suppose Ferris believes that Idexo can be sold at the end of 2015 for an enterprise value equal to nine times its final EBITDA. Idexo's unlevered cost of capital is 10% (specifically, 10% is the pre-tax WACC). Based on your forecast of Idexo's free cash flow in 2011 to 2015 in Question 3, and its final enterprise value in 2015, estimate the following:
a. Idexo's unlevered value at the end of 2010.
b. The present value of Idexo's interest tax shields in 2011 to 2015. (Recall that these tax shields are fixed and so have the same risk level as the debt.)
c. Idexo's enterprise value at the end of 2010. (Add the present value of the interest tax shield in 5b to the unlevered value of the firm in 5a.)
d. Idexo's equity value today. (Adjust the enterprise value in 5c to reflect the firm's debt and excess cash at the end of 2010.)
e. Based on your analysis, should Ferris sell the company now?
Idexo Corporation is a privately held designer and manufacturer of licensed college apparel in Ottawa, Ontario. In late 2010, after several years of lacklustre performance by the firm, the firm's owner and founder, Rebecca Ferris, returned from retirement to replace the current CEO, reinvigorate the firm, and plan for its eventual sale or a possible IPO. She has hired you to assist with developing the firm's financial plan for the next five years.
In 2010, Idexo had total assets of about $103 million and annual sales of $100 million (see Table 1). The firm was profitable, with expected 2010 earnings of over $9 million, for a net profit margin of 9.1%.5 However, revenue growth has slowed dramatically in recent years, and the firm's net profit margin has actually been declining. Ferris is convinced the firm can do better. After only several weeks at the helm, she has already identified a number of potential improvements to drive the firm's future growth.
TABLE-1: Idexo's 2010 Income Statement and Balance Sheet

Operational Improvements. On the operational side, Ferris is quite optimistic regarding the company's prospects. The market is expected to grow by 6% per year, and Idexo produces a superior product. Idexo's market share has not grown in recent years, because prior management devoted insufficient resources to product development, sales, and marketing. At the same time, Idexo has overspent on administrative costs. Indeed, as shown in Table 1, Idexo's current administrative expenses are $18 million/ $100 million = 18% of sales, which exceeds its expenditures on sales and marketing (15% of sales).
Competitors spend less on administrative overhead than on sales and marketing. Ferris plans to cut administrative costs immediately to 15% of sales and redirect resources to new product development, sales, and marketing. By doing so, she believes Idexo can increase its market share from 10% to 14% over the next four years. Using the existing production lines, the increased sales demand can be met in the short run by increasing overtime and running some weekend shifts. The resulting increase in labour costs, however, is likely to lead to a decline in the firm's gross margin to 53%. Table 2 shows sales and operating-cost projections for the next five years based on this plan, including the reallocation of resources from administration to sales and marketing over the five-year period, and an increase in Idexo's average selling price at a 2% inflation rate each year.
TABLE-2: Idexo's Sales and Operating-Cost Projections

Transcribed Image Text:

1 Year 2010 2011 2012 2013 2014 2015 2 Income Statement (S000s) 3 Sales 4 Cost of Goods Sold 100,000 -45333 54,667 一15,000 -18,000 21,667 -6667 15,000 -1021 13,979 -4893 9086 1 5 Gross Profit 6 Sales and Marketing 7 Administration 8 EBITDA 9 Depreciation 10 EBI 11 Interest Expense (net 12 Pre-tax Income 13 Income Tax 14 Net Income 2 1 Year 2010 2011 2012201320142015 2 Working Capital ($000s) 3 Assets 4 Accounts Receivable 5 Inventories 6 Minimum Cash Balance 20,000 8219 8219 36,438 7 Total Current Assets 8 Liabilities 9 Accounts Payable 6205 30,233 10 11 Net Working Capital Increase in Net Working Capital 1 Year 2 Free Cash Flow (S000s) 3 Net Income 4 Plus: After-Tax Interest Expense 5 Unlevered Net Income 6 Plus: Depreciation 7 Less: Increases in NWC 8 Less: Capital Expenditures 9 Free Cash Flow of Firm 10 Plus: Net Borrowing 11 Less: After-Tax Interest Expense 12 Free Cash Flow to Equity 2010 2011 2012 2013 2014 2015 1 Year 2010 2011 2012 2013 2014 2015 2 Balance Sheet ($000s) 3 Assets 4 Cash and Cash Equivalents 5 Accounts Receivable 6 Inventories 7 Total Current Assets 8 9 Property, Plant, and Equipment Goodwill 10 Total Assets 11 12 13 14 Liabilities and Stockholders' Equity Accounts Payable Debt Total Liabilities 15 16 17 18 19 Stockholders' Equity Starting Stockholders' Equity Net Income Dividends Capital Contributions Stockholders' Equity Total Liabilities and Equity 20 21 1 Year 1 Year 2 Balance Sheet ($000s) 3 Assets 4 Cash and Cash Equivalents 2010 2010 2 Income Statement ($000s) 3 Sales 4 Cost of Goods Sold 5 Raw Materials 6 Direct Labour Costs 7 Gross Profit 100,000 -21,333 5 Accounts Receivable -24.000 15,000 20,000 8219 6 Inventories 54,667 7 Total Current Assets 43,219 60,000 8 Sales and Marketing 9 Administration -15.000 -18,000 8 9 Property, Plant, and Equipment Goodwill 103,219 10 EBITDA 11 Depreciation 12 EBIT 13 Interest Expense (net) 14 Pre-tax Income 15 Income Tax 21,66710 Total Assets 6667 11 Liabilities and Stockholders' Equity 15,000 12 Accounts Payable -1021 13 Debt 13,979 14 Total Liabilities 6205 20,000 26,205 77,014 103,219 4893 15 908616 Stockholders' Equity Total Liabilities and Equity 16 Net Income 1 Year 2 Sales Data 3 Market Size (000s units) 6.0% 20,000 21,200 22,472 23,820 25,250 26,765 4 Market Share 5 Average Sales Price (S/unit) 2.00% 50.00 51.00 52.02 53.06 54.12 55.20 2010 20112012 2013 2014 2015 *1.0% 10.0% 11.0% 12.0% 13.0% 14.0% 14.0% 7 8 9 10 11 Operating Expense and Tax Data Gross Margin Sales and Marketing (% sales) Administration(% sales) Tax Rate 54.7% 15.0% 18.0% 35.0% 53.0% 16.5% 15.0% 35.0% 53.0% 18.0% 15.0% 35.0% 53.0% 19.5% 14.0% 35.0% 53.0% 20.0% 13.0% 35.0% 53.0% 20.0% 13.0% 35.0%