0

The Edgar documentation has some limited information on how to handle facts with different dimension break-downs. Take as an example the AAPL annual report:

On page 29 the total Net Sales (365,817) is split for products and services

On page 37 the same total is split as per Apple product lines.

I try to figure out from the available files which elements should be added to get to the total Net Sales. The problem is that in the Xbrl extract file all the dimension sub-elements (product/service and iPhone/Mac/etc.) have the same tag (us-gaap:RevenueFromContractWithCustomerExcludingAssessedTax) and all have a very similar context, with a segment of <xbrldi:explicitMember dimension="srt:ProductOrServiceAxis">. The only difference that one of the dimension sets is in the us-gaap: namespace and the other is in the aapl: namespace, but I do not think this should be enough in general. What, e.g. if there would be a third split of the total Net sales, domestic vs. foreign also in the aapl: namespace.

What the manual says is about calculation rules in chapter 6.14.5 of Edgar Filer Manual that facts in a calculation must appear in the same presentation, but in this case there is no calculation for adding up the dimension elements. If one clicks on the iPhone value e.g. then it does not show that it adds up to the total Net sales, but it adds up to the Gross Profit, as it is not an individual fact, it is only a dimension of the same fact as the total.

The other place where I found a reference is 6.15.3, but then again it is talking about adding up different facts to get to the same total, but as said above it is not facts that are added up, but it is the only dimensions of the same fact.

I could probably do a separation based on where these values appear in a Presentation, but I would think to identify what is one set of a dimension and what is another, can be done better.

jollytall
  • 117
  • 8
  • Did you find a way? – Richard Dev Jun 06 '22 at 12:55
  • I made a lot of progress, so in general I can use it, but the fact is that most SEC filings have some errors in them. There are few cases when algorithmically it simply cannot be done, need to understand the intent. Recent filing get better though. – jollytall Jun 07 '22 at 14:05
  • awesome yeah I've been pulling my teeth scrapping through the bulk data files. I've gotten most of it but difficult to trust since I don't have a categorization key of references and rolling up. – Richard Dev Jun 07 '22 at 14:07
  • Maybe we can chat about it in more details (though I don't know how) – jollytall Jun 08 '22 at 09:43
  • Are you doing work with open source libraries on GitHub? – Michael Tiemann Jun 11 '22 at 21:04
  • (1) I use some from others, (2) I have some of my own projects also published, (3) Xbrl is too messy (yet), so it is not on GitHub, (4) all are in Pascal, what most people do not use anyway, it would be a very small audience using Xbrl and Pascal. – jollytall Jun 13 '22 at 06:29
  • yeah, I've been working on many of the projects. The inconsistency is insane. I've finally reached a point where I am running reports on the market and getting ideas. – Richard Dev Jul 12 '22 at 19:13

1 Answers1

1

FASB provides guidance on this topic here: https://www.fasb.org/consolidatedandnonconsolidatedentities_2018

Here's the data from the report:

tag value uom segments iprx
RevenueFromContractWithCustomerExcludingAssessedTax 3.65817e+11 USD 0
RevenueFromContractWithCustomerExcludingAssessedTax 3.65817e+11 USD 1
RevenueFromContractWithCustomerExcludingAssessedTax 3.65817e+11 USD 2
RevenueFromContractWithCustomerExcludingAssessedTax 1.53306e+11 USD BusinessSegments=AmericasSegment; 0
RevenueFromContractWithCustomerExcludingAssessedTax 8.9307e+10 USD BusinessSegments=EuropeSegment; 0
RevenueFromContractWithCustomerExcludingAssessedTax 6.8366e+10 USD BusinessSegments=GreaterChinaSegment; 0
RevenueFromContractWithCustomerExcludingAssessedTax 2.8482e+10 USD BusinessSegments=JapanSegment; 0
RevenueFromContractWithCustomerExcludingAssessedTax 2.6356e+10 USD BusinessSegments=RestOfAsiaPacificSegment; 0
RevenueFromContractWithCustomerExcludingAssessedTax 6.8366e+10 USD Geographical=CN; 0
RevenueFromContractWithCustomerExcludingAssessedTax 1.63648e+11 USD Geographical=OtherCountries; 0
RevenueFromContractWithCustomerExcludingAssessedTax 1.33803e+11 USD Geographical=US; 0
RevenueFromContractWithCustomerExcludingAssessedTax 3.1862e+10 USD ProductOrService=IPad; 0
RevenueFromContractWithCustomerExcludingAssessedTax 1.91973e+11 USD ProductOrService=IPhone; 0
RevenueFromContractWithCustomerExcludingAssessedTax 3.519e+10 USD ProductOrService=Mac; 0
RevenueFromContractWithCustomerExcludingAssessedTax 2.97392e+11 USD ProductOrService=Product; 0
RevenueFromContractWithCustomerExcludingAssessedTax 6.8425e+10 USD ProductOrService=Service; 0
RevenueFromContractWithCustomerExcludingAssessedTax 6.8425e+10 USD ProductOrService=Service; 1
RevenueFromContractWithCustomerExcludingAssessedTax 3.8367e+10 USD ProductOrService=WearablesHomeandAccessories; 0

Here is a query that shows how the AAPL results can be rolled up consistently:

select sum(value) as total_sum
from num join dim on num.dimh=dim.dimhash
     join pre on num.adsh=pre.adsh and num.tag=pre.tag and num.version=pre.version
where num.adsh='0000320193-21-000105' and ddate='20210930' and pre.stmt='IS' and num.tag='RevenueFromContractWithCustomerExcludingAssessedTax' and segments is null and iprx=0
union all select sum(value) as business_segments_sum
from num join dim on num.dimh=dim.dimhash
     join pre on num.adsh=pre.adsh and num.tag=pre.tag and num.version=pre.version
where num.adsh='0000320193-21-000105' and ddate='20210930' and pre.stmt='IS' and num.tag='RevenueFromContractWithCustomerExcludingAssessedTax' and segments like 'BusinessSegments=%' and iprx=0
union all select sum(value) as geographical_sum
from num join dim on num.dimh=dim.dimhash
     join pre on num.adsh=pre.adsh and num.tag=pre.tag and num.version=pre.version
where num.adsh='0000320193-21-000105' and ddate='20210930' and pre.stmt='IS' and num.tag='RevenueFromContractWithCustomerExcludingAssessedTax' and segments like 'Geographical=%' and iprx=0
union all select sum(value) as product_or_service_sum
from num join dim on num.dimh=dim.dimhash
     join pre on num.adsh=pre.adsh and num.tag=pre.tag and num.version=pre.version
where num.adsh='0000320193-21-000105' and ddate='20210930' and pre.stmt='IS' and num.tag='RevenueFromContractWithCustomerExcludingAssessedTax' and segments like 'ProductOrService=%' and (segments like '%=Product;' or segments like '%=Service;') and iprx=0

Yielding:

total_sum
365817000000.0 (Reported total)
365817000000.0 (total by Business Segments)
365817000000.0 (total by Geographical Segements)
365817000000.0 (total by Products and Services)

What's obviously not to like in this instance is that Apple did not use SubSegments to clarify that iPad, iPhone, Mac, and WearablesHomeandAccessories all belong to the Products segment and should not be duplicatively added to the Product segment.

I have no idea why Apple ignores the clear FASB guidance on this subject, but they are the ones with a Trillion+ dollar market cap, not me.

Michael Tiemann
  • 251
  • 2
  • 9
  • Thanks Michael, I am honored getting an answer from you. However, in your query there is a trick in the last line: and (segments like '%=Product;' or segments like '%=Service;'). That is the human knowledge needed (i.e. that within ProductOrService only these two need to be added). Automatically it cannot be done. Another filing might have one more such line called PoS=Other. Do you add it to the total (i.e. Product+Service+Other) or it is part of Product or part of Service. My solution uses also the presentation file and a heuristic approach to find elements ... – jollytall Jul 04 '22 at 13:02
  • ... that actually add up to the total. Still that is not 100% correct (e.g. rounding) or when the filing has no value for the concept without dimension at all. With multilevel dimensions it gets even more complicated. I found filings that are almost impossible to build up properly, not talking about the errors (hardly any filing without one) and changes over time making time series building even more difficult. – jollytall Jul 04 '22 at 13:05