Question

Eddy Ltd is considering investing in a project at a cost of N$3 000 000. The estimated economic life of the project is 5 years. The company will use the straight-line method to depreciate the cost of the project over 5 years. The company estimates that sales will amount to 240 000 units per year at an estimated selling price of N$40 per unit. The company expects to incur fixed overheads, excluding depreciation of N$300 000 per year and variable cost per unit is N$30. The company cost of capital is 11% and the corporate tax rate is 28%. The expected residual value of the project in 5 years’ time is expected to be zero.

Required:

a) Use the sensitivity analysis to determine what the NPV of the project would be if selling price, sales volume, and variable cost per unit are increased or reduced by 10%.

b) Use break-even analysis to determine the minimum sales volume that the company is required to achieve to break-even in terms of NPV.

Answer 1

First we calculate the base-case NPV and then vary the key drivers (sales volume, price per unit and variable cost per unit) to do the sensitivity analysis.

Formula	Year (n)	0	1	2	3	4	5
	Initial investment (II)	3,000,000
	Sales (u)		240,000	240,000	240,000	240,000	240,000
	Price per unit (p)		40	40	40	40	40
	Variable cost per unit (vc)		30	30	30	30	30
u*p	Revenue ('R)		9,600,000	9,600,000	9,600,000	9,600,000	9,600,000
u*vc	Variable cost (VC)		7,200,000	7,200,000	7,200,000	7,200,000	7,200,000
	Fixed cost (FC)		300,000	300,000	300,000	300,000	300,000
II/5	Depreciation (D)		600,000	600,000	600,000	600,000	600,000
R-VC-FC-D	EBIT		1,500,000	1,500,000	1,500,000	1,500,000	1,500,000
EBIT*(1-Tax rate)	Net income (NI)		1,080,000	1,080,000	1,080,000	1,080,000	1,080,000
	Add: Depreciation		600,000	600,000	600,000	600,000	600,000
NI+D	Operating Cash Flow (OCF)		1,680,000	1,680,000	1,680,000	1,680,000	1,680,000
OCF-II	Free Cash Flow (FCF)	(3,000,000)	1,680,000	1,680,000	1,680,000	1,680,000	1,680,000
1/(1+d)^n	Discount factor @ 11%	1.000	0.901	0.812	0.731	0.659	0.593
FCF*Discount factor	PV of FCF	(3,000,000.00)	1,513,513.51	1,363,525.69	1,228,401.52	1,106,668.04	996,998.23
Sum of all PVs	NPV	3,209,106.99

a). Scenario analysis: Using the base-case table, we can change the key value drivers to find the NPV's.

Formula	Scenario	Sales Volume (V)	Selling price/unit (P)	Variable cost/unit (VC)	NPV
	Base-Case	240,000	40	30	3,209,106.99
Vbase-case*(1-10%); Pbase-case*(1-10%); VCbase-case*(1+10%)	Worst-Case	216,000	36	33	(1,453,045.34)
Vbase-case*(1+10%); Pbase-case*(1+10%); VCbase-case*(1-10%)	Best-Case	264,000	44	27	8,765,370.73

Note: The description given in the question is that of a scenario analysis whereas the term sensitivity analysis is used. In case, a sensitivity analysis is required, please comment below.

Note: The NPV table for each scenario is not posted due to the answer limit.

b). Using the base-case table given above, Solver is used to find the sales volume, at which NPV = 0.

The break-even sales volume is 119,404.30 or 119,404

Formula	Year (n)	0	1	2	3	4	5
	Initial investment (II)	3000000
	Sales (u)		119404.30	119404.30	119404.30	119404.30	119404.30
	Price per unit (p)		40.00	40.00	40.00	40.00	40.00
	Variable cost per unit (vc)		30.00	30.00	30.00	30.00	30.00
u*p	Revenue ('R)		4776171.83	4776171.83	4776171.83	4776171.83	4776171.83
u*vc	Variable cost (VC)		3582128.87	3582128.87	3582128.87	3582128.87	3582128.87
	Fixed cost (FC)		300000.00	300000.00	300000.00	300000.00	300000.00
II/5	Depreciation (D)		600000.00	600000.00	600000.00	600000.00	600000.00
R-VC-FC-D	EBIT		294042.96	294042.96	294042.96	294042.96	294042.96
EBIT*(1-Tax rate)	Net income (NI)		211710.93	211710.93	211710.93	211710.93	211710.93
	Add: Depreciation		600000.00	600000.00	600000.00	600000.00	600000.00
NI+D	Operating Cash Flow (OCF)		811710.93	811710.93	811710.93	811710.93	811710.93
OCF-II	Free Cash Flow (FCF)	-3000000	811710.93	811710.93	811710.93	811710.93	811710.93
1/(1+d)^n	Discount factor @ 11%	1.000	0.901	0.812	0.731	0.659	0.593
FCF*Discount factor	PV of FCF	(3,000,000.00)	731,271.11	658,802.80	593,516.04	534,699.13	481,710.93
Sum of all PVs	NPV	(0.00)

c). Sensitivity analysis:

NPV sensitivity to sales volume:

Base-case sales volume = 240,000; Base-case NPV = 3,209,106.99

Now, sales volume is changed and new NPV is calculated, using the base-case NPV table.

Sales volume = 230,000; NPV = 2,943,002.40

Sensitivity of NPV to sales volume = change in NPV/change in sales volume = (Base-case NPV - new NPV)/(Base-case sales volume - new sales volume)

= (3,209,106.99 - 2,943,002.40)/(240,000-230,000) = 26.61

NPV sensitivity to selling price/unit:

Base-case selling price/unit = 40; Base-case NPV = 3,209,106.99

Now, selling price/unit is changed and new NPV is calculated, using the base-case NPV table.

Selling price/unit = 35; NPV = 15,851.97

Sensitivity of NPV to selling price/unit = change in NPV/change in selling price/unit = (Base-case NPV - new NPV)/(Base-case selling price/unit - new selling price/unit)

= (3,209,106.99 - 15,851.97)/(40-30) = 3,193,255.03/5 = 638,651

NPV sensitivity to variable cost/unit:

Base-case variable cost/unit = 30; Base-case NPV = 3,209,106.99

Now, variable cost/unit is changed and new NPV is calculated, using the base-case NPV table.

Variable cost/unit = 25; NPV = 6,402,362,01

Sensitivity of NPV to variable cost/unit = change in NPV/change in variable cost/unit = (Base-case NPV - new NPV)/(Base-case variable cost/unit - new variable cost/unit)

= (3,209,106.99 - 6,402,362,01)/(30-25) = -3,193,255.02/5 = -638,651