Question

1 Approved Answer

Posted on Jun 27, 2024

1. Refer to file Chapter9.xls, tab Scenario. Under Scenario 1, what revenue growth would make the NPV equal to 0? 2. Opex Capital is a

1. Refer to file Chapter9.xls, tab Scenario. Under Scenario 1, what revenue growth would make the NPV equal to 0? 2. Opex Capital is a small investment advisory ?rm located in Portland, Oregon, that has been hired by Winston Winery to estimate the value of Hilco Wines. Hilco is a small winery that is being considered for purchase by Winston. Opex has obtained the ?nancial statements of Hilco and prepared the following estimates of the ?rm?s free cash ?ow for the next ?ve years: Year Cash Flows 1 $100,000 2 120,000 3 135,000 4 150,000 5 175,000 At the end of ?ve years, Opex has estimated that the winery should be worth approximately ?ve times its Year 5 cash ?ow. a. If the appropriate discount rate for valuing the winery is 15%,what is your estimate of the ?rm?s enterprise value? b. Winston Winery plans to borrow $400,000 to help ?nance the purchase. What is Hilco?s equity worth? image text in transcribed

The following sheets are models replicating the tables shown in chapter nine of the texbook. Immersion purchase GRC. Sheets Tables 9-1a, 9-1b, and 9-1c, show three scenarios. The first one doesn't consider goodwill. The second and third one show how goodwill is generated and even increased when more debt is acquired. Sheet Table 9-2 shows the table where the value of GRC is estimated using a 4% growth rate. It is called the Status Quo in the textbook. Sheet Table 9-3 shows the table where the value of GRC is estimated using a 8% growth rate. It is called the Growth Strategy. Sheets Sensi1 to Sensi3 show tables with results from sensitivity analysis. Revenue growth rate, cost of capital, and EBITDA multiples are changed. Table Scenario show five different scenarios. Acquisition Financing - The Generation of the Goodwill Figure Assumed payables and accrued expenses 9,825,826 Long-term debt (40% of enterprise value) 36,839,923 Sale of common stock 60,000,000 Total 106,665,749 Shares issued ($1.00 par value) 400,000 Net proceeds 42,340,210 Price per share 105.85 Pre-Acquis. 2009 Current assets 41,865,867 Gross PP&E 88,164,876 Less: Acumulated depreciation -41,024,785 Net PP&E 47,140,091 Goodwill Total 89,005,958 Current liabilities 9,825,826 Long-term debt 36,839,923 Total liabilities 46,665,749 Common stock (par) 290,353 Paid-in capital 20,712,517 Retained earnings 21,337,340 Common equity 42,340,210 Total 89,005,959 Scenario One: Immersion assumes liabilities of GRC and pays book value for equity. Since it pays book value of equity, there is no Goodwill amount. Value in Cell E23 is the same as value in cell G23. Goodwill value in cell G15 is 0. Goodwill = Comm Equity Post - Comm.Equity Pre = 0 Post-Acqu 2009 41,865,867 88,164,876 -41,024,785 47,140,091 89,005,958 9,825,826 36,839,923 46,665,749 400,000 41,940,210 0 42,340,210 89,005,959 Book value of equity Acquisition Financing - The Generation of the Goodwill Figure Assumed payables and accrued expenses 9,825,826 Long-term debt (40% of enterprise value) 36,839,923 Sale of common stock 60,000,000 Total 106,665,749 Shares issued ($1.00 par value) 400,000 Net proceeds 60,000,000 Price per share 150.00 Pre-Acquis. 2009 Current assets 41,865,867 Gross PP&E 88,164,876 Less: Acumulated depreciation -41,024,785 Net PP&E 47,140,091 Goodwill Total 89,005,958 Current liabilities 9,825,826 Long-term debt 36,839,923 Total liabilities 46,665,749 Common stock (par) 290,353 Paid-in capital 20,712,517 Retained earnings 21,337,340 Common equity 42,340,210 Total 89,005,959 Scenario Two: Immersion assumes liabilities of GRC and pays $60,000,000 for equity. Since it amount paid is higher than book value, Goodwill is generated. Value in Cell G23 is $17,659,790 higher than value in cell E23, which is goodwill, as reflected in G15. Goodwill = Comm Equity Post - Comm.Equity Pre = 0 60,000,000 - 42,340,210 = 17,659,790 Post-Acqu 2009 41,865,867 88,164,876 -41,024,785 47,140,091 17,659,790 106,665,748 9,825,826 36,839,923 46,665,749 400,000 59,600,000 0 60,000,000 106,665,749 Acquisition Financing - The Generation of the Goodwill Figure Assumed payables and accrued expenses 9,825,826 Long-term debt (40% of enterprise value) 40,000,000 Sale of common stock 60,000,000 Total 109,825,826 Shares issued ($1.00 par value) 400,000 Net proceeds 60,000,000 Price per share 150.00 Pre-Acquis. 2009 Current assets 41,865,867 Gross PP&E 88,164,876 Less: Acumulated depreciation -41,024,785 Net PP&E 47,140,091 Goodwill Total 89,005,958 Current liabilities 9,825,826 Long-term debt 36,839,923 Total liabilities 46,665,749 Common stock (par) 290,353 Paid-in capital 20,712,517 Retained earnings 21,337,340 Common equity 42,340,210 Total 89,005,959 Scenario Three: Since Immersion also borrowed $40,000,000 to pay for GRC, which in turn canceled its long-term debt of $36,839,923, it effectively paid $3,160,077 more, which adds to the existing goodwill. The total Goodwill is then $20,819,867. (60,000,000 - 42,340,210) + (40,000,000 - 36,839.923) = 20,819,867. Post-Acqu 2009 41,865,867 88,164,876 -41,024,785 47,140,091 20,819,867 89,005,958 9,825,826 40,000,000 49,825,826 400,000 59,600,000 0 60,000,000 109,825,826 20,819,867 Total difference Lon-term debt has changed 3,160,077 Difference en debt 17,659,790 Difference en equity The value of GRC is estimated in these tables. First, the income statement and balance sheet are projected. Then FCF is calculated. Finally, the cost of equity is calculated and a value is estimated. Two methods are used to estimate the terminal value: the Gordon model and an EBITDA multiple. The resulting values are similar. Table 9-2 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Status Quo Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pro Forma Income Statements Pre-Acquis. Post-Acqu 2009 2009 2010 2011 2012 2013 2014 4% 4% 4% 4% 4% Revenues 80,000,000 80,000,000 83,200,000 86,528,000 89,989,120 93,588,685 97,332,232 COGS -45,733,270 -45,733,270 -50,932,000 -52,629,280 -54,394,452 -56,230,229 -58,139,438 Gross Profit 34,266,730 34,266,730 32,268,000 33,898,720 35,594,668 37,358,456 39,192,794 G & AE -17,600,000 -17,600,000 -17,984,000 -18,383,360 -18,798,694 -19,230,642 -19,679,868 Depreciation expense -6,500,000 -6,500,000 -7,856,682 -7,856,682 -7,856,682 -7,856,682 -7,856,682 Net operating income 10,166,730 10,166,730 6,427,318 7,658,678 8,939,292 10,271,132 11,656,244 Interest expense -2,523,020 -2,523,020 -2,600,000 -2,549,847 -2,453,680 -2,307,941 -2,108,865 Earnings before taxes 7,643,710 7,643,710 3,827,318 5,108,831 6,485,612 7,963,191 9,547,379 Taxes (25%) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Pro Forma Balance Sheets Pre-Acquis. Post-Acquis 2010 2011 2012 2013 2014 Curent assets 41,865,867 41,865,867 43,540,502 45,282,122 47,093,407 48,977,143 50,936,229 Gross PP&E 88,164,876 88,164,876 96,021,558 103,878,240 111,734,922 119,591,604 127,448,286 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 -56,738,149 -64,594,831 -72,451,513 -80,308,194 Net PP&E 47,140,091 47,140,091 47,140,091 47,140,091 47,140,091 47,140,091 47,140,092 Goodwill 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 Total 89,005,958 109,825,825 111,500,460 113,242,080 115,053,365 116,937,101 118,896,188 Current liabilities 9,825,826 9,825,826 9,975,651 10,131,469 10,293,520 10,462,053 10,637,327 Long-term debt 36,839,923 40,000,000 39,228,419 37,748,922 35,506,789 32,440,078 28,499,462 Total liabilities 46,665,749 49,825,826 49,204,070 47,880,391 45,800,309 42,902,131 39,136,789 Common stock (par) 290,353 400,000 400,000 400,000 400,000 400,000 400,000 Paid-in capital 20,712,517 59,600,000 59,600,000 59,600,000 59,600,000 59,600,000 59,600,000 Retained earnings 21,337,340 2,296,391 5,361,689 9,253,057 14,030,971 19,759,398 Common equity 42,340,210 60,000,000 62296391 65,361,689 69,253,057 74,030,971 79,759,398 Total 89,005,959 109,825,826 111,500,461 113,242,080 115,053,366 116,933,102 118,896,187 Planning Period Cash Flows Estimates Projected Free Cash Flows 2010 2011 2012 2013 2014 Net operating income 6,427,318 7,658,678 8,939,292 10,271,132 11,656,244 Less: Taxes -1,606,830 -1,914,670 -2,234,823 -2,567,783 -2,914,061 NOPAT 4,820,489 5,744,009 6,704,469 7,703,349 8,742,183 Plus: Depreciation 7,856,682 7,856,682 7,856,682 7,856,682 7,856,682 Less: CAPEX -7,856,682 -7,856,682 -7,856,682 -7,856,682 -7,856,682 Less: Changes in Net Working Capital -1,281,602 -1,332,866 -1,386,180 -1,441,628 -1,499,293 Equals FFCF 3,538,887 4,411,143 5,318,289 6,261,721 7,242,890 Panel b. (Step 1 - continued) Terminal Value Cash Flows Estimate Method #1 - DCF Using the Gordong Growth Model Terminal Value Multiples from the Gordon Model Growth Rates Disc.Rate 0% 1% 2% 3% 8.30% 12.05 13.84 16.19 19.43 8.80% 11.36 12.95 15.00 17.76 9.30% 10.75 12.17 13.97 16.35 9.80% 10.20 11.48 13.08 15.15 Terminal Value Estimates(for FCFs received in 2016 and beyond) Growth Rates Disc.Rate 0% 1% 2% 3% 7.80% 99,557,908 114,327,937 133,786,865 160,588,784 8.80% 93,901,209 106,999,223 123,949,595 146,744,923 9.80% 88,852,757 100,553,487 115,459,897 135,098,501 10.80% 84,319,453 94,840,221 108,058,622 125,164,788 2015 4% 101,225,521 -60,125,016 41,100,505 -20,147,063 -7,856,682 13,096,760 -1,852,465 11,244,295 #VALUE! #VALUE! Enterp. Value EBITDA 5.00 5.50 6.00 6.50 7.00 2011 4,411,143 2012 5,318,289 2013 6,261,721 2014 7,242,890 4,411,143 5,318,289 6,261,721 2015 8,263,306 123,949,595 7,242,890 132,212,902 3,538,887 4,411,143 5,318,289 6,261,721 7,242,890 132,212,902 (100,000,000) 3,538,887 4,411,143 5,318,289 6,261,721 7,242,890 132,212,902 100,035,680 8.80% IRR -0.60 -0.60 -0.21 -0.21 -0.20 -6.75% -6.91% -7.11% -7.40% 20,953,442 -6.63% EBITD A Cost of debt - Estimated borrowing rate is 6.5% with a marginal tax of 25%, resulting in an after-tax cost of debt of 4.875%. Cost of equity - An average industry unlevered equity beta of .89 implies a levered equity beta for GRC of 1.28, assuming target debt ratio of 40% and a debt beta of .30. Using the Capital Asset Pricing model with a 10-year Treasury bond yield of 5.02% and a market risk premium of 5% produces an estimate of the levered cost of equity of 11.42% Weighted average cost of capital (WACC) - Using the target debt-tovalue ratio of 40%, the WACC is approximately 8.8%. (100,035,680) IRR -0.60 -0.21 Using Index and Match 3% 4 Identifies the column number 9.80% 3 Identifies the row number 135,098,501 After identifying the column and rwo numbers extracts the value 3,538,887 Value -0.61 EBITDA * 6 2010 3,538,887 8.81% Obviously, in the first method (which is DCF), when 8.80% is used to discount the cash flows, the IRR is 8.8%. Under Method 2 (terminal value is a multiple), the IRR is lower than the cost of capital. 2014 -0.22 2015 13,096,760 -3,274,190 9,822,570 7,856,682 -7,856,682 -1,559,264 8,263,306 (Method 2) ? Enter 1 cell C97 to use the first amount as initial investment and look at the IRR. Enter 2 to use the second amount and look at the IRR. 2013 -0.61 2015 52,973,678 135,304,968 -88,164,876 47,140,092 20,819,867 120,933,637 10,819,613 23,608,049 34,427,662 400,000 59,600,000 26,505,976 86,505,976 120,933,638 What is the IRR if the amount paid is $100,035,680 (Method 1) ? What is the IRR if the amount pais is $ 101,103,406 2009 2012 Terminal Value 104,767,212 115,243,934 125,720,655 136,197,376 146,674,097 Panel d. (Step 3) Calculate the Present Value of Future Cash Flows PV of Expected Cash Flows Discount Plan.Period Terminal Value Enterprise Value Rate Firm FCF Method 1 Method 2 Method 1 Method 2 123,949,595 125,720,655 7.80% 26,201,697 78,982,804 80,111,354 105,184,501 106,313,050 8.80% 25,309,679 74,726,001 75,793,727 100,035,680 101,103,406 9.80% 24,461,568 70,734,465 71,745,158 95,196,033 96,206,726 1 6.00 8.80% 2.00% 2011 Method #2 - Multiples Using Enterprise Value to EBITDA Ratio Terminal Value Estimate Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Cost of debt 6.50% Weights Tax rate 25.00% Debt 40% After-tax cost of debt 4.875% Equity 60% Risk-free rate 5.02% GRC beta 1.28 Calculation of Levered Equity Beta (Cha 5) Market risk premium 5.00% Avg unlevered equity beta 0.89 Cost of equity 11.420% Debt beta 0.30 WACC 8.8000% Levered equity beta 1.283 Method Multiple Disc. Rate Long-Term g 2010 2015 The value of GRC is estimated in these tables. First, the income statement and balance sheet are projected. Then FCF is calculated. Finally, the cost of equity is calculated and a value is estimated. Two methods are used to estimate the terminal value: the Gordon model and an EBITDA multiple. The resulting values are similar. Table 9-2 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Status Quo Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pro Forma Income Statements Pre-Acquis. Post-Acqu 2009 2009 2010 2011 2012 2013 4% 4% 4% 4% Revenues 80,000,000 80,000,000 83,200,000 86,528,000 89,989,120 93,588,685 COGS -45,733,270 -45,733,270 -50,932,000 -52,629,280 -54,394,452 -56,230,229 Gross Profit 34,266,730 34,266,730 32,268,000 33,898,720 35,594,668 37,358,456 G & AE -17,600,000 -17,600,000 -17,984,000 -18,383,360 -18,798,694 -19,230,642 Depreciation expense -6,500,000 -6,500,000 -7,856,682 -7,856,682 -7,856,682 -7,856,682 Net operating income 10,166,730 10,166,730 6,427,318 7,658,678 8,939,292 10,271,132 Interest expense -2,523,020 -2,523,020 -2,600,000 -2,549,847 -2,453,680 -2,307,941 Earnings before taxes 7,643,710 7,643,710 3,827,318 5,108,831 6,485,612 7,963,191 Taxes (25%) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Post-Acquis Pro Forma Balance Sheets Pre-Acquis. 2010 2011 2012 2013 Curent assets 41,865,867 41,865,867 43,540,502 45,282,122 47,093,407 48,977,143 Gross PP&E 88,164,876 88,164,876 96,021,558 103,878,240 111,734,922 119,591,604 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 -56,738,149 -64,594,831 -72,451,513 Net PP&E 47,140,091 47,140,091 47,140,091 47,140,091 47,140,091 47,140,091 Goodwill 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 Total 89,005,958 109,825,825 111,500,460 113,242,080 115,053,365 116,937,101 Current liabilities 9,825,826 9,825,826 9,975,651 10,131,469 10,293,520 10,462,053 Long-term debt 36,839,923 40,000,000 39,228,419 37,748,922 35,506,789 32,440,078 Total liabilities 46,665,749 49,825,826 49,204,070 47,880,391 45,800,309 42,902,131 Common stock (par) 290,353 400,000 400,000 400,000 400,000 400,000 Paid-in capital 20,712,517 59,600,000 59,600,000 59,600,000 59,600,000 59,600,000 Retained earnings 21,337,340 2,296,391 5,361,689 9,253,057 14,030,971 Common equity 42,340,210 60,000,000 62296391 65,361,689 69,253,057 74,030,971 Total 89,005,959 109,825,826 111,500,461 113,242,080 115,053,366 116,933,102 Planning Period Cash Flows Estimates Projected Free Cash Flows 2010 2011 2012 2013 Net operating income 6,427,318 7,658,678 8,939,292 10,271,132 Less: Taxes -1,606,830 -1,914,670 -2,234,823 -2,567,783 NOPAT 4,820,489 5,744,009 6,704,469 7,703,349 Plus: Depreciation 7,856,682 7,856,682 7,856,682 7,856,682 Less: CAPEX -7,856,682 -7,856,682 -7,856,682 -7,856,682 Less: Changes in Net Working Capital -1,281,602 -1,332,866 -1,386,180 -1,441,628 Equals FFCF 3,538,887 4,411,143 5,318,289 6,261,721 2014 2015 4% 4% 97,332,232 101,225,521 -58,139,438 -60,125,016 39,192,794 41,100,505 -19,679,868 -20,147,063 -7,856,682 -7,856,682 11,656,244 13,096,760 -2,108,865 -1,852,465 9,547,379 11,244,295 #VALUE! #VALUE! #VALUE! #VALUE! 2014 50,936,229 127,448,286 -80,308,194 47,140,092 20,819,867 118,896,188 10,637,327 28,499,462 39,136,789 400,000 59,600,000 19,759,398 79,759,398 118,896,187 2011 2012 -0.61 -0.60 -0.60 -0.60 -0.21 -0.21 -0.21 -0.20 -6.75% -6.91% -7.11% -7.40% 20,953,442 -6.63% EBITD A 2015 13,096,760 -3,274,190 9,822,570 7,856,682 -7,856,682 -1,559,264 8,263,306 Cost of debt - Estimated borrowing rate is 6.5% with a marginal tax of 25%, resulting in an after-tax cost of debt of 4.875%. Cost of equity - An average industry unlevered equity beta of .89 implies a levered equity beta for GRC of 1.28, assuming target debt ratio of 40% and a debt beta of .30. Using the Capital Asset Pricing model with a 10-year Treasury bond yield of 5.02% and a market risk premium of 5% produces an estimate of the levered cost of equity of 11.42% Weighted average cost of capital (WACC) - Using the target debtto-value ratio of 40%, the WACC is approximately 8.8%. What is the IRR if the amount paid is $100,035,680 (Method 1) ? What is the IRR if the amount pais is $ 101,103,406 (Method 2) ? Enter 1 cell C97 to use the first amount as initial investment and look at the IRR. Enter 2 to use the second amount and look at the IRR. 1 6.00 8.80% 2.00% 2009 2012 5,318,289 2013 6,261,721 2014 7,242,890 4,411,143 5,318,289 6,261,721 7,242,890 2015 8,263,306 123,949,595 No need for intermediate tables calculating 132,212,902 terminal value 3,538,887 4,411,143 5,318,289 6,261,721 7,242,890 132,212,902 (100,000,000) 3,538,887 4,411,143 5,318,289 6,261,721 7,242,890 132,212,902 100,035,680 8.80% IRR 2011 4,411,143 (100,035,680) IRR 2010 3,538,887 3,538,887 Value 8.81% Obviously, in the first method (which is DCF), when 8.80% is used to discount the cash flows, the IRR is 8.8%. Under Method 2 (terminal value is a multiple), the IRR is lower than the cost of capital. 2014 -0.22 Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Cost of debt 6.50% Weights Tax rate 25.00% Debt 40% After-tax cost of debt 4.875% Equity 60% Risk-free rate 5.02% GRC beta 1.28 Calculation of Levered Equity Beta (Cha 5) Market risk premium 5.00% Avg unlevered equity beta 0.89 Cost of equity 11.420% Debt beta 0.30 WACC 8.8000% Levered equity beta 1.283 Method Multiple Disc. Rate Long-Term g 2013 -0.61 2015 52,973,678 135,304,968 -88,164,876 47,140,092 20,819,867 120,933,637 10,819,613 23,608,049 34,427,662 400,000 59,600,000 26,505,976 86,505,976 120,933,638 2014 11,656,244 -2,914,061 8,742,183 7,856,682 -7,856,682 -1,499,293 7,242,890 2010 2015 Valuing GRC under the Growth Strategy. By spending an additional $3 million in advertising and during 2010 - 2012, plus an additional $1 million per year on capital equipment during 2010 - 2015, Immersion analysists expect growth to increase from 4% to 8%. This action would definetely increase the value of GRC. However, since this strategy is riskier, a 12% discount rate is used. As multiple (to value the terminal value) a more conservative multiple is used. As a result, the value goes under the $100 million paid. The text mentions that NPV is still positive, but that is not shown in the tables. Table 9-3 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Growth Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pre-Acquis. Post-Acquis. 2009 2009 2010 8% Revenues 80,000,000 80,000,000 86,400,000 COGS -45,733,270 -45,733,270 -52,564,000 Gross Profit 34,266,730 34,266,730 33,836,000 G & AE -17,600,000 -17,600,000 -21,368,000 Depreciation expense -6,500,000 -6,500,000 -7,856,682 Net operating income 10,166,730 10,166,730 4,611,318 Interest expense -2,523,020 -2,523,020 -2,600,000 Earnings before taxes 7,643,710 7,643,710 2,011,318 Taxes (25%) #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! Post-Acquis Pre-Acquis. 2010 Curent assets 41,865,867 41,865,867 45,215,137 Gross PP&E 88,164,876 88,164,876 97,021,558 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 Net PP&E 47,140,091 47,140,091 48,140,091 Goodwill 20,819,867 20,819,867 Total 89,005,958 109,825,825 114,175,095 Current liabilities 9,825,826 9,825,826 10,214,944 Long-term debt 36,839,923 40,000,000 42,753,361 Total liabilities 46,665,749 49,825,826 52,968,305 Common stock (par) 290,353 340,353 340,353 Paid-in capital 20,712,517 38,322,307 38,322,307 Retained earnings 21,337,340 22,544,131 Common equity 42,340,210 38,662,660 61,206,791 Total 89,005,959 88,488,486 114,175,096 Planning Period Cash Flows Estimates 2014 8% 117,546,246 -68,448,586 49,097,660 -22,105,550 -8,523,349 18,468,761 -2,737,729 15,731,032 #VALUE! #VALUE! 2015 8% 126,949,946 -73,244,472 53,705,474 -23,233,994 -8,690,015 21,781,465 -2,453,086 19,328,379 #VALUE! #VALUE! 2011 48,832,347 106,044,907 -56,904,815 49,140,092 20,819,867 118,792,306 10,628,033 44,423,608 55,051,641 340,353 38,322,307 25,078,005 63,740,665 118,792,306 Pro Forma Income Statements 2012 2013 8% 8% 100,776,960 108,839,117 -59,896,250 -64,007,950 40,880,710 44,831,167 -23,093,235 -21,060,694 -8,190,015 -8,356,682 9,597,460 15,413,791 -2,887,535 -2,916,242 6,709,925 12,497,549 #VALUE! #VALUE! #VALUE! #VALUE! Pro Forma Balance Sheets 2012 2013 52,738,935 56,958,050 115,234,922 124,591,604 -65,094,831 -73,451,513 50,140,091 51,140,091 20,819,867 20,819,867 123,698,893 128,918,008 11,067,013 11,533,953 44,865,262 42,118,906 55,932,275 53,652,859 340,353 340,353 38,322,307 38,322,307 29,103,960 36,602,489 67,766,620 75,265,149 123,698,895 128,918,008 2014 62,514,694 134,114,953 -81,974,861 52,140,092 20,819,867 135,474,653 12,031,091 37,739,792 49,770,883 340,353 38,322,307 46,041,110 84,703,770 134,474,653 2015 66,435,870 142,804,968 -90,664,876 52,140,092 20,819,867 139,395,829 12,471,376 30,623,656 43,095,032 340,353 38,322,307 57,638,136 96,300,796 139,395,828 2,010 4,611,318 -1,152,830 3,458,489 7,856,682 -8,856,682 -2,563,203 -104,715 2,011 7,002,091 -1,750,523 5,251,568 8,023,349 -9,023,349 -2,768,260 1,483,308 Projected Free Cash Flows 2,012 2,013 9,597,460 15,413,791 -2,399,365 -3,853,448 7,198,095 11,560,343 8,190,015 8,356,682 -9,190,015 -9,356,682 -2,989,720 -3,228,898 3,208,375 7,331,445 2,014 18,468,761 -4,617,190 13,851,571 8,523,349 -9,523,349 -3,487,210 9,364,361 2,015 21,781,465 -5,445,366 16,336,099 8,690,015 -8,690,015 -3,766,187 12,569,912 2010 -104,715 2011 1,483,308 2012 3,208,375 2013 7,331,445 2014 9,364,361 -104,715 1,483,308 3,208,375 7,331,445 9,364,361 2015 12,569,912 182,828,879 No need for intermediate tables calculating 195,398,791 terminal value (132,823,245) -104,715 1,483,308 3,208,375 7,331,445 9,364,361 195,398,791 (100,000,000) -104,715 1,483,308 3,208,375 7,331,445 9,364,361 195,398,791 Net operating income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Changes in Net Working Capital Equals FFCF Method Multiple Disc. Rate Long-Term g 2 6.00 8.80% 2.00% 2009 Value IRR 14.28% 132,823,245 8.80% IRR 2011 8% 93,312,000 -56,089,120 37,222,880 -22,197,440 -8,023,349 7,002,091 -2,778,968 4,223,123 #VALUE! #VALUE! Sensitivity analysis (Risk analysis). The text mentions that three value drivers are tested. (1) Cost of capital; (2) Growth rate of sales; and (3) Terminal value multiple. This differs from the value drivers we have been using in class. Capital requirements and oerating profitability are not used. Instead the multiple is referred as a value driver. In reality, any variable that impacts FCF can be called a value driver. In this case, since the terminal value is an important component of the FCF (its PV can be an important component of the value of a firm), we will also consider it a value driver. Go to cell A71. Table 9-3 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Growth Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pro Forma Income Statements Pre-Acquis. Post-Acquis. 2009 2009 2010 2011 2012 2013 8% 8% 8% 8% Revenues 80,000,000 80,000,000 86,400,000 93,312,000 100,776,960 108,839,117 COGS -45,733,270 -45,733,270 -52,564,000 -56,089,120 -59,896,250 -64,007,950 Gross Profit 34,266,730 34,266,730 33,836,000 37,222,880 40,880,710 44,831,167 G & AE -17,600,000 -17,600,000 -21,368,000 -22,197,440 -23,093,235 -21,060,694 Depreciation expense -6,500,000 -6,500,000 -7,856,682 -8,023,349 -8,190,015 -8,356,682 Net operating income 10,166,730 10,166,730 4,611,318 7,002,091 9,597,460 15,413,791 Interest expense -2,523,020 -2,523,020 -2,600,000 -2,778,968 -2,887,535 -2,916,242 Earnings before taxes 7,643,710 7,643,710 2,011,318 4,223,123 6,709,925 12,497,549 Taxes (25%) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Post-Acquis Pro Forma Balance Sheets Pre-Acquis. 2010 2011 2012 2013 Curent assets 41,865,867 41,865,867 45,215,137 48,832,347 52,738,935 56,958,050 Gross PP&E 88,164,876 88,164,876 97,021,558 106,044,907 115,234,922 124,591,604 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 -56,904,815 -65,094,831 -73,451,513 Net PP&E 47,140,091 47,140,091 48,140,091 49,140,092 50,140,091 51,140,091 Goodwill 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 Total 89,005,958 109,825,825 114,175,095 118,792,306 123,698,893 128,918,008 Current liabilities 9,825,826 9,825,826 10,214,944 10,628,033 11,067,013 11,533,953 Long-term debt 36,839,923 40,000,000 42,753,361 44,423,608 44,865,262 42,118,906 Total liabilities 46,665,749 49,825,826 52,968,305 55,051,641 55,932,275 53,652,859 Common stock (par) 290,353 340,353 340,353 340,353 340,353 340,353 Paid-in capital 20,712,517 38,322,307 38,322,307 38,322,307 38,322,307 38,322,307 Retained earnings 21,337,340 22,544,131 25,078,005 29,103,960 36,602,489 Common equity 42,340,210 38,662,660 61,206,791 63,740,665 67,766,620 75,265,149 Total 89,005,959 88,488,486 114,175,096 118,792,306 123,698,895 128,918,008 Planning Period Cash Flows Estimates Projected Free Cash Flows 2,010 2,011 2,012 2,013 Net operating income 4,611,318 7,002,091 9,597,460 15,413,791 Less: Taxes -1,152,830 -1,750,523 -2,399,365 -3,853,448 NOPAT 3,458,489 5,251,568 7,198,095 11,560,343 Plus: Depreciation 7,856,682 8,023,349 8,190,015 8,356,682 Less: CAPEX -8,856,682 -9,023,349 -9,190,015 -9,356,682 Less: Changes in Net Working Capital -2,563,203 -2,768,260 -2,989,720 -3,228,898 Equals FFCF -104,715 1,483,308 3,208,375 7,331,445 Panel b. (Step 1 - continued) Terminal Value Cash Flows Estimate Method #1 - DCF Using the Gordong Growth Model Terminal Value Estimates (for FCF received in 2016 and beyond) Growth Rates Disc.Rate 0% 1% 2% 3% 7.80% 161,152,713 186,700,158 221,057,067 269,729,354 12.00% 104,749,264 115,414,643 128,213,099 143,855,655 9.80% 128,264,404 144,268,304 164,375,767 190,397,191 10.80% 116,388,071 129,547,048 145,696,703 165,987,295 2014 8% 117,546,246 -68,448,586 49,097,660 -22,105,550 -8,523,349 18,468,761 -2,737,729 15,731,032 #VALUE! #VALUE! 2015 8% 126,949,946 -73,244,472 53,705,474 -23,233,994 -8,690,015 21,781,465 -2,453,086 19,328,379 #VALUE! #VALUE! 2014 62,514,694 134,114,953 -81,974,861 52,140,092 20,819,867 135,474,653 12,031,091 37,739,792 49,770,883 340,353 38,322,307 46,041,110 84,703,770 134,474,653 2015 66,435,870 142,804,968 -90,664,876 52,140,092 20,819,867 139,395,829 12,471,376 30,623,656 43,095,032 340,353 38,322,307 57,638,136 96,300,796 139,395,828 2,014 18,468,761 -4,617,190 13,851,571 8,523,349 -9,523,349 -3,487,210 9,364,361 2,015 21,781,465 -5,445,366 16,336,099 8,690,015 -8,690,015 -3,766,187 12,569,912 Method #2 - Multiples Using Enterprise Value to EBITDA Ratio Terminal Value Estimate Enterp. Value Terminal EBITDA Value 5.00 152,357,399 5.50 167,593,139 6.00 182,828,879 6.50 198,064,619 7.00 213,300,359 Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Based on the analysis presented in Panel c of Table 9-2, GRC's WACC is estimated to be 8.8% Panel d. (Step 3) Calculate the Present Value of Future Cash Flows PV of expected Cash Flows Discount Plan.Period Terminal Value Enterprise Value Rate Firm FCF Method 1 Method 2 Method 1 Method 2 12.00% 19,713,811 64,956,746 77,189,000 84,670,557 96,902,811 Sensitivity to Cost of Capital We first find the IRR at the original $100,000,000 value using the new FCF obtained with 8% growth rate. -100,000,000 1,483,308 3,208,375 7,331,445 9,364,361 -100,000,000 IRR -104,715 -104,715 1,483,308 3,208,375 7,331,445 9,364,361 12,569,912 182,828,879 195,398,791 14.283% The result is 14.28%. This means that to get a negative NPV, a discount rate rate higher than 14.28% will be needed. Using the information with which WACC was estimated, it will require an average unlevered equity beta of 1.98 and a levered equity beta of 3.11. Enter 2 in cell H86. The equity betas (unlevered and levered) will change. Correspondingly, WACC will be 14.28% and NPV will be 0. To get a WACC of 14.28%, the levered beta will have to be higher than 3.11 (indicating a high risk) for the firm to show a negative NPV. Hence, this strategy is not sensitive to the cost of capital. Cost of debt Tax rate After-tax cost of debt Risk-free rate GRC beta Market risk premium Cost of equity WACC 6.50% 25.00% 4.875% 5.02% 3.11 5.00% 20.555% 14.2830% Weights Debt Equity 2 40% 60% Calculation of Levered Equity Beta (Cha 5) Avg unlevered equity beta 1.98 Debt beta 0.30 Levered equity beta 3.11 Sensitivity to Revenue growth rate. Go to cell A71. Table 9-3 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Growth Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pro Forma Income Statements Pre-Acquis. Post-Acquis. 2009 2009 2010 2011 2012 2013 8.00% 8.00% 8.00% 8.00% Revenues 80,000,000 80,000,000 86,400,000 93,312,000 100,776,960 108,839,117 COGS -45,733,270 -45,733,270 -52,564,000 -56,089,120 -59,896,250 -64,007,950 Gross Profit 34,266,730 34,266,730 33,836,000 37,222,880 40,880,710 44,831,167 G & AE -17,600,000 -17,600,000 -21,368,000 -22,197,440 -23,093,235 -21,060,694 Depreciation expense -6,500,000 -6,500,000 -7,856,682 -8,023,349 -8,190,015 -8,356,682 Net operating income 10,166,730 10,166,730 4,611,318 7,002,091 9,597,460 15,413,791 Interest expense -2,523,020 -2,523,020 -2,600,000 -2,778,968 -2,887,535 -2,916,242 Earnings before taxes 7,643,710 7,643,710 2,011,318 4,223,123 6,709,925 12,497,549 Taxes (25%) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Post-Acquis Pro Forma Balance Sheets Pre-Acquis. 2010 2011 2012 2013 Curent assets 41,865,867 41,865,867 45,215,137 48,832,347 52,738,935 56,958,050 Gross PP&E 88,164,876 88,164,876 97,021,558 106,044,907 115,234,922 124,591,604 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 -56,904,815 -65,094,831 -73,451,513 Net PP&E 47,140,091 47,140,091 48,140,091 49,140,092 50,140,091 51,140,091 Goodwill 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 Total 89,005,958 109,825,825 114,175,095 118,792,306 123,698,893 128,918,008 Current liabilities 9,825,826 9,825,826 10,214,944 10,628,033 11,067,013 11,533,953 Long-term debt 36,839,923 40,000,000 42,753,361 44,423,608 44,865,262 42,118,906 Total liabilities 46,665,749 49,825,826 52,968,305 55,051,641 55,932,275 53,652,859 Common stock (par) 290,353 340,353 340,353 340,353 340,353 340,353 Paid-in capital 20,712,517 38,322,307 38,322,307 38,322,307 38,322,307 38,322,307 Retained earnings 21,337,340 22,544,131 25,078,005 29,103,960 36,602,489 Common equity 42,340,210 38,662,660 61,206,791 63,740,665 67,766,620 75,265,149 Total 89,005,959 88,488,486 114,175,096 118,792,306 123,698,895 128,918,008 Planning Period Cash Flows Estimates Projected Free Cash Flows 2,010 2,011 2,012 2,013 Net operating income 4,611,318 7,002,091 9,597,460 15,413,791 Less: Taxes -1,152,830 -1,750,523 -2,399,365 -3,853,448 NOPAT 3,458,489 5,251,568 7,198,095 11,560,343 Plus: Depreciation 7,856,682 8,023,349 8,190,015 8,356,682 Less: CAPEX -8,856,682 -9,023,349 -9,190,015 -9,356,682 Less: Changes in Net Working Capital -2,563,203 -2,768,260 -2,989,720 -3,228,898 Equals FFCF -104,715 1,483,308 3,208,375 7,331,445 Panel b. (Step 1 - continued) Terminal Value Cash Flows Estimate Method #1 - DCF Using the Gordong Growth Model Terminal Value Estimates (for FCF received in 2016 and beyond) Growth Rates Disc.Rate 0% 1% 2% 3% 7.80% 161,152,713 186,700,158 221,057,067 269,729,354 8.80% 142,839,905 162,764,240 188,548,674 223,224,293 9.80% 128,264,404 144,268,304 164,375,767 190,397,191 10.80% 116,388,071 129,547,048 145,696,703 165,987,295 2014 8.00% 117,546,246 -68,448,586 49,097,660 -22,105,550 -8,523,349 18,468,761 -2,737,729 15,731,032 #VALUE! #VALUE! 2015 8.00% 126,949,946 -73,244,472 53,705,474 -23,233,994 -8,690,015 21,781,465 -2,453,086 19,328,379 #VALUE! #VALUE! 2014 62,514,694 134,114,953 -81,974,861 52,140,092 20,819,867 135,474,653 12,031,091 37,739,792 49,770,883 340,353 38,322,307 46,041,110 84,703,770 134,474,653 2015 66,435,870 142,804,968 -90,664,876 52,140,092 20,819,867 139,395,829 12,471,376 30,623,656 43,095,032 340,353 38,322,307 57,638,136 96,300,796 139,395,828 2,014 18,468,761 -4,617,190 13,851,571 8,523,349 -9,523,349 -3,487,210 9,364,361 2,015 21,781,465 -5,445,366 16,336,099 8,690,015 -8,690,015 -3,766,187 12,569,912 Method #2 - Multiples Using Enterprise Value to EBITDA Ratio Terminal Value Estimate Enterp. Value Terminal EBITDA Value 5.00 152,357,399 5.50 167,593,139 6.00 182,828,879 6.50 198,064,619 7.00 213,300,359 Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Based on the analysis presented in Panel c of Table 9-2, GRC's WACC is estimated to be 8.8% Panel d. (Step 3) Calculate the Present Value of Future Cash Flows PV of expected Cash Flows Discount Plan.Period Terminal Value Enterprise Value Rate Firm FCF Method 1 Method 2 Method 1 Method 2 7.80% 23,611,661 120,146,443 116,501,692 143,758,104 140,113,352 8.80% 22,600,449 113,671,113 110,222,796 136,271,562 132,823,245 9.80% 21,643,699 107,599,299 104,335,177 129,242,998 125,978,875 Sensitivity to Revenue Growth Rate Using Goal Seek, we find that NPV becomes 0 when the growth rate = 7.09%. Enter 2 in cell D76. The NPV becomes 0. Growth rate 1 8.00% -100,000,000 14.283% 32,830,307 1,483,308 3,208,375 7,331,445 9,364,361 -100,000,000 IRR NPV -104,715 -104,715 1,483,308 3,208,375 7,331,445 9,364,361 12,569,912 182,828,879 195,398,791 Sensitivity to Terminal-Value EBITDA Multiple. Go to cell A71. Table 9-3 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Growth Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pro Forma Income Statements Pre-Acquis. Post-Acquis. 2009 2009 2010 2011 2012 2013 8.00% 8.00% 8.00% 8.00% Revenues 80,000,000 80,000,000 86,400,000 93,312,000 100,776,960 108,839,117 COGS -45,733,270 -45,733,270 -52,564,000 -56,089,120 -59,896,250 -64,007,950 Gross Profit 34,266,730 34,266,730 33,836,000 37,222,880 40,880,710 44,831,167 G & AE -17,600,000 -17,600,000 -21,368,000 -22,197,440 -23,093,235 -21,060,694 Depreciation expense -6,500,000 -6,500,000 -7,856,682 -8,023,349 -8,190,015 -8,356,682 Net operating income 10,166,730 10,166,730 4,611,318 7,002,091 9,597,460 15,413,791 Interest expense -2,523,020 -2,523,020 -2,600,000 -2,778,968 -2,887,535 -2,916,242 Earnings before taxes 7,643,710 7,643,710 2,011,318 4,223,123 6,709,925 12,497,549 Taxes (25%) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Post-Acquis Pro Forma Balance Sheets Pre-Acquis. 2010 2011 2012 2013 Curent assets 41,865,867 41,865,867 45,215,137 48,832,347 52,738,935 56,958,050 Gross PP&E 88,164,876 88,164,876 97,021,558 106,044,907 115,234,922 124,591,604 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 -56,904,815 -65,094,831 -73,451,513 Net PP&E 47,140,091 47,140,091 48,140,091 49,140,092 50,140,091 51,140,091 Goodwill 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 Total 89,005,958 109,825,825 114,175,095 118,792,306 123,698,893 128,918,008 Current liabilities 9,825,826 9,825,826 10,214,944 10,628,033 11,067,013 11,533,953 Long-term debt 36,839,923 40,000,000 42,753,361 44,423,608 44,865,262 42,118,906 Total liabilities 46,665,749 49,825,826 52,968,305 55,051,641 55,932,275 53,652,859 Common stock (par) 290,353 340,353 340,353 340,353 340,353 340,353 Paid-in capital 20,712,517 38,322,307 38,322,307 38,322,307 38,322,307 38,322,307 Retained earnings 21,337,340 22,544,131 25,078,005 29,103,960 36,602,489 Common equity 42,340,210 38,662,660 61,206,791 63,740,665 67,766,620 75,265,149 Total 89,005,959 88,488,486 114,175,096 118,792,306 123,698,895 128,918,008 Planning Period Cash Flows Estimates Projected Free Cash Flows 2,010 2,011 2,012 2,013 Net operating income 4,611,318 7,002,091 9,597,460 15,413,791 Less: Taxes -1,152,830 -1,750,523 -2,399,365 -3,853,448 NOPAT 3,458,489 5,251,568 7,198,095 11,560,343 Plus: Depreciation 7,856,682 8,023,349 8,190,015 8,356,682 Less: CAPEX -8,856,682 -9,023,349 -9,190,015 -9,356,682 Less: Changes in Net Working Capital -2,563,203 -2,768,260 -2,989,720 -3,228,898 Equals FFCF -104,715 1,483,308 3,208,375 7,331,445 Panel b. (Step 1 - continued) Terminal Value Cash Flows Estimate Method #1 - DCF Using the Gordong Growth Model Terminal Value Estimates (for FCF received in 2016 and beyond) Growth Rates Disc.Rate 0% 1% 2% 3% 7.80% 161,152,713 186,700,158 221,057,067 269,729,354 8.80% 142,839,905 162,764,240 188,548,674 223,224,293 9.80% 128,264,404 144,268,304 164,375,767 190,397,191 10.80% 116,388,071 129,547,048 145,696,703 165,987,295 2014 8.00% 117,546,246 -68,448,586 49,097,660 -22,105,550 -8,523,349 18,468,761 -2,737,729 15,731,032 #VALUE! #VALUE! 2015 8.00% 126,949,946 -73,244,472 53,705,474 -23,233,994 -8,690,015 21,781,465 -2,453,086 19,328,379 #VALUE! #VALUE! 2014 62,514,694 134,114,953 -81,974,861 52,140,092 20,819,867 135,474,653 12,031,091 37,739,792 49,770,883 340,353 38,322,307 46,041,110 84,703,770 134,474,653 2015 66,435,870 142,804,968 -90,664,876 52,140,092 20,819,867 139,395,829 12,471,376 30,623,656 43,095,032 340,353 38,322,307 57,638,136 96,300,796 139,395,828 2,014 18,468,761 -4,617,190 13,851,571 8,523,349 -9,523,349 -3,487,210 9,364,361 2,015 21,781,465 -5,445,366 16,336,099 8,690,015 -8,690,015 -3,766,187 12,569,912 Method #2 - Multiples Using Enterprise Value to EBITDA Ratio Terminal Value Estimate Enterp. Value Terminal EBITDA Value 5.00 152,357,399 5.50 167,593,139 6.00 182,828,879 6.50 198,064,619 7.00 213,300,359 Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Based on the analysis presented in Panel c of Table 9-2, GRC's WACC is estimated to be 8.8% Panel d. (Step 3) Calculate the Present Value of Future Cash Flows PV of expected Cash Flows Discount Plan.Period Terminal Value Enterprise Value Rate Firm FCF Method 1 Method 2 Method 1 Method 2 7.80% 23,611,661 120,146,443 116,501,692 143,758,104 140,113,352 8.80% 22,600,449 113,671,113 110,222,796 136,271,562 132,823,245 9.80% 21,643,699 107,599,299 104,335,177 129,242,998 125,978,875 Sensitivity to Terminal-Value EBITDA Multiple Using Goal Seek, we find that NPV becomes 0 when the multiple = 4.21. Enter 2 in cell D76. The NPV becomes 0. EBITDA Mult. 1 6.00 -100,000,000 14.283% 32,830,307 1,483,308 3,208,375 7,331,445 9,364,361 -100,000,000 IRR NPV -104,715 -104,715 1,483,308 3,208,375 7,331,445 9,364,361 12,569,912 182,828,879 195,398,791 Sensitivity to Terminal-Value EBITDA Multiple. Go to cell A71. Table 9-3 Estimating GRC's Enterprise Value Using DCF Ana;ysis (Growth Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Post-Acquis. Pre-Acquis. 2009 2009 Revenues COGS Gross Profit G & AE Depreciation expense Net operating income Interest expense Earnings before taxes Taxes (25%) Net income 80,000,000 -45,733,270 34,266,730 -17,600,000 -6,500,000 10,166,730 -2,523,020 7,643,710 #VALUE! #VALUE! Pre-Acquis. Curent assets Gross PP&E Less:acumulated depreciation Net PP&E Goodwill Total Current liabilities Long-term debt Total liabilities Common stock (par) Paid-in capital Retained earnings Common equity Total Planning Period Cash Flows Estimates 80,000,000 -45,733,270 34,266,730 -17,600,000 -6,500,000 10,166,730 -2,523,020 7,643,710 #VALUE! #VALUE! Post-Acquis 41,865,867 88,164,876 -41,024,785 47,140,091 41,865,867 88,164,876 -41,024,785 47,140,091 20,819,867 109,825,825 9,825,826 40,000,000 49,825,826 340,353 38,322,307 38,662,660 88,488,486 2010 8.00% 86,400,000 -52,564,000 33,836,000 -21,368,000 -7,856,682 4,611,318 -2,600,000 2,011,318 #VALUE! #VALUE! 89,005,958 9,825,826 36,839,923 46,665,749 290,353 20,712,517 21,337,340 42,340,210 89,005,959 2010 45,215,137 97,021,558 -48,881,467 48,140,091 20,819,867 114,175,095 10,214,944 42,753,361 52,968,305 340,353 38,322,307 22,544,131 61,206,791 114,175,096 2,010 4,611,318 -1,152,830 3,458,489 7,856,682 -8,856,682 -2,563,203 -104,715 Net operating income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Changes in Net Working Capital Equals FFCF Panel b. (Step 1 - continued) Terminal Value Cash Flows Estimate Method #1 - DCF Using the Gordong Growth Model Terminal Value Estimates (for FCF received in 2016 and beyond) Growth Rates Disc.Rate 0% 1% 2% 7.80% 161,152,713 186,700,158 221,057,067 8.80% 142,839,905 162,764,240 188,548,674 9.80% 128,264,404 144,268,304 164,375,767 10.80% 116,388,071 129,547,048 145,696,703 Pro Forma Income Statements 2011 2012 2013 8.00% 8.00% 8.00% 93,312,000 100,776,960 108,839,117 -56,089,120 -59,896,250 -64,007,950 37,222,880 40,880,710 44,831,167 -22,197,440 -23,093,235 -21,060,694 -8,023,349 -8,190,015 -8,356,682 7,002,091 9,597,460 15,413,791 -2,778,968 -2,887,535 -2,916,242 4,223,123 6,709,925 12,497,549 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Pro Forma Balance Sheets 2011 2012 2013 48,832,347 52,738,935 56,958,050 106,044,907 115,234,922 124,591,604 -56,904,815 -65,094,831 -73,451,513 49,140,092 50,140,091 51,140,091 20,819,867 20,819,867 20,819,867 118,792,306 123,698,893 128,918,008 10,628,033 11,067,013 11,533,953 44,423,608 44,865,262 42,118,906 55,051,641 55,932,275 53,652,859 340,353 340,353 340,353 38,322,307 38,322,307 38,322,307 25,078,005 29,103,960 36,602,489 63,740,665 67,766,620 75,265,149 118,792,306 123,698,895 128,918,008 2,011 7,002,091 -1,750,523 5,251,568 8,023,349 -9,023,349 -2,768,260 1,483,308 Projected Free Cash Flows 2,012 2,013 9,597,460 15,413,791 -2,399,365 -3,853,448 7,198,095 11,560,343 8,190,015 8,356,682 -9,190,015 -9,356,682 -2,989,720 -3,228,898 3,208,375 7,331,445 2014 2015 8.00% 8.00% 117,546,246 126,949,946 -68,448,586 -73,244,472 49,097,660 53,705,474 -22,105,550 -23,233,994 -8,523,349 -8,690,015 18,468,761 21,781,465 -2,737,729 -2,453,086 15,731,032 19,328,379 #VALUE! #VALUE! #VALUE! #VALUE! 2014 62,514,694 134,114,953 -81,974,861 52,140,092 20,819,867 135,474,653 12,031,091 37,739,792 49,770,883 340,353 38,322,307 46,041,110 84,703,770 134,474,653 2015 66,435,870 142,804,968 -90,664,876 52,140,092 20,819,867 139,395,829 12,471,376 30,623,656 43,095,032 340,353 38,322,307 57,638,136 96,300,796 139,395,828 2,014 18,468,761 -4,617,190 13,851,571 8,523,349 -9,523,349 -3,487,210 9,364,361 2,015 21,781,465 -5,445,366 16,336,099 8,690,015 -8,690,015 -3,766,187 12,569,912 Method #2 - Multiples Using Enterprise Value to EBITDA Ratio Terminal Value Estimate Enterp. Value Terminal EBITDA Value 5.00 152,357,399 5.50 167,593,139 4.57 139,140,928 6.50 198,064,619 7.00 213,300,359 3% 269,729,354 223,224,293 190,397,191 165,987,295 Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Based on the analysis presented in Panel c of Table 9-2, GRC's WACC is estimated to be 8.8% Panel d. (Step 3) Calculate the Present Value of Future Cash Flows PV of expected Cash Flows Discount Plan.Period Terminal Value Enterprise Value Rate Firm FCF Method 1 Method 2 Method 1 Method 2 7.80% 23,611,661 120,146,443 88,662,981 143,758,104 112,274,642 10.00% 21,458,574 106,430,811 78,541,426 127,889,385 100,000,000 9.80% 21,643,699 107,599,299 79,403,721 129,242,998 101,047,419 Scenario Analysis Enter a scenario number in cell C77. Observe how NPV changes. You can also inspect the cells where the different values for the variables are shown: A68 for cost of capital, F11 for revenue growth, and G57 for EBITDA Multiple Cost of Capit. Reven. Growth EBITDA Mult. Enterp.Value NPV 2 Initial Scenarios Negative NPV 1 2 3 4 5 8.80% 10.00% 10.00% 14.28% 10.00% 8.00% 8.00% 7.28% 8.00% 6.00% 6.00 4.57 6.00 6.00 5.50 100,000,000 100,000,000 100,000,000 100,000,000 -0.00 0.00 0.00 0.00 -- -100,000,000 10.000% - 1,483,308 3,208,375 7,331,445 9,364,361 -100,000,000 IRR NPV -104,715 -104,715 1,483,308 3,208,375 7,331,445 9,364,361 12,569,912 139,140,928 151,710,840 Valuing GRC under the Growth Strategy. By spending an additional $3 million in advertising and during 2010 - 2012, plus an additional $1 million per year on capital equipment during 2010 - 2015, Immersion analysists expect growth to increase from 4% to 8%. This action would definetely increase the value of GRC. However, since this strategy is riskier, a 12% discount rate is used. As multiple (to value the terminal value) a more conservative multiple is used. As a result, the value goes under the $100 million paid. The text mentions that NPV is still positive, but that is not shown in the tables. Table 9-3 Estimating GRC's Enterprise Value Using DCF Analysis (Growth Strategy) Panel a. (Step 1) Estimate the Amount and Timing of the Planning Period Future Cash Flows ($) Pro Forma Income Statements Pre-Acquis. Post-Acquis. 2009 2009 2010 2011 2012 2013 8.00% 8.00% 8.00% 8.00% Revenues 80,000,000 80,000,000 86,400,000 93,312,000 100,776,960 108,839,117 COGS -45,733,270 -45,733,270 -52,564,000 -56,089,120 -59,896,250 -64,007,950 Gross Profit 34,266,730 34,266,730 33,836,000 37,222,880 40,880,710 44,831,167 G & AE -17,600,000 -17,600,000 -21,368,000 -22,197,440 -23,093,235 -21,060,694 Depreciation expense -6,500,000 -6,500,000 -7,856,682 -8,023,349 -8,190,015 -8,356,682 Net operating income 10,166,730 10,166,730 4,611,318 7,002,091 9,597,460 15,413,791 Interest expense -2,523,020 -2,523,020 -2,600,000 -2,778,968 -2,887,535 -2,916,242 Earnings before taxes 7,643,710 7,643,710 2,011,318 4,223,123 6,709,925 12,497,549 Taxes (25%) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Net income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Post-Acquis Pro Forma Balance Sheets Pre-Acquis. 2010 2011 2012 2013 Curent assets 41,865,867 41,865,867 45,215,137 48,832,347 52,738,935 56,958,050 Gross PP&E 88,164,876 88,164,876 97,021,558 106,044,907 115,234,922 124,591,604 Less:acumulated depreciation -41,024,785 -41,024,785 -48,881,467 -56,904,815 -65,094,831 -73,451,513 Net PP&E 47,140,091 47,140,091 48,140,091 49,140,092 50,140,091 51,140,091 Goodwill 20,819,867 20,819,867 20,819,867 20,819,867 20,819,867 Total 89,005,958 109,825,825 114,175,095 118,792,306 123,698,893 128,918,008 Current liabilities 9,825,826 9,825,826 10,214,944 10,628,033 11,067,013 11,533,953 Long-term debt 36,839,923 40,000,000 42,753,361 44,423,608 44,865,262 42,118,906 Total liabilities 46,665,749 49,825,826 52,968,305 55,051,641 55,932,275 53,652,859 Common stock (par) 290,353 340,353 340,353 340,353 340,353 340,353 Paid-in capital 20,712,517 38,322,307 38,322,307 38,322,307 38,322,307 38,322,307 Retained earnings 21,337,340 22,544,131 25,078,005 29,103,960 36,602,489 Common equity 42,340,210 38,662,660 61,206,791 63,740,665 67,766,620 75,265,149 Total 89,005,959 88,488,486 114,175,096 118,792,306 123,698,895 128,918,008 Planning Period Cash Flows Estimates Projected Free Cash Flows 2,010 2,011 2,012 2,013 Net operating income 4,611,318 7,002,091 9,597,460 15,413,791 Less: Taxes -1,152,830 -1,750,523 -2,399,365 -3,853,448 NOPAT 3,458,489 5,251,568 7,198,095 11,560,343 Plus: Depreciation 7,856,682 8,023,349 8,190,015 8,356,682 Less: CAPEX -8,856,682 -9,023,349 -9,190,015 -9,356,682 Less: Changes in Net Working Capital -2,563,203 -2,768,260 -2,989,720 -3,228,898 Equals FFCF -104,715 1,483,308 3,208,375 7,331,445 Panel b. (Step 1 - continued) Terminal Value Cash Flows Estimate Method #1 - DCF Using the Gordong Growth Model Terminal Value Estimates (for FCF received in 2016 and beyond) Growth Rates Disc.Rate 0% 1% 2% 3% 7.80% 161,152,713 186,700,158 221,057,067 269,729,354 12.00% 104,749,264 115,414,643 128,213,099 143,855,655 9.80% 128,264,404 144,268,304 164,375,767 190,397,191 10.80% 116,388,071 129,547,048 145,696,703 165,987,295 2014 8.00% 117,546,246 -68,448,586 49,097,660 -22,105,550 -8,523,349 18,468,761 -2,737,729 15,731,032 #VALUE! #VALUE! 2015 8.00% 126,949,946 -73,244,472 53,705,474 -23,233,994 -8,690,015 21,781,465 -2,453,086 19,328,379 #VALUE! #VALUE! 2014 62,514,694 134,114,953 -81,974,861 52,140,092 20,819,867 135,474,653 12,031,091 37,739,792 49,770,883 340,353 38,322,307 46,041,110 84,703,770 134,474,653 2015 66,435,870 142,804,968 -90,664,876 52,140,092 20,819,867 139,395,829 12,471,376 30,623,656 43,095,032 340,353 38,322,307 57,638,136 96,300,796 139,395,828 2,014 18,468,761 -4,617,190 13,851,571 8,523,349 -9,523,349 -3,487,210 9,364,361 2,015 21,781,465 -5,445,366 16,336,099 8,690,015 -8,690,015 -3,766,187 12,569,912 Method #2 - Multiples Using Enterprise Value to EBITDA Ratio Terminal Value Estimate Enterp. Value Terminal EBITDA Value 5.00 152,357,399 5.50 167,593,139 6.00 182,828,879 6.50 198,064,619 7.00 213,300,359 Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate Based on the analysis presented in Panel c of Table 9-2, GRC's WACC is estimated to be 8.8% Panel c. (Step 2) Estimate a "Risk-Appropiate" Discount Rate FCF are discounted using as discout rate the unlevered cost of equity. This cost is calculated using the unlevered equity beta and the CAPM. The unlevered equity beta is estimated using equation 4-7, shown in page 119. BUnlevered Debt Value B Levered B Debt Equity Value Debt Value 1 Equity Value The authors mention that the equation used is the following (9-7) in page 334, but this is not the case. BUnlevered r B Levered 1 T * d LB Debt 1 rd rd 1 1 T * L 1 rd Once the unlevered equity beta is is estimated for each firm, an average is taken. Compar. Firms A B C D Average Levered Debt/Equity Equity Betas Ratio 1.45 0.65 1.40 0.6 0.88 0.35 1.25 0.8 1.25 0.60 Assumed Unlevered Debt Beta Equity Beta 0.3 1.00 0.3 0.99 0.3 0.73 0.3 0.83 0.30 0.89 This average is then used in calculating the unlevered cost of equity using CAPM. Risk-free rate GRC beta Market risk premium Unlevered cost of equity 5.02% 0.89 5.00% 9.470% The resulting unlevered cost of equity is 9.47%. Table 9-4 Panel a. Unlevered FCF Status Quo strategy Growth strategy Panel b. Interest tax savings Status Quo strategy Growth strategy 2,010 3,538,887 -104,715 2,010 -650,000 -650,000 2,011 4,411,143 1,483,308 2,011 -637,462 -694,742 2,012 5,318,289 3,208,375 2,012 -613,420 -721,884 2,013 6,261,721 7,331,445 2,013 -576,985 -729,061 2,014 7,242,890 9,364,361 2,014 -527,216 -684,432 2,015 8,263,306 12,569,912 2,015 -463,116 -613,272 The PV of the unlevered FCF using the unlevered cost of equity is: PV 21,953,625 The PV of the interest tax savings for the planning period, using the interest rate of the debt (6.5%) is: PV 3,307,031 The combined value of operating FCF and interest tax savings for the planning period is: PV 25,260,655 The terminal value is estimated using the Gordon model and the following parameters: Terminal Value using the DCF Method FCF 12,569,912 Long-run growth rate 2% WACC 8.80% Terminal value From Footnote 7 Unlevered cost of equity Tax rate Interest rate Debt (as a percentage) Resulting WACC 188,548,674 The PV of the terminal value using the unlevered cost of equity is: PV 109,560,190 The enterprise value (APV approach) is: PV 134,820,845 Terminal Value using the EBITDA Multiple Method EBITDA Multiple 6 FCF at year 2015 30,471,480 or Terminal value 182,828,879 Discount rate 9.47% PV of Terminal value 106,236,582 The enterprise value (Hybrid APV approach) is: PV 131,497,237 30,471,480 9.47% 25% 6.50% 40% 8.99% The authors report 8.8% Table 9-5 APV Valuation Summary for GRG for Status Quo and Growth Strategies Panel a. Status Quo Strategy APV Estimate of Enterprise Value Present Values APV Calculation of Planning DCF Estimates of Period Cash FlowsAPV Calculation Terminal ValuesDCF of Planning Estimates of $24,738,517 $72,033,945 Period Cash Flows Terminal Values Unlevered free cash flows Interest tax savings 2,830,870 Total $27,569,388 Hybrid APV Estimate ol Enterprise Value $72,033,945 Present Values APV Calculation of Planning EBITDA Multiple Period Cash FlowsAPV Calculation Terminal ValueEBITDA of Planning Multiple Period Cash Flows Terminal Value Unlevered free cash flows Interest tax savings Total Panel b. Growth Strategy APV Estimate of Enterprise Value $24,738,517 2,830,870 $27,569,388 $73,060,734 $73,060,734 Present Values APV Calculation of Planning DCF Estimates of Period Cash Flows Terminal Values Unlevered free cash flows $21,953,625 Interest tax savings 3,307,031 Total $25,260,655 Hybrid APV Estimate of Enterprise Value $109,560,190 $109,560,190 Present Values APV Calculation of Planning EBITDA Multiple Period Cash Flows Terminal Value Unlevered free cash flows Interest tax savings Total $21,953,625 3,307,031 $25,260,655 $106,236,582 $106,236,582 Values for the status quo strategy have not been estimated using the APV method. Total $96,772,462 2,830,870 $99,603,332 Total $97,799,251 2,830,870 $100,630,121 Total $131,497,237 3,307,031 $134,820,845 Total $128,190,206 3,307,031 $131,497,237 Values for the growth strategy have been estimated using the APV method in sheet APV. of GRC's Enterprise ValueTable 9-6 Summary of WACC and APV Estimates of GRC's Enterprise Value Status Quo Strategy Traditional WACC APV DCF estimate of terminal value $100,035,680 $99,603,333 EBITDA multiple estimate of terminal value 101,103,406 100,630,121 Growth Strategy Traditional WACC APV DCF estimate of terminal value $136,276,460 $134,820,845 EBITDA multiple estimate of terminal value 132.824.689 131,497,236.80 Legend Status quo strategy represents a set of cash flow estimates that correspond to the current method of operations of the business. Growth strategy represents cash flow estimates reflecting the implementation of an explicit plan to grow the business by making additional investments in capital equipment and changing the current method of operating the firm. DCF estimate of terminal value is based on the Gordon growth model, where cash flows are expected to grow at a constant rate of 2% per year, indefinitely. EBITDA multiple estimate of terminal value is based on a six times multiple of EBITDA estimated for 2015 (the end of the planning period)