discounted cash flow template

7/29/2019 Discounted Cash Flow Template

1/19

Fiscal

($ in millions) 2009 2010 2011 CAGR

Actual Actual Actual ('09-11')

Revenue -$ -$ -$ #DIV/0!

% Growth 0.0% 0.0% 0.0%

Direct costs of services - - -

Gross profit -$ -$ -$ #DIV/0!

Gross margin 0.0% 0.0% 0.0%

Selling, general and administrative expens - - -

0.0% 0.0% #DIV/0!

Depreciation and amortization - - -

Operating income -$ -$ -$ #DIV/0!

Operating Margin 0.0% 0.0% #DIV/0!

Interest expense (income) - - -

Cash Interest Rate 0.0% 0.0% 0.0%

Earnings before taxes - - -

Tax expense(1)

- - -

Net income -$ -$ -$ #DIV/0!

Supplemental Items

EBITDA -$ -$ -$Capex - - -

(1) Projected tax rate @ 40%


2/19

Year Ended December 31,

2012 2013 2014 2015 2016 CAGR

Projected Projected Projected Projected Projected ('12-'15)

-$ -$ -$ -$ -$ #DIV/0!

0.0% 0.0% 0.0% 0.0% 0.0%

- - - - -

-$ -$ -$ -$ -$ #DIV/0!

0.0% 0.0% 0.0% 0.0% 0.0%

- - - - -

#DIV/0! #DIV/0! #DIV/0! #DIV/0! 19.4%

- - - - -

-$ -$ -$ -$ -$ #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

- - - - -

0.0% 0.0% 0.0% 0.0% 0.0%

- - - - -

- - - - -

-$ -$ -$ -$ -$ #DIV/0!

-$ -$ -$ -$ -$- - - - -


3/19

($ in millions) Fiscal Year Ended December 31,

2009 % of 2010 % of 2011

Actual Sales Actual Sales Actual

CURRENT ASSETSExcess cash (plug)

Cash and cash equivalents -$ #DIV/0! -$ #DIV/0! -$

Accounts receivable, net - #DIV/0! - #DIV/0! -

Other current assets - #DIV/0! - #DIV/0! -

Total current assets - #DIV/0! - #DIV/0! -

LONG-TERM ASSETS

Fixed assets, net - #DIV/0! - #DIV/0! -

Other assets - #DIV/0! - #DIV/0! -

Total long-term assets - #DIV/0! - #DIV/0! -

Total assets -$ #DIV/0! -$ #DIV/0! -$

CURRENT LIABILITIES

Accounts payable -$ #DIV/0! -$ #DIV/0! -$

Accrued expenses - #DIV/0! - #DIV/0! -

Accrued payroll - #DIV/0! - #DIV/0! -

Total current liabilities - #DIV/0! - #DIV/0! -

LONG-TERM LIABILITIES

Long-term debt (plug)

Other liabilities - #DIV/0! - #DIV/0! -

Total long-term liabilities - #DIV/0! - #DIV/0! -

Total liabilities -$ #DIV/0! -$ #DIV/0! -$

Shareholders' equity -$ #DIV/0! -$ #DIV/0! -$

Total liabilities & shareholders' equ -$ #DIV/0! -$ #DIV/0! -$

Trial Liabilities

T


4/19

(Fiscal Year Ended December 31)

2009 2010 2011 2012 2013

Revenue $0.00 $0.00 $0.00 $0.00 $0.00

Cost of revenue - - - - -SG&A - - - - -

Current Assets

Accounts receivable, net - - - - -

Other current assets - - - - -

Total current assets - - - - -

Current Liabilities

Accounts payable -$ -$ -$ -$ -$

Accrued expenses - - - - -

Accrued payroll - - - #DIV/0! #DIV/0!Total current liabilities - - - #DIV/0! #DIV/0!

Net Working Capital - - - #DIV/0! #DIV/0!

% of revenue #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Change in NWC - - #DIV/0! #DIV/0!

Assumptions

Days sales outstanding - - - - -

Other current assets (% of revenue) 0.00% 0.00% 0.00% 0.00% 0.00%

Days Payables Outstanding - - - - -

Accrued expenses (% of revenue) 0.00% 0.00% 0.00% 0.00% 0.00%

Cash (% of Sales) 0.00% 0.00% 0.00% 0.00% 0.00%

Accrued Payroll (% of Sales) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Other Assets (% of Sales) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Capex - - - - -Depreciation & Amortization - - - - -

Other Liabilities (% of Sales) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Actual

Working Capital Projections


5/19

% of 2012 2013 2014 2015 2016

Sales Projected Projected Projected Projected Projected


#DIV/0! -$ -$ -$ -$ -$

#DIV/0! - - - - -

#DIV/0! - - - - -

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! - - - - -




#DIV/0! -$ -$ -$ -$ -$

#DIV/0! - - - - -







#DIV/0! -$ -$ -$ -$ -$


2012 2013 2014 2015 2016

Trial Assets #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

and Equity #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

ial Balance #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!


6/19

2014 2015 2016

$0.00 $0.00 $0.00

- - -- - -

- - -

- - -

- - -

-$ -$ -$

- - -

#DIV/0! #DIV/0! #DIV/0!#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

- - -

0.00% 0.00% 0.00%

- - -

0.00% 0.00% 0.00%

0.00% 0.00% 0.00%

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

- - -- - -

#DIV/0! #DIV/0! #DIV/0!

Projected


7/19

($ in millions)

Preferred arket Valu Total

Debt Stock of Equity apitalizatio

Company A - - - -

Company B - - - -Company C - - - -

Company D

Median - - - -

Mean - - - -

Levered Unlevered Equity Risk Size Risk

Beta Beta Premium Premium

Company A 0.00 0.00 0.00% 0.00%

Company B 0.00 0.00 0.00% 0.00%

Company C 0.00 0.00 0.00% 0.00%Company D

Median 0.00 0.00 0.0%

Mean 0.00 0.00 0.0%

Market Assumptions

Risk Free Rate 0.00%

Equity Risk Premium 0.00%

Tax Rate 0.0%

Target Capital Structure

Debt-to-Total Capitalization 0.82

Equity-to-Total Capitalization 0.92

1.02

Cost of Debt 1.12

Cost of Debt1.22

Tax Rate 1.32

After-tax Cost of Debt 1.42

1.52

Cost of Equity 1.62

Risk-free Rate 1.72

Market Risk Premium 1.82

Unlevered Beta 1.92

Size Premium

0%

0.00%

0.00%

0.00

0.00%

Weighted Average Cost of C

UnleveredB

eta

ITS: WACC Calculation

0.0%

100.0%

0%40%


8/19

Cost of Equity

WACC

0.00%

0.00%


9/19

Debt to Preferred to Equity to

Debt Total Total Total

to Equity CapitalizationCapitalizationCapitalization

0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 0.0% 0.0%0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 100.0%

0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 0.0% 0.0%

Cost of Cost of Cost of

Equity Debt Preferred WACC

0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 0.0% 0.0%

0.0% 0.0% 0.0% 0.0%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

1.76% 2.26% 2.76% 3.26% 3.76% 4.26% 4.76%

pital

Size Premium


10/19


11/19

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%

5.26%


12/19

CAGR

2009 2010 2011 ('09-'11)

Revenue -$ -$ -$ #DIV/0!

% Growth 4% #DIV/0! #DIV/0!Cost of Revenue - - -

Gross Profit -$ -$ -$ #DIV/0!

% Margin #DIV/0! #DIV/0! #DIV/0!

Selling, General and Administrative - - -

EBITDA -$ -$ -$ #DIV/0!


Depreciation & Amortization - - -

EBIT - - - #DIV/0!


Taxes @ 40% - - -EBIAT -$ -$ -$ #DIV/0!

Plus: Depreciation & Amortization - - -

Less: Capital Expenditures - - -

Less: Change in Net Working Capital

Unlevered Free Cash Flow

Terminal Value

Discount Period

Discount Factor

Present Value of Free Cash Flow

Debt-to-Total Capitalization

Assumptions Equity-to-Total Capitalization

WACC (All Equity) 0.00% Preferred Equity

Terminal Growth Rate 3.0% Debt-to-Equity

Enterprise Value ($ in millions)

Cumulative PV of FCF #DIV/0! % Senior

PV of Terminal Value #DIV/0! % Mezzanine% of Enterprise Value #DIV/0! Cost of Senior Debt

Cost of Mezzanine Debt

Enterprise Value #DIV/0! Tax Rate

After-tax Cost of Debt

PV of Tax Shield - Cost of Preferred Equity

Actual


13/19

NPV all Equity -

APV - Risk-free Rate

Market Risk Premium

Unlevered Beta

Relevered Beta

Size PremiumCost of Equity

WACC

PV of Tax Shield 41,732,000

NPV all Equity 200,000,000

APV 241,732,000

Adjusted Present Value


14/19

CAGR

2012 2013 2014 2015 2016 ('12-'16)

-$ -$ -$ -$ -$ #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!- - - - -

-$ -$ -$ -$ -$ #DIV/0!


- - - - -

-$ -$ -$ -$ -$ #DIV/0!


- - - - -

- - - - - #DIV/0!


- - - - - 0-$ -$ -$ -$ -$ #DIV/0!

- - - - -

- - - - -



#DIV/0!

1 2 3 4 5

0.90 0.81 0.73 0.65 0.49


34.7% 18.9% 0.2% 0.0% 0.0%

39.7% 32.6% 43.4% 47.4% 100.0%

25.3% 48.4% 56.4% 52.6% 0.0%

0.87 0.58 0.00 0.00 -

#DIV/0!

0.0%

59.4% 52.4% 42.2% 0.0% 0.0% 0.5%

38.4% 42.3% 57.8% 100.0% 100.0% 1.0%8.5% 8.5% 8.5% 8.5% 8.5% 1.5%

12.0% 12.0% 12.0% 12.0% 12.0% 2.0%

40.0% 40.0% 40.0% 40.0% 40.0% 2.5%

5.8% 5.7% 6.3% 7.2% 7.2% 3.0%

3.5%

4.0%

8.0% 8.0% 8.0% 8.0% 8.0% 4.5%

Debt

TerminalGrowthRate

Projection

Capital Structure

Preferred Equity


15/19

5.0%

5.5%

4.5% 4.5% 4.5% 4.5% 4.5% 6.0%

5.8% 5.8% 5.8% 5.8% 5.8%

1.32 1.32 1.32 1.32 1.32

2.02 1.78 1.33 1.32 1.32

3.3% 3.3% 3.3% 3.3% 3.3%19.4% 18.0% 15.4% 15.4% 15.4%

11.73% 10.84% 11.20% 11.50% 15.37%

Common Equity


16/19

#DIV/0!

Enterprise #DIV/0! #DIV/0!

EBITDA M #DIV/0! #DIV/0!

Mean #DIV/0!

4.0% 5.0% 6.0% 7.0% 8.0% 9.0% 10.0% 11.0% 12.0% 13.0%

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!


#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!







WACC

100.0 120.0 140.0 160.0 180.0 200.0

nterprise Value

Discounted Cash Flow Valuation


17/19





18/19

14.0% 15.0% 16.0%

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!


19/19

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

discounted cash flow template

Documents