In: Finance
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:
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% |
Develop the annual pro forma after-tax cash flow statement for each scenario.
Note: I am pretty sure the other "experts" who answered the question previously had major flaws in their math.
a] | BASE CASE [Growth rate = 0%]: | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Revenue | $ 1,50,00,000 | $1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | $ 1,50,00,000 | ||
Cash expenses [55%] | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | $ 82,50,000 | ||
Depreciation [50000000/10] | $ 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 | ||
NOI | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | $ 17,50,000 | ||
Tax at 20% | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | $ 3,50,000 | ||
NOPAT | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | $ 14,00,000 | ||
Add: Depreciation | $ 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 | ||
OCF | $ 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 | ||
Capital expenditure | $ 5,00,00,000 | |||||||||||
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 | |
b] | BEST CASE [Growth rate = 7%]: | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Revenue | $ 1,60,50,000 | $1,71,73,500 | $ 1,83,75,645 | $ 1,96,61,940 | $ 2,10,38,276 | $ 2,25,10,955 | $ 2,40,86,722 | $ 2,57,72,793 | $ 2,75,76,888 | $ 2,95,07,270 | ||
Cash expenses [45%] | $ 72,22,500 | $ 77,28,075 | $ 82,69,040 | $ 88,47,873 | $ 94,67,224 | $ 1,01,29,930 | $ 1,08,39,025 | $ 1,15,97,757 | $ 1,24,09,600 | $ 1,32,78,272 | ||
Depreciation [50000000/10] | $ 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 | ||
NOI | $ 38,27,500 | $ 44,45,425 | $ 51,06,605 | $ 58,14,067 | $ 65,71,052 | $ 73,81,025 | $ 82,47,697 | $ 91,75,036 | $ 1,01,67,289 | $ 1,12,28,999 | ||
Tax at 20% | $ 7,65,500 | $ 8,89,085 | $ 10,21,321 | $ 11,62,813 | $ 13,14,210 | $ 14,76,205 | $ 16,49,539 | $ 18,35,007 | $ 20,33,458 | $ 22,45,800 | ||
NOPAT | $ 30,62,000 | $ 35,56,340 | $ 40,85,284 | $ 46,51,254 | $ 52,56,841 | $ 59,04,820 | $ 65,98,158 | $ 73,40,029 | $ 81,33,831 | $ 89,83,199 | ||
Add: Depreciation | $ 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 | ||
OCF | $ 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 | ||
Capital expenditure | $ 5,00,00,000 | |||||||||||
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 | |
c] | WORST CASE [Growth rate = -10%]: | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Revenue | $ 1,35,00,000 | $1,21,50,000 | $ 1,09,35,000 | $ 98,41,500 | $ 88,57,350 | $ 79,71,615 | $ 71,74,454 | $ 64,57,008 | $ 58,11,307 | $ 52,30,177 | ||
Cash expenses [60%] | $ 81,00,000 | $ 72,90,000 | $ 65,61,000 | $ 59,04,900 | $ 53,14,410 | $ 47,82,969 | $ 43,04,672 | $ 38,74,205 | $ 34,86,784 | $ 31,38,106 | ||
Depreciation [50000000/10] | $ 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 | ||
NOI | $ 4,00,000 | $ -1,40,000 | $ -6,26,000 | $ -10,63,400 | $ -14,57,060 | $ -18,11,354 | $ -21,30,219 | $ -24,17,197 | $ -26,75,477 | $ -29,07,929 | ||
Tax at 20% | $ 80,000 | $ -28,000 | $ -1,25,200 | $ -2,12,680 | $ -2,91,412 | $ -3,62,271 | $ -4,26,044 | $ -4,83,439 | $ -5,35,095 | $ -5,81,586 | ||
NOPAT | $ 3,20,000 | $ -1,12,000 | $ -5,00,800 | $ -8,50,720 | $ -11,65,648 | $ -14,49,083 | $ -17,04,175 | $ -19,33,757 | $ -21,40,382 | $ -23,26,343 | ||
Add: Depreciation | $ 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 | ||
OCF | $ 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 | ||
Capital expenditure | $ 5,00,00,000 | |||||||||||
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 |