Tuesday, December 18, 2012

2013 Stock Market Return Estimate

As I mentioned in the Ohlson's blog, you must estimate the projected market return.

Note, it doesn't have to be 2013's projected return.  It could be the projected annual average over the next five years, if that is what would make sense to your financial analysis.

I used a projected return of 10%.

I found a couple of published projections from Vanguard and Gurufocus of 6-9% and 4.3% respectively.

A survey from the CFA institute is projecting better performance over last year for the U.S.

And from January 3 to December 14, the S&P 500 gained 12% (pretty good!). (Jan 3 opening of 1258.86 to 1413.58 closing on December 14).

So I'm just attempting to be conservative by landing somewhere in the middle.  Besides "TEN" is such a nice tidy number.  Far superior to say, 8.9%, which is ghastly or 11.25%, which is even worse.  So when in doubt, pick 10.  You heard it here first and I guarantee they'll never teach you that at either U of T or Harvard. 

Calculating Intrinsic Value using Ohlson's Clean Surplus Theory

I haven't posted in a long time and it isn't because I lost interest.  I love doing financial analyses, but some other things were demanding my attention these past five months. 

This is my favourite analysis model currently, but it is a bit tedious to put together; there are a lot of moving parts and it is easily broken.

Just as a bit of background: I've been using this as a proxy for margin of safety, however, it is actually a calculation for the intrinsic value of a security, which is then compared to the actual price.  When the stock is trading at a discount, in my mind, a margin of safety is created and the stock becomes a purchase candidate.

This isn't a margin of safety in the way Mr. Graham described it however.  You can read a definition here.

I guess part of reason for not using margin of safety is because currently interest rates are so low almost everything has a margin of safety.  Or maybe I don't understand it well enough and I'm scared because I don't really get it.  Perhaps I should actually attempt the calculation and then maybe some kind soul out there can provide some feedback.

Ohlson's Clean Surplus Theory says that the intrinsic value of a stock equals the net book value of the firm's assets plus the expected present value of future abnormal earnings (goodwill).  Abnormal earnings is the difference between actual and expected earnings.  Expected earnings are calculated by multiplying opening shareholders' equity (I use book value per share) by the firm's return on equity less the dividend payout ratio.

To build the spreadsheet for the calculation, set up a financial analysis template in Verdant Analysis that includes:
  • beta (this is a measure, not a line item),
  • Shareholder's Equity (B/S),
  • Preferred shares, redeemable,
  • Nonredeemable Pref. Stock,
  • Total Shares Outstanding,
  • Dividends  per share,
  • EPS,
  • Preferred Dividends.
Apply the template to the portfolio, in this case, Integrated Oil & Gas, and export to Google Docs or Excel.

Once exported, you can optionally name the front sheet "data" and set up a second sheet called "formula".

On the formula sheet, I carried over the security names and actual price directly.  The next 22 columns are:
  • Theoretical price (this is the intrinsic value),
  • Premium/discount (vis a vis the actual price),
  • A column for each of book value at t through t+6,
  • Sum of the present value of expected abnormal earnings,
  • A column for each of the expected abnormal earnings at t+1 through t+7,
  • Firm's Cost of Capital (calculated using CAPM),
  • Return on Equity,
  • Risk Free Rate,
  • Expected Market Premium,
  • Dividend Payout Ratio.
This spreadsheet is calculated roughly from right to left, with the key and final calculations being the theoretical price and the premium/discount.

Dividend payout is dividends per share divided by EPS: 

=round(data!L2/data!M2,2)

The next category, projected market return, is the only estimate that is required in this formula, which is one reason why I like it.  This number drives the firm's cost of capital calculation (using CAPM).  I'm using a value of 10%, but you can and should adjust this number to your own insights and predictions.  I did another little post on why I choose 10% projected market return, for those who are interested.

The one year risk free rate is .14%

Return on Equity is (Net Income after Tax - Preferred Dividends) divided by (Shareholder Equity - Preferred Shares).  The formula looks like this:

=round((data!G2-data!N2)/(data!H2-data!I2-data!J2),2)

Next we calculate the firm's individual cost of capital using the capital asset pricing model (CAPM), which employees "beta".  Mr. Graham and that fellow from Omaha have raised some very worthy points about the usefulness of beta. One of them said, and I paraphrase here, "beta is a really stupid measure of risk."  He used an example of an over-priced low beta stock versus an under-valued high-beta stock.  

Those guys are geniuses and not because they know a lot of esoteric stuff (they might, I don't know).  For me they're geniuses because they THINK.  They look at something and they figure out if it makes sense.  They don't just accept something blindly.  I think it is also called common sense, in my humble opinion, a most uncommon virtue.

How do we reconcile this then?


Well, mathematically, beta is just the output of a regression that measures how a security moves with the market.  So I feel that if we drop the pre-conceived notions about it (like it's the only statistic of interest) and we just take it for what it is, then it can be useful.

Here is another aside: we've had some trouble with our betas (we meaning Verdant Analysis).  At this nascent stage in our development we don't have a dedicated data feed; we collect publicly available, non-propriety information (like financial statement data, closing price information, interest rates etc.) from a number of places.  We collect all the price and market information needed to calculate beta and we run the calculation ourselves (so we know exactly what it is: a 5-year rolling beta). 

However, if one of those places "changes" something, we can break.

Anyway, something broke and some of our betas got off. It's fixed, but not yet deployed.  I'm sorry and it's disappointing to not use our own betas in the calculation, but sometimes in life, a lot of the time in fact, you just have to let it go and move on.  So, on the data sheet there is another column for beta where I pulled betas from Google Finance and Yahoo Finance.  

To calculate the firm's cost of capital using CAPM add the risk free rate to the firm's beta multiplied by the market premium, or:

=V2+data!O2*(W2-V2)

We next calculate the BV per share for time t.  It is the common shareholders' equity divided by the common shares outstanding, or:

 =round((data!H2-data!I2-data!J2)/data!K2,2)

To calculate subsequent book values we multiply the previous BV (for example, to get BV(t+1) the previous is BV(t)) by a growth factor.  The growth factor is the return on equity for the earnings that remain in the company (earnings that are not paid out as dividends).  It is 1 + ROE x (1 - dividend payout ratio), or:


=E2*(1+$U2*(1-$X2))

This calculation is repeated until all of the future book value per share are calculated.

Calculating the abnormal earnings involves multiplying the previous year's book value per share by the difference between the return on equity  and the dividend payout ratio.  In other words, E(a)(t+1) = BV(t) * (ROE - DPO), or:

=E2*(1+$U2*(1-$X2))

Once again, repeat until all abnormal earnings are calculated.  Note, there can be negative abnormal earnings is the dividend payout ratio is greater than the return on equity.

Next, sum the present value of the abnormal earnings:

=ROUND(M2/(1+T2)+N2/(1+T2)^2+O2/(1+T2)^3+P2/(1+T2)^4+Q2/(1+T2)^5+R2/(1+T2)^6+S2/(1+T2)^7,2)

and then add this to the book value at time to calculate the theoretical price:

 =E2+L2.

The premium/discount calculation is calculated as the premium or discount of the actual price relative to the theoretical price or:

 =round((B2-C2)/abs(C2),2).

Note, we take the absolute value of the denominator in case the theoretical price is negative.

We are looking for securities trading at a discount relative to their theoretical price.

In the next post, we'll look at the numbers and have a think about them, because of course, just because it says it doesn't mean it's real. If we ever want to approach the great ones, we have to apply some common sense.

Thank you for reading and please let me know if you have any comments or questions.

The spreadsheet with all the calculations is here.

Jen 

Saturday, July 28, 2012

Owner's Earnings


It has been over a month since I last posted, for which I apologize.

The next value indicator is Owners' Earnings, which is defined as Net Income less the costs of granting stock options, unusual, nonrecurring or extraordinary charges and income from pension funds. According to Ben Graham, it should grow at a steady 6 to 7% per year.

Now, there was a time when employee stock options weren't expensed, roughly before 2006.  So we don't need to worry about this one, as our analysis isn't going back that far.

As for pensions...this is information that needs to be extracted from the financial statement notes and is beyond the scope of this analysis.  Perhaps at some point Verdant will make that kind of information available and manipulable, but for today, we are going to have to content ourselves with a proxy figure for Owners' Earnings, Net Income Before Extraordinary Items.

This analysis is then very straightforward:
1) Set up an Analysis Template with Net Income Before Extraordinary Items for years Y, Y-1, Y-2, Y-3, Y-4, Y-5.
2) From the US Oil & Gas Integrated portfolio, click the Analyze button and select that template and export the results to Google Docs or Excel.
3) Calculate the growth between Y and Y-1, Y-1 and Y-2 etc. with the following formula:
 =(F2-G2)/G2
Where F2 is Y and G2 is Y-1 for the first security.
4) I create an average of all of the inter-years growth numbers with the following formula:
 =AVERAGE(L2:O2)
Note: some securities may not have data for every year.  If that is the case you'll get a divide by zero error (DIV/0).  To correct this, I manually shorten the average calculation to account for only the years that have a growth calculation.
4) To "rate" the results, I employ two "if" calculations:
 =if(P2>7%,1,0) and  =if(P2<0,-1,0),
5) When these two "if" statements are summed, we get the effect of applying a rating of 1 to securities that have been growing owners' earnings at an average rate of at least 7%, a rating of 0 for securities that have been growing at a rate of 0% to 7%, and a rating of -1 for securities with negative growth.

If you have to look at the data you'll notice that most of these companies have widely fluctuating changes in growth between years.  Welcome to the oil and gas industry - it is volatile and the volatility in reflected in the stock price.  It isn't an investment for everyone.  For that reason, I think that the value indicators are extra important, it can save you a lot of stress if you follow that very important maxim "buy low, sell high".

You can view the spreadsheet here.

The next piece of the analysis is a calculation that I really like, though it is rather long and complicated.  It is a stock valuation calculation and I use it to calculate out Ben Graham's "Margin of Safety".

Thursday, June 21, 2012

Value Indicators: Capital Structure

In this indicator, we want to see long term debt (LTD) including preferred stock be less than 50% of total capitalization.

In Verdasis, set up an analysis template and pull four pieces of financial statement information, all from the balance sheet:
  • Redeemable Preferred Share Stock,
  • Preferred Stock - Non Redeemable, Net,
  • Total Debt,
  • Total Equity.
All items are for the current annual period or Y.

Apply the template to the portfolio and export to the spreadsheet application of your choice.  The formula is:

=(F2+G2+H2)/(F2+G2+H2+I2)
 
Where the F and G column are the two preferred stocks line items, H2 is total debt; all of which is divided by the sum that makes up the total capitalization.

The Rating formula is:

 =if(J2<50%, 1, -1)

You can see the spread sheet here.

Thanks for reading, be back early next week.
 

Monday, June 11, 2012

Value Indicators: Accounting Changes, Discontinued Ops, Extra-Ordinary Items

For this indicator I'm using a new feature in Verdasis called "Fast Exports" where you can down-load entire financial statements into excel. I could have accomplished the same thing with the analysis templates, but I don't know, I felt like shaking things up.  There are so few opportunities to pierce your eye-brow and dye your hair pink, figuratively speaking, when you're doing investment analysis.

The How-To:
1) I took my list of securities that I'm analyzing, the US Integrated Oil & Gas ones, and then clicked open the "fast exports" tab on the left.  I then clicked "Financial Statements (beta)",
2) I entered my first security, PBR into the security field and choose "PBR:NYSE" from the list.  The financial statement is "Income Statement", reporting interval is yearly, I selected all of the available years by choosing the bottom year, holding the "shift" key down, then clicking the top year.
3) Click "OK" when the pop-up window asks if you want to open with Microsoft excel.  Click "enable" on the next box.
4) After clicking in the affirmative on those above mentioned pop-up windows you'll wind up with an excel spreadsheet that will display several years of income statements for the security you entered.
5) Scroll down and see if there are any entries for Accounting Changes, Discontinued Ops or EO Items.  They will be in rows 23, 24 and 25 respectively.

For this indicator, I'm just eye-balling the results, rather than writing an logic equation.  Why? Just faster for the purposes of this blog analysis.  There are advantages to writing the logic equation, number one being that it will always stay up to date.  So for that reason, I probably should, but I'm not.  I won't apologize. 

Ideally, you want to see all zeros from B23 to F25.  This indicator is rather akin to the quality of earning indicator in that it gives you a sense of how much faith you can put into the statements themselves.  These three classifications open up the possibility of abuse as they can mask and manipulate actual results.  Let me give you a couple of examples:
 1) Management decides to reduce the rate at which capital items are amortized.  Now, the reason could be perfectly legitimate - it could be more reflective of what the industry as a whole does or it could be more indicative of their actual consumption.  Or it could be an effort to reduce expenses and pump up earnings.
2) "Unusual" transactions that have a detrimental effect on earnings are classified as extra-ordinary whereas similar type transactions that have a positive effect on earnings are classified as operational.


However, sometimes management should use those categories.  Sometimes accounting changes are mandated by GAAP, sometimes you really do discontinue operations and sometimes there are acts of God.

So, arbitrarily, I make the following rules:

The company can earn a rating of one if all of the following are true:
  • The company records one or zero items in the past three years,
  • The company records one or zeros items in years Y-3 or Y-5,
  • The company records no more than two items is years > Y-6.
The company can earn a rating of zero if the following is true:
  • The company records two items in the past three years,
  • The company records two items in the years Y-3 to Y-5,
  • The company records no more than three items in years > Y-6.
If everything is false, then the company earns a rating of -1.

To see the spreadsheet click here.





Saturday, June 9, 2012

Value Indicators:  Growth in Cash Flow from Operations


As a bit of background to financial statement analysis, let me share a truth: profit is an accounting construct and cash is king.

I have an accounting background and tremendous respect for its symmetry.  I love the way the transactions get captured and each of the three statements flow into one another.  Its development, without a doubt, was inspired genius.  But it has weaknesses.  A lot of them stem from the rule framework (GAAP) that has developed in the past one hundred years or so.

GAAP offers discretion in certain policy choices that ultimately effect the number that we call "net income" or "profit" or "earnings".  Cash on the other hand is much more independent, much harder to manipulate.  In the Statement of Cash Flows, the movement of cash is separated into three areas: Cash from Operations, Cash from Financing and Cash from Investment.

Growth in cash from operations is indicative of how well the company is managing its operations and creating value.

According to Mr. Graham, we want to see growth of 6 to 7% per year.

To execute this analysis, build an analysis template in Verdant by selecting Cash Flow from Operations Activities for each of Y, Y-1, Y-2, Y-3 etc.

Go back into portfolios and choose US Integrated Oil & Gas.  Click the "Analyze" button and select the appropriate template.  Export to Google.  Once in Google build several growth equations.  We want to see the growth year over year, for example the growth in Y over Y-1.  The equation is

=(F2-G2)/G2

Where F2 is CFO in the current year G2 is CFO in the prior year.

There are different ways to determine the growth of 6 or 7% per year.  For expediency, I just took an average with this equation:

=average(L2:O2)
I then wrote two "if" equations.  One to give a rating of 1 if the average was greater than 7%, zero if not and the other to give a rating of -1 if the average was less than zero, zero if otherwise.  Here they are respectively:

=if(Q2>7%,1,0)
 =if(Q2<0,-1,0)

Q2 is the average that was calculated above.

The final step is to sum the two ratings to get a final rating:
=sum(R2:S2).

 That is it for this indicator!  To see the whole spreadsheet click here.
 

Tuesday, June 5, 2012

Value Indicators: Preamble and Ben Graham's P/E Ratio

I've been woefully lax on posting  for the last little while.  Let's jump right in and look at the value indicators for this sector (oil & gas, integrated).

The value indicators I use are:
1) The Ben Graham P/E ratio,
2) Growth in Cash Flow from Operations,
3) Accounting changes, Extra-ordinary earnings, special items,
4) Capital Structure,
5) Owners' Earnings,
6) Theoretical Price.

Those of you familiar with Ben Graham's work will no doubt recognize these indicators.  For the majority, I've taken his work verbatim (or at least intended too).  A couple of times I've tempered the measure a bit to make it fit the information I have access to and one, as you'll see, is most definitely NOT something that the eminent Mr. Graham bestowed upon us.  It is, however, a nifty calculation.  And even thought it uses beta, I still think it speaks to the spirit of the measure, if not the law.


Mr. Benjamin Graham.  Definitely one of the good guys.  Thank you for sharing your great mind.

 

Ben Graham's P/E Ratio

You know, for the "simplest" and most often cited financial ratio the P/E ratio has a lot of different ways it can be calculated.  I find that disturbing, or at the very least, a pet peeve.  It is like driving down a road that suddenly, for no apparent reason, changes name, or the analog of that, a road that continues in name, even though it ended somewhere else.  The point is, it is confusing.  And you can get lost.

Which is why Verdant Analysis is just so good, so useful.  You know exactly what financial ratios you are getting because you've built them yourself.  And we're going to develop more and more great stuff beyond the ability to do ratios that will be simple to use, yet powerful in scope.

Ben Graham P/E ratio takes the average of the last three earnings figures as its denominator and Mr. Graham's rule of thumb is to limit yourself to securities trading at 15 times earnings or less.

It is a simple and easy to do calculation.  
1) Build an analysis template in Verdant by requesting the diluted, normalized EPS for Y, Y-1 and Y-2.  This is found on the income statement.
2) Export to Google or excel
3) Calculate the PE ratio with the following formula: C2/(average(F2:H2)).  C2 is the security's price. F2, G2 and H2 are the three earnings.

The next step is to rate the results.  I used three "if" calculations in order give a rating of 1 to PEs greater than 0 and less than 15, -1 for PEs less than zero and greater than 25 and 0 for PEs between 15 and 25.  

You can see the spread sheet here

Thanks for reading and I'll be back with the second indicator in a couple of days! 

Tuesday, May 22, 2012

Summary of the Quality of Earnings Measures


The next step is easy and quite satisfying: sum all of the quality of earnings ratings to get a final figure*.  The bigger the number, the higher our confidence in what the financial statements are telling us.

I turned the sums into a chart:




You can see that CVX (5 out of 6) and BP (4 out of 6) look the best, but I would still consider the 3 out of 6's as well, depending how the other factors turn out.

If you were looking for a short candidate the securities with a negative number would be a consideration.

If you want to see the spreadsheet that drives the chart, click here:


Quality of Earnings Summary Spreadsheet

Our next step is to do some value analysis.  I just think it is common sense not to overpay for a stock.  When we over pay, we are letting our emotions get involved - objectivity is definitely a good discipline to develop when investing.  I think I could trace at least 70% of my investing errors to a loss of objectivity.

Besides that, the greater the difference between the price and the value of the security, the greater our risk.   I don't like excessive risk, I like to sleep well at night, which is why I like pessimistic markets, unfashionable sectors, and undervalued securities.

*To sum all of the ratings, I created a new spreadsheet and used the "importrange" feature to collect the rating for each measure.

Friday, May 11, 2012

Investment Analysis: Selling and Administration Expenses

This is the last of the quality of earnings factors that I look at.  Just for complete disclosure, Lev & Thiagarajan have six others.  I'll do a brief post on those to explain them and why I choose not to use them at another time.

This signal is similar to the last one we looked at, "Gross Margin".  Many S&A costs are fixed in the short term and a disproportionate changes relative to sales are rich in information.  We look at the percentage changes and the formula is:

% change in sales - % change in S&A.

If the result is negative, it is a negative signal, if it is positive, it is a positive signal.

In Verdasis, we set up an analysis template that has four elements:
  • Total Revenue, Y
  • Total Revenue, Y-1
  • Selling/General/Admin Expenses, Y
  • Selling/General/Admin Expenses, Y-1
If you need more how-to, we have a video tutorial here.  The third one shows how to set up templates.

(I'll just also point out that once you've set up the templates, you can use them again and again on different portfolios.  We're also playing around with different ideas to make the investment analysis process faster with per-fabricated ratios - some users will set up their own propitiatory ones and others will be available for all users for a fee.) 

Once the information is exported to the spreadsheet, calculate the percentage changes and take the difference.  I am using a 5% band around zero to remove the small changes as I did in Gross Margin.  If there is missing data, for example one of the stocks is missing the S&A information and I got the "dividing by zero" warning, I manually enter a "zero" for the final rating.

You can see the spreadsheet here.

OK, exciting times!  All done the quality of earnings analysis and next post I'll bring it all together by summing the ratings.  Hopefully you are starting to get a sense of how bringing this financial statement data into context gives us true, meaningful insight into these companies.  This stuff is a lot deeper than most of the everyday common ratios that we can easily find on financial web-sites and in the press.

But the proof will be in the pudding - once we're done (all done, there is still some more analysis we have to do, this is just the start) I'm going to take a position and we'll see how the pick performs vis a vis the rest of the group and the market.

Thank you for reading, have a great weekend!

Sunday, May 6, 2012

Investment Analysis: Gross Margin, Part 2

Hello,

As per my last post, I decided to exclude variances that are within a certain threshold.  I decided on a "+-5%" band, so that only securities with % change in Gross Margin - % change in Sales less than -5% earned a negative one rating and securities with a greater than 5% difference earned a positive rating.

I did two "if" formulas in order to reflect this:
=if(L2<-5%,-1,0)
and
=if(L2>5%,1,0)
and then summed the two together to get the "Final Rating".

**It seems to me that it should be formulatically possible to just do it in one logic test, but alas, Google docs and excel both defeat me.  If YOU know how to do it, I'd appreciate the enlightenment. 


To access the spreadsheet, click here.

We have one more quality of earnings indicator, selling and administration expenses, and then we'll take a look at the full Q of E picture.  I will post the S&A analysis by the end of the week.

Thank you for reading!
Jennifer

Thursday, May 3, 2012

Investment Analysis: Gross Margin

Today I'm covering the quality of earnings indicator, "Gross Margin."  

The premise behind this marker is that disproportionate changes relative to sales are informative.  According to Lev, Thiagarajan and presumably many analysts, gross margin is a better-than-earnings figure for determining the relationship between a firm's input and output prices.  This price/cost ratio reflects interesting underlying factors such as the intensity of competition and the degree of operating leverage.

This measure takes the difference between the percentage change in sales and the percentage change in gross margin:

 % change in gm - % change in sales

As I mentioned above, it is the disproportionate changes which are informative.  L & T specifically state that a larger decrease in the gross margin relative to a decrease in sales is a negative sign.

Thus, in the above example, the formula would yield a negative number.

We would also get a negative value if the % change in gm increased, but the % change sales increased more.  

We'll get a positive value when there is a larger increase in the gross margin than in sales.  We'll also get a positive value when there is a smaller decline in the gross margin when sales also decrease.

If this isn't clear, let me discuss this for a moment in business terms.  The formula for gross margin is:

(Sales - Cost of Goods Sold) / Sales

The cost of goods sold are the inputs that are going to track very closely with the actual sales made.  For example, if you make nails, you cost of goods sold will be the raw materials, steel, and the direct labour.  If your sales go down, you need less inputs and your cost of goods sold go down. The reverse happens when your sales go up.  So the normal situation is a tight correlation between sales and the cost of goods sold.

When the situation deviates from the norm it can indicate either that efficiencies have been lost or they've been gained.  They're lost if the relationship moves apart.  They're gained if they move tighter together.

We capture this "movement" in the formula above.

I'm going to do the actual investment analysis tomorrow.  There is another nuance that I want to mention with this particular measure that isn't discussed by L & T, but does concern me.  I think that either small deviations should be ignored (and given a rating of zero) by arbitrarily choosing a range (such as between -5% and +5%) OR the analyst should benchmark what is "normal" for the company and then fluctuations outside that range be rated appropriately.  It is something to consider and when we build the actual financial model on this indicator we can make some judgments.

Thank you for reading, and I'll be back tomorrow.


Tuesday, May 1, 2012

Investment Analysis: Capital Expenditure

It's been longer than anticipated between posts.  I got busy with other things, particularly the launch of our new alpha calculator.  We'll take advantage of it once this analysis is complete and the best investing option (if any) becomes apparent.  I'm also going to dedicate a post to describing the alpha calculator (which I debated calling the "alphalator" when I was feeling rather silly) once we get further down the road on this analysis.

This next factor is similar to the R&D calculation and seeks to uncover the same kind of information - how much is the company investing in staying competitive?

Capital expenditures have the potential of making the company more productive and so we consider it a good thing when these are greater than the industry average.  Another reason is that investment in capital items can indicate management confidence in future earnings.

I'm going to use the capital expenditures line item from the cash flow statement.  I think it is a nice, tidy alternative to figuring out the numbers from the balance sheet and getting involved in the non-cash, highly subjective area of amortization.

For those of you not familiar with cash flow statements, a negative number demonstrates cash out of the company, whereas a positive number indicates cash into the company.  This number should usually be negative; if it was positive it would indicate that the company was liquifying its assets.

Here is what we need to do:

  1. Create an analysis template in Verdasis with two data columns: Capital Expenditure, Y and Capital Expenditures, Y-1.  
  2. In the portfolios section, apply the analysis template to the "US Oil & Gas, Integrated" portfolio by clicking the "Analyze" button beside that portfolio in the left hand menu bar.
  3. Export to Google Docs or Excel.
  4. On the spreadsheet I calculate the industry average for both Y and Y-1 at the bottom of those two columns.  I use the following formula:
  5. I calculate the industry growth (or decline) with this formula:
  6. I repeat the above growth/decline formula for each company,
  7. I take the difference between the company's growth and the industry average with this formula:
  8. Finally, each value gets a rating.  If the number is positive, they get a "1", if expenditures are less than the industry average they get a "-1".  I use this formula:
Note to step 8.  Dividing by zero of course give us a "#DIV/0!" answer.  I just let it be until the rating, and then I manually put in a negative 1.

To see the spreadsheet Click Here.

 Thank you for reading!  The next post will be on Gross Margin.  A cliff hanger, I know, but you'll just have to wait until Thursday, May 3, 2012 to find out what happens next.



Thursday, April 12, 2012

Energy Sector: Integrated Oil & Gas - Quality of Earnings, Research & Development

The basic premise of this factor compares R&D expenditures with that of the industry.  Relative decreases may indicate that the company will have trouble competing in the future.

There are differences in how R&D expenditures are treated under U.S. Gaap and under IFRS.  Since Canada has adopted IFRS and there is a lot of cross border investment, I'm going to give a brief overview of both.

Under U.S. Gaap, R&D expenditures are expensed as incurred (there is an exception (of course) but we don't need to worry about it right now).  Under IFRS, Canada expenses research costs and capitalizes development costs.  If we were doing an analysis on securities that report in Canada, we would look at changes in intangibles which is found on the balance sheet and R&D expenses found on the income statement.  Since this analysis deals with securities that report in the States, we only need the Research and Development expenses and we need them for Y and Y-1.

Verdasis, the fundamental analysis software, doesn't at this stage have ready made information on industry averages.  However, you can easily manufacture them yourself with a high degree of confidence.  Higher, perhaps, than numbers that you might find elsewhere because you know how they've been calculated.  That being said, lets get into the calculations needed for this factor.

  1. To get the industry average, use the AVERAGE function for each of R&D Y and R&D Y-1,
  2. Find the growth (or decline) in the industry from Y-1 to Y by doing a simple return calculation: 
    • [(Industry R&D Y) - (Industry R&D Y-1)] / (Industry R&D Y-1)
  3. Do the same return calculation for each security to determine the change in R&D
    •  [(Security i R&D Y) - (Security i R&D Y-1)] / (Security i R&D Y-1)
  4. Subtract the industry figure calculate in 2 from each security figure found in 3.
If the company's R&D expenditures are greater than the industry average, then this is positive and the company gets a "1" rating.  If the expenditures are less than the industry average then the company gets a "-1" rating.

You can see the spreadsheet here:
https://docs.google.com/spreadsheet/ccc?key=0Ap4zxzjwKsV6dDllY1hMcmRHM0puQWJvOHdXeVZBVXc


Sunday, April 8, 2012

Energy Sector: Integrated Oil & Gas - Quality of Earnings, Accounts Receivable

For the next quality of earnings indicator in our investment analysis, we're going to look at is Accounts Receivable. 

The calculations for Accounts Receivable are identical to Inventory.  To begin, we collect the three most recent years of revenue and the three most recent years of accounts receivable.

I should probably discuss where each of the line items I use is found.  Accounts receivables and inventory are balance sheet figures, found near the top under current assets.  Revenue or sales is the top line on the income statement.

We need to pull:
  • Total revenue, Y, Y-1, and Y-2
  • Accounts Receivable - Trade, Net, Y, Y-1, Y-2.
And from this information we need to make five calculations:
  • Expected Sales,
  • Expected A/R,
  • % change in Sales,
  • % change in A/R,
  • % change in Sales - % change in A/R.
We proxy expected sales and expected A/R with an average of Y-1 and Y-2.  % change in sales and A/R is:
(Sales Y - Expected Sales)/Expected Sales.

If the percentage change in sales minus the percentage change in a/r is negative, that is an unfavorable signal.

Let's understand the logic.

If our actual sales is greater than our expected sales, we have a positive number.  The same reasoning applies to a/r.  When we take the difference between the % change in Sales and the % change in A/R, if accounts receivable has grown more than sales we get a negative number and a negative signal.

Lev & Thiagarajan discuss how disproportionate a/r increase can indicate sales difficulties (triggering credit extensions) as well as future bad debt write-offs.

I think of it like this - accounts receivable is a component of sales, but they're only on paper.  A sale on paper is not as good as a sale realized in cash.  The reasons are noted above and it's also because it can be indicative of aggressive policies that make the likelihood of actually collecting even more remote (cash is always king). So when I see the less desirable accounts receivable component growing as a percentage of all sales, it's a red flag.

I use the same rating system: 1 is a positive outcome, -1 is negative and 0 is a null or neutral result.  I use an "if, then" formula, rather than plugging the numbers in for each stock.  Verdasis was designed with normalized data - meaning that when it pulls new financial statement information, each Y, Y-1, Y-2 etc is automatically updated - this means that your data doesn't go stale and your ratings can change.

You can see the spreadsheet here:
https://docs.google.com/spreadsheet/ccc?key=0Ap4zxzjwKsV6dDlkYnJEVjRQTWNPMVo4Zi1ScXFRQ2c#gid=0

The next factor we're going to look at is research and development.  These calculations are different from the inventory and accounts receivable ones.

Thank you for reading and I'll be back to you by the end of the week.

Monday, April 2, 2012

Energy Sector: Integrated Oil & Gas, US Stocks - Quality of Earnings, Overview and Inventory

I'm going to start our financial analysis by looking at the quality of earnings calculations but before I go any further, I need to give credit where credit is due.  I am indebted to Baruch Lev and S. Ramu Thiagarajan.  I extracted these indicators from their article "Fundamental Information Analysis", JSTOR, Journal of Accounting Research, Vol. 31, No. 2.  Thank you Dr. Lev and Dr. Thiagarajan.

The five indicators I look at are:
  • Inventory,
  •  Accounts Receivable,
  • Research & Development, 
  • Capital Expenditures,
  • Gross Margin,
  • Selling & Administration Expense.
As I mentioned, I use these six indicators to gauge the trust-worthiness of the financial statement themselves.   Although the importance is probably self-evident, I'll provide an example.

Let's say an investor finds a security that for some reason looks appealing - the earnings are growing at an impressive pace.  It is important to keep in mind that net income is an abstract concept.  There are a number of judgement calls made before arriving at that figure.  Some are benign, an honest reflection of the business or industry in which the company operates.  Some are not, some are manipulative.

For example, many frauds and subsequent company implosions have occurred around receivables.   Receivables can be aggressively recorded and used to artificially inflate earnings.

The one I'm going to discuss today is inventory.  Changes in inventory relative to other financial statement factors can tell a story.  Here are some examples:
  • Inventory increases that are greater than cost of goods sold increases can be negative sign as it can suggest trouble generating sales,
  • Inventory increases can hint at future earnings weakness as prices are cut to move excess inventory,
  • Inventory build-ups may indicate the need to write off obsolete products.
All of this tells us that we don't want to see inventory growing faster than sales. If it is, it's a red-flag.

I'm going to go into detail on the inventory quality of earnings calculation in this post.

When I set up this portfolio in Verdasis, I used the securities in Google's similarly named energy sub-sector, integrated oil & gas.  At the time, there were 23 securities.  If you want the list, you can get it here:
http://www.google.ca/finance?catid=us-60884163

I set up the analysis template (where the user tells the software what financial information they want) to capture sales and and inventory information for the current period plus the last previous two years, or in other words:
  • Total Revenue, Y
  • Total Revenue, Y-1
  • Total Revenue, Y-2
  • Total Inventory, Y
  • Total Inventory, Y-1
  • Total Inventory, Y-2.
Once the data has been sent to a spread sheet, we have to make five calculations:
  • Expected Sales,
  • Expected Inventory,
  • % change in sales,
  • % change in inventory,
  • % change in sales - inventory.
Estimating or guessing on what the future sales might be isn't an exercise I would particularly like to undertake, so it is fortunate that Expected Sales and Expected Inventory can be proxied by taking the average of y-1 and y-2.  The value for expected sales is then compared to this year's sales with a simple return formula:

(Actual Sales in current year - Expected Sales) / Expected Sales

If the actual sales is greater than the expected sales, then the figure is a positive number, if not, it is negative.

Do the same calculations for inventory.

We then want to see how the numbers are moving in relationship to one another.

The best and fastest way to do this is just to subtract:

% change in sales - % change inventory.

If the number is positive, excellent.  If it isn't, red flag.

The final step is to summarize the result and a quick and meaningful way.  I use a 1, 0 and -1 system.  If my indicator is positive, I give a 1 rating.  I use -1 for a unfavorable rating and  0 for a null or neutral result.

You can click the link below to view the finished spreadsheet

https://docs.google.com/spreadsheet/ccc?key=0Ap4zxzjwKsV6dHg3X0FoWDh0TDZSV0Rxc0NQUFk3cmc#gid=0


The next post will be brief, as it will go through the Accounts Receivable calculation, which is identical to  Inventory.

Thank you so much for reading.  If you have any questions, please do not hesitate to ask!












Monday, March 26, 2012

Investment Analysis: The Process

My analyses currently involve 12 financial statement constructs.  Four are value indicators, two are growth indicators and six help me determine the quality of earnings.  The four value indicators give me an idea of whether the stock is under or over priced.  For the growth indicators, I use growth in cash flow from operations as my main indicator, but I'm also curious to see how this compares to growth in earnings. If they diverge too much it's a red flag.  The quality of earnings indicators give me a sense of confidence (or not) in the financial statements themselves.

Once I have one or two or three candidates, I look at the management and directors.  I look for intelligence, experience and track record obviously, but I also look for diversity in the management team and independence on the board.  I'm not interested in investing in a company that is over-paying their managers, or has an illogical or harmful (to my interests as a shareholder) compensation policy.

As I proceed through various analyses, I'll get into more detail about each factor and how I make a decision when so many factors are coming into play.  I should also mention that none of my factors are set in stone.  If one factor stops offering useful guidance or another appears to be more promising I will make a change.  I am happy to discover more efficient and effective paths to understanding.  I am always interested in new ideas and new research.

Without quantitative analysis software, it would take at least a full day to complete the analysis on one stock, let alone looking at a whole industry, sector, or sub-sector.  Clearly not a viable strategy.  Verdasis was designed to take the pain out of analysis - to make gathering the data, keeping it fresh and constructing ratios, regressions and equations a lot easier, but the necessity to exert some mental effort by thinking about the results that emerge still exists.

My first analysis will be in the energy sector, integrated oil and gas.

Thanks for reading and I'll be back to you early next week (first week of April, 2012).

Jen