“Yeahh come on its mista steal your girl. Let’s go. Bottoms up Bottoms up…” Whoops.
Nah not like that Trey Songz piece. We got a lot of requests for how to build a legit bottoms up model.
Revised Model here: http://wallstreetplayboys.com/apple-bottoms-up/
With that said here’s the document and we will work with you guys together to make this thing better every quarter. The overwhelming choice on who to model was Apple, it’s easy, everyone knows the product and it won’t be as difficult to understand the numbers.
The Basics.
1. Updating the model. Take a look at the first column and you see this is where we begin by building out the first quarter of numbers. Here’s the document to follow through if you need the release.Update Income Statement, Balance Sheet, Cash Flow, Revenue Build and Geographic numbers down the left hand column (last column for page 5). There are multiple EPS numbers and the highlighted row in yellow is what the press looks at in terms of EPS results.
2. Walk Through. We previously contributed a basic three statement model so not much has changed, the template is simply more detailed. To give an idea, we now include y/y and q/q changes, you see share count is flat-lined as the company stated in press releases that it plans to keep the share count the same in the future. In addition, we now project each revenue line-item (more below) and we also added assumptions for the ongoing dividend, stock based compensation expense and cash outlay for the share buyback program.
2. Begin Bottoms Up. Sheet four is where you build out the products line item by line item (iPhone, iPad, iPod etc.). Notice how each line item is projected at different growth rates. For simplicity we took the previous y/y growth numbers and divided them by two to get to the forward projections for now. The most important things to notice with each product line is: seasonality (more sales in December due to holidays), growth rates of iPad are the highest (newest product), iPhone sales are the second highest, Mac sales appear to be flattish, iPod sales are declining (cannibalization from other Apple products) and the rest of the lines are essentially run off a 10%+ or so y/y growth number.
3. Margins. Here is a glaring change that needs to occur. For simplicity we flat-lined gross and operating margins. This does not make sense. Essentially in the future we must assume margin targets for the iPhone, iPad etc. to build into the company profile. This is incredibly important and needs serious thought and attention.
4. Geographies. While this table is important for tracking, we note that growth within each region will likely become increasingly important as the company generates more and more revenue in other countries. As an example, you see that China represents 12.5% of sales in the most recent quarter, however it represents 13.1% of total operating income. This means higher sales in China likely expand the margin profile. Now you’ve got the basic explanation of why people are increasingly focused on Asia sales and the impact of Samsung.
Notes: Blue cells are inputs, black cells are calculations, y/y means year over year growth and q/q means quarter over quarter growth.
Important Questions.
1. Growth. Now once you’ve printed the document out lets think for a second for the product line growth rates. What kind of year-over-year numbers do you think Apple deserves by line item?
2. Selling Prices. Similar to margins, average selling prices is revenue per sale. Do we want to build out a model that is based on unit sales or on gross margins per note three above?
3. Cash. The company has a ton of cash and you can see that in the balance sheet numbers. Do we want to assume they will buy more shares?
4. Future Products. Do we want to assume the company will come out with a new product? If so when and where do we add the numbers?
5. Valuation. The company is large and spits out some dough. Do we want to use a DCF analysis to get a $/share valuation? Use multiples?
We have a couple weeks here before we’ll release the official WSPs Apple analysis built by… Our readers. After that it should take on a life of its own and will be updated quarterly.
