Question

Perform a sensitivity analysis with data provided
Assume that your selected company is considering a potential project with a new product that is expected to sell for an average price of $22 per unit and the company expects it can sell 400 000 units per year at this price for a period of 4 years. Launching this project will require purchase of a $2 450 000 equipment that has residual value in four years of $250 000 and adding $ 500 000 in working capital which is expected to be fully retrieved at the end of the project. Other information is available below:
Depreciation method: straight line
Variable cost per unit: $12
Cash fixed costs per year $250 000
Discount rate: 10%
Tax Rate: 30%
Do an analysis with cash flows of the project to determine the sensitivity of the project NPV with the following changes in the value drivers and provide your results in (a) relevant tables:
Unit sales decrease by 10%
Price per unit decreases by 10%
Variable cost per unit increases 10%
Cash fixed cost per year increases by 10%

Answer 1

Please find the answer below. No rounding off has been done to intermediate calculations. Screenshots have been attached for each of the calculations. kindly let me know in case of any concern.

Scenario	NPV	Deviation in NPV from orignial scenario
Original	62,35,431	-
Unit sale decreases by 10%	53,47,869	-8,87,562
Price per unit decreases by 10%	42,82,794	-19,52,637
Variable cost per unit increases 10%	51,70,357	-10,65,075
Cash fixed cost per year increases by 10%	61,79,959	-55,473

Normal scenario NPV calculation

1 Calculation of yearly cash inflows Calculation of NPV 2 3 Particulars Present value of cash Inflows 91,85,431 Amount 4 A. Sales (4,00,000* $22) 5 B. Variable cost (4,00,000*$12) Less: Present value of cash outflows 29,50,000 62,35,431 88,00,000 48,00,000 NPV 6 C. Cash fixed costs 2,50,000 D. Profit before depreciation and 7 tax [A-(B+C)] 8 E. Depreciation 9 F. Profit before tax (D-E) Calculation of present value of cash inflows 37,50,000 Particulars Period Present Value 5,50,000 Amount PVF PVAF 27,90,000 1-4 5,00,000 4 32,00,000 Yearly Cash flow after tax 3.17 88,43,925 10 G. Tax amount @ 30% Release of working capital at year 4 9,60,000 0.68 3,41,507 11 H. Profit after tax (F-G) 12 . Cash flow after tax (H+E) 22,40,000 91,85,431 27,90,000 13 Calculation of present value of cash outflows 14 Calculation of PVF and PVAF @ 10% 15 Particulars Period Present Value Amount PVF PVAF Cost of equipment Additional Working capital 24,50,000 24,50,000 0 PVAF 16 Period 5,00,000 PVF 0 1 5,00,000 17 0.91 0.91 29,50,000 18 1.74 0.83 19 20 0.75 2.49 0.68 4 3.17

Unit sales decrease by 10%

G 1 Calculation of yearly cash inflows Calculation of NPV 2 3 Particulars 4 A. Sales (4,00,000*$22*90%) 5 B. Variable cost (4,00,000*$12*90%) Present value of cash Inflows Amount 82,97,869 79,20,000 Less: Present value of cash outflows 29,50,000 43,20,000 NPV 53,47,869 6 C. Cash fixed costs D. Profit before depreciation and 7 tax [A-(B+C)] 8 E. Depreciation 9 F. Profit before tax (D-E) 2,50,000 Calculation of present value of cash inflows 33,50,000 Particulars Period Present Value 5,50,000 Amount PVF PVAF 25,10,000 1-4 5,00,000 4 Yearly Cash flow after tax 79,56,362 28,00,000 3.17 Release of working capital at year 4 10 G. Tax amount @ 30% 8,40,000 3,41,507 0.68 11 H. Profit after tax (F-G) 12 . Cash flow after tax (H+E) 19,60,000 82,97,869 25,10,000 13 Calculation of present value of cash outflows 14 Calculation of PVF and PVAF @ 10% 15 16 Period 17 Period Particulars Amount PVF PVAF Present Value Cost of equipment Additional Working capital 24,50,000 0 1 24,50,000 PVAF PVF 5,00,000 0 5,00,000 1 0.91 0.91 29,50,000 18 2 0.83 1.74 19 3 0.75 2.49 20 4 0.68 3.17 Sheet3 Sheet5 Shoet6. Sheet1 Sheet2. Sheetl. Sheet7. Sheat Shoota. Sheat10 st

Price per unit decreases by 10%

G Calculation of yearly cash inflows Calculation of NPV 1 2 3 Particulars Present value of cash Inflows 72,32,794 Amount 4 A. Sales (4,00,000*$22*90%) 5 B. Variable cost (4,00,000*$12) 79,20,000 Less: Present value of cash outflows 29,50,000 42,82,794 48,00,000 NPV 6 C. Cash fixed costs 2,50,000 D. Profit before depreciation and 7 tax [A-(B+C)] 8 E. Depreciation 9 F. Profit before tax (D-E) 10 G. Tax amount @ 30% Calculation of present value of cash inflows 28,70,000 Period Particulars 5,50,000 Amount PVF PVAF Present Value 21,74,000 1-4 5,00,000 4 Yearly Cash flow after tax 3.17 23,20,000 68,91,287 Release of working capital at year 4 6,96,000 16,24,000 0.68 3,41,507 1 H. Profit after tax (F-G) 72,32,794 12 I. Cash flow after tax (H+E) 21,74,000 13 14 Calculation of PVF and PVAF @ 10% Calculation of present value of cash outflows Particulars Amount Period PVF PVAF Present Value 15 Cost of equipment 24,50,000 24,50,000 1 PVF Additional Working capital 16 Period PVAF 5,00,000 0 1 5,00,000 17 0.91 1 0.91 29,50,000 18 2 0.83 1.74 19 0.75 2.49 20 0.68 3.17 4 оо

Variable cost per unit increases 10%

Н В 1 Calculation of yearly cash inflows Calculation of NPV 2 3 Particulars Present value of cash Inflows Amount 81,20,357 4 A. Sales (4,00,000*$22) 5 B. Variable cost (4,00,000*$12*110%), Less: Present value of cash outflows 29,50,000 51,70,357 88,00,000 52,80,000 NPV 6 C. Cash fixed costs 2,50,000 D. Profit before depreciation and tax 7 [A-(B+C)] E. Depreciation F. Profit before tax (D-E) Calculation of present value of cash inflows 32,70,000 8 5,50,000 Particulars PVF Present Value Amount Period PVAF 24,54,000 1-4 5,00,000 4 9 Yearly Cash flow after tax 77,78,850 27,20,000 3.17 10 G. Tax amount @ 30% Release of working capital at year 4 8,16,000 19,04,000 0.68 3,41,507 11 H. Profit after tax (F-G) 81,20,357 12 . Cash flow after tax (H+E) 24,54,000 13 Calculation of present value of cash outflows 14 Calculation of PVF and PVAF @ 10% 15 PVF Particulars Period Present Value Amount PVAF Cost of equipment 24,50,000 0 1 24,50,000 Additional Working capital 16 Period PVF PVAF 5,00,000 0 5,00,000 17 1 0.91 0.91 29,50,000 8 2 0.83 1.74 19 20 3 0.75 2.49 4 0.68 3.17

Cash fixed cost per year increases by 10%

A В C E F Н 1Calculation of yearly cash inflows Calculation of NPV 2 3 Particulars 4 A. Sales (4,00,000*$22) 5 B. Variable cost (4,00,000*$12) Present value of cash Inflows 91,29,959 Amount Less: Present value of cash outflows 88,00,000 29,50,000 48,00,000 NPV 61,79,959 6 C. Cash fixed costs (increased by 10%) 2,75,000 7 D. Profit before depreciation and tax [A-(B+C)] 8 E. Depreciation 9 F. Profit before tax (D-E) Calculation of present value of cash inflows 37,25,000 5,50,000 31,75,000 Particulars Present Value Amount Period PVF PVAF Yearly Cash flow after tax 87,88,452 27,72,500 1-4 3.17 10 G. Tax amount @ 30% 11 H. Profit after tax (F-G) 12 . Cash flow after tax (H+E) Release of working capital at year 4 5,00,000 4 3,41,507 91,29,959 9,52,500 0.68 22,22,500 27,72,500 13 Calculation of present value of cash outflows 14 Calculation of PVF and PVAF @ 10% Particulars Period Present Value Amount PVF PVAF 15 Cost of equipment Additional Working capital 24,50,000 0 1 24,50,000 PVF PVAF 16 Period 5,00,000 0 1 5,00,000 17 1 0.91 0.91 29,50,000 18 2 0.83 1.74 19 3 0.75 2.49 20 4 0.68 3.17