Saturday, January 24, 2009

Mining for non-GAAP, ASP's, and ATV's (Part 2)

This post continues with my (probably extremely boring for most) exercise of puzzling out the stuff that Apple doesn't want explicitly disclosed in their financial reports. Yeah, I know what whoever might still be reading is thinking: "Just give me the current numbers! this old stuff is peanuts, whoopee you're up to $23m baked-in revenue. Tell me why I should care."

Well, you should care because, as I'm sure you've heard, this will keep building up to over $1.2b currently baked into the next quarter (09Q2), and it'll continue to build up. But the only way to get there, that is, to be able to know how much is already baked in (even before Apple tells us), is to figure out the previous figures, the non-GAAP numbers from 8 or 9 quarters before the quarter we want to predict. So bear with me for these initial baby steps. If you haven't done so yet, please check the first part here.

So we move on to the next report, 07Q4: CDR=346, NCDR=290, TDR=636 (previous was CDR=92, NCDR=88, TDR=180). Again, the increase in CDR must be half of current non-GAAP sales. So 2*(346-92)=508 is the combined ATV and iPhone sales value. TDR goes from 180 to 636 by adding this 508 amount and backing out recognized revenue of 23 from previous periods (calculated in part 1) and some fraction of the combined ATV and iPhone sales value. To find this fraction we just reconcile the change in TDR as I just described it:

180 + 508 - 23 - x = 636

This solves as x=29 and the fraction is 29/508 = 5.7% = 1/17.5 slightly lower than the 1/16 fraction from our rule of thumb, which means that the distribution of iPhone and ATV sales along the quarter was not linear but slightly back-loaded, just 4 days after the midpoint of the quarter (91/2 - 29/508*728 gives the number of days after the middle of the quarter where the sales "center of mass" lies, that is, the point in time where you could place all the sales and achieve the same revenue recognition).

The question now is how do we split the $508m combined non-GAAP revenue and the 29 recognized from those sales between ATV and iPhone. Well, let's run some extreme scenarios.

I can't see more than 50k ATV's sold, and you'll see why in a minute. Let's go with that generous number, and let's keep being generous and assume a $340 ASP (an important contribution from the $399 model), that gives 50*340 = $17m in non-GAAP sales, which leaves 508-17 = 491 as iPhone sales value. Divide this by 1.12m iPhone units and we get a $439 ASP. But we know that at least 389k iPhones were sold at $399 (from the Sep-10 1M milestone) and surely some more between Sep-5 (the date of the price cut) and Sep-10. Excluding the 389k we know for sure, 389*399= $155m, 491-155 = $336m on 1119-389 = 730k units and we're up to a 336/730 = $460 ASP for the original pricing mix (and some discounted from those 5 interim days). Hmm.. 460? this seems at odds with the 499/599 original price points, and quite far from the 540 calculated for the launch days. So, we need to hack ATV sales. Simple as that, no way around it.

Now let's check the other extreme: let's assume zero ATV's sold, just to get a maximum limit on the iPhone ASP. In this case all the $508m revenue comes from iPhone, 508/1.12 = $454 ASP (just $15 higher than before so not much upside there). Again, taking out the $155m from the 389k discounted units sold since Sep-10 leaves 508-155 = 353, divided by 730k we get a $483 ASP for the mixed pricing. Still seems a bit low, but now let's see what this implies as to how many discounted units we didn't count in the 5 days between the new price and the 1M milestone by assuming a $535 ASP between the launch and the price cut. We'll call x the number of units (at the lowered price of course) sold in those 5 days:

x*399 + (730-x)*535 = 353

Solving for x gives 278k discounted units sold in those 5 days (and 730-278 = 452k before that). More than 55k a day! (Compare to 452/66 = less than 7k/day before the discount) Wow. Perhaps that $535 estimated ASP is too high for the weeks following the launch, when the crazyness was winding down (although Apple did say on Sep-5 that the high-end remained the most popular model). Who knows, maybe AT&T gets a discount from Apple, which would explain the disconnect between these ASP's I'm getting and the 499/599 retail prices. Anyway, solving the formula using a $520 ASP for pre-discount sales results in 222k discounted iPhones sold in those 5 days (44k per day) and 730-222 = 508k before Sep-5. Compare this to the other sales volume rates we do know: 270k for 2 days at launch, 730k for the first 71 days of the quarter (over 10k/day), and 389k over the last 20 days (just short of 20k/day).

This run rate by Sep-5 would mean that as Apple said they were "on track to reach the 1M goal by the end of September" the actual cummulative units sold stood at 270+508 = 778k, with a unit volume rate down to under 7k per day and with 25 days left in September (25*7k=175k) the 1M goal would seem just out of reach. In addition, remember we had set ATV sales at zero! Surely some ATV units must have sold, right? Well, for every 1k ATV units added (at a more realistic $325 ASP) another 2.7k iPhones must get shifted from the original pricing timeframe to this 5 day period, and that would put the 1M goal by the end of September further and further away as seen from Sep-5. That means ATVs sales must not have been too big, because in my opinion Apple would have needed to implement the iPhone price cut earlier than Sep-5.

We saw two extreme scenarios and found that even though 50k ATVs takes only 17m in sales value, a very small fraction of the 508, the other iPhone pricing constraints and time frames that we know do limit the ATV sales to probably less than half that number. I'm going to go with a nice and even $500m + $8m breakdown of iPhone and ATV sales values. This puts iPhone ASP at 500/1.12 = $447 and about 25k ATVs @ $325 ASP. Contribution to the baked-in revenue of the following 8 quarters is 8/8 = 1m from ATV and 500/8 = 62.5 from iPhone, building up to $86m combined. Assuming both products had the same linearity of sales along the quarter, we can multiply each product's sales value by the 29/508 combined factor. So recognized revenue (from current sales) for ATV is 8*29/508 = $457k, and for iPhone is 500*29/508 = 28.5m. If we combine this with the 18m that gets recognized from iPhones sold during the 2-day launch (see the first part) gives a total of $47m total recognized from iPhone handsets. Subtract this from the 118 reported as iPhone related revenue and we're left with $71m for AT&T payments and accessories, which I'll leave combined from now on and call it Other iPhone related revenue.

Phew!! So here's our completed table (I fine-tuned some rounding glitches in the previous quarters):

(click on image for larger view)

Stay tuned for part 3, in which I'll try to come up with some method for all this chaos.


(continue to part 3...)


Turley M Muller said...

That window (March - July) when Apple stopped recognizing revenue on handset sales kinda throws a wrench in there, and not surprisingly adds more work.

Also, when you get to 3G sales for Sep & Dec, the DR deviates from cash flow because of the currency translation of the balance sheet, hence DR. I think it's about 28M in Sep and 75M for Dec.

What I struggle over is the split between accessories and carrier payments.

Really good work here, Deag, In my model I use the same methodology, but been too lazy to break out DR from ATV from the mix. Maybe, I'm being a little too tough on myself, perhaps not lazy because it's a lot of work and a major headache! :-)

Thanks for the endeavor and sharing with us, really awesome stuff. Keep it coming !!!

deagol said...

Thanks Turley. I agree about the March-July wrench. Something fishy is going on there, even when taking into account what Apple said about deferring any recognition during that time. I'm waiting for a response from Joan at IR about that.

For the currency translation thing which supposedly explains the discrepancy between the non-GAAP sales adjustment and change in DR, I got $119m for Dec (1632 vs 1513).

About splitting accessories and carrier payments, I've decided to lump both together... ok I've given up on it. It's not worth it because I think most legacy contracts will either expire or get upgraded over the second half of 09.