Question

Your firm may purchase certain assets from a struggling competitor. The competitor is asking $50,000,000 for the assets. Last year, the assets produced revenues of $15,000,000. Revenues earned in the next year (i.e., year 1) and in future years are estimated using the information in the table below.

Your staff expects that the following assumptions will hold over the operating period:

• The assets will be viable for another 10 years but will be worthless at the end of the 10 year period
• The assets are qualified by the IRS for depreciation using the straight-line method
• A constant tax rate of 20%

Your staff has also identified three key areas of uncertainty, which include

	Worst-Case	Base-Case	Best-Case
Cash Expenses as a % of Revenues	60%	55%	45%
WACC	20%	15%	8%
Revenue Growth Rate	-10%	0%	7%
Probability	10%	80%	10%

For this case, address the following goals (each goal should be shown in a separate worksheet in an Excel workbook; provide labels on each worksheet):

Goal 2- Calculate the NPV and IRR for each scenario. Within the Goal 2 worksheet, discuss/interpret the NPV and IRR values that you have calculated in terms of whether the acquisition should be accepted or rejected.

Answer 1

GOAL 2:
BASE CASE: [WACC 15%]
Annual after tax cash flow	$   -5,00,00,000	$     64,00,000	$    64,00,000	$    64,00,000	$     64,00,000	$     64,00,000	$      64,00,000	$      64,00,000	$     64,00,000	$       64,00,000	$       64,00,000
PVIF at 15%	1	0.869565	0.756144	0.657516	0.571753	0.497177	0.432328	0.375937	0.326902	0.284262	0.247185
PV at 15%	$   -5,00,00,000	$     55,65,217	$    48,39,319	$    42,08,104	$     36,59,221	$     31,81,931	$      27,66,897	$      24,05,997	$     20,92,171	$       18,19,279	$       15,81,982	$     -1,78,79,881
NPV	$   -1,78,79,881
IRR:
IRR is that discount rate for which NPV = 0. It has to be found out by trial and error by trying different discount rates to get 0 NPV.
Discounting with 4%:
PVIFA at 4%	1	0.96154	0.92456	0.88900	0.85480	0.82193	0.79031	0.75992	0.73069	0.70259	0.67556	NPV
PV at 4%	$   -5,00,00,000	$     61,53,846	$    59,17,160	$    56,89,577	$     54,70,747	$     52,60,333	$      50,58,013	$      48,63,474	$     46,76,417	$       44,96,555	$       43,23,611	$          19,09,733
Discounting with 5%:
PVIFA at 5%	1	0.952380952	0.907029478	0.863837599	0.822702475	0.783526166	0.746215397	0.71068133	0.676839362	0.644608916	0.613913254
PV at 5%	$   -5,00,00,000	$     60,95,238	$    58,04,989	$    55,28,561	$     52,65,296	$     50,14,567	$      47,75,779	$      45,48,361	$     43,31,772	$       41,25,497	$       39,29,045	$           -5,80,896
IRR = 4%+1%*1909733/(1909733+580896) =		4.77%
As the NPV is negative and the IRR is less than the cost of capital, the acquisition should be rejected.
BEST CASE [WACC 8%]:
Annual after tax cash flow	$   -5,00,00,000	$     80,62,000	$    85,56,340	$    90,85,284	$     96,51,254	$ 1,02,56,841	$ 1,09,04,820	$ 1,15,98,158	$ 1,23,40,029	$   1,31,33,831	$   1,39,83,199
PVIF at 15%	1	0.869565	0.756144	0.657516	0.571753	0.497177	0.432328	0.375937	0.326902	0.284262	0.247185
PV at 15%	$   -5,00,00,000	$     70,10,435	$    64,69,822	$    59,73,722	$     55,18,136	$     50,99,463	$      47,14,455	$      43,60,177	$     40,33,977	$       37,33,454	$       34,56,433	$            3,70,074
NPV	$           3,70,074
IRR:
IRR is that discount rate for which NPV = 0. It has to be found out by trial and error by trying different discount rates to get 0 NPV.
Discounting with 16%:
PVIFA at 16%	1	0.86207	0.74316	0.64066	0.55229	0.47611	0.41044	0.35383	0.30503	0.26295	0.22668	NPV
PV at 16%	$   -5,00,00,000	$     69,50,000	$    63,58,754	$    58,20,557	$     53,30,301	$     48,83,416	$      44,75,799	$      41,03,771	$     37,64,023	$       34,53,580	$       31,69,762	$        -16,90,037
IRR = 15%+1%*370074/(370074+1690037) =		15.18%
As the NPV is positive and the IRR is greater than the cost of capital, the acquisition should be accepted.
WORST CASE [WACC 20%]
Annual after tax cash flow	$   -5,00,00,000	$     53,20,000	$    48,88,000	$    44,99,200	$     41,49,280	$     38,34,352	$      35,50,917	$      32,95,825	$     30,66,243	$       28,59,618	$       26,73,657	$     -1,18,62,909
PVIF at 15%	1	0.869565	0.756144	0.657516	0.571753	0.497177	0.432328	0.375937	0.326902	0.284262	0.247185
PV at 15%	$   -5,00,00,000	$     46,26,087	$    36,96,030	$    29,58,297	$     23,72,364	$     19,06,351	$      15,35,159	$      12,39,023	$     10,02,360	$         8,12,882	$         6,60,887	$     -2,91,90,560
NPV	$   -2,91,90,560
IRR:
The IRR will be negative as the cumulative value of the undiscounted cash flows is negative at $11862909
As the NPV is negative and the IRR is less than the cost of capital, the acquisition should be rejected.