Question

For a new 5-year project, your company is considering buying new machine costing $1,000,000. The expected salvage of the machine at the end of the project is $80,000. The marketing department has forecasted that the company will be able to sell 120,000 units of new product per year at $15 per unit. The production department has indicated that the variable cost per unit will be $6. The company has forecasted that the incremental fixed costs associated with the project are $100,000. The company believes that the project will require an initial investment in operating net working capital of $150,000. Thereafter, the investment in operating net working capital will be 10% of sales.

The CCA rate is 25%, the tax rate is 40%, and the required rate of return is 15%.

1) Using net present value (NPV) calculation to determine if the company should purchase the new machine. Show all work.

2) Figure out the break-even number of units that the company must sell each year.

3) Using sensitivity analysis to determine which input (units sold, price per unit, variable cost per unit, or fixed cost) has the greatest forecasting risk. Change the input forecast by +5% and -5% to determine the impact on the NPV of the project.

4) Complete scenario analysis: a pessimistic scenario and a very pessimistic scenario. The value of the inputs for each scenario are shown in the table below. Calculate the NPV under these two additional scenarios.

		Pessimistic		Very Pessimistic
Units sold	125,000		75,000
Price per unit	$13		$11
Variable cost per unit	$7		$8
Fixed Cost		$140,000		$160,000

Answer 1

1.

Please refer below table for income statement and cash flow:-

Year	0	1	2	3	4	5
Units Sold		$1,20,000.00	$1,20,000.00	$1,20,000.00	$1,20,000.00	$1,20,000.00
Per Unit Sale Price		$15.00	$15.00	$15.00	$15.00	$15.00
Sales		$18,00,000.00	$18,00,000.00	$18,00,000.00	$18,00,000.00	$18,00,000.00
Unit Variable cost		$6.00	$6.00	$6.00	$6.00	$6.00
Variable Cost		$7,20,000.00	$7,20,000.00	$7,20,000.00	$7,20,000.00	$7,20,000.00
Fixed cost		$1,00,000.00	$1,00,000.00	$1,00,000.00	$1,00,000.00	$1,00,000.00
Depriciation		$2,50,000.00	$1,87,500.00	$1,40,625.00	$1,05,468.75	$79,101.56
Investment / Salvage Gain	$10,00,000.00					$80,000.00
Working capital investment	$1,50,000.00	$1,80,000.00	$1,80,000.00	$1,80,000.00	$1,80,000.00	$1,80,000.00
PBT	-$11,50,000.00	$5,50,000.00	$6,12,500.00	$6,59,375.00	$6,94,531.25	$8,00,898.44
PAT		$3,30,000.00	$3,67,500.00	$3,95,625.00	$4,16,718.75	$4,80,539.06
Cash Flow	-$11,50,000.00	$5,80,000.00	$5,55,000.00	$5,36,250.00	$5,22,187.50	$5,59,640.63

In the table depriciation is calculated as below:-

Year	Initial value	Depriciation
1	1000000	250000
2	750000	187500
3	562500	140625
4	421875	105468.75
5	316406.3	79101.5625

Here CCA rate is 25% so each year have 25% of the asset value depriciation till the 5 year and at the 5 year we have salvage value of $80000.

We will calculate now NPV in excel as below:-

NPV (0.15, Value1 value2.....) + (-$1150000)

here value1 - $580000

Here NPV @15% will be $703403.34

2.

Now Quantity will be=

= (Fixed Cost) / (P-V)

= (100000 + 180000 ) / ( 15-6)

= 31111.11

= 31112

3.

In excel we will change rate with 20% and 10%

NPV @ 20% = $505813.41

NPV @ 10% = $942996.86

As here IRR is 39% so till 39% we will have positive NPV

4)

For pessimistic we have below:-

Year	0	1	2	3	4	5
Units Sold		125000	125000	125000	125000	125000
Per Unit Sale Price		$13.00	$13.00	$13.00	$13.00	$13.00
Sales		$16,25,000.00	$16,25,000.00	$16,25,000.00	$16,25,000.00	$16,25,000.00
Unit Variable cost		$7.00	$7.00	$7.00	$7.00	$7.00
Variable Cost		$8,75,000.00	$8,75,000.00	$8,75,000.00	$8,75,000.00	$8,75,000.00
Fixed cost		$1,40,000.00	$1,40,000.00	$1,40,000.00	$1,40,000.00	$1,40,000.00
Depriciation		$2,50,000.00	$1,87,500.00	$1,40,625.00	$1,05,468.75	$79,101.56
Investment / Salvage Gain	$10,00,000.00					$80,000.00
Working capital investment	$1,50,000.00	$1,62,500.00	$1,62,500.00	$1,62,500.00	$1,62,500.00	$1,62,500.00
PBT	-$11,50,000.00	$1,97,500.00	$2,60,000.00	$3,06,875.00	$3,42,031.25	$4,48,398.44
PAT		$1,18,500.00	$1,56,000.00	$1,84,125.00	$2,05,218.75	$2,69,039.06
Cash Flow	-$11,50,000.00	$3,68,500.00	$3,43,500.00	$3,24,750.00	$3,10,687.50	$3,48,140.63

and NPV @ 15% will be negative

NPV = -$5577.47

For very pessimistic:-

Year	0	1	2	3	4	5
Units Sold		75000	75000	75000	75000	75000
Per Unit Sale Price		$11.00	$11.00	$11.00	$11.00	$11.00
Sales		$8,25,000.00	$8,25,000.00	$8,25,000.00	$8,25,000.00	$8,25,000.00
Unit Variable cost		$8.00	$8.00	$8.00	$8.00	$8.00
Variable Cost		$6,00,000.00	$6,00,000.00	$6,00,000.00	$6,00,000.00	$6,00,000.00
Fixed cost		$1,60,000.00	$1,60,000.00	$1,60,000.00	$1,60,000.00	$1,60,000.00
Depriciation		$2,50,000.00	$1,87,500.00	$1,40,625.00	$1,05,468.75	$79,101.56
Investment / Salvage Gain	$10,00,000.00					$80,000.00
Working capital investment	$1,50,000.00	$82,500.00	$82,500.00	$82,500.00	$82,500.00	$82,500.00
PBT	-$11,50,000.00	-$2,67,500.00	-$2,05,000.00	-$1,58,125.00	-$1,22,968.75	-$16,601.56
PAT		-$2,67,500.00	-$2,05,000.00	-$1.58,125.00	-$1.22.968.75	-$16,601.56
Cash Flow	-$11,50,000.00	-$17,500.00	-$17,500.00	-$17,500.00	-$17,500.00	$62,500.00

NPV @ 15% Shall be -$1168888.58

Very bad NPV for very pessimistic case. for pessimistic case NPV is a little bit less than 0, however if Required rate of return is less than 15% than it could be more than 0.

Thank You!!