How come Total Liability <> (Current Liability + Long-Term Debt + Deferred Taxes + Other Liabilities) ?

Report a problem or error
1

P123, all,

  1. I was trying to compute the Return on Capital. To do so, I decided to check my understanding of the balance sheet line items and make sure they all add up.

However, it seems that Total Liability <> (Current Liability + Long-Term Debt + Deferred Taxes + Other Liabilities) for almost all (bar 4) of the SP500 stocks. Sometimes with significant difference.
Is that normal?

I used KHC or JNJ or other large cap stocks from the SP500 to make sure these are companies with reliable numbers
I used TTM or Qtr and the issue is the same.

See the public screener here to save you time → https://www.portfolio123.com/app/screen/summary/227664?st=0&mt=1

Line items are listed here → https://www.portfolio123.com/doc/side_help_item.jsp?id=63

  1. Should deferred taxes be added or subtracted?

  2. BTW, has anyone already computed the Return On Capital?

Thank you

Jerome

2

There’s a problem. According to the CompuStat database, this should be generally true:

LiabTotQ = LiabCur(0,QTR,ZERONA) + TxDfdIC(0,QTR,ZERONA) + LiabNonCurOther(0,QTR,ZERONA) + DbtLT(0,QTR,ZERONA)

I say generally, because there are two things that I would expect on our site that might throw that off. The first is rounding, where out eight digits past the decimal point there’s rounding, and as far as the computer is concerned 1.000000000001 is not equal to 1. Fair enough. We nearly always use inequalities in practice, so that was what I was hoping to see. It is not what is causing this.

Also, we fallback, which is why I’m using ZERONA in that equation. When we figure this out, I still expect to see some companies where the relationship doesn’t hold because we don’t have the data and it’s being replaced by zero by that equation.

However I found loads of cases where this isn’t the case. We’re investigating now.

3

Thank you Paul.

There is another one for you: GE has N/A for Current Asset TTM (I did not check Qtr).

Yet I could re-calculate it manually in 2 different ways:

  1. Adding up its sub-components which do exist in the database : Current Assets = Cash & Equivalents + Receivables + Inventory + Other Current Assets

  2. Current Assets = Total Assets - (Net Plant + other non-current assets)

Both calculated numbers are identical. So how come this is n/a?

Screener here → https://www.portfolio123.com/app/screen/summary/227678?st=0&mt=1

Thank you

Jerome

4

It’s cuz what’s included in LiabNonCurOther() differs annually and quarterly.

5

Hi David,

Thank you but I do not understand. Can you elaborate with an example please?

Also: I doubt this explains the GE issue with current assets being N/A when clearly the information is there…

Thank you

Jerome

6

What seems to be happening is that the current quarter numbers are falling back to previous quarters when the current quarter’s statement shows 0.

Let’s take two stocks for example: BPY and WMT.

The most recent statement for BPY shows 0 in current liabilities, 54,082 in long-term debt, 2,507 in deferred taxes, 0 in other liabilities, and 63,174 in total liabilities. But the P123 numbers show 10,306 in current liabilities and 2,289 in non-current liabilities (which are the numbers in the previous quarter’s statement). So the P123 numbers add up to 69,184 rather than 63,174. (The math in this case is complicated by the fact that BPY’s statement shows 6,357 in payables but 0 in total current liabilities; the 6,357 is reflected in total liabilities.)

The most recent statement for WMT shows 79,888 in current liabilities, 66,954 in long-term debt, no deferred taxes, and 12,792 in other liabilities, adding up to 159,634. These are the exact same numbers you get from P123 except that P123 reports 6,296 in deferred taxes, which is the number for the previous quarter. So if you add them up by hand, you get 165,930 rather than 159,634.

7

Oh, and you wanted a formula for return on capital. This one is, I think, as close to Greenblatt’s formula as one can reasonably get:

OpIncTTM / (Max (0, WorkCapQ) + AstTotQ - AstCurQ - IsNA (AstIntanQ, IsNA (AstIntanA, 0)))

8

Forget what I said. AstNonCurOther differs quarterly and annually.

9

Thank you yuval.

So there seem indeed to be an issue with our database.

On the ROC, let me create a new thread to avoid distracting from the primary issue here (data).
While I am always interested in various versions of Greenblatt, I was actually trying to filter companies by Return on Capital > Weighted Average Cost of Capital

Let me follow up on this new thread

Thanks again ,

Jerome

10

For BPY, LiabTot(0,QTR,KeepNA)=63,174 and LiabCur(0,QTR,KeepNA)=NA. LiabTot matches the Total liabilities line in BPY’s quarterly report. Given the weirdness of having a number for LiabTot and not for LiabCur, I ran a screener backtest looking for;

LiabTot(0,QTR,KeepNA)!=NA & LiabCur(0,QTR,KeepNA)=NA

About 20% of the PRussell 1000 satisfy that condition. They appear to be mostly from the Financial and Real Estate sectors.

I don’t know if this is similar to P123 not reporting AstCur for banks.

11

Banks do not have current assets and liabilities. I know this to be true this time around.

12

Hi Paul,

Any update on this from the P123 perspective?

Thank you

Jerome

13

The investigation is ongoing.

14

Any news on this please?

15

We are still investigating. It’s not an easy problem, as it turns out.

16

So I think that I’ve tracked down the problem.

Let me lead off by saying what the root issue is: We’re not getting current assets and current liabilities totals for a bunch of companies. Meaning it’s in the data. Our AstCur and LiabCur functions just report what we get from CompuStat, and there are more nulls in those fields than I, for one, was expecting.

Here’s where I do expect them: Financial companies – just about all of them, as far as I know, though I’m no expert in financial-firm accounting – those companies that just reported and don’t have complete balance sheets available, and semi-annually-reporting companies (mostly ADRs) in the off season.

What surprised me is that some companies just don’t report current assets and current liabilities. The largest of those is GE. Look at the most recent SEC doc, and the balance sheet doesn’t have a delineated current asset or liability section. (That was the hint that got me over the finish line.)

And yet CapitalIQ gives a current asset line on its site. They calculate it themselves.

So I inserted an Eval into the prior equation:

LiabTotQ=Eval(LiabCurQ!=NA,LiabCurQ,PayablesQ+DbtSTQ+TxPayableQ+LiabCurOtherQ)+LiabNonCurOtherQ+DbtLTQ+TxDfdICQ

Dump that into the screener as a not-equal-to test run today and 881 companies are returned. As I mentioned earlier in the thread, equalities are kind of strange because to the computer 1 is not equal to 1.000000000001, and we might get rounding errors.

So I changed that equality to a ratio and then searched for companies where the equation is more than 0.01% different. The number of companies where the above relationship doesn’t hold drops to three. (COGLF, INVH, and PLYA for the curious. And PLYA didn’t show up in an earlier test for companies more than 0.10% different.)

I don’t think that we’re going to change LiabCurQ or AstCurQ, because they are just database dumps. If you really, really want to correct for this, create custom formulas:

Name:CurrentLiabilities
Formula:

Eval(LiabCurQ!=NA,LiabCurQ,PayablesQ+ DbtSTQ + TxPayableQ+LiabCurOtherQ )

I haven’t tested as extensively on the other side of the sheet, but this should work:

Name:CurrentAssets
Formula:

Eval(AstCurQ!=NA,AstCurQ, CashEquivQ+ RecvblQ + InventoryQ + AstCurOtherQ )

EDIT: Aaron pointed out a slightly more elegant way of doing this, with IsNA. Also, I corrected a couple of other dumb errors. :slight_smile:

Name:CurrentLiabilities
Formula:

IsNA(LiabCurQ,PayablesQ+ DbtSTQ + TxPayableQ+LiabCurOtherQ )

Name:CurrentAssets
Formula:

IsNA(AstCurQ, CashEquivQ+ RecvblQ + InventoryQ + AstCurOtherQ )
17

Thank you Paul for looking at this in depth.

I am of two minds regarding the database “holes”:

  • On the one hand, I can understand why P123 does not want to start playing with database “dumps” at the risk of not knowing what is original vs. “backfilled”
  • On the other hand, it is quite annoying that there are such gaps and I wonder how many other fields might have a lot of empty values (P123 is not at fault)

Suggestion: could you pls check with Aaron if you have an easy way on your end to do a run through the database for fields that are empty / N/A / 0, say for the PRussell 1000 companies (i.e. companies for which we should have pretty decent data). Anything that has more than 10% data missing probably shows a systemic issue of the kind discussed above.

At a minimum, I have learnt -once more- that before typing a formula in a ranking system or otherwise, I need to go and check what each data field looks like!

Thanks

Jerome

18

The difficulty in viewing the underlying data makes it easier to misinterpret data (or assume what is there). For me it requires putting data in a screen report to see when a calc is erroring out, and that requires (unless there’s another method) creating a custom calc and loading it into a screen view to see if it’s calcing as expected, or erroring out more than expected. It would be nice to put calcs directly into the screen report (or into some type of report) to make it easier to troubleshoot without having to hop around so much between different tabs. And often the screen report just isn’t the best way to view many columns of data, but I understand why we can’t export the data to excel for better analysis due to contractual limitations by S&P, but I do find this sometimes frustrating trying to “see” whether data is there or not, and what part of the data in a calc is causing a problem. Not sure what a good solution would be.

19

Coming at this from a slightly different angle: Why doesn’t GE report current assets and liabilities?

I have not followed the company closely, but the last I heard they had a financial division lumped in there. They might not be reporting current assets and liabilities because when it’s all consolidated they might still have to play by financial rules.

Many of the other companies that don’t report current positions might have something similar going on. I know that when I first started as a retail analyst in the late 1990s there were retailers who had their own consumer credit divisions. Back then there was a vogue developing for splitting everything out to minimize risk (and to offshore), so that number ultimately went down.

Now that offshoring is less in fashion and the interest rates are lower, I wouldn’t be terribly surprised if some non-financial companies have finance divisions hoping to make hay from those sweet, sweet credit spreads.

To be very clear, I just pulled that out of my butt.

But I don’t know, and so – and here we come back to P123 philiosophy – if I’m looking at the current position then it’s important to me and I want it to be a fairly normal situation. I would want to eliminate those companies that have veered so close to financial-company status that it’s affected their presentations or, for whatever reason, aren’t otherwise reporting current positions. That’s what CompuStat is doing. Furthermore, the CapIQ site that I check against is formatted to consistently display balance sheets.

I’m just saying that I would tend toward the accounting and auditing decision of GE that agrees with the analytic decision of CompuStat rather than what seems like a simple display calculation from CapitalIQ. So I’m not sure that I’d describe this as a data “hole”.

20

Data hole or not, it was unexpected by me. And since it took several weeks for P123 to explain, I think it must have been bit non-obvious to you guys, too.

For those serious about building strategies, the extra work to needed to understand a factor’s coverage is necessary.

Maybe we can call it a documentation hole.