I was going through the line item reference in Excel. Some factors are defined in terms of other factors, eg. EBIT is equal to OpIncAftDepr and WorkCap is equal to AstCur - CurLiab.
I checked whether there is any difference between using one or the other version. I used this screen: https://www.portfolio123.com/app/screen/summary/153876?st=1&mt=1. Basically, it uses All Fundamentals with a single rule of the form ShowVar(@diff, Abs(IsNA(FactorVersionA,10000000) - IsNA(FactorVersionB, 10000000))). When you look at the ‘Screen Factors’ report and sort on @diff, you’ll quickly see all the differences (where @diff is not equal to zero).
However, I found some inconsistencies I could not explain:
[] EBITDA = OpIncBDepr: this one does not hold for around 50 companies (that don’t have NA values), and OpIncBDepr has many more NAs than EBITDA. (I also tried EBITDA = (OpIncAftDepr + DepAmortIS), but that gave very different results.)
[] DbtTot = DbtLT + DbtST: some very strange differences. For some companies, DbtTot = DbtLT, for some DbtTot = DbtST (even though the other part is not NA or 0). Only about 10 companies show this.
[] TanBV = ComEq - AstIntan: differences for around 50 companies, but I don’t see a pattern.
[] FCF = OperCashFl - CapEx - IsNA(DivPaid,0): differences for around 20 companies, again no pattern.
[*] WorkCap = AstCur - CurLiab: Small differences for only 3 companies.
Any idea what is causing the differences I observed?
EDIT: I also see some differences when I check things like PEExclXorTTM = Close(0) / Eval(EPSExclXorTTM > 0, EPSExclXorTTM, NA) and Pr2BookQ = Close(0) / (Eval(ComEqQ > 0, ComEqQ, NA) / SharesFDQ).
I noticed that CompleteStmt=0 in all the cases where there was a difference. (Except for the fact that OpIncBDepr has many more NAs than EBITDA). So I guess that factors computed by P123 (eg. FCFQ) handle incomplete statements differently from formulas that I write using only compustat data directly (eg. OperCashFlQ - CapExQ - IsNA(DivPaidQ,0)).
The help mentions that “When CompleteStmt is FALSE our ‘fallback’ mechanism kicks in for most ratios that evaluate to N/A because of incomplete data”. However, factors like OperCashFlQ etc also fallback by default. So I still don’t understand where the difference really comes from.
Can anyone (P123?) explain the actual difference that I’m seeing here? Which factors fallback in which case? How can I replicate the values of factors like FCF exactly?
I think the confusion is that “falling back” is on a factor by factor . This can cause problems with long formulas where some factors fall back others do not. So you could be mixing factors from different periods. If you are writing a long formula and you don’t want this mixing going on, you can control it using the formulas and Evals() , etc , but it gets very complicated very fast. We’ve talked about possible other solutions , nothing finalized yet.
Peter, thanks for sharing. I did a spot check and so far confirmed that it’s a fallback issue.
I have a online Google spreadsheet here where I recorded these types of observations a few years ago. I have added a couple of new observations from your data.
Here’s the list of everything I checked, including remarks where I could not reproduce a formula.
I only checked the Q versions of the factors in this Excel sheet. I often got small differences, typically for around 20-50 companies. That is probably caused by preliminary data etc.
Some things that surprised me (besides what has been discussed on the forums the past week already):
[]R&D is included in SGA2Sales%
[]ROE% seems to use NetIncBXor instead of IncBXorForCom
[]PayRatio seems more complicated than necessary and is doing something special when DivPaidTTM and/or NetIncBXorTTM are NA or zero or negative.
[]FloatPct is NA when the float is equal to shares (so it would be 100%).