Question

Your boss wants you to conduct a sensitivity and scenario analysis to determine whether the following project is a winner. You are entering an established market, and you know the market size will be 1,100,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 $150 million, and that investment will depreciate in straight-line form over the 20-year life of the project. There are no new NWC requirements, and there will be no salvage value at the end of the 20 years. The tax rate is 35%. The discount rate is 18%.

a) Use the following table to conduct a full sensitivity analysis for the project. Make sure to include the NPV for the expected outcome as part of the full sensitivity analysis. Also add the best- and worst-case scenarios to the full sensitivity analysis. Show all of your work (written out, not an Excel file). Round to the nearest dollar.

	Pessimistic	Expected	Optimistic
Market Share	4.0%	5.0%	6.0%
Price/unit	$2310	$2500	$2690
VC/Unit	$2000	$1540	$1000
FC	$1.8 Million	$2 Million	$2.2 Million

b) Using the information above, what is the break-even market share for the project? Show your work, and round to the nearest 0.01%.

Answer 1

a) Market Size = 1,100,000

=> First we will work on Pessimistic Scenario

Expected Market Share = 4% | Number of units = 4% * 1,100,000 = 44,000

Price per unit = 2,310

Revenue = Price per unit * Number of Units = 2,310 * 44,000 = 101,640,000

Variable cost per unit or VC/unit = 2,000 | Total Variable Cost = 2,000 * 44,000 = 88,000,000

Expected Gross Profit = 101,640,000 - 88,000,000 = 13,640,000

Fixed Cost = 1,800,000

Operating Profit in the Scenario = Gross Profit - Fixed Cost = 13,640,000 - 1,800,000 = 11,840,000

As no new NWC investments are required, therfore, Cashflow to the company = (Operating Profit - Depreciation) * (1 - Tax-rate) + Depreciation

Initial Investment Cost for the project = 150,000,000 | Tax-rate = 35%

Depreciation over 20 years of project-life using Straight Line method = 150,000,000 / 20 = 7,500,000

Cashflow to the company = (11,840,000 - 7,500,000)*(1-35%) + 7,500,000 = 10,321,000

Assuming the same cashflow throughout the project's life of 20 years, we can find the PV of all the cashflows as an Annuity

Formula of Annuity: PV = (CF / r) * (1 - (1+r)^-n)

Discount Rate = 18%

Present Value of all the Cashflows = (10,321,000 / 18%) * (1 - (1+18%)^-20) = 55,245,697

Net Present Value of the Project = PV of all the CFs - Initial Investment cost = 55,245,697 - 150,000,000 = -94,754,303

=> Now we will work on the Expected Scenario

Expected Market Share = 5% | Number of Units = 5% * 1,100,000 = 55,000

Price per unit = 2,500

Revenue = Price per unit * Number of Units = 2,500 * 55,000 = 137,500,000

Variable cost per Unit or VC/unit = 1,540 | Total Variable cost = 1,540 * 55,000 = 84,700,000

Expected Gross Profit = 137,500,000 - 84,700,000 = 52,800,000

Fixed Cost = 2,000,000

Operating Profit in the Scenario = Gross Profit - Fixed Cost = 52,800,000 - 2,000,000 = 50,800,000

As no new NWC investments are required, therfore, Cashflow to the company = (Operating Profit - Depreciation) * (1 - Tax-rate) + Depreciation

Initial Investment Cost for the project = 150,000,000 | Tax-rate = 35%

Depreciation over 20 years of project-life using Straight Line method = 150,000,000 / 20 = 7,500,000

Cashflow to the company = (50,800,000 - 7,500,000)*(1-35%) + 7,500,000 = 35,645,000

Assuming the same cashflow throughout the project's life of 20 years, we can find the PV of all the cashflows as an Annuity

Formula of Annuity: PV = (CF / r) * (1 - (1+r)^-n)

Discount rate = 18%

Present Value of all the Cashflows = (35,645,000 / 18%) * (1 - (1+18%)^-20) = 190,798,649

Net Present Value of the Project = PV of all the CFs - Initial Investment cost = 190,798,649 - 150,000,000 = 40,798,649

=> Now we will work on Optimistic Scenario

Expected Market Share = 6% | Number of units = 6% * 1,100,000 = 66,000

Price per unit = 2,690

Revenue = Price per unit * Number of Units = 2,690 * 66,000= 177,540,000

Variable cost per Unit or VC/unit = 1,000 | Total Variable cost = 1,000 * 66,000 = 66,000,000

Expected Gross Profit = 177,540,000 - 66,000,000 = 111,540,000

Fixed Cost = 2,200,000

Operating Profit in the scenario = Gross Profit - Fixed Cost = 111,540,000 - 2,200,000 = 109,340,000

As no new NWC investments are required, therfore, Cashflow to the company = (Operating Profit - Depreciation) * (1 - Tax-rate) + Depreciation

Initial Investment cost of the project = 150,000,000 | Tax rate = 35%

Depreciation over 20 years of project-life using Straight Line method = 150,000,000 / 20 = 7,500,000

Cashflow to the company = (109,340,000 - 7,500,000)*(1-35%) + 7,500,000 = 73,696,000

Assuming the same cashflow throughout the project's life of 20 years, we can find the PV of all the cashflows as an Annuity

Formula of Annuity: PV = (CF / r) * (1 - (1+r)^-n)

Discount rate = 18%

Present Value of all the Cashflows = (73,696,000 / 18%) * (1 - (1+18%)^-20) = 394,476,006

Net Present Value of the project = PV of all the CFs - Initial Investment cost = 394,476,006 - 150,000,000 = 244,476,006

=> Optimistic Scenario is the Best scenario since its NPV is 244,476,000

=> Pessimistic Scenario is the Worst scenario since its NPV is -94,754,303

b) To calculate the break-even market share of the project, we need to go start from the end and reach the beginning of the whole process that we did in part (a)

Since it is break-even, that means NPV of the scenario is 0

NPV = PV of all the CFs - Initial Investment cost

=> 0 = PV of CFs - 150,000,000

Therefore, PV of CFs = 150,000,000

Now using the Annuity formula, we can find the Cashflow to the company which is required for break-even

Formula of Annuity: PV = (CF / r) * (1 - (1+r)^-n)

Discount rate = 18% | PV in this case is 150,000,000

=> 150,000,000 = (CF / 18%) * (1 - (1+18%)^-20)

Solving for CF, we will get Cashflow to the company.

Cashflow to the company required for break-even = $ 28,022,997

Now we will calculate the Operating profit required for break-even

Cashflow = (Operating Profit - Depreciation) * (1-35%) + Depreciation

=> 28,022,997 = (OP - 7,500,000)*0.65 + 7,500,000

Solving for OP, we will get Operating profit

Operating Profit = 39,073,842

=> Pessimistic Scenario's Break-Even

Using Pessimistic Scenario's Fixed Cost, Variable cost and Price to find the Break-even market share.

Gross Profit for the project = Operating Profit + Fixed Cost = 39,073,842 + 1,800,000 = 40,873,842

Since (Price per unit - Variable Cost per unit) * Number of units = Gross Profit, we will use this formula to solve for number of units

=> 40,873,842 = (2,310 - 2000) * Number of units

=> Number of units = 131,851

Market Share = 131,851 / 1,100,000 = 11.99%

=> Expected Scenario's Break-Even

Using Expected Scenario's Fixed Cost, Variable cost and Price to find the Break-even market share.

Gross Profit for the project = Operating Profit + Fixed Cost = 39,073,842 + 2,000,000 = 41,073,842

Since (Price per unit - Variable Cost per unit) * Number of units = Gross Profit, we will use this formula to solve for number of units

=> 41,073,842 = (2,500 - 1,540) * Number of units

=> Number of units = 42,785

Market Share = 42,785 / 1,100,000 = 3.89%

=> Optimistic Scenario's Break-Even

Using Optimistic Scenario's Fixed Cost, Variable cost and Price to find the Break-even market share.

Gross Profit for the project = Operating Profit + Fixed Cost = 39,073,842 + 2,200,000 = 41,273,842

Since (Price per unit - Variable Cost per unit) * Number of units = Gross Profit, we will use this formula to solve for number of units

=> 41,273,842 = (2,690 - 1,000) * Number of units

=> Number of units = 24,422

Market Share = 24,422 / 1,100,000 = 2.22%

Hence, Break-even market share for Pessimistic Scenario is 11.99%, Expected Scenario is 3.89% and Optimistic Scenario is 2.22%