View the step-by-step solution to:

BREAK-EVEN ANALYSIS Based On NPV (in thousands of $) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Key Assumptions Sales Growth Rate Change...

Suppose a firm is considering the following project where all of the dollar figures are in thousands of dollars. In year 0, the project requires $24,490 investment in plant and equipment, is depreciated using the straight-line method over seven years and there is a salvage value of $5,800 in year 7. The project is forecast to generate sales of 4,800 units in year 1 and grow at a sales growth rate of 72.0% in year 2. The sales growth rate is forecast to decline by 12.0% in year 3, to decline by 15.0% in year 4, to decline by 18.0% in year 5, to decline by 23.0% in year 6, to decline by 29.0% in year 7. Unit sales will drop to zero in year 8.

The inflation rate is forecast to be 2.7% in year 1 and rising to 3.5% in year 7. The real cost of capital is forecast to be 10.2% in year 1, rising to 11.9% in year 7. The tax rate is forecast to be a constant 38.0%. Sales revenue per unit is forecast to be $12.20 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $7.30 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $6,740 in year 1 and then grow with inflation. What is the project NPV? What is the NPV Break-Even Point in Year 1 Unit Sales, where NPV equals zero? What is the NPV Break-Even Point in the Year 2 Sales Growth Rate, where NPV equals zero? What is the NPV Break-Even Contour in the two-dimensional space of Year 1 Unit Sales and Year 2 Sales Growth Rate?
Based On NPV (in thousands of $) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Key Assumptions Sales Growth Rate 72.0% 60.0% 45.0% 27.0% 4.0% -25.0% Change in Sales Growth Rate -12.0% -15.0% -18.0% -23.0% -29.0% Inflation Rate 2.7% 2.8% 2.9% 3.0% 3.2% 3.4% 3.5% Real Cost of Capital 10.2% 10.5% 10.8% 11.1% 11.4% 11.7% 11.9% Tax Rate 38.0% 38.0% 38.0% 38.0% 38.0% 38.0% 38.0% Discounting Discount Rate = Cost of Capital 13.2% 13.6% 14.0% 14.4% 15.0% 15.5% 15.8% Cumulative Discount Factor 0.0% 13.2% 28.6% 46.6% 67.7% 92.8% 122.7% 157.9% Price or Cost / Unit Unit Sales 4,800 8256 13210 19154 24325 25298 18974 Sales Revenue / Unit $12.20 $12.54 $12.91 $13.29 $13.72 $14.18 $14.68 Variable Cost / Unit $7.30 $7.50 $7.72 $7.95 $8.21 $8.49 $8.78 Cash Fixed Costs $6,740 $6,929 $7,130 $7,344 $7,579 $7,836 $8,110 Cash Flow Forecasts Sales Revenue $58,560 $103,543 $170,474 $254,603 $333,693 $358,840 $278,549 Variable Costs $35,040 $61,956 $102,005 $152,344 $199,669 $214,716 $166,673 Gross Margin $23,520 $41,587 $68,469 $102,259 $134,024 $144,124 $111,876 Cash Fixed Costs $6,740 $6,929 $7,130 $7,344 $7,579 $7,836 $8,110 Depreciation $2,670 $2,670 $2,670 $2,670 $2,670 $2,670 $2,670 Total Fixed Costs $9,410 $9,599 $9,800 $10,014 $10,249 $10,506 $10,780 Operating Profit $14,110 $31,988 $58,669 $92,245 $123,776 $133,618 $101,096 Taxes $5,362 $12,156 $22,294 $35,053 $47,035 $50,775 $38,416 Net Profit $8,748 $19,833 $36,375 $57,192 $76,741 $82,843 $62,679 Add Back Depreciation $2,670 $2,670 $2,670 $2,670 $2,670 $2,670 $2,670 Operating Cash Flow $11,418 $22,503 $39,045 $59,862 $79,411 $85,513 $65,349 Investment in Plant & Equip ($24,490) $5,800 Cash Flows ($24,490) $11,418 $22,503 $39,045 $59,862 $79,411 $85,513 $71,149 Present Value of Each Cash Flow ($24,490) $10,089 $17,504 $26,638 $35,689 $41,181 $38,396 $27,583 Net Present Value $172,590 By trial and error the Solver adjusts the value of the Year 1 Unit Sales in cell C16 until the Net P This results in a NPV Break-Even Point in Year 1 Unit Sales of 851. By trial and error the Solver adjusts the value of the Sales Growth Rate in cell D5 until the Net P Data Table: Sensitivity of Net Present Value to Year 1 Unit Sales and Year 2 Sales Growth Rate Output Formula: Input Values for Year 1 Unit Sales Net Present Value $172,590 3,200 4,000 4,800 5,600 52.0% $49,149 $70,740 $92,331 $113,923 Input Values for Year 2 62.0% $72,436 $99,849 $127,261 $154,674 Sales Growth Rate 72.0% $102,655 $137,623 $172,590 $207,558 82.0% $141,643 $186,357 $231,072 $275,786 92.0% $191,610 $248,816 $306,023 $363,229 B REAK- E VEN A NALYSIS in Sales Growth Rate of -9.6% 52.0% 62.0% 72.0% 82.0% 92.0% $0 $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $350,000 $400,000 3,200 4,000 4,800 5,600 NPV Break-Even Contour (Based On NPV = 0) Across Year 1 Unit Sales And Year 2 Sales Growth Rate Year 2 Sales Growth Rate Net Present Value Year 1 Unit Sales NPV Break-Even Contour
Background image of page 1
Sign up to view the entire interaction

Top Answer

Dear student! I have reviewed the... View the full answer

8442523.xlsx

BREAK-EVEN ANALYSIS Based On NPV (in thousands of $)
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Key Assumptions
Sales Growth Rate
Change in Sales Growth Rate
Inflation Rate
Real Cost...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online