Question

QUESTION: XYZ, Inc. is considering a 5-year project. The production will require $1,500,000 in net working capital to start and additional net working capital investments each year equal to 15% of the projected sales increase for the following year. Total fixed costs are $1,350,000 per year, variable production costs are $225 per unit, and the units are priced at $345 each. The equipment needed to begin production has an installation cost of $23,000,000. The equipment is qualified as seven-year MACRS property. MACRS stands for Modified Accelerated Cost Recovery System, where businesses apply MACRS rates to the capital expenditure for annual depreciation amount. In five years, this equipment can be sold for about $4,600,000. The company is in the 35% marginal tax bracket and has a required rate of return on all its projects of 18%.   Year projected unit sales are YR 1 = 80,000, YR2 = 85,000, YR 3 = 90,000, YR 4 = 95,000, YR 5 = 95,000 YR 6-8 = 0. MACRS Rate: YR 1 = 14.29%, YR2 = 24.49%, YR 3 = 17.49%, YR 4 = 12.49%, YR 5 = 8.93% YR 6 = 8.92%, YR 7 = 8.93% and YR 8 = 4.46%.

PART A: Complete a cash flow estimation table.   What are the projected cash flows for each year?

Required columns in excel:

SALES

VARIABLE COSTS

FIXED COSTS

DEPRECIATION

EBIT

TAXES

NET INCOME

(+) DEPRECIATION

OPERATING CASH FLOW

CHANGE IN NWC

CAPEX

SALVAGE VALUE

TOTAL CASH FLOWS

PART B: What is the NPV and IRR for this project (EXCEL FORMULA)? Shall the project be accepted? Why or why not?

PART C: The projected unit sales presented above is the most likely case. Please conduct a scenario analysis for the worst and best case scenarios. It's your understanding that the unit sales will be 10% less (more) for the worst (best) case scenario compares to the most likely case. What is the NPV and IRR for the best case, most likely, and worst case? (EXCEL FORMULA)

PART D: Based on the results from the scenario analysis, what is your recommendation on the project. Explain your answer.

PART E: Based on the most likely scenario, what is the breakeven variable cost? Please analyze your results. (EXCEL FORMULA)

Answer 1

PART A and PART B :

Operating cash flow (OCF) each year = income after tax + depreciation - change in NWC

In year 5, the entire NWC, and hence the change in NWC is negative

loss on sale of equipment at end of year 5 = book value - sale price

book value = original cost - accumulated depreciation

after-tax salvage value = salvage value + tax benefit on loss on sale of equipment (the loss is tax deductible, and hence reduces the tax outgo. This is treated as a cash inflow)

NPV and IRR are calculated using NPV and IRR functions in Excel

NPV is -$1,586,368

IRR is 15.90%

The project should not be accepted because the NPV is negative and the IRR is lower than the required return.

PART C

Worst case

NPV is -$3,469,329

IRR is 13.28%

Best case

NPV is $296,592

IRR is 18.38%

PART D

It is recommended to accept and proceed with the project only if the best case scenario occurs because the NPV is positive only in the best case scenario. In the case of the most likely and worst case scenarios, it is recommended not to proceed with the project as the NPV is negative.

PART E

The breakeven variable cost is where the NPV is zero.

The variable cost where the NPV is zero is calculated using GoalSeek in Excel.

The variable cost is $216.10