fint_logo_4
 
                 
Fast DCF model                                        
                                         
                                         
Neither this site, the available materials, nor its contents (including any information concerning    
In this spreadsheet we will create a DCF model but we'll do it fast using very rough estimates and some tricks any securities mentioned on this site) constitutes an advertisement of any securities or      
The example may help you to understand the logic of DCF model. an offer to sell or a solicitation of an offer to purchase any securities.            
Also sometimes you have to do things like this at work because you simply don't have time to dig in details.
Simple does not mean bad. When you logically simplify your model you often come up with more accurate projections.
We'll use financials of one American company which name we won't disclose to avoid any complaints.
We'll use 2008-2010 data for our model.
Please remember that some calculations will be simplified to a great extend (for the sake of the speed) and some approaches shouldn't be used in the detailed complicated models (for M&A or LBO, for example).
Some formulas may look inappropriate although their logic is correct. All that is done for the simplicity of the model.
STEP 1 Understanding of what we need for the model
We use 10-K form to insert key numbers in our model
This form is usually available on companies' website or at http://www.sec.gov/edgar.shtml for the stocks traded in US
First we present a final form for DCF model (it's empty for now), so we understand which numbers we need to get from financial reports.
The basic idea of DCF model: we need to find free cash flows for the forecasting period (FCF= Fully taxed EBIT + DD&A - investments), calculate Terminal value,
discount everything to current moment in time and that's it.
Basically we just need to fill the data in the table below and the model is ready
DCF model          
  2011F 2012F 2013F 2014F 2015F
EBIT (or operating profit)
Tax rate
Fully taxed EBIT (NOPAT)
DD&A
Unleveraged cash flows
Investments (or CAPEX)
Movements in working capital
Unleveraged free cash flows
Terminal value          
Net debt          
WACC
Terminal growth rate
NPV          
For now we leave this table empty
Where do we get all the data? - From companys financial reports. We will mark red all the important inputs
The last row (NPV) is the fair value that we get for the end of every year
EBIT (Earnings before interest and tax) or operating income comes from the Income statement
Tax rate - is the corporate tax rate in the country where the company operates (our company is incorporated in US)
Fully taxed EBIT or NOPAT (net operating profit after tax)
DD&A - depreciation, depletion and amortization
Unleveraged cash flows = Fully taxed EBIT + DD&A
Investments or CAPEX (capital expenditure) - we get it from Cash flow statement
Movements in working capital (WC) is calculated using the Balance Sheet items
Net debt also comes from the Balance sheet
WACC - weighted average cost of equity. It's calculation will be shown below
Note: F is for forecast
STEP 2 Forecasting income statement  
It's important to understand that we can't forecast income statement, balance sheet and cash flows statement one after the other.
We have to leave some items for later because they are linked to other forms.
As it is a short model, we'll use financial data only for three years (2008, 2009, 2010). And only for two years (2009, 2010) for the Balance sheet.
We start with the Income statement.
We ignore full costs breakdown and put only DD&A, other costs, total operating costs and net interest expense.
We use the financials of oil company and therefore our forecast will be pretty much straightforward: we assume that the revenue is driven primarily by the oil price (WTI - West Texas Intermediate)
We get the oil price forecast from Bloomberg terminal using the function CPF <GO>. If you don't have the terminal we recommend to use the current oil price and adjust it for US inflation (CPI) every year.
Income statement                
  2008 2009 2010 2011 2012F 2013F 2014F 2015F COMMENTS
Oil price ($/bbl) 98 62 79 110 100 110 117 115 Taken from Bloomberg terminal
                   
in $mn 2008 2009 2010 2011F 2012F 2013F 2014F 2015F  
Revenue 821 370 438 610 555 610 649 638 Changes in line with oil price change
 
DD&A -222 -166 -163 -181 -187 -193 -200 -206 We calculate it as a percentage of Property and Equipment (simplified formula)
Other operating costs -226 -155 -168 -280 -232 -267 -290 -275  
Total operating costs -448 -321 -331 -460 -419 -460 -490 -481 Calculated backwards, i.e. Revenue minus EBIT
 
EBIT (operating income) 373 49 108 150 136 150 160 157 First input to our DCF model. Calculated using EBIT margin
 
Net interest expense -31 -31 -33 -33 -33 -33 -33 -33 Constant percentage of total debt (simplified calculation)
Other non-operating expense/income -754 -83 0 0 0 0 0 0 This line is too volatile and non-sustainable, we just ignore it
 
Profit before tax -413 -65 74 117 103 117 126 123 EBIT minus financial expenses
 
 
Tax expense (benefits) 156 26 -28 -41 -36 -41 -44 -43 Profit before tax multiplied by tax rate. US corporate tax rate is 35%
tax rate 35% 35% 35% 35% 35% 35% 35% 35% Official corporate tax rate
tax rate actual neg. neg. 37,5% Actual tax rate base on historical data
other -3 -0 -0 We do not calculate deferred tax income/loss for time saving purposes
Net income -260 -39 46 76 67 76 82 80 Profit before tax minus tax expense
To make forecasts for 2011-2015 we'll use the table below
Ratios table which will help us to forecast                
  2008 2009 2010 2011F 2012F 2013F 2014F 2015F COMMENTS
Oil price growth -37% 27% 39% -9% 10% 6% -2%
Revenue change -55% 18% 39% -9% 10% 6% -2% We assume that the revenue will change in line with oil price change
EBIT margin 45% 13% 25% 25% 25% 25% 25% 25% We use 2010 EBIT margin for the whole forecasting period (can also use average)
Net interest expense as % of total debt   6,5% 7,1% 7,1% 7,1% 7,1% 7,1% 7,1% Total debt = Long-term debt + Short-term debt (from Balance sheet)
STEP 3 Forecasting Balance sheet  
Now we make Balance sheet forecast
Again we use only the most important items from the balance sheet
                 
in $mn 2008 2009 2010 2011F 2012F 2013F 2014F 2015F COMMENTS
Cash and equivalents 38 86 196 273 370 474 568 Cash comes from Cash flow statement. It should always be positive!
Accounts receivable 54 47 65 59 65 70 68 We calculate it using receivables turnover ratio (in the table below)
Other current assets 16 27 27 27 27 27 27 Leave this line unchanged
Total current assets 109 161 161 161 161 161 161
Property and equipment 1 316 1 573 1 626 1 682 1 739 1 798 1 859 We add CAPEX and subtract DD&A. Simplified calculation.
Other long-term assets 10 13
Total non-current assets 1 326 1 586 1 626 1 682 1 739 1 798 1 859
                 
Total assets   1 435 1 746 1 787 1 843 1 900 1 959 2 020
 
Accounts payable and accrued liabilities 61 82 114 103 114 121 119 We calculate it using payables turnover ratio (in the table below)
Short-term debt 0 0 0 0 0 0 0 Debt changes via the Statement of cash flows (see the link in the cell)
Other 43 81 81 81 81 81 81 Leave this line unchanged
Total current liabilities 104 163 195 184 195 202 200
 
Long-term debt 471 472 472 472 472 472 472 Debt changes via the Statement of cash flows (see the link in the cell)
Other long-term liabilities 181 232 232 232 232 232 232 Leave this line unchanged
Total non-current liabilities   652 704 704 704 704 704 704
 
Equity 679 880 889 955 1 001 1 053 1 117 Its a little trick. We determine Equity extracting liabilities from total assets
                 
Total liabilities and equity   1 435 1 746 1 787 1 843 1 900 1 959 2 020
 
check 0 0 0 0 0 0 0
 
Ratios table which will help us to forecast                 COMMENTS
  2008 2009 2010 2011F 2012F 2013F 2014F 2015F We use simplified formulas in calculations. Correct formulas are shown below
Receivables turnover ratio 6,83 9,33 9,33 9,33 9,33 9,33 9,33 Correct formula for that is: Net credit sales/Average accounts receivables
Payables turnover ratio 5,28 4,05 4,05 4,05 4,05 4,05 4,05 Correct formula for that is: Cost of goods sold/Average accounts payables
DDA as % of Property and equipment   0,13 0,10 0,11 0,11 0,11 0,11 0,11 We need that ratio to forecast DD&A
STEP 4 Forecasting Cash flow statement  
                 
in $mn 2008 2009 2010 2011F 2012F 2013F 2014F 2015F COMMENTS
  Different adjustments in cash flow statement are often not sustainable
  and sometimes it's better just to ignore them and focus on core numbers
Cash flows from operating activities  
Net income -260 -39 46
EBIT   150 136 150 160 157 We introduce this line for simplicity and time saving purposes.
DD&A 222 166 163 181 187 193 200 206 Calculated as a constant share of property and equipment
Other adjustments 604 98 49           We ignore that because it's time consuming to project different items
Operating cash flow before changes in WC 566 225 258 331 323 343 359 363 In fact we use EBITDA (EBIT + DD&A) as a proxy for operating cash flows
                 
(Increase) decrease in accounts receivable 26 3 -7 -18 6 -6 -4 1
Increase (decrease) in accounts payable -4 2 0 32 -10 10 7 -2
Change in other WC items 0 -4 8
Total change in WC 22 1 1 14 -4 4 3 -1
Net cash flow from operating activities 588 226 259 344 319 347 362 362
                 
Cash flows from investing activities   CAPEX is one of the most important lines in the whole model. But now we calculate
Capital expenditures (CAPEX) -675 -215 -354 -234 -242 -250 -259 -268 it as a fixed share of DD&A
Other 81 36 5 Non-sustainable volatile items. We ignore them for the sake of simplicity
Net cash used in investing activity -594 -179 -349 -234 -242 -250 -259 -268
 
Cash flows from financing activities                
Proceeds from long-term debt 0 221 0             Use these 4 lines to adjust cash flows. You need to have positive cash at  
Payments of long-term debt -6 -331 0           the end of the year. You can achieve that by regulating companys debt.
Proceeds from short-term debt             Ideally you should dig into 10-K filling to find a debt maturity schedule. But
Payments of short-term debt                   we're not looking for perfection right now.        
Net proceeds from issuances of stocks 9 110 143
Other -2 -9 -5
Dividend payouts   We introduce this line. If you see that the company generates too much cash
Net cash provided by financing activities 1 -8 137 0 0 0 0 0 you may assume that it will pay some dividends
                 
Net increase/decrease in cash -5 38 48 110 77 97 103 95 The sum of operating, investing and financing cash flows
Cash at the beginning of the year 6 0 38 86 196 273 370 474
Cash at the end of the year 0 38 86 196 273 370 474 568
CAPEX calculation                
CAPEX/DD&A 3,04 1,30 2,18 1,30 1,30 1,30 1,30 1,30 To be honest capex deserves detailed calculation because it often determines
companys growth. But for the sake of simplicity we will assume that
the company invests proportionally to DD&A, i.e. it not only replaces
old (depreciated) assets but also constantly buy new assets proportionally.
NOTE: As you may see, a lot of calculations were simplified, which is actually not that bad. We do not build this model for merger or acquisition purposes. It looks more like a buy-side model for some asset
management company. PMs often use a very simple way to determine FCF (free cash flow = operating cash flows - investing cash flows): they just extract CAPEX from EBITDA and that's it. EBITDA is a good proxy for
operating cash flows while CAPEX is a good proxy for cash flow from investing because the majority of smaller items are often non sustainable and projecting them correct is a real pain in the ass.
STEP 5 Check your financials model  
To check your model calculate basic financial ratios and see if there any rapid changes in ratios which look not logical
                 
Liquidity ratios 2008 2009 2010 2011F 2012F 2013F 2014F 2015F                
Current ratio 1,05 0,99 0,83 0,87 0,83 0,80 0,80 Current assets/Current liabilities
Cash ratio   0,37 0,53 1,01 1,48 1,90 2,34 2,84 (Cash + cash equivalents + invested funds)/Current liabilities
Profitability ratios                                
Return on Assets (ROA) -3% 3% 4% 4% 4% 4% 4% Net income/Average total assets (we don't use average for simplicity)
Return on Equity (ROE) -6% 5% 9% 7% 8% 8% 7% Net income/Average equity (we don't use average for simplicity)
EBIT margin 45% 13% 25% 25% 25% 25% 25% 25% EBIT/Revenue. The higher - the better
EBITDA margin 72% 58% 62% 54% 58% 56% 55% 57% EBITDA/Revenue. The higher - the better
Net margin -32% -11% 11% 12% 12% 12% 13% 13% Net income/revenue. The higher - the better
Solvency ratios 2008 2009 2010 2011F 2012F 2013F 2014F 2015F                
Total debt/EBITDA 2,19 1,74 1,43 1,46 1,37 1,31 1,30 The lower - the better. Compare it with sector's average value
Interest coverage ratio 11,99 1,61 3,22 4,49 4,08 4,49 4,77 4,69 EBIT/Interest expense. The higher the better
Debt-to-Equity ratio   0,69 0,54 0,53 0,49 0,47 0,45 0,42 Total debt/Equity.
Performance ratios                                
Cash flow-to-revenue 0,72 0,61 0,59 0,56 0,57 0,57 0,56 0,57 Cash flows from operations/Revenue. The higher - the better
Cash return-on-assets 16% 15% 19% 17% 18% 18% 18% Cash flows from operations/Total assets. The higher - the better
Cash return-on-equity   33% 29% 39% 33% 35% 34% 32% Cash flows from operations/Equity. The higher - the better
IF you want to know more different ratios used in financial analysis you can download our FREE iPhone app http://itunes.apple.com/app/cfaformula/id456730098?mt=8
OR you can find these formulas on our website in CFA exam section http://www.financetoys.com/cfa/cfa_financials.html
STEP 6 Calculation of WACC    
The detailed explanation of WACC calculation can be found here http://www.financetoys.com/valuation/wacceng.htm
You can also download our iPhone app for calculation of WACC here http://itunes.apple.com/app/finforms/id444850569?mt=8
You can find WACC calculation in Bloomberg terminal (use function <Ticker> <Equity> <wacc> + Enter)
Or calculate Beta manually using our tutorials http://www.financetoys.com/risk/udbetaeng.htm
WACC calculation
   
Cost of equity 5,6%
Rf (Risk free rate) 2,0%
Rm (market return) 5,0%
B (Beta) 1,2
Cost of debt 7,1%
Tax rate 35%
Market capitalization ($mn) 1 481
Total debt ($mn) 472
Weight of equity 75,8%
Weight of debt 24,2%
WACC 5,4%
STEP 7 Calculation of fair value  
Now we need a DCF model table from STEP 1
We just fill the table with values calculated above
           
  2011F 2012F 2013F 2014F 2015F COMMENTS
EBIT (or operating profit) 150 136 150 160 157 From Income statement
Tax rate 35% 35% 35% 35% 35% From Income statement
Fully taxed EBIT 98 89 98 104 102 EBIT x (1 - tax rate)
DD&A 181 187 193 200 206 From Income statement or cash flows statement
Unleveraged cash flows 278 275 291 303 308 Fully taxed EBIT (NOPAT) + DD&A
Investments (or CAPEX) -234 -242 -250 -259 -268 From cash flow statement
Movements in working capital 14 -4 4 3 -1 From balance sheet or Cash flow statement
Unleveraged free cash flows 58 29 45 48 40 2 604 We added and extracted DD&A to demonstrate the logic for terminal value, i.e. we show that we invest
Terminal value         2 604 only to compensate DD&A
Net debt 275 199 101 -2 -97
WACC 5,4%
Terminal growth rate 1% The rate of growth after the forecasting period
NPV 1 817 1 948 2 131 2 309 2 480 We use NPV function in excel to calculate the fair value of the company at the end of each year
We came with a fair value of $1 948mn for the company at the end of 2012. We do not disclose the name of the company but it's current market capitalization is $1481mn.
Thus our fair value implies a 31.5% upside potential to the current price by the end of this year ( (1948/1481 - 1) x 100 )
If you need to find stock's fair value - simply divide the fair value ($1 948mn) by the number of shares
That's it.
If you have any questions or comment - mail me iamanalista@gmail.com
Or write something in the comments section on the page with this model http://www.financetoys.com/valuation/valmodels.html