Published on August 9th, 2018 | by Maarten Vinkhuyzen0
A Tesla Financial Model & Shareholder Letter Walkthrough
August 9th, 2018 by Maarten Vinkhuyzen
Last Wednesday, August 1, 2018, we got a ton of fresh information in the Tesla shareholder letter and conference call. The prediction of our financial model of the Tesla results, “Vijay’s SimTesla Game,” was right within a few million, after correcting for the ignored restructuring costs. That’s not because we got everything right, but because the mistakes cancelled each other out.
The new information calls for an updated “Vijay’s SimTesla Game” ( aka CleanTechnica Financial Model) — you can download the next version of Vijay’s game at that link. In the comments below my pre-call article, I promised a walkthrough the next day. I did need a few more days, but here it is.
First of all, note that the new version of the model has more tabs for worksheets and they are more clearly labeled.
The first two are text. Please read them. The next two, “CleanTechnica Green” tabs, include the financial prediction model and are what this walkthrough is all about. The “Tesla Red” tabs contain the figures of the last 6 quarters as published by Tesla. They are for reference purposes only. The last tab (data) was hidden, and perhaps you should hide it too. It contains some calculations used in making the model. It is only of interest to curious spreadsheet wizards.
For this walkthrough, I suppose you are using Microsoft Excel and a computer with a large screen. If you use another app or a phone, I have no idea what you are seeing.
Note: Figures below use European punctuation, which generally reverses where you use commas and where you use decimal points — compared to US styling.
The first green tab is the Productivity & Profitability Prediction Model.
You can’t predict the financial results without predicting what they are going to produce, how much of it, and at what prices. Those prices are both the sales prices and the production prices. Below the numbers on production volume and prices are all those other costs, like R&D, SG&A, interest, and taxes.
For four years with four quarters each, displayed over 2 columns, that is a dizzying 32 columns of confusing numbers.
But there are some tricks to get a better view of what is important and what you are looking at.
In the left border, there are vertical lines ending in a box with a minus sign, “-“. You can use these to fold and unfold groups of figures. You can, for example, fold the individual car model lines to see only the automotive (cost of) revenue. Above these lines in the top left corner, there are buttons to fold and unfold all groups at once. You can also scroll the quarters without losing sight of the left and top headings. Regretfully, some parameters are in the 2020 Q4 column and they move out of sight when you scroll. I have to come up with a better layout in a future version.
For the rest of the walkthrough, I scroll to the 2018 Q3 and 2018 Q2 columns. The 2018 Q2 column is no longer my prediction, but is brought in line with Tesla’s published numbers. In so far as next quarter predictions are based on previous quarter results, this should give a slightly improved prediction on the coming quarters.
The Basic Assumptions
At the top of the sheet, we see the production volume and prices for the models that are offered. This is the base of our predictive model. These numbers are a combination of Tesla guidance and our best deductions.
Models S and X together have a yearly capacity of 100,000 cars. The first 2 quarters in 2018 are below the average for these models, but Tesla expects to compensate for that in the rest of the year to finish around 100,000 cars delivered.
Later quarters, from 2019 Q1 onwards, are based on 12,500 per model per quarter, with Model X cannibalizing the sales of Model S a little bit. There is a parameter down around line 52 for the size of the shift, just to visualize what these small shifts do over time. The ASP (Average Sales Price) of the Models S and X is based on volume and revenue of preceding quarters.
The Model 3 production volume and ASP are calculated with the large parameter block at the bottom of the sheet.
The Semi and Model Y have their line, and when they reach production, best guess numbers can be provided. We have entered our WAG, but feel free to put in there any numbers that seem more likely to you.
Going down to the Revenues group, we have revenue per product and not only per product group.
The P&L in the shareholder letter is called “Condensed Consolidated Statements of Operations.” To model the results, we have a less condensed statement.
The computation of the revenue for the cars is simple — just the multiplication of production volume and average sales price. There is nothing fancy about it. In the gray field behind the revenue is the change compared to the previous quarter. Look at the change of Model 3 revenue between Q2 and Q3 to understand why Q3 is profitable while Q2 is still a huge loss.
Contrary to the Tesla results, there is no line for revenue from leasing. The logic to offer leasing is that it increases sales and the revenue on a leased car is higher than on a sold car. But the lease revenue in this quarter is from cars delivered in the previous 2–4 years. It has nothing to do with how well Tesla performed in the quarter you are looking at. For a predictive model, it makes more sense to pretend all cars that are produced are also sold in the quarter, and with the “build to order” sales model, that is a legit assumption. When predicting next quarter, cars in transit at beginning and end of quarter are corrections to be made to this assumption, but for looking at the development over time, this does not matter.
The ZEV credits are isolated from the individual car model revenues. They are unpredictable and the market for them could be gone tomorrow. The cars should be profitable without them.
The energy generation and storage line from the shareholder letter is split in separate lines for energy storage and energy generation. They have different growth paths and profit abilities/margins. Their growth is controlled by a set of parameters below the prediction part of the sheet. But in individual quarters, they can have outliers above and below the trend line.
In the first quarter, the large Australian “hundred days or it is free” project was accepted and the revenue counted. That was a large increase from the previous Q4 and followed by a little slump in the following Q2. This is typical for large industrial projects and both storage and generation can show this behavior. For companies which do a lot of this kind of work, giving information about the order portfolio and the status of the projects is essential.
The last revenue line is Services and others. This is revenue from the service centers, sales of used cars, deliveries to other companies (like drivetrains), and whatever else creates revenue and does not belong under one of the previous lines. This line is not really predictable, it is also not very big. It will not make much difference in the grand scheme of things. The assumption is that it will grow in line with the growth of the company.
Cost of Revenues
This is where the rubber hits the road, where the secret sauce makes the dish delicious (or makes you call for pizza delivery), where it becomes clear whether you are running a real business or a philanthropic institution.
Whatever it is that Tesla is doing to make and sell its products, it has to cost less than the revenue it generates. This is what the competition and tear-down engineering consultants like Sandy Munro try to find out.
All we have is the mythical Gross Margin (GM) that Tesla publishes. And that gross margin includes the effects of the auto leasing program, the sale of ZEV credits, the one-time costs of ramping a new product, and other distortions. By again making separate lines for each product, we can see that the gross margin of the Model S & X is rather constant and the gross margin of a new product is horrendous in its first quarters. We can also see what the overall effect of launching a new product is on the gross margin as a percentage of revenue, and what the effect on gross profit is as a growing pile of cash.
The Cost of Revenue is calculated as Revenue*(1-GM). For Model S&X in Q2, that is: 2,260,960*(1-0.232) = 1,736,417. For easy math, the GM percentage is translated to the fraction it really is.
The GM for the Model S & X is calculated based on historical data and Tesla’s guidance that the company will try to improve it over time.
The GM for the new Model 3 is the all-important factor everybody has been guessing about. Make your own guess, or use the guidance from Tesla (and maybe add some optimism or doubt).
The future GM of the Semi and Model Y is based on the CapEx spend for these products and the inefficiency of early production. If you feel up to it, you can fine tune this formula. Or start a discussion about it in the comments.
Looking at the historical GM for the energy products, the energy products have had a rough time. The energy generating products are transitioning from the SolarCity business model to the new Tesla business model. Energy storage is in a delayed ramp up mode. But according to management, which is optimistic as usual, the future for both product lines is bright. I tend to agree, but when and how bright is a point of deliberation. Good luck finding the right GM.
And then there is the “services and others” category. If a salesperson is willing to give you a discount, this is the place to do it. He is not allowed to lower the price of your new Tesla car, but the money for the trade-in is negotiable. If you can get an old Honda Civic appraised as a nearly new Audi A5, that part of the invoice will appear as a cost in the “services and others” line. And as long as there are customers better at haggling than salespeople (eager to hit sales targets), the margin on this line item will stay red, IMHO.
This is the amount of money Tesla has earned doing its basic business activities. This is not the profit some confuse it with, but just the difference between Revenue and Cost of Revenue.
It is the money Tesla has available to pay all other necessary bills, collectively called overhead or “Operating Expenses” or OpEx.
This is the HR department, the security at the port, the accounting department (including where I started my financial career), the department that computes on a weekly or monthly basis all of the money due to those who worked for Tesla and then makes the payments — one of the best places to work in the company, since everybody is nice to you.
You get it — opex is all of those parts of the company that enable the core activities of making and selling the products. It is roughly divided in two parts, R&D and SG&A.
Research and Development (R&D) takes care of the products of tomorrow.
Selling, General, and Administrative (SG&A) is all the rest, including the lavish coach in the factory conference room the CEO spends his few non-waking hours on.
Arriving at EBIT
This essentially concludes the “Production & Profitability Walkthrough,” because now we have reached EBIT, one of those scary acronyms those Wall Street dudes are so fond of. EBIT is “Earnings Before Interest and Taxes.” If this number is positive — and I mean not a few cents, but a few percent of the total revenue — and it is not a fluke or an accounting trick, then the company is essentially healthy. It is expressed as thousands of dollars and also as a percentage of revenue.
What comes after this point is for the financial wizards, the accounting consultants, and fiscal lawyers. Their job it to minimize what goes to Uncle Sam / the IRS and maximize what is the stockholders’ gain. And I could not care less. The numbers that are important for Tesla and the transition to a sustainable future are above this point. My advice is to just close the block between EBIT and Net Profit using the grouping function in the left border.
With this attitude, do I have to warn you this is not investment advice?
We at CleanTechnica tend to be long on Tesla (among others) and fight the short trolls. But this financial model is not intended to judge Tesla as an investment vehicle. Rather, it is intended to help you understand the reporting on Tesla and separate the news from the FUD.
After this walkthrough of the Profit and Loss Model, we come to the controls of the model. The knobs your Elon and Deepak avatars use to create a Bankwuptcy or a trillion dollar company. The parameters are reasonably well annotated, no need to explain them here. Just go on and experiment with them.
You can always download a fresh copy of the spreadsheet to start over.
Addendum 1: The CapEx Prediction Model
This is a single line of the cash flow statement put under a microscope.
It looks like a normal project planning model. To be honest, it is the financial part of a project planning model.
We have projects, what they will probably cost (the budget), and when that part of the budget will likely be spent. What has to happen in these projects is outside the scope of this model (nice 😉 ) — I’m saving this for another article).
This is not all the CapEx Tesla spends, only the large, long-running projects. A bunch of new computers for the accounting office or the clay modeling tools for the design center are not in this model. And dozens of those small expenditures each quarter add up to tens of millions of dollars. But that is outside the scope of this model.
Below the projects, we have the cash sources that can provide the money for these projects.
The most logical sources of cash are the depreciation on the tools and equipment that are in production making cars and batteries. The profit, after taxes, is also a source of cash for investments.
And last but not least, there is non-cash stock based compensation. This is paying your workers in stock instead of in money. For the profit or loss, that does not make a difference; for the cash flow, it is a huge difference. If you pay in stock, the money stays in your bank account and can be used for other purposes.
Addendum 2: Tesla’s Figures
On the sheets with red tabs are the figures published in the “Tesla Shareholder Update Letters.” It is an exact copy of the blue and white tables. The lay-out is over two columns to make them a bit less daunting and easier to understand.
The terms in the first column are very well explained on Investopedia.
This concludes the walkthrough. The goal of “Vijay’s SimTesla Game” is to offer education, playful fun, and deeper understanding of Tesla and how large businesses work.