DCF MATRIX SOLVE FOR NON-CASH-WORKING-CAPITAL(NCWC)
Here are the assumptions
B M ASSUMPTIONS D F H K DCF Matrix Case Exercise (Acquiring Company is Valuing Target F Projections 2011 2012 2013 2014 2015 2016 2017 2018 2019 Actual 2009 2010 50.80% -2.00% 3.60% Assumptions: Sales Growth Rate % Operating Margin % of Sales Depreciation Exp. % of Sales Marginal Tax Rate %2 Net Non Cash Working Capital % of Sales Gross P&E % of Sales WACC (2010 - 2019) %5 WACC (Terminal Period) %6 Terminal Period Growth Rate % Working Capital ($Mil) Cash? Non Cash Working Capital 45.00% -1.00% 3.40% 40.00% 11.40% 5.00% 9.31% 8.50% 3.00% 40.00% -1.00% 6.00% 40.00% 10.00% 8.00% 40.00% 2.00% 6.00% 40.00% 10.00% 8.00% 40.00% 4.00% 6.00% 40.00% 10.00% 8.00% 35.00% 8.00% 6.00% 40.00% 10.00% 8.00% 30.00% 10.00% 6.00% 40.00% 10.00% 8.00% 25.00% 12.00% 7.00% 40.00% 10.00% 7.00% 20.00% 15.00% 7.00% 40.00% 10.00% 7.00% 10.00% 15.00% 7.00% 40.00% 10.00% 6.00% 10.00% 15.00% 6.00% 40.00% 10.00% 6.00% 10.40% 8.70% $112.80 $98.70 $17.471 Selected Financial Data ($Mil.) Sales Depreciation Expense & Amort. Gross Plant & Equipment $168.00 $6.10 $14.60 Deferred Tax Assets Funded Debt Number of Shares Outstanding $73.10 $22.00 61.80 EXPLANATORY NOTES: NOTE: ACQUIRIER (large profitable firm) buys TARGET (high growth software tech firm) 'Includes depreciation & amortization expense 2A 40% marginal tax rate is used as acquirer is extremely profitable Net Non Cash Working Capital excludes target cash balances Capital Expenditures increases faster than depreciation through 2015 to support growth of sales and than scales down for the remainder of the forecast period. SWACC 2010 - 2019: Cost of equity = 0.0265 + 1.3145 (0.055), where 0.0265 is the 10 year Treasury bond at the time of the transaction, 1.3145 is target's beta, and 0.055 is the market premium. Cost of Debt = 7%. Capital structure: 90% equity & 10% debt WACC Terminal Period: comparable firms 17 CLILI. --- lin. | Sheet3 Sheet1 Sheet2 DCF CASE EXERCISE MATRIX - CALCULATE NCWC 1 2 3 4 5 6 7 8 9 10 HISTORICAL AVE ASSUMPTION ACT REV 2009 $ 168.00 $ % REVENUE GROWTH 50.80% REVENUE 2010 2011 2012 2013 243.60 $ 341.04 $ 477.46 $ 668.44 $ 45.00% 40.00% 40.00% 40.00% 2014 2015 2016 2017 2018 2019 902.39 $1,173.11 $1,466.39 $1,759.66 $1,935.63 $2,129.19 35.00% 30.00% 25.00% 20.00% 10.00% 10.00% -$ OPERATING MARGINS (INCLUDES DEPRECIATION) % OF REVENUE -2.00% 2.44 $ -1.00% 3.41 $ -1.00% 9.55 $ 2.00% 26.74 $ 4.00% 72.19 $ 117.31 $ 175.97 $ 263.95 $ 290.34 $ 319.38 8.00% 10.00% 12.00% 15.00% 15.00% 15.00% $ $ 2.44 $ 0.97 $ 40.00% 3.41 $ 1.36 $ 40.00% 9.55 $ 3.82 $ 40.00% 26.74 $ 10.70 $ 40.00% 72.19 $ 117.31 $ 175.97 $ 263.95 $ 290.34 $ 319.38 28.88 $ 46.92 $ 70.39 $ 105.58 $ 116.14 $ 127.75 40.00% 40.00% 40.00% 40.00% 40.00% 40.00% EBIT TAXES % OF EBT A) EBIT (1-TAX)or NOPAT 1) DEPRECIATION % OF REVENUE -$ 1.46 $ 2.05 $ 5.73 $ 16.04 $ 43.31 $ 70.39 $ 105.58 $ 158.37 $ 174.21 $ 191.63 3.60% 3.60% 3.40% 6.00% 6.00% 6.00% 6.00% 6.00% 7.00% 7.00% 7.00% 6.00% 2) CAPEX % OF REVENUE 8.70% 10.40% 11.40% 10.00% 10.00% 10.00% 10.00% 0.00% 10.00% 10.00% 10.00% 10.00% 3) NON-CASH WORKING CAPITAL $ 10.30 $ 6.33 $ 13.64 $ 19.10 $ 23.40 $ 27.07 $ 29.33 $ 29.33 $ 17.60 S 19.36 B) FCFF PV YR by YR WACC 1) PV (2009 - 2119) Terminal Growth Rate/WACC TV TV 2019 % OF REVENUE 8.70% 10.40% 11.40% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 3) NON-CASH WORKING CAPITAL $ 10.30 $ 6.33 $ 13.64 $ 19.10 $ 23.40 $ 27.07 $ 29.33 $ 29.33 $ 17.60 $ 19.36 TV 2019 B) FCFF PV YR by YR WACC 1) PV (2009 - 2119) Terminal Growth Rate/WACC TV 2) PV of Terminal Value Net deferred Tax Assets c) VALUE OF EQUITY FUNDED DEBT CASH c) VALUE OF FIRM Number of Shares Outstanding E) PRICE PER SHARE 2013 NON CASH WORKING CAPITAL REVENUE ACTUAL 2009 S 168.00 2010 243.60 $ 2011 341.04 $ 2012 477.46 $ 2014 2015 2016 2017 2018 2019 902.39 $1,173.11 $1,466.39 $1,759.66 $1,935.63 $2,129.19 S 668.44 $ RATIO OF ADJUSTED NON-CASH WORKING CAPITAL TO REVENUE 10.40% 11.40% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% WORKING CAPITAL $ 17.47 $ 27.77 $ 34.10 $ 47.75 $ 66.84 $ 90.24 $ 117.31 $ 146.64 $ 175.97 $ 193.56 $ 212.92 CHANGE IN NET WORKING CAPITAL $ 10.30 $ 6.33 $ 13.64 $ 19.10 $ 23.40 $ 27.07 $ 29.33 $ 29.33 $ 17.60 $ 19.36 B M ASSUMPTIONS D F H K DCF Matrix Case Exercise (Acquiring Company is Valuing Target F Projections 2011 2012 2013 2014 2015 2016 2017 2018 2019 Actual 2009 2010 50.80% -2.00% 3.60% Assumptions: Sales Growth Rate % Operating Margin % of Sales Depreciation Exp. % of Sales Marginal Tax Rate %2 Net Non Cash Working Capital % of Sales Gross P&E % of Sales WACC (2010 - 2019) %5 WACC (Terminal Period) %6 Terminal Period Growth Rate % Working Capital ($Mil) Cash? Non Cash Working Capital 45.00% -1.00% 3.40% 40.00% 11.40% 5.00% 9.31% 8.50% 3.00% 40.00% -1.00% 6.00% 40.00% 10.00% 8.00% 40.00% 2.00% 6.00% 40.00% 10.00% 8.00% 40.00% 4.00% 6.00% 40.00% 10.00% 8.00% 35.00% 8.00% 6.00% 40.00% 10.00% 8.00% 30.00% 10.00% 6.00% 40.00% 10.00% 8.00% 25.00% 12.00% 7.00% 40.00% 10.00% 7.00% 20.00% 15.00% 7.00% 40.00% 10.00% 7.00% 10.00% 15.00% 7.00% 40.00% 10.00% 6.00% 10.00% 15.00% 6.00% 40.00% 10.00% 6.00% 10.40% 8.70% $112.80 $98.70 $17.471 Selected Financial Data ($Mil.) Sales Depreciation Expense & Amort. Gross Plant & Equipment $168.00 $6.10 $14.60 Deferred Tax Assets Funded Debt Number of Shares Outstanding $73.10 $22.00 61.80 EXPLANATORY NOTES: NOTE: ACQUIRIER (large profitable firm) buys TARGET (high growth software tech firm) 'Includes depreciation & amortization expense 2A 40% marginal tax rate is used as acquirer is extremely profitable Net Non Cash Working Capital excludes target cash balances Capital Expenditures increases faster than depreciation through 2015 to support growth of sales and than scales down for the remainder of the forecast period. SWACC 2010 - 2019: Cost of equity = 0.0265 + 1.3145 (0.055), where 0.0265 is the 10 year Treasury bond at the time of the transaction, 1.3145 is target's beta, and 0.055 is the market premium. Cost of Debt = 7%. Capital structure: 90% equity & 10% debt WACC Terminal Period: comparable firms 17 CLILI. --- lin. | Sheet3 Sheet1 Sheet2 DCF CASE EXERCISE MATRIX - CALCULATE NCWC 1 2 3 4 5 6 7 8 9 10 HISTORICAL AVE ASSUMPTION ACT REV 2009 $ 168.00 $ % REVENUE GROWTH 50.80% REVENUE 2010 2011 2012 2013 243.60 $ 341.04 $ 477.46 $ 668.44 $ 45.00% 40.00% 40.00% 40.00% 2014 2015 2016 2017 2018 2019 902.39 $1,173.11 $1,466.39 $1,759.66 $1,935.63 $2,129.19 35.00% 30.00% 25.00% 20.00% 10.00% 10.00% -$ OPERATING MARGINS (INCLUDES DEPRECIATION) % OF REVENUE -2.00% 2.44 $ -1.00% 3.41 $ -1.00% 9.55 $ 2.00% 26.74 $ 4.00% 72.19 $ 117.31 $ 175.97 $ 263.95 $ 290.34 $ 319.38 8.00% 10.00% 12.00% 15.00% 15.00% 15.00% $ $ 2.44 $ 0.97 $ 40.00% 3.41 $ 1.36 $ 40.00% 9.55 $ 3.82 $ 40.00% 26.74 $ 10.70 $ 40.00% 72.19 $ 117.31 $ 175.97 $ 263.95 $ 290.34 $ 319.38 28.88 $ 46.92 $ 70.39 $ 105.58 $ 116.14 $ 127.75 40.00% 40.00% 40.00% 40.00% 40.00% 40.00% EBIT TAXES % OF EBT A) EBIT (1-TAX)or NOPAT 1) DEPRECIATION % OF REVENUE -$ 1.46 $ 2.05 $ 5.73 $ 16.04 $ 43.31 $ 70.39 $ 105.58 $ 158.37 $ 174.21 $ 191.63 3.60% 3.60% 3.40% 6.00% 6.00% 6.00% 6.00% 6.00% 7.00% 7.00% 7.00% 6.00% 2) CAPEX % OF REVENUE 8.70% 10.40% 11.40% 10.00% 10.00% 10.00% 10.00% 0.00% 10.00% 10.00% 10.00% 10.00% 3) NON-CASH WORKING CAPITAL $ 10.30 $ 6.33 $ 13.64 $ 19.10 $ 23.40 $ 27.07 $ 29.33 $ 29.33 $ 17.60 S 19.36 B) FCFF PV YR by YR WACC 1) PV (2009 - 2119) Terminal Growth Rate/WACC TV TV 2019 % OF REVENUE 8.70% 10.40% 11.40% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 3) NON-CASH WORKING CAPITAL $ 10.30 $ 6.33 $ 13.64 $ 19.10 $ 23.40 $ 27.07 $ 29.33 $ 29.33 $ 17.60 $ 19.36 TV 2019 B) FCFF PV YR by YR WACC 1) PV (2009 - 2119) Terminal Growth Rate/WACC TV 2) PV of Terminal Value Net deferred Tax Assets c) VALUE OF EQUITY FUNDED DEBT CASH c) VALUE OF FIRM Number of Shares Outstanding E) PRICE PER SHARE 2013 NON CASH WORKING CAPITAL REVENUE ACTUAL 2009 S 168.00 2010 243.60 $ 2011 341.04 $ 2012 477.46 $ 2014 2015 2016 2017 2018 2019 902.39 $1,173.11 $1,466.39 $1,759.66 $1,935.63 $2,129.19 S 668.44 $ RATIO OF ADJUSTED NON-CASH WORKING CAPITAL TO REVENUE 10.40% 11.40% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% WORKING CAPITAL $ 17.47 $ 27.77 $ 34.10 $ 47.75 $ 66.84 $ 90.24 $ 117.31 $ 146.64 $ 175.97 $ 193.56 $ 212.92 CHANGE IN NET WORKING CAPITAL $ 10.30 $ 6.33 $ 13.64 $ 19.10 $ 23.40 $ 27.07 $ 29.33 $ 29.33 $ 17.60 $ 19.36
