Docstoc

Discounted Cash Flow (DCF) Financial Model

Document Sample
Discounted Cash Flow (DCF) Financial Model Powered By Docstoc
					Discount Rate Calculation

        Note: all cells in blue font with blue titles are assumptions that the user must fill in

        Calculation of Beta from Comparables
        (in $000s)

                  Comparable Companies                Levered Beta      Equity Market Capitalization     Total Debt   Tax Rate     Unlevered Beta
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606
                            xxx                             1                         1                       1         35%            0.606

        High Unlevered Beta                                     0.606
        Mean Unlevered Beta                                     0.606
        Median Unlevered Beta                                   0.606
        Low Unlevered Beta                                      0.606

        Calculation of the Cost of Equity

        Market Risk Premium (Rm)                                  6%
        Risk-Free Rate (Rf)                                       5%
        Target's Estimated Unlevered Beta (βu)                  0.606
        CAPM = Rf + ((βu) * (Rm - Rf))                          0.056

        Cost of Debt of Target Company                             8%

        Calculation of the Weighted Average Cost of Capital (WACC)

                     Target Company                       Equity                 Total Debt               Tax Rate
                          MyCo                              1                        1                      35%

        Weighted Average Cost of Capital =           ((Equity/Enterprise Value)*(Cost of Capital)) + ((Debt/Enterprise Value)*(Cost of Debt)*(1-taxrate))
        Enterprise Value = Equity + Total Debt                      2
        WACC                                            0.054030303
Discounted Cash Flow Calculation

Income Statement
(in $000's)
                                                                                          Year ending December 31,
                                                   2007        2008           2009           2010         2011        2012        2013        2014


Revenue                                              $100        $150            $200           $200         $200       $200        $200        $200


Cost of Sales                                              0           0              0             20           20          20          20          20
Gross Profit                                          100         150             200            180          180        180         180         180


Operating Expenses
                Research & Development                     0           0              0             24           24          24          24          24
                Sales & Marketing                          0           0              0             26           26          26          26          26
                General & Administrative                   0           0              0             28           28          28          28          28
Total Operating Expenses                                   0           0              0             78           78          78          78          78


Operating Income                                      100         150             200            102          102        102         102         102


Interest Expenses
                Interest Expense                           0           0              0              0            0           0           0           0
                Interest Income                            0           0              0              0            0           0           0           0
Net Interest Expense                                       0           0              0              0            0           0           0           0


Pretax Income                                         100         150             200            102          102        102         102         102


Taxes                                                      0           0              0              0            0           0           0           0


Net Income                                            100         150             200            102          102        102         102         102


Revenue Growth                                                        0.5   0.33333333          0.0%         0.0%       0.0%        0.0%        0.0%
Gross Margin                                       100.0%      100.0%          100.0%          90.0%        90.0%      90.0%       90.0%       90.0%
R&D/Revenue                                          0.0%        0.0%            0.0%          12.0%        12.0%      12.0%       12.0%       12.0%
S&M/Revenue                                          0.0%        0.0%            0.0%          13.0%        13.0%      13.0%       13.0%       13.0%
G&A/Revenue                                          0.0%        0.0%            0.0%          14.0%        14.0%      14.0%       14.0%       14.0%
Operating Margin                                   100.0%      100.0%          100.0%          45.0%        45.0%      45.0%       45.0%       45.0%
Pretax Margin                                        0.0%        0.0%            0.0%            0.51         0.51       0.51        0.51        0.51
Net Margin                                           0.0%        0.0%            0.0%          51.0%        51.0%      51.0%       51.0%       51.0%




Relevant Sections from Balance Sheet

                                                                                          Year ending December 31,
                                                   2007        2008           2009           2010         2011        2012        2013        2014


Current Assets
             Cash                                         $0          $0             $0             $0           $0          $0          $0          $0
             Accounts Receivable                           0           0              0             $0           $0          $0          $0          $0
             Inventory                                     0           0              0             $0           $0          $0          $0          $0
             Other Current Assets                          0           0              0              0            0           0           0           0
Total Current Assets                                       0           0              0              0            0           0           0           0

Current Liabilities
               Accounts Payable                           $0          $0             $0             $0           $0          $0          $0          $0
               Short Term Debt                             0           0              0             $0           $0          $0          $0          $0
               Current Portion of Long Term Debt           0           0              0             $0           $0          $0          $0          $0
               Other Current Liabilities                   0           0              0              0            0           0           0           0
Total Current Liabilities                                  0           0              0              0            0           0           0           0

Property, Plant & Equipment Schedule
Gross Property, Plant & Equipment                         $0          $0             $0             $0           $0          $0          $0          $0
                Accumulated Depreciation                   0           0              0              0            0           0           0           0
Net Property, Plant & Equipment                            0           0              0


                Net Capital Expenditures                   0           0              0              0            0           0           0           0
                Depreciation Expense                                   0              0              0            0           0           0           0
Free Cash Flow Calculation                                                                                   Year ending December 31,
                                                                  2007           2008           2009            2010         2011           2012           2013         2014
Net Income                                                           100.0         150.0          200.0           102.0         102.0          102.0         102.0        102.0
(+) Non-Cash Charges, like Depreciation                                  0.0            0.0            0.0             0.0          0.0              0.0          0.0          0.0
(+) After-tax Interest Payments on Debt                                  0.0            0.0            0.0             0.0          0.0              0.0          0.0          0.0
(-) Investments in Fixed Capital                                         0.0            0.0            0.0             0.0          0.0              0.0          0.0          0.0
(-) Investments in Working Capital                                       0.0            0.0            0.0             0.0          0.0              0.0          0.0          0.0
              (Current Assets - Current Liabilities)
              Free Cash Flow                                         100.0         150.0          200.0           102.0         102.0          102.0         102.0        102.0


Discounted Cash Flow Calculation

              Discount Rate                                                          5.4% from "Discount Rate" tab


              Terminal Growth Rate                                                   4.0%           5.0%            6.0%
              Terminal Value                                                      7,560.8      26,573.7        (18,111.5)
              Present Value of Terminal Value                                      4,963         17,443          (11,889)
              Sum of the Present Value of Free Cash Flows                            774            715             604
               Entity Value                                                        5,737         18,159          (11,284)
              (+) Excess Cash                                                            0              0               0
              (-) Debt                                                                   0              0               0


               Equity Value                                                        5,737         18,159          (11,284)


              Note: if the terminal growth rate is larger than the discount rate, then the calculation for the terminal value will not make sense.
				
DOCUMENT INFO
Description: Discounted cash flow (DCF) analysis uses future free cash flow projections and discounts them (most often using the weighted average cost of capital) to arrive at a present value. Present value can then be used to evaluate a potential investment. If the value arrived at through DCF analysis is higher than the current cost of the investment, the investment opportunity may be a good one. This is a basic discounted cash flow model, computing unlevered beta from comparables and computing free cash flow from the income statement and balance sheet.
This document is also part of a package Financial Models Bundle 7 Documents Included