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:

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:

(click on image for larger view)

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!