This article is a continuation of the “Case Study on Oil Stocks”. Rather than look at the long term growth prospects and investment potential, we will look at oil companies from a short term trading perspective. To assist in performing the relevant calculations, I have designed a spreadsheet enabling a trader to gauge how much the earnings per share and book value per share change when the price of commodities change.
My first motivation for researching this area began when I traded oil and gas exploratory companies. I could not understand why, despite the lack of significant news, different exploratory companies were affected by the price of oil and gas differently. When I was trading a long/short pair in two oil companies, I starting losing money on my position. I asked a colleague of mine why one leg of the pair was rising so much faster than the other. He explained to me that it’s very simple. Company A mainly produces natural gas whereas company B mainly produces crude oil, and natural gas prices have dropped quite considerably in the past few months.
The methods that I have employed to choose my long short pair were based on fundamental analysis of the ratios described in the article “Advanced Equity Trading”. For the long position, I chose a stock which I believed had a low P/E, low forward P/E, low price to book, and a high return on capital, as well as other variables which I described in the article. Despite the pair having performed well I should have also checked to see if both companies had the same proportion of oil and gas with each other. This has the effect of hedging the oil exposure and instead creates the return on the long/short position on other ,non oil, related factors.
After having created the relative value table for oil companies, one needs to carefully choose which companies to long and short. The second step is to see how well correlated the two companies are with oil and natural gas. For this reason I have created the oil and gas company evaluator, which holds volume of reserves constant against the variable, price. The aim of this sensitivity gauge is simply to see how much net income or earnings per share and book value per share change for any fluctuation in the price of oil and natural gas.
The spreadsheet is split into 3 components. The commodity price inputs, balance sheet and income statement inputs, and the theoretical changes in book value and earnings that these changes will produce. All the input fields are highlighted in yellow. The rest of the variables are calculated by based on changes in the yellow cells.
Click Here to Download the Spreadsheet
Begin with the annual report of the company. We need to understand the income statement and fill out the necessary fields in the spaces provided. If we look at W&T Offshore, we can go to page 52 of its 2006 annual report and read the income statement. The income statement will specify its revenues and costs. We do not need to input the revenues but we do need to put in the total costs and expenses in the costs field which is C27. In WTI’s case this will be 482,851,000 USD in operating expenses and 11,261,000 USD in net capitalized interest expenses (this is calculated by subtracting 13,238,000 USD in capitalized expenses from 30,418,000 in incurred expenses and adding back “other income” to arrive at (30,418,000-13,238,000+5919000=11,261,000).
We need to input the price of the stock in cell C11. We then need to put in the sales volume information which is on page 32 of the report. WTI produced 60,447,000 Mcf of natural gas and 6,456 MBbls of oil. We fill this data in cells C38 and C40. Now we can simply calculate the EPS for WTI using the average prices on page 32 or simply changing the prices in fields D2 to D4 and E2 to E4. This is especially useful if there has been some news that the price of oil and gas has changed. We can input the figures for yesterdays oil prices and today,s and calculate the theoretical amount that the stocks EPS will rise as a result of the price changes in commodities. I have split up the EPS effects of oil and natural gas separately as well as a total for both changes. This allows the trader to gauge how much each commodity should have an effect on the price of the stock. Furthermore it also allows us to find put how the P/E of the stock changes with varying oil and natural gas prices.
The same principal is applied when evaluating the book value per share on the company. First we need to input the proven and unproved reserves for oil and gas in fields C30 and C32. This can be extracted from the annual report on page 71.
Cell C13 contains the total current assets which can be extracted from the annual report. “Other Long term Assets” is simply the furniture and other office buildings which is contained in the balance sheet. Restricted assets in cell C15 is labeled as “Restricted deposits for asset retirement obligations”.
We excluded oil and gas properties of the company because we want the input to be from the price variations and reserve sizes in cells C30 to C32. Cell 17 is the total liabilities of the company, which is the sum of short term and long term liabilities. If anyone needs to see the behind the scenes calculations simply unhide rows 7-9 and 19-26.
This model allows us to vary the oil and gas prices to see how book value and EPS change. It is useful for a trader who wants to gauge sensitivity of an oil company’s price to changes in oil and gas commodity prices.
As a more advanced tool the spreadsheet could also be used to evaluate how much the earnings of the company will change if the company changes guidance on production.
For example on July 13th 2007 there was a press release that WTI is revising its second quarter and full year production guidance. For example for crude oil the prior guidance was 8.1-8.5 MMBbls of oil for the full year, the revised quantity is 7.7-8.0. Similarly the prior guidance for natural gas was 84.1-88.7 Bcf, the revised quantity is 74.7-78.7 Bcf.
To gauge how much theoretically the price of WTI should drop following the press release we need to change 2 main variables. Changes in the size of reserves and prices of commodities. For the sake of simplicity we will replace the updated reserves with the higher end values. For example crude oil’s prior guidance was 8.1-8.5; we will input the prior value as 8.5 MMBbls. Similarly the revised guidance is expressed as 7.7-8.0; we will input a value of 8.0. To calculate the EPS using the guidance expected the day before the press release we need to find out how much the price of oil and gas was on July 12th 07 and input these values in fields D2 and D4. The price of the front end contract for oil and natural gas was 72.5 and 6.50 USD respectively. Now we input the quantities 88,700,000 and 8,500,000 in cells C36 and C40 respectively. The EPS Values we will get is 6.47 USD per share. If we now change the reserves to 80,000,000 and 78,700,000 respectively and change the natural gas and oil price to 6.66 and 73.93 USD we will arrive at an EPS of 5.70 USD per share. This implies a 12% decrease earnings for WTI.
If one wants to simply change the prices of commodities in cells D2 to E4 without changing reserves one can arrive at a percentage change in book value and earnings in column I.
Did you like this article?
Submitted By 
