




















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 20082010 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 10K
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 company’s 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 nonoperating
expense/income 
754 
83 
0 
0 
0 
0 
0 
0 

This line is too volatile and nonsustainable,
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 20112015 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 = Longterm debt + Shortterm 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 longterm assets 

10 
13 

















Total noncurrent 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) 



Shortterm 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 

































Longterm debt 

471 
472 
472 
472 
472 
472 
472 

Debt changes via the Statement of cash flows
(see the link in the cell) 



Other longterm liabilities 

181 
232 
232 
232 
232 
232 
232 

Leave this line unchanged 








Total noncurrent
liabilities 

652 
704 
704 
704 
704 
704 
704 

































Equity 

679 
880 
889 
955 
1 001 
1 053 
1 117 

It’s 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 






Nonsustainable 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 longterm debt 
0 
221 
0 






Use these 4 lines to adjust cash flows. You
need to have positive cash at 



Payments of longterm debt 
6 
331 
0 






the end
of the year. You can achieve that by regulating company’s debt. 


Proceeds from shortterm debt 









Ideally you should dig into 10K filling to
find a debt maturity schedule. But 


Payments of shortterm 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 












company’s 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
buyside 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 






DebttoEquity ratio 

0,69 
0,54 
0,53 
0,49 
0,47 
0,45 
0,42 

Total debt/Equity. 









Performance ratios 




















Cash flowtorevenue 
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 returnonassets 

16% 
15% 
19% 
17% 
18% 
18% 
18% 

Cash flows from operations/Total assets. The
higher  the better 



Cash returnonequity 

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 
































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































