Multiples (Valuation ratios)  
 
Neither this site, the available materials, nor its contents (including any information concerning   
In this spreadsheet I'll demonstrate how to create a multiples table  any securities mentioned on this site) constitutes, in the absence of an express statement on this site 
to compare valuations adequacy of different  companies  to the contrary, an advertisement of any securities or an offer to sell or a solicitation of an offer  
Financial formulas in some cases may be simplified to make understanding easier  to purchase any securities.                 
Multiples table for one company    
Move scrolls to change company's financials
STEP 1
Here is the example of creation of a table with multiples (valuation ratios) for one company REVENUE
First, we need to find a financial history for the company, then make a forecast for the next several years
I made the forecast for 2010-2012 interactive. You can change revenue, EBITDA and net income with the scrolls
For further calculation of P/E we also find earnings per share (EPS) = Net income / Number of shares
In this example I use financials of Patterson-UTI - a drilling company
Patterson-UTI Interactive cells
  2005 2006 2007 2008 2009 2010F 2011F 2012F EBITDA
Revenue, $mn 1 740 2 547 2 114 2 064 782 1514 1891 2189
EBITDA, $mn 746 1 224 859 818 245 416 430 798
EBITDA margin, % 42.9% 48.1% 40.6% 39.6% 31.3% 27.5% 22.7% 36.5%
Net income, $mn 373 673 439 347 -38 101 151 209
Net margin, % 21.4% 26.4% 20.7% 16.8% -4.9% 6.7% 8.0% 9.5%
EPS, $ 2.4 4.4 2.8 2.3 -0.2 0.7 1.0 1.4
NET INCOME
STEP 2
After we inserted all the financials in the table we need a price of the stock to calculate its market capitalization
Last price, $ 21.38              
Number of shares, mn 154
MktCap, $mn 3 295 3 295 3 295 3 295 3 295 3 295 3 295 3 295
Then we find enterprise value (EV) by adding the net debt to the market capitalization
Short-term debt, $mn 5.0
Long-term debt, $mn 95.0
Cash, $mn 73.9
Net debt, $mn 26.1
Enterprise Value, $mn 3 321 3 321 3 321 3 321 3 321 3 321 3 321 3 321
Now we have all the inputs we need and so we can start multiples calculation
STEP 3
We use all the data we got in step 1 and 2 to construct the final table with multiples: EV/Sales, EV/EBITDA, P/E
  2005 2006 2007 2008 2009 2010F 2011F 2012F
Revenue, $mn 1 740 2 547 2 114 2 064 782 1 514 1 891 2 189
EBITDA, $mn 746 1 224 859 818 245 416 430 798
Net income, $mn 373 673 439 347 -38 101 151 209
EPS, $ 2.4 4.4 2.8 2.3 -0.2 0.7 1.0 1.4
MktCap, $mn 3 295 3 295 3 295 3 295 3 295 3 295 3 295 3 295
Enterprise Value, $mn 3 321 3 321 3 321 3 321 3 321 3 321 3 321 3 321
EV/Sales 1.9 1.3 1.6 1.6 4.2 2.2 1.8 1.5
EV/EBITDA 4.5 2.7 3.9 4.1 13.6 8.0 7.7 4.2
P/E 8.8 4.9 7.5 9.5 -86.1 32.5 21.8 15.8
The result gives us nothing, because we need multiples of other companies from drilling industry for comparison
Multiples table for several companies  
In this example we will compare Patterson-UTI with other American drilling companies on multiples
Similar to what we did in the first example for one company, we do for four companies form this sector
Then we calculate an average value for multiples and compare them with Patterson's values
To compare Patterson's multiples with industrial average we calculate premium or discount of Patterson's values to average industrial values
By the way, the forecast for Patterson is taken from the first example, so you can change the estimates for the company
Company Share price, $ Number of shares, mn MktCap, $mn Net debt, $mn EV, $mn Net income 2010F Net income 2011F EBITDA 2010F EBITDA 2011F Revenue 2010F Revenue 2011F P/E 2010F P/E 2011F EV/ EBITDA 2010F EV/ EBITDA 2011F EV/ Sales 2010F EV/ Sales 2011F
Pride International 32.08 176 5 637 429 6 066 266 492 495 860 1 481 1 957 21.2 11.5 12.3 7.1 4.1 3.1
Rowan Cos 32.03 126 4 043 213 4 255 297 286 624 673 1 809 2 014 13.6 14.1 6.8 6.3 2.4 2.1
Helmerich&Payne 48.33 106 5 122 297 5 419 357 384 885 944 2 256 2 426 14.3 13.3 6.1 5.7 2.4 2.2
Ensco 49.21 143 7 035 -867 6 168 511 612 831 970 1 681 1 936 13.8 11.5 7.4 6.4 3.7 3.2
Average  15.7 12.6 8.2 6.4 3.1 2.7
Patterson-UTI Energy 21.38 154 3 295 26 3 321 101 151 416 430 1 514 1 891 32.5 21.8 8.0 7.7 2.2 1.8
Premium (-) / Discount (+)                     -51.6% -42.2% 2.1% -17.5% 42.7% 51.3%
We calculate premium with "-" because the existence of a premium means that Patterson's stock are expensive relative to industrial average and therefore have relative downside potential
The same logic applies to a discount. If Patterson's stocks trades with discounts, then they have positive upside potential because they are relatively cheap
NOTE! In the example above I compared only American companies. But if you compare companies from different countries pay close attention to the currencies in which the stock 
price and  company's financials nominated. Adjust figures so everything would be nominated in a single currency. 
I also strongly recommend to find a lot more peers in the industry thus your average multiples will be more representative
Trailing multiples    
What is trailing multiple?
Using trailing multiples you can see does the stock trade above or below its historical multiples
In the examples above we calculated multiples on the basis on annual financials. Trailing multiples are calculated on the base of the last four quarters financial results. 
For example we have revenue for 2008. We subtract 1Q2008 revenue and add 1Q2009 revenue. We get the revenue for the last four quarters from 2Q2008 to 1Q2009. 
After that we continue to subtract the last quarter and add the next. Thus we get trailing revenue. 
Below is the table with calculation of trailing financials for Patterson-UTI
We make calculations only for EV/S (EV to sales) multiple only just to explain the principle of trailing multiples
  1Q2007 2Q2007 3Q2007 4Q2007 1Q2008 2Q2008 3Q2008 4Q2008 1Q2009 2Q2009 3Q2009 4Q2009 1Q2010 2Q2010 3Q2010
Revenue, $mn 547 523 524 521 505 526 609 532 268 140 160 214 272 307 379
Trailing revenue, $mn       2 114 2 072 2 075 2 160 2 171 1 935 1 549 1 100 782 785 952 1 171
Now we need a pricing information, number of shares and net debt info (for enterprise value calculation). We will use monthly data for price. Net debt is taken from quarterly balance sheets -
That is why it changes every three month
We add revenue in the table only after 2007, because our first annual number is four quarters from 1Q2007 to 4Q2007. The logic here is that in the beginning of 2008 you know full year 2007
results and thus market price is based on knowledge of this annual figure
To calculate our multiple we simply divide EV by revenue (sales) for each period and get our trailing EV/S multiple
Date Share price, $ # of shares, mn Market Cap., $mn Net debt, $mn EV, $mn Trailing revenue, $mn Trailing EV/S Average EV/S
31.01.2007 24.15 158.9 3 837 106.6 3 944      
28.02.07 22.29 156.5 3 489 106.6 3 596
30.03.07 22.44 156.5 3 513 -16.9 3 496
30.04.07 24.39 156.5 3 818 -16.9 3 801
31.05.07 26.42 156.7 4 140 -16.9 4 123
29.06.07 26.21 156.7 4 107 -12.5 4 095
31.07.07 22.9 156.7 3 589 -12.5 3 576
31.08.07 21.47 157.2 3 375 -12.5 3 362
28.09.07 22.57 157.2 3 548 -10.5 3 537
31.10.07 19.94 157.2 3 134 -10.5 3 124
30.11.07 18.85 154.9 2 921 -10.5 2 910
31.12.07 19.52 154.9 3 025 32.6 3 057
31.01.08 20.2 154.9 3 130 32.6 3 162 2 114 1.50 1.85
29.02.08 23.73 154.0 3 655 32.6 3 688 2 114 1.74 1.85
31.03.08 26.18 154.0 4 032 -50.3 3 982 2 114 1.88 1.85
30.04.08 27.94 154.0 4 304 -50.3 4 253 2 072 2.05 1.85
30.05.08 31.48 154.4 4 862 -50.3 4 811 2 072 2.32 1.85
30.06.08 36.13 154.4 5 580 -62.2 5 517 2 072 2.66 1.85
31.07.08 28.42 154.4 4 389 -62.2 4 327 2 075 2.08 1.85
29.08.08 28.42 156.6 4 452 -62.2 4 389 2 075 2.11 1.85
30.09.08 20.02 156.6 3 136 -25.0 3 111 2 075 1.50 1.85
31.10.08 13.27 156.6 2 079 -25.0 2 054 2 160 0.95 1.85
28.11.08 12.49 154.6 1 931 -25.0 1 906 2 160 0.88 1.85
31.12.08 11.51 154.6 1 780 -81.2 1 698 2 160 0.79 1.85
30.01.09 9.56 154.6 1 478 -81.2 1 397 2 171 0.64 1.85
27.02.09 8.59 153.1 1 315 -81.2 1 234 2 171 0.57 1.85
31.03.09 8.96 153.1 1 372 -192.3 1 179 2 171 0.54 1.85
30.04.09 12.71 153.1 1 946 -192.3 1 754 1 935 0.91 1.85
29.05.09 14.34 153.4 2 199 -192.3 2 007 1 935 1.04 1.85
30.06.09 12.86 153.4 1 972 -167.7 1 805 1 935 0.93 1.85
31.07.09 13.81 153.4 2 118 -167.7 1 950 1 549 1.26 1.85
31.08.09 13.29 153.6 2 042 -167.7 1 874 1 549 1.21 1.85
30.09.09 15.1 153.6 2 320 -119.2 2 200 1 549 1.42 1.85
30.10.09 15.58 153.6 2 393 -119.2 2 274 1 100 2.07 1.85
30.11.09 15.39 153.6 2 364 -119.2 2 245 1 100 2.04 1.85
31.12.09 15.35 153.6 2 358 -49.9 2 308 1 100 2.10 1.85
29.01.10 15.36 153.6 2 359 -49.9 2 309 782 2.95 1.85
26.02.10 15.44 153.6 2 371 -49.9 2 321 782 2.97 1.85
31.03.10 13.97 153.6 2 145 -63.9 2 081 782 2.66 1.85
30.04.10 15.31 153.6 2 351 -63.9 2 287 785 2.91 1.85
31.05.10 14.03 153.9 2 159 -63.9 2 095 785 2.67 1.85
30.06.10 12.87 153.9 1 980 -96.0 1 884 785 2.40 1.85
30.07.10 16.43 153.9 2 528 -96.0 2 432 952 2.55 1.85
31.08.10 14.75 154.1 2 274 -96.0 2 178 952 2.29 1.85
30.09.10 17.08 154.1 2 633 26.1 2 659 952 2.79 1.85
29.10.10 19.41 154.1 2 992 26.1 3 018 1 171 2.58 1.85
30.11.10 19.7 154.1 3 036 26.1 3 062 1 171 2.62 1.85
After we made all the calculation we can draw a chart to see if Patterson's stock is cheap or expensive right now vs. its historical values
We also calculated average EV/S to know if currently the stock is cheap/expensive vs. its average EV/S
As we see currently the stock trades above its average EV/S which means it is relatively expensive. Although we should understand that the key reason behind that is the impact of 
financial crisis 2008. It is always better to take longer history. Anyway the chart is quite useful. We can see that now stock trades at pre-crisis levels on EV/S multiple
It is  also useful to compare trailing multiples history with the forecasted multiples calculated in the first two examples
The same calculation you can make for all other multiples (EV/EBITDA, P/E, etc)