Chat with us, powered by LiveChat finance question | Abc Paper
+1(978)310-4246 credencewriters@gmail.com
  

need someone to look at the documents and supporting materials and answer the questions in the excel sheet “solution format” I have attached instructions sheet doc, Solution format, and the data necessary to answer the questions.
I need all questions 1-5 answered with the excel table filled outs

Final Project

Your Numbers

Sales in 2019 120000

Market Share in 2019 14%

TABLE 2
SPREADSHEET Idexo’s Income Statement and Balance Sheet

1 Year 2019 1 Year 2019

2 Income Statement ($000s) 2 Balance Sheet ($000s)

3 Sales 120,000 3 Assets

4 Cost of Goods Sold 4 Cash and Cash Equivalents 15,000

5 Raw Materials -21,333 5 Accounts Receivable 20,000

6 Direct Labor Costs -24,000 6 Inventories 8,219

7 Gross Profit 74,667 7 Total Current Assets 43,219

8 Sales and Marketing -15,000 8 Property, Plant, and Equipment 60,000

9 Administration -18,000 9 Goodwill 1000

10 EBITA 41,667 10 Total Assets 104,219

11 Depreciation -6,667 11 Liabilities and Stockholders’ Equity

12 EBIT 35,000 12 Accounts Payable 6,205

13 Interest Expense (net) -1,021 13 Debt 20,000

14 Pretax Income 33,979 14 Total Liabilities 26,205

15 Income Tax -4,893 15 Stockholders’ Equity 78,014

16 Net Income 29,086 16 Total Liabilities and Equity 104,219

TABLE 2
SPREADSHEET Idexo’s Sales and Operating Cost Projections

1 Year 2019 2020 2021 2022 2023 2024

2 Sales Data Growth/Yr

3 Market Size (000s units) 6.0% 20,000 21,200 22,472 23,820 25,250 26,765

4 Market Share* 1.0% 14.0% 15.0% 16.0% 17.0% 18.0% 18.0%

5 Average Sales Price ($/unit) 2.0% 42.86 43.71 44.59 45.48 46.39 47.32

6

7 Operating Expense and Tax Data

8 Gross Margin 62.2% 53.0% 53.0% 53.0% 53.0% 53.0%

9 Sales and Marketing (% sales) 12.5% 16.5% 18.0% 19.5% 20.0% 20.0%

10 Administration (% sales) 15.0% 15.0% 15.0% 14.0% 13.0% 13.0%

11 Tax Rate 14.4% 35.0% 35.0% 35.0% 35.0% 35.0%

* Market Share growth is expected through 2017 only. Growth is shown in absolute terms.

TABLE 3
SPREADSHEET Idexo’s Capital Expenditure Forecast

1 Year 2019 2020 2021 2022 2023 2024

2 Fixed Assets and Capital Investment ($000s)

3 Opening Book Value 60,167 60,000 58,500 57,150 73,935 77,341

4 Capital Investment 6,500 5,000 5,000 25,000 12,000 8,000

5 Depreciation -6,667 -6,500 -6,350 -8,215 -8,594 -8,534

6 Closing Book Value 60,000 58,500 57,150 73,935 77,341 76,807

TABLE 4
SPREADSHEET Idexo’s Debt and Interest Forecast

1 Year 2019 2020 2021 2022 2023 2024

2 Debt and Interest Table ($000s)

3 Outstanding Debt 20,000 20,000 20,000 35,000 40,000 40,000

4 Interest on Term Loan 7.0% -1,400 -1,400 -1,400 -2,450 -2,800

Year 2019

Working Capital ($000s)

Assets

1 Accounts Receivable 20,000

2 Raw Materials 2,630

3 Finished Goods 5,589

2 Inventories 8,219

3 Minimum Cash Balance 8,219

4 Total Current Assets 36,438

Liabilities

5 Wages Payable 1,726

6 Other Accounts Payable 4,479

5 Accounts Payable 6,205

Net Working Capital 30,233

Berk/DeMarzo, Corporate Finance
&A

&P

FINAL PROJECT

Instructions:

1. The data for this final project can be found in the excel file “Final Projet Data.xls”
2. Your analyses should be done in Excel. Submit your final project on D2L.

Idexo Corporation is a privately held designer and manufacturer of licensed college apparel in Cincinnati, Ohio. In late 2019, 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.
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.

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% 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.

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 2019. 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.

However, once Idexo’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.

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). 
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 7%, 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 7% as well.

Questions:

1. Based on the forecasts in this case, use the following spreadsheet 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 following spreadsheet to project Idexo’s working capital needs over the next five years.

3. Based on the forecasts you have already developed, use the following spreadsheet to project Idexo’s free cash flow for 2020–2024. Will the firm’s free cash flow steadily increase over this period? Why or why not?

4. 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 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.
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 2019, 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 2019. Suppose Ferris believes that Idexo can be sold at the end of 2022 for an enterprise value equal to nine times its final EBITDA. Idexo’s unlevered cost of capital is 10% (specifically, 10% is the pretax WACC). Based on your forecast of Idexo’s free cash flow in 2020–2024 in Question 3, and its final enterprise value in 2024, estimate the following:
a. Idexo’s unlevered value at the end of 2019.
b. The present value of Idexo’s interest tax shields in 2019–2024. (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 2019. (Add the present value of the interest tax shield in (b) to the unlevered value of the firm in (a).)
d. Idexo’s equity value today. (Adjust the enterprise value in (c) to reflect the firm’s debt and excess cash at the end of 2019.)
e. Based on your analysis, should Ferris sell the company now?

Q1

FIRM DATA

Year 2019 2020 2021 2022 2023 2024

Sales Data Growth/Yr

1 Market Size (000 units) 6.0% 20,000.00 21,200 22,472 23,820 25,250 26,765

2 Market Share 1.0% 10.0% 11.0% 12.0% 13.0% 14.0% 14.0%

3 Ave. Sales Price ($/unit) 2.00% 50.00 51.00 52.02 53.06 54.12 55.20

4 Sales Revenue ($000s) 100,000 118,932 140,279 164,309 191,316 206,851

Operating Expense & Tax Data

5 Gross Margin 54.7% 53.0% 53.0% 53.0% 53.0% 53.0%

6 Sales & Marketing (% sales) 15.0% 16.5% 18.0% 19.5% 20.0% 20.0%

7 Administration (% sales) 18.0% 15.0% 15.0% 14.0% 13.0% 13.0%

8 Tax Rate 35.0% 35.0% 35.0% 35.0% 35.0% 35.0%

Year 2019 2020 2021 2022 2023 2024

Fixed Assets and Capital Investment ($000s)

1 Opening Book Value 60,167 60,000 58,500 57,150 73,935 77,342

2 Capital Investment 6,500 5,000 5,000 25,000 12,000 8,000

3 Depreciation (6,667) (6,500) (6,350) (8,215) (8,594) (8,534)

4 Closing Book Value 60,000 58,500 57,150 73,935 77,342 76,807

FINANCING ASSUMPTIONS

Year 2019 2020 2021 2022 2023 2024

Debt & Interest Table ($000s)

1 Outstanding Debt 20,000 20,000 20,000 35,000 40,000 40,000

2 Interest on Term Loan 6.80% (1,360.00) (1,360.00) (1,360.00) (2,380.00) (2,720.00)

INITIAL FINANCIAL STATEMENTS >> <2019

Working Capital Days

Assets Based On: Days Days

1 Accounts Receivable Sales Revenue

2 Raw Materials Raw Materials Costs

3 Finished Goods Raw Materials + Labor Costs

4 Minimum Cash Balance Sales Revenue

Liabilities

5 Wages Payable Direct Labor + Admin Costs

6 Other Accounts Payable Raw Materials + Sales & Marketing

Year 2019 2020 2021 2022 2023 2024

Working Capital ($000s)

Assets

1 Accounts Receivable

2 Raw Materials

3 Finished Goods

2 Inventories

3 Minimum Cash Balance

4 Total Current Assets

Liabilities

5 Wages Payable – 0

6 Other Accounts Payable

5 Accounts Payable

Net Working Capital

6 Increase in Net Working Capital

Q3

FREE CASH FLOW

Year 2019 2020 2021 2022 2023 2024

Free Cash Flow ($000s)

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

Year 2019 2020 2021 2022 2023 2024

Free Cash Flow ($000s)

EBIT

Taxes

Unlevered Net Income

Plus: Depreciation

Less: Increases in NWC

Less: Capital Expenditures

Free Cash Flow of Firm

Plus: Net Borrowing

Less: After-Tax Interest Expense

Free Cash Flow to Equity

Q4

PRO FORMA FINANCIALS: BALANCE SHEET

Year 2019 2020 2021 2022 2023 2024

BALANCE SHEET ($000s)

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

Peter DeMarzo:
Adjusts for any initial change in cash and/or debt

Q5

COST OF CAPITAL

Unlevered cost of capital

Cost of Debt

CONTINUATION VALUE

Continuation Value: Multiples Approach ($000s)

1 E+BITDA in 0 Common Multiples

2 EBITDA multiple EV/Sales

3 Cont. Enterprise Value P/E (levered)

4 Debt P/E (unlevered)

5 Cont. Equity Value

Continuation Value Estimate ($ millions)

E+BITDA in 0 – WACC Method

EBITDA multiple .0x Continuing D/(E+D) 0.0%

Cont. Enterprise Value – Continuing WACC 0.00%

Debt – Unlevered Net Income –

Cont. Equity Value – Working Capital –

Value/Sales .0x Property, Plant, Equipment –

P/E (levered) .0x

P/E (unlevered) .0x Implied Growth Rate 0.00%

Continuation Value: DCF and EBITDA Multiple ($000s)

1 Long-term growth rate

2 Target D/(E+D)

3 Projected WACC

Free Cash Flow in 2011

4 Unlevered Net Income Cont. Enterprise Value

5 Less: Inc. in NWC

6 Less: Inc. in Fixed Assets Implied EBITDA Multiple

7 Free Cash Flow

VALUATION

Year 0 1 2 3 4 5

APV Method ($ millions)

1 Free Cash Flow

2 Unlevered Value Vu

3 Interest Tax Shield

4 Tax Shield Value Ts

5 APV: VL = Vu + Ts

6 Debt

7 Excess Cash

8 Equity Value

error: Content is protected !!