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 1- Develop the annual pro forma after-tax cash flow statement for each scenario.

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.

Goal 3- Use the probability distribution given along with your estimates from Goals 1 and 2 to calculate the expected value of the NPV and IRR for acquiring the assets. Interpret the expected values for both capital budgeting measures (compare your estimate of the expected value of the IRR to a benchmark IRR of 14.8%).

Goal 5- Discuss three ways in which your financing modeling assumptions may be incorrect and state the associated impact on the ATCFs, NPV and IRR. Your discussion should be at least 250 words. Proof read before submitting.

Answer 1

Goal-1:

Straight line depreciation = ((Value at the start - Salvage value)/(no. of years))

Worst case:

Last year revenue was $ 15,000,000
Years	1	2	3	4	5	6	7	8	9	10
Revenue (in $) (growing by -10%%)	13500000	12150000	10935000	9841500	8857350	7971615	7174454	6457008	5811307	5230177
Cash Expense (in $) (60% of revenues)	8100000	7290000	6561000	5904900	5314410	4782969	4304672	3874205	3486784	3138106
Depreciation (in $) (50,000,000/10= $500,000 every year)	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000
Profit (in $) (revenue- cash expense-depreciation)	49,00,000	43,60,000	38,74,000	34,36,600	30,42,940	26,88,646	23,69,781	20,82,803	18,24,523	15,92,071
Tax (in $) (20% of profit)	980000	872000	774800	687320	608588	537729.2	473956.3	416560.7	364904.6	318414.1
After tax cash flow (in $) (Profit -Tax)	39,20,000	3488000	3099200	2749280	2434352	2150917	1895825	1666243	1459618	1273657

Base case:

Last year revenue was $ 15,000,000
Years	1	2	3	4	5	6	7	8	9	10
Revenue (in $) (growing by 0%%)	15000000	15000000	15000000	15000000	15000000	15000000	15000000	15000000	15000000	15000000
Cash Expense (in $) (55% of revenues)	8250000	8250000	8250000	8250000	8250000	8250000	8250000	8250000	8250000	8250000
Depreciation (in $) (50,000,000/10= $500,000 every year)	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000
Profit (in $) (revenue- cash expense-depreciation)	62,50,000	62,50,000	62,50,000	62,50,000	62,50,000	62,50,000	62,50,000	62,50,000	62,50,000	62,50,000
Tax (in $) (20% of profit)	1250000	1250000	1250000	1250000	1250000	1250000	1250000	1250000	1250000	1250000
After tax cash flow (in $) (Profit -Tax)	50,00,000	5000000	5000000	5000000	5000000	5000000	5000000	5000000	5000000	5000000

Best case:

Last year revenue was $ 15,000,000
Years	1	2	3	4	5	6	7	8	9	10
Revenue (in $) (growing by 0%%)	16050000	17173500	18375645	19661940.15	21038275.96	22510955.28	24086722.15	25772793	27576888	29507270
Cash Expense (in $) (45% of revenues)	7222500	7728075	8269040	8847873.068	9467224.182	10129929.87	10839024.97	11597757	12409600	13278272
Depreciation (in $) (50,000,000/10= $500,000 every year)	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000	5,00,000
Profit (in $) (revenue- cash expense-depreciation)	83,27,500	89,45,425	96,06,605	1,03,14,067	1,10,71,052	1,18,81,025	1,27,47,697	########	########	########
Tax (in $) (20% of profit)	1665500	1789085	1921321	2062813.417	2214210.356	2376205.081	2549539.436	2735007	2933458	3145800
After tax cash flow (in $) (Profit -Tax)	66,62,000	7156340	7685284	8251253.666	8856841.423	9504820.322	10198157.74	10940029	11733831	12583199

Goal 2:

Worst case:

Years	0	1	2	3	4	5	6	7	8	9	10
After-tax cash flow	-5,00,00,000	39,20,000	34,88,000	30,99,200	27,49,280	24,34,352	21,50,917	18,95,825	16,66,243	14,59,618	12,73,657
WACC (Cost of capital)	20%
PV ((After tax cash flow/((1+WACC)^n)); n=No. of year	-50000000	3266667	2422222	1793519	1325848.765	978311.4712	720337.6843	529089.9974	387514.8	282883.8	205702.6
NPV (Sum of present value of after tax cash flow)	-38087903.4
IRR (Summation till 10th year (((After tax cash flow/((1+IRR)^n)); n =10)	-28%

Base case:

Years	0	1	2	3	4	5	6	7	8	9	10
After-tax cash flow	-5,00,00,000	50,00,000	50,00,000	50,00,000	50,00,000	50,00,000	50,00,000	50,00,000	50,00,000	50,00,000	50,00,000
WACC (Cost of capital)	20%
PV ((After tax cash flow/((1+WACC)^n)); n=No. of year	-50000000	4166667	3472222	2893519	2411265.432	2009387.86	1674489.883	1395408.236	1162840	969033.5	807527.9
NPV (Sum of present value of after tax cash flow)	-29037639.6
IRR (Summation till 10th year (((After tax cash flow/((1+IRR)^n)); n =10)	-17%

Best case:

Years	0	1	2	3	4	5	6	7	8	9	10
After-tax cash flow	-5,00,00,000	66,62,000	71,56,340	76,85,284	82,51,254	88,56,841	95,04,820	1,01,98,158	########	########	########
WACC (Cost of capital)	20%
PV ((After tax cash flow/((1+WACC)^n)); n=No. of year	-50000000	5551667	4969681	4447502	3979192.547	3559365.927	3183145.095	2846118.662	2544301	2274095	2032257
NPV (Sum of present value of after tax cash flow)	-14612675.4
IRR (Summation till 10th year (((After tax cash flow/((1+IRR)^n)); n =10)	-7%

In all the 3 cases, we are getting a negative NPA and IRR.

For the project to viable, it should have positive NPV and IRR more than WACC (Cost of capital).

In this case, we will reject the project.

Goal 3:

Expected value of NPV = Probability of worst case scenario * NPV (worst case scenario)+ Probability of base case scenario * NPV (base case scenario) + Probability of best case scenario * NPV (best case scenario)

= 0.1* -38087903.4 + 0.8* -29037639.6 + 0.1*-14612675.4

= $ -28500169.6

Expected value of IRR = Probability of worst case scenario * IRR (worst case scenario)+ Probability of base case scenario * IRR (base case scenario) + Probability of best case scenario * IRR (best case scenario)

= = 0.1* -28% + 0.8* -17% + 0.1*-7%

= -17.1%

Now, expected NPV and Expected IRR of the project are also coming out to be negative, therefore, project needs to be rejected because the project will not make any money.

Goal 4:

(a) Growth rate: Growth rate can be more or very less based on the future as to how it pans out. If the growth rate is very high, it will lead to high NPV and IRR, where as on the other hand, if it low then it will lead to very low NPV and IRR.

(B) Cost of capital: Cost of capital of the project can be very high or low. Very high cost of capital will lead to low NPV and IRR and low cost of capital will lead to high NPV and IRR.

(C) Expenses: Expenses can happen along with unexpected expenses. Depending on which NPV and IRR will change. If unexpected expenses happen, it will lead to low after tax cash flows which will lead to low NPV and IRR. Less expenses will lead to high after tax cash flows which will ultimately lead to higher NPV and IRR.