Brett Caughran

Brett Caughran

04-08-2022

23:30

MODELING SERIES: SCRATCH CONSTRUCTION

Lots of questions on how to build a model. So I'm going to walk through my process to build a model from scratch. 3 caveats: 1) I'm using TSLA as an example here. It is JUST an example. I'm hugging consensus and this does not reflect my view on TSLA (have no view)

2) This is a very basic modeling architecture and does not include the bells and whistles. See these threads, 1) 12 common buyside modeling approaches, 2) approaches to rev build for more.

3) there are many, many ways to build a model. Simple. Complex. Annual. Quarterly. We can have a philosophical discussion on what is best. This is just my general, introductory approach (I have about 40 hours of lecture content just on modeling...)

With that out of the way, let's get started. My first step is always source collection. I personally use BamSec for K's/Q's/call transcripts due to ease of use. I will spend time saving down all of the source files that I will need to reference in constructing my model.

This batching process saves me time, and also forms the foundation for my Reading Stack, a concept we will get into when we discuss Deep Thoughts on Deep Dives. I will also spend some time carefully going through the IR website so see what else is disclosed. The SEC is quite

strict with K & Q disclosures, and companies generally have more latitude on what they release in press releases (PRs). Check the PRs (often I will print as PDF and save those down too). Scrub the IR site for materials that might be useful in constructing your model. You are

looking for things like Supplemental Disclosures, or often after a company re-segments they will 8-K a historical segment reconciliation and post it on IR website. Check all of this. In TSLA's example, under "Other Documents & Events", there was a 2021 AI day (for which BamSec

did not have a transcript). You don't want to miss these - as you are doing your deep dive on TSLA, that's an event to listen to (often I will listen to an event like that while I'm doing the core data entry part of the model construction. Two birds.)

After I have collected my data, I will determine my formatting convention (actually I did this long ago, but YOU should do this now). Are you going to input numbers as thousands, millions or billions? Are you going to input costs as negative numbers or positive numbers and

subtract? How many decimal places are you going to go to? Senior buy-side folks who are using models to make big, important investment decisions are quite anal about flow, formatting & convention. Many different conventions work. Here are mine.

My best advice is when you join a new shop, ask to see ~10 models and figure out the formatting conventions and stick to it (or even better, PMs, put together a formatting convention one-pager on conventions you prefer and give it analyst during training. That would be too easy..

I'm also an OG TTS Turbo guy and cried for weeks when they discontinued it. I'm on Macabus now, but it's just not the same (IYKYK). Once conventions are set, open & pre-format your excel file. Modeling templates give me hives - the idea of shoving a company's unique reporting

standards into a standardized template just seems so dumb to me. So this is all the template I will ever start with. My header, which clearly separates quarters from years (in light gray), clearly flows from quarters to year, denotes period end date,

and includes days in period (for those pesky leap years, calendar shifts, and also calc'ing DSO/DPOs/etc). I'm a mini-row guy, which for Control-arrow and Control-Shift-arrow navigation makes things way easier (and looks nice). I'm also incredibly anal about all of my columns

being the exact same width (ALT-HOW to set column width, then F4 to replicate that width). I also like the hide-able quarters, so if i want to look at annual trends i can easily hide them (highlight columns, Alt-DGG)

It's weird i'm so anal about my models as I enjoy a messy office... Once that is set. Pull up the 10-K on one monitor, and model on another (are you even on buy-side if you don't have at least 3 monitors?), and key (use 10 key) the data from 10-K to model. Some services have a

way to download SEC data into an excel file then port that over. Go wild, but I don't like doing that personally. Entering the data, once you get fast, only takes ~60 minutes, and I save that work for times in the day when my mind needs a break (input data & listen to a podcast)

Complete the same exercise for balance sheet & cash flow. Once SEC data is inputted, scan investor disclosures for all operating data. I'm crazy, but I like EVERYTHING I can find in my model. Does the company report mkt share on their call every quarter? Put that in the model.

Every consistent or semi-consistent disclosure should be included in the model. After all of the data is dumped into the model, it is time to add the Analysis Rows. At a very basic level, add % yoy, % 2 year and % qoq to your revenue items.

For your profit items, add % margin, bps yoy, % yoy and incremental margin (again see my 12 modeling approaches for more). For cost items, add % of sales, bps yoy, % yoy as a baseline. There's a lot more to add and use your judgement on what is relevant for each item.

Once all of the Analysis Rows are added below your numbers, it is time to build the forecasting architecture. I like to denote key forecast items with a yellow shade (to remind me where I need to do research and make a forecast). Here, I drive revenue with a simple % yoy forecast

I will do the same process with cost & margin items. I generally approach more variable cost items like GM on a bps yoy basis, and more fixed / semi-fixed items with a % yoy basis.

Model out your below-the-line items and you get to 2025 EPS. Yeah buddy!!! On the assumption that TSLA is currently trading on '23 EPS, applying a P/E ratio to '25 EPS should get us a basic estimation of where TSLA could trade in 2-2.5 years. P/E ratio time future EPS = Price!

Pretty simple, right? There's complexity that then gets layered onto this core chassis (GAAP to non-GAAP, re-segmenting UGH, revenue modeling, accounting bs, etc). But as an investor you get paid for researching & being correct on these inputs & the model provides the framework



Follow us on Twitter

to be informed of the latest developments and updates!


You can easily use to @tivitikothread bot for create more readable thread!
Donate 💲

You can keep this app free of charge by supporting 😊

for server charges...