Question

please write all formulas and show your work without using excel

Using the information in Question 1, what is the break-even market size for the project? Show your work, and round to the nearest unit.

Refer to question 1:

1. Consider a project with annual expected income based on approximately $125 million in revenue and approximately $77.5 million in total variable cost. You realize that both of these numbers are projected, and that your projections may be incorrect. Your boss wants you to conduct a sensitivity analysis to determine whether the project is a winner. You are entering an established market, and you know the market size will be 1,000,000 units. You are unsure of your exact market share, the price you will be able to charge, and your variable cost per unit, but have determined a range of possible values for each (in the table below). Your initial investment cost is $165 million, but you already own the plant and equipment, which are fully depreciated. Your fixed costs will be $2 million per year. There are no new NWC requirements, and there will be no salvage value because the project does not have a finite lifetime. The tax rate is 35%. The discount rate is 17%. Use the following table to conduct a full sensitivity analysis for the project. Make sure to include the NPV for the expected outcome in your analysis. Also calculate the best- and worst-case scenarios, but assume that cash flows will increase at 0.5% per year over the life of the project for these two scenarios. What are the results of this analysis?

	Pessimistic	Expected	Optimistic
Market Share	3.9%	5.0%	6.1%
Price/unit	$2410	$2500	$2590
VC/Unit	$1980	$1550	$960

Answer 1

1) => Pessimistic Scenario

Market Size = 1,000,000

Market share = 3.9%

Expected Sales in Units = 3.9% * 1,000,000 = 39,000

Price per unit = 2,410 | Total Sales = 39,000 * 2,410 = 93,990,000

Variable Cost per unit = 1,980 | Total Variable Costs = 39,000 * 1,980 = 77,220,000

Contribution Margin = Sales - Variable Costs = 93,990,000 - 77,220,000 = 16,770,000

Fixed Costs = 2,000,000

Operating Profit = Contribution Margin - Fixed Costs = 16,770,000 - 2,000,000 = 14,770,000

Since, there are no new NWC requirements and even though, the initial cost is 165 million, howver, we already own the assets which is fully depreciated. Hence, no NWC and Depreciation will be used in calculation of cashflow.

Cashflow = 14,770,000 * (1 - Tax rate) = 14,770,000 * (1 - 35%) = 9,600,500

Since project has no finite life, we will calculate the cashflow's present value as a Perpetuity

Formula of Perpetuity = CF / R

Discount rate = 17%

PV of the Cashflows = 9,600,500 / 17% = 56,473,529.41

NPV = PV of all cashflows - Initial cost = 56,473,529.41 - 165,000,000

NPV of the Project in Pessimistic Scenario = -108,526,470.59

=> Expected Scenario

Market Size = 1,000,000

Market share = 5%

Expected Sales in Units = 5% * 1,000,000 = 50,000

Price per unit = 2,500 | Total Sales = 50,000 * 2,500 = 125,000,000

Variable Cost per unit = 1,550 | Total Variable Costs = 50,000 * 1,550 = 77,500,000

Contribution Margin = Sales - Variable Costs = 125,000,000 - 77,500,000 = 47,500,000

Fixed Costs = 2,000,000

Operating Profit = Contribution Margin - Fixed Costs = 47,500,000 - 2,000,000 = 45,500,000

Since, there are no new NWC requirements and even though, the initial cost is 165 million, howver, we already own the assets which is fully depreciated. Hence, no NWC and Depreciation will be used in calculation of cashflow.

Cashflow = 45,500,000 * (1 - Tax rate) = 45,500,000 * (1 - 35%) = 29,575,000

Since project has no finite life, we will calculate the cashflow's present value as a Perpetuity

Formula of Perpetuity = CF / R

Discount rate = 17%

PV of the Cashflows = 29,575,000 / 17% = 173,970,588.24

NPV = PV of all cashflows - Initial cost = 173,970,588.24 - 165,000,000

NPV of the Project in Expected Scenario = $ 8,970,588.24

=> Optimistic Scenario

Market Size = 1,000,000

Market share = 6.1%

Expected Sales in Units = 6.1% * 1,000,000 = 61,000

Price per unit = 2,590 | Total Sales = 61,000 * 2,590 = 157,990,000

Variable Cost per unit = 960 | Total Variable Costs = 61,000 * 960 = 58,560,000

Contribution Margin = Sales - Variable Costs = 157,990,000 - 58,560,000 = 99,430,000

Fixed Costs = 2,000,000

Operating Profit = Contribution Margin - Fixed Costs = 99,430,000 - 2,000,000 = 97,430,000

Since, there are no new NWC requirements and even though, the initial cost is 165 million, howver, we already own the assets which is fully depreciated. Hence, no NWC and Depreciation will be used in calculation of cashflow.

Cashflow = 97,430,000 * (1 - Tax rate) = 97,430,000 * (1 - 35%) = 63,329,500

Since project has no finite life, we will calculate the cashflow's present value as a Perpetuity

Formula of Perpetuity = CF / R

Discount rate = 17%

PV of the Cashflows = 63,329,500 / 17% = 372,526,470.6

NPV = PV of all cashflows - Initial cost = 372,526,470.6 - 165,000,000

NPV of the Project in Optimistic Scenario = $ 207,526,470.6

Worst-Case Scenario: Pessimistic Scenario has the lowest NPV of -108,526,470.59, therefore, it is the worst case scenario.

Best-Case Scenario: Optimistic scenario has the highest NPV of 207,526,470.6, therefore, it is the best case scenario.

Using a growth rate of 0.5% for Best and Worst case scenario.

=> Best Case Scenario with 0.5% growth

Cashflow in Best Case or Optimistic Scenario = 63,329,500

Instead of stable perpetuity, we will use growing perpetuity formula

Growing Perpetuity formula = CF₀ * (1+growth) / (rate - growth)

PV of the Cashflows = 63,329,500 * (1 + 0.5%) / (17% - 0.5%) = 385,734,227.27

NPV = PV of all cashflows - Initial cost = 385,734,227.27 - 165,000,000

NPV for Best case 0.5% growth in Cashflows = $220,734,227.27

=> Worst Case Scenario with 0.5% growth

Cashflow in Worst Case or Pessimistic Scenario = 9,600,500

Instead of stable perpetuity, we will use growing perpetuity formula

Growing Perpetuity formula = CF₀ * (1+growth) / (rate - growth)

PV of the Cashflows = 9,600,500 * (1 + 0.5%) / (17% - 0.5%) = 58,475,772.73

NPV = PV of all cashflows - Initial cost = 58,475,772.73 - 165,000,000

NPV for Worst case 0.5% growth in Cashflows = - $106,524,227.27

2) To find the break-even market size of the project, we need to go in the reverse direction to find the the break-even point.

The break-even point is where project's NPV is 0 (zero).

As NPV = PV of all CFs - Initial cost, therefore, at zero NPV, PV of all CFs = 165,000,000

Using perpetuity formula and discount rate of 17%, we can find the cashflow required for break-even

PV = CF / R

=> 165,000,000 = CF / 17%

Cashflow for breakeven = 28,050,000

As Cashflow for this question = Operating Profit * (1-tax rate)

Therefore, 28,050,000 = OP * (1 - 35%)

Operating Profit = 28,050,000 / 0.65 = 43,153,846.15

Operating Profit = Contribution Margin - Fixed Costs

As Contribution margin = (Price per unit - Variable costs per unit) * Number of units

=> Pessimistic Scenario

Putting values of Pessimistic scenario in the formula

=> 43,153,846.15 = (Price per unit - Variable costs per unit) * Number of units - Fixed costs

=> Fixed costs = (Price per unit - Variable costs per unit) * Number of units

=> 43,153,846.15 + 2,000,000 = (2410 - 1980) * N

=> Number of units for break-even = 45,153,846.15 / 430 = 105,008.9 units or 105,009 units

If we go in reverse and use market share of 3.9%, we can find market size required for break-even

=> Number of units = Market Share * Market Size in units

=> 105,009 = 3.9% * Market Size

Market size for break-even in Pessimistic scenario = 105,009 / 3.9% = 2,692,538.5 or 2,692,539 units

=> Expected Scenario

Putting values of Expected scenario in the formula

=> 43,153,846.15 = (Price per unit - Variable costs per unit) * Number of units - Fixed costs

=> Fixed costs = (Price per unit - Variable costs per unit) * Number of units

=> 43,153,846.15 + 2,000,000 = (2500- 1550) * N

=> Number of units for break-even = 45,153,846.15 / 950 = 47,530.4 or 47,530 units

If we go in reverse and use market share of 5%, we can find market size required for break-even

=> Number of units = Market Share * Market Size in units

=> 47,530 = 5% * Market Size

Market size for break-even in Expected scenario = 47,530 / 5% = 950,600 units

=> Expected Scenario

Putting values of Optimistic scenario in the formula

=> 43,153,846.15 = (Price per unit - Variable costs per unit) * Number of units - Fixed costs

=> Fixed costs = (Price per unit - Variable costs per unit) * Number of units

=> 43,153,846.15 + 2,000,000 = (2590 - 960) * N

=> Number of units for break-even = 45,153,846.15 / 1630 = 27,701.75 or 27,702 units

If we go in reverse and use market share of 6.1%, we can find market size required for break-even

=> Number of units = Market Share * Market Size in units

=> 27,702 = 6.1% * Market Size

Market size for break-even in Optimistic scenario = 27,702 / 6.1% = 454,131.15 or 454,131 units