Can you please help me fill out the spreadsheet?

Idexo Corporation is a privately held designer and manufacturer of licensed college apparel in Cincinnati, Ohio. In late 2020, after several years of lackluster performance, the firms 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 firms financial plan for the next five years.

In 2020, Idexo had total assets of about $103 million and annual sales of $100 million (see Table 1 below). The firm was profitable, with expected 2020 earnings of more than $9 million, for a net profit margin of 9.1%. However, revenue growth has slowed dramatically in recent years and the firms 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 firms future growth.

On the operational side, Ferris is quite optimistic regarding the companys prospects. The market is expected to grow by 6% per year, and Idexo produces a superior product. Idexos 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, Idexos 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 firms gross margin to 53%. Table 2 below 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 Idexos average selling price at a 2% inflation rate each year.

Table 3 shows the forecast for Idexos capital expenditures over the next five years. Based on the estimates for capital expenditures and depreciation, this spreadsheet tracks the book value of Idexos plant, property, and equipment starting from its level at the end of 2020. Note that investment is expected to remain relatively low over the next two yearsslightly below depreciation. Idexo will expand production during this period by using its existing plant more efficiently.

However, once Idexos 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 2023. Therefore, Table 3 budgets for a major expansion of the plant at that time, leading to a large increase in capital expenditures in 2023 and 2024.

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, which has resulted in an elevated accounts receivables days. The accounts receivable days 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 Idexos 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 firms shareholders as dividends.

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 2023 and 2024 associated with the expansion of its manufacturing plant, as shown in Table 4. You expect that rates on these future loans will be about 6.8% as well.

Question:

Based on the forecasts in this case, use the provided Excel spreadsheet, tab Prob 8 and Part a within it to construct:

1. A pro forma income statement for Idexo over the next five years. (5pts)

Calculate the annual (year over year) growth rate of the firms sales (YOY Sales Growth) and net income (YOY NI Growth) for each year 2021-2025

1. At the end of 2020, soon after Ferriss return as CEO, the firm receives an unsolicited offer of $240 million for the entire company. Suppose Ferris believes that Idexo can be sold at that time for an enterprise value equal to nine times its final (horizon) EBITDA. Idexos unlevered cost of capital is 11% (specifically, 11% is the pretax WACC). Based on your forecast of Idexos free cash flow in 20212025 in the questions above, estimate the following: Enter all of your answers on the provided Excel sheet, Part d, and show all calculations used to determine your answers:

1. What is the continuation value for Idexo? (2pts)

2. What is Idexos firm value at the end of 2020? Construct a spreadsheet, listing a row for every item included in this calculation in the blank area on Part d of the Excel sheet. (5pts)

3. Based on your analysis, should Ferris accept the unsolicited offer and sell the company? Why or why not? Type your answer below. (2pts)

Problem 8: Case Study Year 2020 Year 2020 Sales 100,000 15,000 20,000 8,219 43,219 60,000 Financial Statements (Table 1) INCOME STATEMENT (5000s) 1 2 Cost of Goods Sold 3 Raw Materials 4 Direct Labor Costs 5 Gross Profit 6 Sales & Marketing 7 Administration 8 EBITDA 9 Depreciation 10 EBIT 11 Interest Expense 12 Pretax Income 13 Income Tax 14 Net Income 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 & Stockholders' Equity 8 Accounts Payable 9 Debt 10 Total Liabilities 11 Stockholders' Equity 12 Total Liabilities & Equity (21,333) (24,000) 54,667.00 (15,000) (18,000) 21,667 (6,667) 15,000 (1,021) 13,979 (4,892.77) 9,087 103,219 6,205 20,000 26,205 77,014 103,219 Year 2020 2021 2022 2023 2024 2025 Operating Projections (Table 2) Sales Data 1 Market Size (000 units) 2 Market Share 3 Ave. Sales Price (S/unit) Growth/YT 6.0% 1.0% 2.00% 20,000.00 10.0% 50.00 21,200 11.0% 51.00 22,472 12.0% 52.02 23,820 13.0% 53.06 25,250 14.0% 54.12 26,765 14.0% 55.20 53.0% 53.0% Operating Expense & Tax Data 1 Gross Margin 2 Sales & Marketing (% sales) 3 Administration (% sales) 4 Tax Rate 16.5% 20.0% 54.7% 15.0% 18.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% 15.0% 35.0% 13.0% 35.0% Year 2020 2021 2022 2023 2024 2025 Capital Expenditure Projections (Table 3) Fixed Assets and Capital Investment ($000s) 1 Opening Book Value 2 Capital Investment 3 Depreciation 4 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,342 77,342 8,000 (8,534) 76,807 Year 2020 2021 2022 2023 2024 2025 Debt Forecast (Table 4) Debt Table ($000s) 1 Outstanding Debt 20.000 20,000 20,000 35,000 40,000 40,000 Year 2020 2021 2022 2023 2024 2025 Parta INCOME STATEMENT (S000s) 1 Sales 2 Cost of Goods Sold 3 Gross Profit 4 Sales & Marketing 5 Administration 6 EBITDA 7 Depreciation EBIT 9 Interest Expense 10 Pretax Income 11 Income Tax 12 Net Income 13 YOY Sales Growth 12 YOY NI Growth 100.000 (45,333) 54,667 (15,000) (18,000) 21,667 (6,667) 15,000 (1,021) 13,979 (4,892.77) 9,087 Year 2020 2021 2022 2023 2024 2025 14,663 17,295 20,257 23,587 25,502 Part b Working Capital ($000s) Assets 1 Accounts Receivable 2 Inventories 3 Minimum Cash Balance 4 Total Current Assets Liabilities 5 Accounts Payable 20,000 8.219 8,219 36,438 9.775 11,530 13,505 15,725 17,001 6.205 Calculate Net Working Capital Increase in Net Working Capital Inventory Days Accounts Reeivable Days Accounts Payable Days Year 2021 2022 2023 2024 2025 Parte Free Cash Flow (S000s) Year 2021 2022 2023 2024 2025 Part d Firm Value (5000) EBITDA Continuation Value Firm Value Year 2021 2022 2023 2024 2025 Parte Free Cash Flow to Equity (5000s) Problem 8: Case Study Year 2020 Year 2020 Sales 100,000 15,000 20,000 8,219 43,219 60,000 Financial Statements (Table 1) INCOME STATEMENT (5000s) 1 2 Cost of Goods Sold 3 Raw Materials 4 Direct Labor Costs 5 Gross Profit 6 Sales & Marketing 7 Administration 8 EBITDA 9 Depreciation 10 EBIT 11 Interest Expense 12 Pretax Income 13 Income Tax 14 Net Income 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 & Stockholders' Equity 8 Accounts Payable 9 Debt 10 Total Liabilities 11 Stockholders' Equity 12 Total Liabilities & Equity (21,333) (24,000) 54,667.00 (15,000) (18,000) 21,667 (6,667) 15,000 (1,021) 13,979 (4,892.77) 9,087 103,219 6,205 20,000 26,205 77,014 103,219 Year 2020 2021 2022 2023 2024 2025 Operating Projections (Table 2) Sales Data 1 Market Size (000 units) 2 Market Share 3 Ave. Sales Price (S/unit) Growth/YT 6.0% 1.0% 2.00% 20,000.00 10.0% 50.00 21,200 11.0% 51.00 22,472 12.0% 52.02 23,820 13.0% 53.06 25,250 14.0% 54.12 26,765 14.0% 55.20 53.0% 53.0% Operating Expense & Tax Data 1 Gross Margin 2 Sales & Marketing (% sales) 3 Administration (% sales) 4 Tax Rate 16.5% 20.0% 54.7% 15.0% 18.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% 15.0% 35.0% 13.0% 35.0% Year 2020 2021 2022 2023 2024 2025 Capital Expenditure Projections (Table 3) Fixed Assets and Capital Investment ($000s) 1 Opening Book Value 2 Capital Investment 3 Depreciation 4 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,342 77,342 8,000 (8,534) 76,807 Year 2020 2021 2022 2023 2024 2025 Debt Forecast (Table 4) Debt Table ($000s) 1 Outstanding Debt 20.000 20,000 20,000 35,000 40,000 40,000 Year 2020 2021 2022 2023 2024 2025 Parta INCOME STATEMENT (S000s) 1 Sales 2 Cost of Goods Sold 3 Gross Profit 4 Sales & Marketing 5 Administration 6 EBITDA 7 Depreciation EBIT 9 Interest Expense 10 Pretax Income 11 Income Tax 12 Net Income 13 YOY Sales Growth 12 YOY NI Growth 100.000 (45,333) 54,667 (15,000) (18,000) 21,667 (6,667) 15,000 (1,021) 13,979 (4,892.77) 9,087 Year 2020 2021 2022 2023 2024 2025 14,663 17,295 20,257 23,587 25,502 Part b Working Capital ($000s) Assets 1 Accounts Receivable 2 Inventories 3 Minimum Cash Balance 4 Total Current Assets Liabilities 5 Accounts Payable 20,000 8.219 8,219 36,438 9.775 11,530 13,505 15,725 17,001 6.205 Calculate Net Working Capital Increase in Net Working Capital Inventory Days Accounts Reeivable Days Accounts Payable Days Year 2021 2022 2023 2024 2025 Parte Free Cash Flow (S000s) Year 2021 2022 2023 2024 2025 Part d Firm Value (5000) EBITDA Continuation Value Firm Value Year 2021 2022 2023 2024 2025 Parte Free Cash Flow to Equity (5000s)