Can anyone answer this question using 4 tables ??

Problem Integrative Case This case draws on material from Chapters 18-20. See MyFinanceLab for the data in Tables 1, 2, 3, and 4 and the spreadsheet referenced in the case questions. Idexo Corporation is a privately held designer and manufacturer of licensed college apparel in Cincinnati, Ohio. In late 2016, after several years of lackluster performance, 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 possible IPO. She has hired you to assist with developing the firm's financial plan for the next five years. In 2016, Idexo had total assets of about $103 million and annual sales of $100 million (see Table 1). The firm was profitable, with expected 2016 earnings of more than $9 million, for a net profit margin of 9.1%.1 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 2016 Income Statement and Balance Sheet 2016 2016 100,000 15,000 20,000 8.219 43.219 60,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 EBITDA 11 Depreciation 12 EBIT 13 Interest Expense (net) 14 Pretax Income 15 Income Tax 16 Net Income 1 Year 2 Balance Sheet (5000s) 3 Assets 4 Cash and Cash Equivalents 5 Accounts Receivable 6 Inventories 7 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 -21,333 - 24.000 54,667 -- 15.000 -18,000 21,667 -6.667 15,000 -1,021 13.979 -4,893 9.086 103.219 6,205 20,000 26,205 77,014 103,219 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, from 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 labor 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 2018 - 2019 2020 2021 22.472 12.0% 52.02 23,820 25,250 26,765 13.0% 14.0% 14.0% 53.06 54.12 55.20 1 Year 2016 2017 2 Sales Data Growth Yr 3 Market Size (000s units) 6.0% 20.000 21.200 4 Market Share *1.0% 10.0% 11.0% 5 Average Sales Price (S/unit) 2.00% 50.00 51.00 6 7 Operating Expense and Tax Data 8 Gross Margin 54.7% 53.0% 9 Sales and Marketing (% sales) 15.0% 16.5% 10 Administration (% sales) 18.0% 15.0% 11 Tax Rate 35.0% 35.0% "Market Share growth is expected through 2020 only. 53.0% 18.0% 15.0% 35.0% 53.0% 19.5% 14.0% 35.0% 53.0% 53.0% 20.0% 20.0% 13.0% 13.0% 35.0% 35.0% Expansion Plans Table 3 shows the forecast for Idexo's capital expenditures over the next five years. Based on the estimates for capital expenditures and depreciation, this spreadsheet tracks the book value of Idexo's plant, property, and equipment starting from its level at the end of 2016. Note that investment is expected to remain relatively low over the next two years-slightly below depreciation. Idexo will expand production during this period by using its existing plant more efficiently. TABLE 3 Idexo's Capital Expenditure Forecast 2016 2017 2018 2019 2020 2021 1 Year 2 Fixed Assets and Capital Investment (5000s) 3 Opening Book Value 4 Capital Investment 5 Depreciation 6 Closing Book Value 60.167 60.000 58,500 57,150 73,935 77.341 6,500 5,000 5,000 25,000 12,000 8,000 -6,667 -6,500 -6,350 -8,215 -8,594 -8,534 60.000 58.500 57.150 73,935 77,341 76.807 However, once ldexo's volume grows by more than 50% over its current level, the firm will need to undertake a major expansion to increase its manufacturing capacity. Based on the projections in Table 2, sales growth exceeds 50% of current sales in 2019. Therefore, Table 3 budgets for a major expansion of the plant at that time, leading to a large increase in capital expenditures in 2019 and 2020. Working Capital Management To compensate for its weak sales and marketing efforts, Idexo has sought to maintain the loyalty of its retailers, in part through a very lax credit policy. This policy affects Idexo's working capital requirements: For every extra day that customers take to pay, another day's sales revenue is added to accounts receivable (rather than received in cash). From Idexo's current income and balance sheet (Table 1), we can estimate the number of days of receivables as: Accounts Receivable Days = Accounts Receivable ($) Sales Revenue ($/yr) -X 365 Days/Yr = 20 Million/100 Million * 365 = 73 Days The standard for the industry is 45 days. Ferris believes that Idexo can tighten its credit policy to achieve this goal without sacrificing sales. Ferris does not foresee any other significant improvements in Idexo's working capital management, and expects inventories and accounts payable to increase proportionately with sales growth. The firm will also need to maintain a minimum cash balance equal to 30 days' sales revenue to meet its liquidity needs. It earns no interest on this minimal balance, and Ferris plans to pay out all excess cash each year to the firm's shareholders as dividends. Capital Structure Changes: Levering Up Idexo currently has $20 million in debt outstanding with an interest rate of 6.8%, and it will pay interest only on this debt during the next five years. The firm will also obtain additional financing at the end of years 2019 and 2020 associated with the expansion of its manufacturing plant, as shown in Table 4. While Idexo's credit quality will likely improve by that time, interest rates may also increase somewhat. You expect that rates on these future loans will be about 6.8% as well. Given Idexo's outstanding debt, its interest expense each year is computed as: Interest in Year t = Interest Rate