First let's agree on some concepts and terms.

1. Sources:

The only source you'll need for the financial data is the Apple Investor Relations site at http://www.apple.com/investor/, under the heading "Earnings Releases". Sometimes I'll refer to data relating to a period prior to the earnings release in which it appeared, because that's the way Apple reported it. I might say "in Apple's Q3 report you'll find the deferred revenue for Q2 had been $20 million."

2. Terminology and conventions:

I will refer to each quarter as yyQ#, e.g. ATV shipped near the end of 07Q2 (3/21/07), the original iPhone launch was at the end of 07Q3 (6/29/07), iPhone 3G launch was early 08Q4 (7/11/08). BTW these dates can be verified through Apple's press releases at http://www.apple.com/pr/library/.

DR stands for deferred revenue, CDR is current DR (DR scheduled for recognition in the following 4 quarters), NCDR is non-current DR (DR scheduled for recognition in quarters beyond the following 4), TDR is total DR. All of these will refer to iPhone+ATV DR exclusively.

Most figures will be in millions, be that dollars or units, except a few in thousands which I'll note as ###k, and ASPs in $ which will be obvious. Any decimals will appear rounded but full precision will be carried in internal calculations.

3. How subscription accounting works, and some assumptions:

Each unit's non-GAAP selling price is gradually recorded to GAAP revenue on a daily basis, over 728 days (8 quarters of 91 days each) starting on the purchase/ship date (except those sold between 3/6/08 and 7/10/08 which start on 7/11/08).

To simplify and aggregate all these different schedules for each unit sold, we can assume all the units sold during any period as sold in a straight line, an equal amount each day for the duration of that period. This is equivalent to assuming all the units got sold at the midpoint of the period in question, and the number of days they end up contributing to GAAP revenue is about half the number of days the product was being sold.

This generally results in any quarter's units GAAP revenue contribution towards both the first and 9th quarter of their schedule is 1/16 of their associated sales (units*ASP), and 1/8 of that to each of the 7 quarters in between (the exact recognition formula for units that started shipping x number of days before the end of any reporting period is [1/2*units*ASP*(x+1)/728]). There are only a couple of special cases where this simplified rule does not apply: 07Q3 and 08Q3.

This also means that for the first 4 quarters of this (strictly until 08Q2, but meaningfully until 08Q4) half of the non-GAAP sales from each quarter should get added to the CDR line of that quarter as well as to the following 3 quarters. This is very helpful given that Apple reports the CDR as part of their quarterly reports.

The same subscription schedule applies to the gross costs of the hardware sold. Carrier payments and iPhone accessories are recorded as received, as well as SG&A and R&D costs as incurred.

So let's start from the beginning. From the 07Q3 DR schedule, we see CDR and NCDR by 07Q2 (the middle column) stood at $10m each, so TDR amounted to $20m. The fact that CDR=NCDR means that GAAP revenue of ATV sales during its initial shipping quarter (last 9 days of 07Q2 between 3/21 and 3/29) was negligible, as it should (even assuming all units shipped the first day, units*ASP*9/728 would require 270k units in order to reach a mere $1m in GAAP revenue). So we know that the TDR reported of $20m must account for all the ATV sales in those 9 days, because there had not been any material recognition yet. Thus, units = sales/ASP = $20m/299 = 67k ATV units sold in 07Q2 (this is still huge for only 9 days but ATV had been available for pre-order for a couple of months).

These initial sales will contribute about $2.5m to GAAP revenue in each of the following 8 quarters (07Q3 to 09Q2), as well as get it deducted from DR. From the 67k units we can also estimate about $150k ($200k at most if we assume most were sold in the first few days) as GAAP revenue in 07Q2, and now we should tweak the 9th quarter after that initial one which would see $2.35m recognized instead of the $2.5m mentioned earlier (and NCDR would have been $9.85m but got rounded to $10m).

From the same 07Q3 DR schedule we now move to the left column, the 07Q3 numbers: CDR=92 and NCDR=88 for a TDR of $180m. Remember what I mentioned above about half of the non-GAAP sales being responsible for the increase in CDR, so from this we know combined non-GAAP sales of ATVs and iPhones were $164m. And the TDR should be reconciled by adding this amount and backing out what got recognized as GAAP from current and prior periods' sales: the $2.5m from the previous quarter, a fraction of the 07Q3 ATV sales (1/16 or about 6% per the rule of thumb described above), and a tiny fraction (about 0.2%) from the 270k iPhones sold the last couple of days of 07Q3.

So let's call x and y the non-GAAP sales of ATVs and iPhones sold in 07Q3 and we'll write the equation that reconciles the change in TDR during 07Q3:

20 + 164 - 2.5 - .06*x - .002*y = 180 (TDR)

And we know x+y=164, so y=164-x, substitute for y above and solve x, we get x=18 and y=146, that is non-GAAP sales of $18m for ATV and $146m for iPhone. So, iPhone ASP of 146/270k or about $541. Before figuring out the ATV units, notice that on May-30 Apple added the 160GB model at $399. Assuming an even split of demand for the whole month of June results in a 5:1 ratio favoring the 299 model. ASP=(5*299+399)/6 = $316 for 07Q3. ATV units would be 18/316 or about 57k. Baked-in GAAP revenue for each of the following 7 quarters is simply 18/8 or $2.25m from the ATVs (on top of the $2.5m from 07Q2 = $4.75m baked-in) and 146/8 or $18.25m from the iPhones. The total baked-in revenue goes to $23m. And lo and behold, add up the following 4 quarters of that, 23*4, and you get the $92m CDR as reported. ATV GAAP revenue from current sales is 18/16 about $1.1m on top of the $2.5m carried over, total $3.6m. iPhone GAAP revenue from current sales is .002*146 = $301k. This gives combined GAAP revenue of 4m which subtracted from the 164m GAAP gives the 160m increase in TDR. This $4m is also the difference between CDR and NCDR, because for the first year NCDR gets charged with all recognitions.

Finally, subtract the 301k recognized from handset sales from the $5m iPhone related revenue and you get what should be only accessories sales (no AT&T payments yet) of $4.7m, or $17 per handset.

Phew! Here's a table of the results so far:

(click on image for larger view)

Next up is 07Q4 which is a tough one and I'll leave it for the next post. Happy puzzling!

:D

(continue to part 2...)

## 3 comments:

Let's try to keep things simple. Whether we should account for iPhone sales on a daily basis or a monthly or quarterly basis, it really affects the figures for the month in which the sales are made. For the remainder of the 23 months the revenue to be recognised is one quarter or 90 days worth of sales. I think a fair estimate is to take half of the sales in the quarter and half in the 25th quarter. All other quarters will be one. Yes it may not be 100% accurate (but then again accounting is only meant to be true and fair and not 100% accurate) but any discrepancy is not material and only affects the quarter in which the sales are made.

The goal here is to be able to fill in the blanks that Apple doesn't explicitly provide in their financial reporting. You can't calculate iPhone ASP's from simple quarterly or even monthly subscription accounting. I do use simple things like "take half the sales" (I called it a 1/16 rule of thumb, see 1/16 is half the quarterly recognition which would be 1/8) when appropriate, and resort to daily accounting when needed. But some people go overboard with simplicity and for example multiply every quarter's net increase in deferred revenue times 8 to estimate non-GAAP sales, which is simply wrong for most quarters.

In my spreadsheet I need to model ASP's (for example) in order to project things far into the future, and model sales from it. If I get things wrong, my projections will be way off, and I don't like that.

I said "multiply every quarter's net increase in deferred revenue times 8"

Sorry, what I meant to say is they multiply the recognized revenue by 8, not the increase in deferred revenue. Again this would be wrong.

Oh and another reason I do this is because its fun! haha yea go ahead call me a geek. :D

Post a Comment