Saving time with Gurufocus and Google Sheets. Plus, the usefulness of Free Cash Flow.

Gurufocus is a website that presents historical financial data in one giant table.  You can use Google Sheets to pull that data into a spreadsheet and perform your own calculations.  This is much faster than going into a 10-K to copy+paste the financial data.  Here is my spreadsheet that automatically pulls data from Gurufocus.  To use it, sign in to your Google account (registration is free) and then make a copy of the spreadsheet.

Calculations that I like to perform

Free cash flow

I like to look at free cash flow because it often contains fewer distortions than GAAP earnings.  There are many different definitions of free cash flow.

Gurufocus’ definition is:

cash flow from operations – capital expenditures

Gurufocus’ definition is a good one.  In most cases however, I am not worried about management playing games with working capital.  Examples of shenanigans would be:

  • Fake cash
  • Fake inventory
  • Channel stuffing
  • Booking revenue too early, creating dubious receivables

In the majority of cases, adjusting for changes in working capital presents a more accurate picture of what a business is actually making.  Suppose that a company is turning cash into inventory to stock new stores or to stock up for the holiday shopping season.  The cash spent reduces cash flow from operations (CFO).  With the Gurufocus definition of free cash flow, lower CFO means lower free cash flow.  If the inventory is real, then free cash flow would be unfairly penalized/distorted.

The second adjustment that I like to make is to subtract stock-based compensation from free cash flow.  If an employee is paid with stock rather than cash, the stock-based compensation should also be considered an expense.  However, I think that GAAP overstates stock-based compensation when a company’s stock is overpriced.  So for certain hot tech stocks, you might want to make adjustments for stock-based compensation.

Free cash flow multiple

Usually this is more accurate than a P/E ratio.

GA:RD ratio

For pharma companies whose only business is to develop new drugs, I like to look at G&A spending versus R&D.  See the section “My approach to shorting bioturds” in my blog post “Year in review 2014”.

GA:Revenues

For oil and gas, the ratio between G&A and revenues is one way to measure how low-cost an operator is.  See “My approach to shorting oil and gas“.

4-wall pre-tax return

I use this to measure how well retailers are run.  I look at how much operating income is generated by the company’s inventory and PP&E.  My metric can be thrown off by:

  1. Intangible assets such as software, capitalized advertising, etc.
  2. Seasonal fluctuations in inventory and differences in the year-end between retailers.

Cash conversion cycle

If I think that there might be something suspicious going on with a company’s working capital, I would like to examine:

  1. Changes in inventory turns / days inventory.
  2. Changes in days receivables.
  3. Changes in days payables.
  4. Changes in the cash conversion cycle.

For example, a company that is creating fake/phantom inventory would likely see its inventory days go up unusually over time.  To examine these things, it is probably better to go to the Gurufocus site directly.

Gurufocus data issues

In some cases, Gurufocus might display the HTML table differently (e.g. recent IPOs may have their historical data replaced by other data) which can cause issues with my spreadsheet.  Try unhiding the columns between A and L.

Closing thoughts

Feel free to copy the spreadsheet and make your own adjustments.  Because the Gurufocus historical financial data is now in a spreadsheet, you can perform any calculation that you’d like.

Advertisements

13 thoughts on “Saving time with Gurufocus and Google Sheets. Plus, the usefulness of Free Cash Flow.

  1. Thanks for posting – this is great!

    It looks like Gurufocus is trying to keep the financial data feature behind paywall. How does the Google Sheet you made get around that?

  2. Interesting post, like many others on the blog! How would you deal with working capital in a hypothetic situation where company has 100 sales in year 1, then say next year have grown to 120. For this, they needed to add working capital, increasing it from 10 to 15. Would you subtract 5 from the cash flow (let’s say this was certain to continue, company needs constantly more working capital as it grows) or not? I’d “model” the working capital growth into the next years by assuming some increases in current assets etc. But am I double-counting the effect of increases in working capital if I both subtract it from CFO and model the increases in coming years into the balance sheet? Shouldn’t be that difficult to get my head around it but somehow it is now.. Thanks for the help!

    • Adjusting for changes in working capital would be more appropriate in that situation. That would be reflected in how I calculate free cash flow, and in reported earnings.

  3. Thanks for the spreadsheet Glenn. Does this only work with US stocks or can we input UK tickers, e.g. BARC. Entering this or LSE:BARC returns the error: “Error
    Imported content does not have query with given index.” Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s