How to create a 3-statement model in Google Sheets, an Alphabet example

Downloadable copy of the final Alphabet model provided within.

I was a bit bored this weekend and with an interview coming up I decided to create a 3-statement model for Alphabet using Google sheets, a platform I hadn’t used much before (note: I’m a certified excel nerd). I downloaded Alphabet’s 10-K filing from Bamsec.com and got started by creating the P&L, the balance sheet, then the cash flow statement. I populated a fairly conservative operating case — at least it would have been conservative before the Coronavirus !— with minimal operating leverage.

Below I provide a few of my thoughts from doing this 3-statement exercise, as well as tips for creating your own 3-statement models. I’ve included a link to the Google Sheet here so you can download the model and play around with the build and assumptions.

Analysis of Results

Having done this analysis, I’m struck by the power of Alphabet’s business model.

• ~80% of revenue today is the flagship ads business, which saw 16% growth in 2019. In core search, Google exhibits 85%+ market share and impossibly high barriers to entry. YouTube ads grew more than 30% last year at $15bn of scale
• Google cloud is a $9bn business that grew over 50% last year
• “Other Bets” is small contribution but includes Google Play, hardware such as Nest and Pixel, YouTube non-ad revenue, and Waymo, a developer of self-driving car tech recently valued at $30bn

A pretty strong portfolio! Alphabet has $133 billion of cash & cash-like securities and is trading at 3.9x Enterprise Value to 2019 Revenue and 13.8x Enterprise Value to 2019 Operating income + D&A.

Model Outputs

Profit & Loss

Balance Sheet

Cash Flow Statement

Public Market Valuation

Tips for creating your own 3-statement model

Below are a few tips to creating a model just like this. Remember, practice makes perfect.

1. Do the P&L first. When creating a 3-statement model, it’s typically best to start with P&L, move next to the Balance Sheet, then end with the cash flow statement. In fact, the majority of the items on the cash flow statement should fall right from the balance sheet.
2. Schedules. When in doubt of how a line item should be driven, make a schedule. PP&E, depreciation, debt, and shareholder’s equity are all line items that work very well inside of a schedule.
3. Use of “checks”. I can’t say this enough — you are bound to make mistakes while working in excel and google sheets — add checks anywhere and everywhere you can to validate your work.
4. How to add additional analyses to this model.

• Multiple operating cases. To add more cases you could extract the cells with assumptions (blue) into a new tab. We could then add new assumptions for these items through a case selector (example video here).
• 2020 quarterly build. To do a quarterly model we could (i) bring in historical financials to the model, (ii) create a new tab for 2020 quarterly, focusing first on seasonality in working capital, revenue, and the expense base
• Alphabet future share price. To predict future Alphabet share prices we can add a new tab that sensitizes future share price under (i) range of Revenue or Operating Income results, and (ii) Revenue or Operating Income Enterprise Value multiples. We might also elect to contextualize this via a discounted cash flow (DCF) analysis in a separate tab.

Again, a public link to the model is here — check it out, download, and play around with the assumptions. Happy modeling!

Note: full disclosure I did buy a few Alphabet shares last week :)