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

Report a problem or error
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.

21

Paul, I always knew you had a great . . . uh, let’s skip the metaphor. FWIW, you’re spot on. GE and many other companies don’t report current assets and liabilities because it’s not relevant to them. These situation, and others like them, are not data holes. They are the inconvenient and statistically irritating but in fact inevitable reality that the corporate world is not a singular monolith in which all data-points are relevant for all companies. Much can be standardized, and Compustat does an amazing job of it. But sooner or later, we all run into areas where standardization can’t go.

Thus current asset/current liabilities thing came under discussion because somebody got tripped up trying to do a uniform analysis on the database. Think of how many years this oddity has been out there (as many year as financial results have been tabulated, audited and reported) and not discussed. Now, imagine all the other oddities out there that haven’t been discussed only because nobody has – yet – done a study or something like that which bumps into them.

If you tackle REITs or biotech, you’ll find oddities that are at least as poignant if not more so but harder to spot because conventional data exists – its just that the numbers don’t mean what you might think they mean. Media companies are often like that too. Tech is a whole different class because the accounting rules that force companies to charge all of R&D against earnings in the period in which they were expended as opposed to allowing them to do what other companies do with less human but similarly future-growth-seeking expenditures (depreciate them over the expected life of the asset).

It’s important, if you aren’t well versed in financial statements and fundamental analysis, to manage your expectations.

P123 users have seen again and again and again and again that they can succeed using the tools and ideas we offer even without getting into the weeds of financial/fundamental analysis. But for that to happen, you’ll have to ease up and allow it to happen; i.e., refrain from approaching p123, the data and the tools with expectations of precision and uniformity you might import from other disciplines.

The following from British logician Craveth Reade sums it up: “It is better to be vaguely right than exactly wrong.” Or to paraphrase one of Paul’s former professors, Aswath Damodaran, Don’t got bogged down trying to be right (as if anyone ever could be). The goal is to be a bit less wrong then everyone else.

22

Marc,

I understand and agree with all this.

This said, I also think we need a better tool to have a look at the data before starting to plug in variables into formulas. Otherwise, we might get unexpected results which might look vaguely right on backtest but will fail at some point when using live money.

We can do it on the screener but it is very cumbersome.
I think this is what “Data Views” was intended for. But right now, it is not very helpful.

A helpful first fix which would still be a step change would be to allow us to select custom universes in data views (see screenshot attached).
Could Aaron maybe chip in to let us know how easy or not it is to implement?

Many thanks,

Jerome

DataView.pdf (60.7 KB)

23

I think that plussing up the single company views is he best way to tease out the nuances between “as reported” values and Compustat “normalized” values.

Plussed up financial statements, with all articulating items shown, with option for displaying different time periods and normalizations (e.g., TTM) over custom rolling time windows, are the easiest and best way to satisfy our information requirements.

The ability to look at this data cross-sectionally is not going answer your questions about the nuances, IMHO.

Plussed up statements shouldn’t be a lot of work for P123 since the bones are already built in the panels tool.

Please make this happen.

…

For those who are not familiar with concept, CompuStat should be using an articulating financial statement balancing model to ensure the primary financial statement line items aggregate properly. While Capital IQ analysts make exceptions to Compustat’s original rules, the normalization priced does a fairly good job at standardizing different reporting paradigms.

24

Do not expect this.

It would require a revision (and likely a very costly revision) to our data license. P123 is an investment modeling platform and whatever display we do is tangential to it.

What you seek is a full-blown financial data reference capability. Firstly, that is not our business model. Secondly, if it were, we would not use Compustat but would have instead prefer the Capital IQ offering (see data white paper in Help area). And thirdly, the chances of our expanding into the data reference area are slim to nil since it has not proven to be an attractive business for third-party providers; the only providers I know of who do it well are the data vendors themselves, who own the data and sell subscriptions to their own platforms. As to third party providers, there’s a severe mismatch between the costs associated with licensing, developing and maintaining versus potential for monetization via advertising (not even close) or subscriptions (not enough demand at the price it would take to do it properly).

I understand your desire for it. But there are times in the life of every business when it has to simply decline a request.

I also understand, from your profile, that you hope to launch an equity fund in the future. If you go down that route, you may want to consider subscribing to the Capital IQ platform (which is, actually, quite good), and take an entitlement to Compustat data with the full reconciliations you seek and/or Capital IQ data.

25

I didn’t anticipate that expanding the financial statements views by several line items would’ve resulted in an issue with your data vendors.

I’m sorry to hear that this is the case.

In case you decide to revisit this, I am attaching a snippet of an analysis that may helpf reconcile P123’s holding versus the Compustat’s articulating line items. In an ideal world, there would be a financial statement view to break out as many of these as possible. I don’t think anyone is asking for the supplemental data points, metadata, and footnotes that are included with a Capital IQ license. Note, that there are differences between quarterly and annual line items that affect this reconciliation.

Capture.PNG
Capture.PNG719×856 25.7 KB