This is the third part of my attempt to peek under Apple's financial reports to see if we can find any of the hidden tidbits we would like: the baked-in goodies for the future, the average price of assorted things, and whatever happened to that hobby of Steve Jobs, the Apple TV. Skim over part 1 and part 2 if you haven't.

On to the next report for 08Q1: CDR=816, NCDR=624, TDR=1440 (compare to previous 07Q4: CDR=346, NCDR=290, TDR=636). Once again the increase in CDR must be half of current non-GAAP sales. So 2*(816-346) = 940 is the combined ATV and iPhone sales value. TDR goes from 636 to 1440 from adding this 940 amount and backing out the baked in of 87 from previous periods and the fraction recognized from current sales. That gives us the formula:

636 + 940 - 87 - x = 1440

And we get x=49.5, which is the amount to get recognized from those $940m in sales value for the current quarter. The fraction of GAAP (current) over non-GAAP, 49.5/940 = 5.3% = 1/19 again not too far from the 1/16 (6.25%) if sales had been spread flat along the quarter. Calculating the day of the "center of mass" (91/2-49.5/940*728) we see it lies about a week after the middle of the quarter, making it slightly more back-loaded than the previous. This back-loaded quarter is of course a Christmas quarter, so this is not some great revelation here. It just confirms and reaffirms that the exact way to reconcile the deferred schedules is through daily subscription accounting, and that the 1/16 rule is a relatively good approximation only for a linear quarter, otherwise the non-linearity must be taken into account.

Now we need to figure out the breakdown between ATV and iPhone. Before, we used the fact that the iPhone had had a cut in pricing at a specific date, and the 1M milestone was achieved only 5 days later, which gave us important clues about the likely ASP range and units sold around those dates. This allowed us to check our calculated results against the expected pricing backdrop. But for this quarter we know the iPhone was launched in different markets with pricing varying wildly for different carrier partners and plans. I remember there was a €999 plan (back when the euro/dollar was up above 1.45), and I think another plan in which the iPhone was free? (this is out of memory so I could be wrong). Despite all these pricing plans, I would expect the ASP to come out a little higher than $399 which was the only price in the US where the bulk of sales still came from.

So let's organize what we know:

$940m combined sales value

$49.5m combined recognition from these sales

2315k iPhone units sold

Somewhat over $399 iPhone ASP

$241m total iPhone related revenue

$87m baked-in for recognition from previous periods (81+6 break between iPhone+ATV)

$325 ATV ASP

And we want to determine the separate sales value and recognition for ATV and iPhone. From these we would be able to calculate ATV units (sales/$325) and iPhone ASP (sales/2.3k). Using some variables and equations:

x + y = 940

p + q = 49.5

We might try using the fraction (which measures linearity) as follows:

f(iPhone) = p/x

f(ATV) = q/y

And we might even safely assume that both products had the same linearity because there were no major product introductions or changes in the models (this assumption would not work for the first quarter of iPhone sales for those 2-days of sales because the linearity is obviously very different than ATV). And of course this same linearity would be the one we calculated before, about 5.3%. So we have:

x + y = 940

f*x + f*y = 49.5 (we know f=.053)

Hmm... cool, two unknowns and two equations, right? Well, nice try but it won't work. Look:

f*x + f*y = 49.5

f*(x + y) = 49.5, and substituting x+y=940:

f*(940) = 49.5

f=49.5/940

Which was just what we had done before to get that combined linearity factor. The system is indeterminate when we assume both products have the same linearity. We could guess any value for one variable, and all the others would have a valid solution. Any value for x or y less than 940 determines the other, as well as p and q less than 49.5. In fact this is what we've been doing for the last couple of quarters that we "solved". We simply guessed at the variable we thought we could pin down as best we could by using some outside information. For the last quarter it was trying a range of ATV sales (y) and checking the resulting iPhone ASP against external pricing and an intermediate unit milestone that Apple luckily shared. In the previous quarter, the iPhone launch, we simply assumed ATV linearity was flat (1/16 rule of thumb), and used the very strong non-linearity from the late-in-the-quarter iPhone sales (3/(2*728) =0.002). This allowed us to solve our system for all the variables.

But what could we do now? We know that most of the sales value must come from iPhone. And we know the overall linearity. We could also assume there should be an uptick in ATV sales given this is a Holiday quarter. Taking a shot at a slight sequential increase in ATV sales might give us a pretty good idea of things, but I'm tired of this guessing game of "financial sudoku" (I'm sure you are even more tired of it). So, what then?

Well, here's a promising approach: take the overall sales value and divide by iPhone units. Because iPhone is the main contributor to these sales, this should give a rough estimate for its ASP, but with some ATV component added. Let's introduce u and v variables for iPhone and ATV units, and build that equation:

940/2.315 = 406 = (x+y)/u = x/u + y/u = ASP(iPhone) + ASP(ATV)*v/u

Because the ATV units (v) is very small compared to iPhone units (u) that ratio v/u is a small percentage (2% would represent 46k ATVs), and thus we know the ASP for iPhone should be only a few dollars less than $406. In fact using that 2% generous guess and our $325 ASP for ATV we get .02*325= $6.50 of "ATV froth" in that $406, or a lower limit for iPhone ASP of $399.50. Well, knowing that this was the US pricing and that European pricing was generally higher, I'm pretty sure this is the extreme case. Oh I hear you saying, "but you were tired of guessing!" Sure, but notice how simple and intuitive this aproach made the guessing game: you just divide two known numbers and you get the top limit for iPhone ASP. We could leave it at that if we want, in case we don't care about the measly ATV sales, or if we happen to know some external info about the expected real iPhone ASP or the expected ATV units sold we can easily integrate any of it into our rough number.

So, finishing up this quarter, I'll rather use about 31k ATV units: 31*325 = $10m sales value (y), 940-10 = 930m iPhone sales value (x). For the recognized fraction of these I'll assume the same overall linearity fraction (49.5/940=5.3%) for both products, so we get 10*.053 = 0.5m for ATV and 49.5-0.5 = 49 for iPhone to get recognized from these sales (p and q). Baked-in contribution to the following quarters increases by 10/8 = 1.25m for ATV and 930/8 = 116m for iPhone, and the total has now built up to $204m. Total recognized this quarter for iPhone is 81+49 = $130m, which subtracted from 241 total reported iPhone revenue leaves 111 from carriers and accessories. You could divide this amount into 2.3m units to get $48 other revenue per unit, although this is not the right way to measure carrier payments per user (you'd need to know the size of the installed base paying a contract) but it's still a somewhat insightful approach. The extra "ATV froth" built into that $406 ASP is 325*31/2315 = $4.35, so the real iPhone ASP is $401 and change. So much for all that currency-inflated premium pricing in Europe.

Here's our updated table:

I know you must have a huge headache by now from all this. I'd just like to summarize the approach, a sort of "recipe" when navigating through this chaos, and make some general description of this "simple" method (don't look at me like that) which I'll use going forward. It's like a 5-step program, all based on the DR schedule for the current and previous periods, except for the final step where ASP's would need to get validated against the externally known pricing backdrop:

1. Get the combined sales value for the quarter (call it S) from the DR schedule and previous quarters' data. The formula is:

S/2 = CDR + [total recognized in the fourth-previous quarter] - [previous quarter CDR], or

S = 2*(CDRchg + R[-4]) (CDRchg is the net change in CDR, see R below).

2. Get the the total amount recognized for the quarter (call this R) using the S calculated before and the change in TDR:

R = S + [previous quarter's TDR] - TDR, or R = S - TDRchg (TDRchg is the net change in TDR).

3. Get the amount to get recognized from only the current quarter's sales (call it r) by backing out from R any baked-in revenue recognition from previous periods' sales. This baked-in amount is simply the average of all the S's from the previous 8 quarters, less the r from the very first of those 8. So the formula is:

r = R - (average(S[-8 to -1]) - r[-8]), or r = R - sum(S[-8 to -1])/8 + r[-8]

4. Check the quarter's linearity r/S: near zero is a back-loaded quarter, 1/16 is a linear quarter, and near 1/8 is a front-loaded one. Also you could check the "center of mass" of the combined sales: days after mid-quarter = 91/2 - r/S*728. Positive is back-loaded and negative is front-loaded.

5. Calculate S/u to see if this maximum iPhone ASP makes sense. For the other extreme, consider some generous ATV unit sales and subtract this "froth" amount (v/u*(ATV ASP)) from S/u.

Finally (only if you're brave), try to incorporate any info or guesses you may have either about ATV units or the real ASP for iPhone, or anything else that might help. Guessing any one variable will lead you to all the others, even the "other iPhone related revenue," through the following equations:

x = (iPh ASP)*(iPh units)

y = (ATV ASP)*(ATV units)

p = (r/S)*x (unless a product launch suggests a different fraction, e.g. p = x*(days+1)/(2*728))

q = (r/S)*y (same observation, or alternatively consider linear ATV sales: q = y/16)

S = x + y

r = p + q

Where only x, y, p, and q are unknown, but all solvable once any single one of them gets assigned a value. Carriers+accessories can be derived (or even used as a guess to resolve the whole system) by backing out a couple of things from the reported total iPhone revenue: back out baked-in recognition from previous periods' iPhone sales, and back out p. So, calling this other iPhone sales 'o' (stands for "other"):

o = (total iPhone reported revenue) - sum(p[-7 to 0])

That's it. For the next part I'm jumping right up to the latest two reports with 3G sales. Here's a final summary of the five calculation steps:

1. S = 2 * ( CDRchg + R[-4] )

2. R = S - TDRchg

3. r = R - sum( S[-8 to -1] ) / 8 + r[-8]

4. linearity factor = r / S

5. max iPhone ASP = S / u

If you read all the way through here, I apologize for the length and please don't bill me for any pain killers you may have needed. Any drinks are on me though!

:D

## Monday, January 26, 2009

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

Labels:
AAPL,
Apple,
ATV,
baked-in,
deagol,
deferred revenue,
finance,
general method,
iPhone,
non-GAAP,
spreadsheet,
subscription accounting

Reactions: |

## 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):

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

:D

(continue to part 3...)

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.

:D

(continue to part 3...)

Labels:
AAPL,
Apple,
ATV,
baked-in,
deagol,
deferred revenue,
finance,
iPhone,
non-GAAP,
spreadsheet,
subscription accounting

Reactions: |

## Wednesday, January 21, 2009

### Apple Reports Q1 2009 Results

Well this is quite interesting. I suppose I've got some work to do, but it's cool to be able to leave all those highly paid professionals (even Munster) miles behind on the EPS side.

:D

:D

## Monday, January 19, 2009

### Mining for non-GAAP, ASP's, and ATV's. Oh my! (Part 1)

This puzzle is one of my favorite "games" I like to play when it comes to deciphering Apple's earnings reports. There are now enough pieces to allow one to pin down a satisfactory solution, but the pieces are all scattered all over the 6 or so relevant earnings reports while each item is vague enough on its own to make it quite a vexing scavenger hunt. Let's see if we can put the whole puzzle together.

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:

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...)

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...)

Reactions: |

## Friday, January 16, 2009

### Historical record

Another investor had the following suggestion:

WJR42 said:

Ok, here's a chart comparing the last 16 quarterly estimates from my model, analysts consensus, and actual results as reported by Apple:

And here's one comparing the model's 12-month forward-looking estimate and the actual results from Apple:

(click on image for larger view)

I believe it's possible to verify these claims by digging for my posts in the Yahoo message board (up to July 2006), the Investor Village board (sporadically), and the Apple Finance board at The Mac Observer (up to this moment).

:D

WJR42 said:

Deagol, I think you should insert about 2 years previous numbers by quarter and year end along with your estimates for those periods. This info would give your model more standing, especially for anyone who is not familiar with the reliability.

Ok, here's a chart comparing the last 16 quarterly estimates from my model, analysts consensus, and actual results as reported by Apple:

And here's one comparing the model's 12-month forward-looking estimate and the actual results from Apple:

(click on image for larger view)

I believe it's possible to verify these claims by digging for my posts in the Yahoo message board (up to July 2006), the Investor Village board (sporadically), and the Apple Finance board at The Mac Observer (up to this moment).

:D

### An explanation

Here's somebody who had some great questions, which I imagine many would also ask themselves about this thing I do.

Coldatoms said:

Here's my reply:

And this:

Hope that helps!

Coldatoms said:

Caligula and Deagol,

I was wondering if you both have any kind of written description of your models that you could point us to or perhaps post. I think it's great stuff but I'd like to know what's included in the various estimates and the Christmas model etc. Are you both simply getting web order #'s from personal purchases at the Apple online store or do people send web receipts directly to you? Are the web order numbers from various countries all part of the same sequence? How do all of the sales at other retailers and the Apple Stores get included? It would seem that as we sell in more and more places that the web orders would become a smaller fraction of total sales. Do you see any effect like that?

Your modeling efforts are much appreciated and I'm curious about what goes into them.

Here's my reply:

Hey Coldatoms, thanks for the interest.

As you may have noticed, Caligula and I have different methodologies, although based on the same simple concept: web orders show a strong correlation with revenue and earnings. Since we have access to web orders in real time, as soon as the quarter ends, then we can make a fairly accurate prediction for revenue (my model) and EPS (Caligula's model).

There's descriptions and discussions of some of the details scattered in several message boards, from the old Yahoo boards (perhaps you can do a search for posts by Deagol_The_Stoor and/or clgl_fubar there although proceed at your own risk) to the TMO AFB (search for deagol) and here. I haven't written a serious writeup of the model or the methodology mainly because I'm not pitching this as professional financial research (because it's free, I'm not making a living out of it, and there's still a lot of guesswork) and the only one I consider a client would be myself and my family. Oh yeah, and I'm too lazy to do it. ;)

However, I do feel I have a responsibility for everyone who follows it, and I take it seriously. I just think no one should be blindly basing their investing decisions on others' opinions, as I've repeatedly stated regarding my work. In any case, I share it in the hope that others will find it useful, perhaps by taking the trouble of trying to understand it on their own, perhaps by just taking the raw data and running their own analysis, or just as yet another datapoint to consider or as yet another opinion from all the opinions out there and parse it through their own opinion-making filters.

I'll try to answer your questions as best I can. Feel free to ask if you have more questions.

First, currently we both mainly get our own orders ourselves, although Caligula insists on making real paid orders (I think it's an excuse of his for giving in to his Apple cravings) and I sort of cheat to get them for free. But at the beginning there was a great compilation effort by Caligula to get submissions of old 2001-2004 orders from people. This effort was invaluable and without it we wouldn't be where we are now with this.

Second, I'll skim over Caligula's model even though perhaps he should be the one explaining it in his own words. His is much simpler than mine, so I think it's fine if I describe the general method and he can fill in the details if needed. It's pretty straightforward: he derives a ratio of EPS/orders and applies the most recent quarter's ratio to the total web orders observed in the quarter which he wants to predict EPS for:

current EPS estimate = (previous EPS / previous orders) * (current orders)

This works relatively ok as long as there are no big sequential changes in gross margin, opex (as a percentage of revenue), the effective tax rate, as well as any shift in online vs retail sales. However, in the December quarter there's a seasonal pattern of a significant sequential shift to online orders due to gift giving. I think Caligula uses the previous year's ratio for the December prediction, but I could be wrong.

On to my model. I don't estimate EPS directly from the web orders. Initially I would estimate revenue through a similar ratio formula:

current revenue estimate = (estimated rev/order ratio) * (current orders)

That change there to an "estimated rev/order ratio" is important. In Caligula's model, it's based on the previous quarter data, but I derive this "estimate" from a regression analysis of the observed ratio over the previous 10 quarters (sometimes I've gone further back, but have found this window offers the strongest and most reliable predictive value). This regression model already takes into account the different seasonality for the Christmas quarter. That would give me an estimate for total revenue, from which I derive EPS by guesstimating gross margin, opex, taxes, etc. The historical revenue-orders correlation and the ratio can be seen in the chart below:

Notice in the chart the trend change three years ago for the rev/ord ratio, which reflects the retail expansion effect that you wondered about intuitively. Also, the seasonal patterns are quite evident, although some might say there's too much variability to make any reliable prediction. Maybe they're right, but the guesses have always been pretty close.

Still, these uncertainties made me realize that I needed an independent model for revenue: a traditional model of unit sales, ASPs, market share, installed base, underlying industry growth, etc. So after a year or so of "blindly" guessing the revenue from the orders (I think during 2006) I came up with that model, and included some oddities like the way I implement the halo effect, i.e. how to predict or influence the Mac unit estimate from previous iPod unit sales. This resulted in a quite complex model, but it's turned out very useful. Even if not always accurate at the specific product segments level, the overall revenue predictability has been quite good, I guess from the law of averages taking charge. Like, it would often be a little high in Macs but low in iPods, or vice-versa, or it would underestimate the units but higher ASPs would compensate for it, and thus total reported revenue would come pretty close to the estimate. EPS is another matter, whenever there was a significant miss it would usually be due to my guess for the gross margin.

So now I just come up with the revenue number using this products model, and then see where the rev/order ratio would fall when applying that estimated revenue. If the ratio seems to fall in contradiction of the historical seasonality pattern or the general trend from the last couple of years, it suggests a revision of my assumptions for the other model (for the product segments and units and all that) and then I adjust the variables as needed. Or, I'll consider the possibility that the seasonality or historical trend for the ratio might have changed and what implications such a change means.

This last quarter was turning out to be precisely such a case. After an awful October and an even worse beginning of November, the web orders were coming too low for a Christmas quarter. It would require an amazing recovery in orders after Thanksgiving and throughout December to justify my product segment predictions, or it would mean a break from the normal seasonality pattern of the rev/order ratio. Because of that I asked for everyone's help with their thoughts regarding the choice of a significantly weaker Christmas quarter (maintaining the seasonal pattern for the rev/order ratio) or significantly weaker online gift buying (breaking with the historical pattern of the ratio). People voted for a mitigated online shopping season, but without completely breaking from the historical seasonality.

Well, fortunately the amazing December recovery did happen, and the ratio now does fall within the historical seasonal pattern, thus justifying my revenue estimate. Without that, I would have had to revise revenue down to a little over $10 billion. Hope this example helps you to understand the subjective aspects and uncertainties in the interpretation of the otherwise factual web order data.

And then there's the projections into the future, which Caligula's model doesn't attempt. On the orders side, another regression model is used to project the growth of web orders into the future, with seasonal adjustments. Same with the product segments model, projecting growth into the future but here I have more input based on my intuitive take on Apple's and the industry's product developments. Combining these two I see what the rev/orders ratio might do for the next few years, and if something looks odd there then I may need to adjust the product predictions or accept (and rationalize or justify) such a trend change.

I've kept quarterly archives of the spreadsheet, which lets you see the evolution of complexity in the model. Feel free to explore them once you download from here, and let me know if there's any other questions.

Thanks again for your interest and sorry for the long post.

:D

And this:

I forgot to answer this one: there's a different sequence for the US store, the European store, and the Japan store (not sure which other Asian countries this one includes). Maybe there are other sequences (Africa?) but those are the one's I've verified. I only track the first two, with US representing about 70% and EU about 30% of the orders. I think the Asian sequence would represent less than 10%, and thus not help much in improving the model's predictive power.

:D

Hope that helps!

## Thursday, January 15, 2009

### Welcome!

So I finally got around to setting this up. The lack of file sharing had kept me away, but I decided this vehicle can still help get the word out about my work, and I can always link from here to where the files can be downloaded.

Apple reports on Wednesday, January 21, 2009. So, without further ado, here are my latest and final estimates for FY2009 Q2:

[download page]

Apologies for the horribly dry and messed up presentation. Maybe I'll have to try some new ideas, so any suggestions are welcome.

Cheers,

:D

Apple reports on Wednesday, January 21, 2009. So, without further ado, here are my latest and final estimates for FY2009 Q2:

**Long term recommendation:**

** STRONG BUY below $189**

buy from $189 to $211

neutral from $211 to $235

sell above $235

**3mo ending Dec-2008:**

FY09Q1 Revenue($B) EPS($)

----------- ------

Apple guidance 9.50 1.21

Analysts cons. 9.85 1.39

My estimate 10.88 1.80

My non-GAAP e. 13.43 2.83

Product Units(M)

------- --------

Mac 2.7

iPhone 6.0

iPod 19.5

**3mo ending Mar-2009:**

FY09Q2 Revenue($B) EPS($)

----------- ------

Analysts cons. 8.33 1.13

My estimate 9.64 1.52

My non-GAAP e. 10.77 1.97

Apple gd (est) 8.85 1.15

**12mo ending Sep-2009:**

FY09 Revenue($B) EPS($)

----------- ------

Analysts cons. 36.17 5.05

My estimate 41.13 6.50

My non-gaap e. 47.10 8.92

**Valuation (25 x fwd EPS):**

Timeframe Period Price fwd-12mo EPS

------ ----- ------------

Fair Value Mar-09 189 7.57

1yr Target Dec-09 235 9.40

Analysts mean target 131

[download page]

Apologies for the horribly dry and messed up presentation. Maybe I'll have to try some new ideas, so any suggestions are welcome.

Cheers,

:D

Labels:
AAPL,
Apple,
deagol,
finance,
investment,
model,
spreadsheet,
weborders

Reactions: |

Subscribe to:
Posts (Atom)