Near-future quarterly financial projections

i am struggling with my financial models and their results for the rest of the year. i think i have done everything reasonably well but would like to hear what others think.

you may find the inventory evolution odd: it's because i am modeling tesla gaming the 200k cutoff for the us tax credit. that is, they intentionally build cars and withhold delivery to avoid crossing 200k, so inventory builds a bit in q2 and is worked off in q3.

those not familiar please understand there are massive inaccuracies trying to project cash flows and balance sheet items, getting close on the income statement is hard enough. so don't be surprised to see things that make you go "hmmmm?".

thank you in advance for any constructive feedback.

…	luv q4-18	luv q3-18	luv q2-18	Mar-18
s deliveries	15,000	15,000	11,500	11,738
x deliveries	13,000	12,000	10,500	10,077
s+x deliveries	28,000	27,000	22,000	21,815
3 deliveries	60,000	50,000	23,000	8,182
ls veh % total	0.11	0.11	0.11	0.11
avg price s+x	106.00	106.00	106.00	105.76
avg price model 3	58.00	60.00	56.00	56.00
revenue
auto sales ex 3	2,641,520	2,547,180	2,075,480	2,053,375
auto sales mod 3	3,480,000	3,000,000	1,288,000	458,192
auto leasing	186,903	180,913	175,192	173,436
1 time autopilot	0	0	0	0
zev credits	100,000	100,000	0	50,314
total auto	6,408,423	5,828,093	3,538,672	2,735,317
energy storage	180,000	180,000	164,500	185,022
solarcity	220,000	275,000	275,000	225,000
grohmann	0	0	0	0
services/other	300,000	300,000	275,000	263,412
total revenue	7,108,423	6,583,093	4,253,172	3,408,751
cost of revenue
auto sales ex 3	1,973,415	1,903,004	1,553,374	1,540,031
auto sales mod 3	2,836,200	2,490,000	1,288,000	551,366
auto leasing	119,618	115,784	112,123	104,496
total auto	4,929,233	4,508,789	2,953,497	2,195,893
energy storage	180,000	189,000	180,950	217,863
solarcity	154,000	192,500	192,500	157,500
grohmann	10,999	10,999	11,000	11,000
services & other	375,000	384,000	365,750	369,969
total cost of rev	5,649,232	5,285,288	3,703,697	2,952,225
gross profit	1,459,191	1,297,805	549,475	456,526
auto gaap ex 3 gm	28.5%	28.6%	26.0%	27.8%
auto-zev ex 3 gm	26.0%	26.0%	26.0%	26.1%
model 3 gm	18.5%	17.0%	0.0%	-20.3%
auto-zev incl 3 gm	21.9%	21.3%	16.5%	18.2%
storage gm	0.0%	-5.0%	-10.0%	-17.7%
scty gm	30.0%	30.0%	30.0%	30.0%
grohmann gm	-100.0%	-100.0%	-100.0%	-100.0%
services gm	-25.0%	-28.0%	-33.0%	-40.5%
opex
tesla r&d	335,000	335,000	335,000	332,096
tesla sg&a	545,000	545,000	545,000	546,404
1time acq cost	0	0	0	0
solarcity r&d	35,000	35,000	35,000	35,000
solarcity sg&a	140,000	140,000	140,000	140,000
total opex	1,090,000	1,080,000	1,070,000	1,053,500
op income	369,191	217,805	-520,525	-596,974
interest inc	6,000	6,000	6,000	5,214
interest exp	-107,000	-107,000	-107,000	-102,546
scty interest	-53,000	-53,000	-53,000	-47,000
other income exp	-12,000	-12,000	-12,000	-37,716
1time scty gain	0	0	0	0
pretax income	203,191	51,805	-686,525	-779,022
income tax	19,999	19,999	20,000	5,605
net income	183,192	31,806	-706,525	-784,627
non-cont int.	-50,001	-50,001	-50,000	-75,076
net inc to common	233,193	81,807	-656,525	-709,551
basic shares	170,000	170,000	170,000	169,146
diluted shares	170,000	170,000	170,000	169,146
diluted gaap eps	1.37	0.48	-3.86	-4.19
gaap net income	233,193	81,807	-656,525	-709,551
- stock based comp	140,000	140,000	140,000	141,639
- one time scty	0	0	0	0
non-gaap net income	373,193	221,807	-516,525	-567,912
non-gaap diluted eps	2.20	1.30	-3.04	-3.36
balance sheet
current assets
cash & eq.	2,176,308	1,688,360	1,881,948	2,665,673
restricted cash	150,000	130,000	100,000	120,194
accts rcvbl	1,168,508	1,082,152	745,762	652,848
inventory	4,457,476	4,633,677	3,490,608	2,565,826
prepaids+other	342,700	339,358	336,017	379,379
total current assets	8,294,992	7,873,547	6,554,334	6,383,920
op lease vehicles	2,519,505	2,439,234	2,359,070	2,315,124
solar energy sys	6,345,887	6,349,381	6,352,910	6,346,374
pp&e	12,420,083	11,695,516	11,061,438	10,519,226
intangible assets	361,502	361,502	361,502	346,428
goodwill	60,237	60,237	60,237	61,284
mypower rcvbls	428,754	435,754	442,754	449,754
restricted cash	440,000	440,000	440,000	433,841
other assets	273,123	273,123	273,123	415,478
total assets	31,144,084	29,928,294	27,905,368	27,271,429
current liabiliites
accts payable	4,767,023	4,691,598	3,429,725	2,603,498
accrued liabs+other	1,972,157	1,900,064	1,828,971	1,898,431
deferred revenue	604,681	585,416	542,586	536,465
resale value guar	600,000	600,000	600,000	629,112
cust deposits	965,000	965,000	965,000	984,823
curr debt+leases	1,500,000	1,500,000	1,800,000	1,915,530
curr solar bonds	100,000	100,000	100,000	82,500
total current liabs	10,508,862	10,342,078	9,266,282	8,650,359
lt debt+leases	9,415,700	9,415,700	9,415,700	8,761,070
solar bonds	100	100	100	100
rel party conv debt	2,519	2,519	2,519	2,556
deferred revenue	881,827	853,732	825,674	818,250
resale value guar	650,000	670,000	700,000	756,800
other lt liabilities	2,806,088	2,726,344	2,647,600	2,561,886
comm stk warrants	0	0	0	0
capital lease oblg	0	0	0	0
total liabilities	24,265,095	24,010,473	22,857,875	21,551,021
commits/contings
rdmbl ncis in subs	402,943	402,943	402,943	405,835
conv senior notes	0	0	0	2
nci in subsidiaries	900,000	900,000	900,000	863,876
common equity	5,576,045	4,614,878	3,744,550	4,450,695
cash flow statement
cash flows from ops
net loss	183,192	31,806	-706,525	-784,627
dep/amortization	484,383	456,125	442,458	416,233
stock-based comp	140,000	140,000	140,000	141,639
am of debt discount	35,000	35,000	35,000	39,345
inv write-down	46,337	34,906	25,658	18,546
loss on disposals	45,000	45,000	45,000	52,237
forex loss (gain)	25,000	25,000	25,000	47,661
loss on acq scty	0	0	0	0
non-cash int/other	0	0	0	-3,984
chgs in op as/lb
accts rcbl	-86,356	-336,391	-92,914	-169,142
inv / op leases	95,930	-1,223,234	-968,727	-419,277
prepaids/other ca	0	0	0	-50,001
mypower rcvbls + other	-15,000	-15,000	-15,000	-57,583
accts pybl/accr liabs	147,519	1,332,966	756,767	317,983
deferred revenue	75,000	65,000	50,000	45,795
customer deposits	0	0	-19,823	67,359
other lt liabs	0	0	0	-60,560
net cash from ops	1,176,005	591,179	-283,107	-398,376
cash flows from inv
pp&e purchases	-900,000	-800,000	-700,000	-655,662
purchase solar sys	-60,000	-60,000	-70,000	-72,975
net cash from inv	-960,000	-860,000	-770,000	-728,637
cash flows from fin
stock issued	0	0	0	0
debt issued	100,000	200,000	100,000	1,775,481
debt repayments	0	-300,000	0	-1,389,388
rel pty solar repaids	0	0	0	-17,500
coll lease borrowing	100,000	100,000	100,000	-87,092
stock option excrs	75,000	75,000	75,000	94,018
capital lease paids	-30,000	-30,000	-30,000	-18,787
stock+debt issue cost	-12,000	-12,000	-12,000	-2,913
investment by nci in subs	75,000	75,000	75,000	73,704
dist to nci in subs	-50,000	-50,000	-50,000	-52,942
buyouts of nci in subs	0	0	0	-2,921
net cash from fin	258,000	58,000	258,000	371,660
forex effect	13,944	17,232	11,382	10,102
net change in cash	487,949	-193,589	-783,725	-745,251
cash & eq start	1,688,360	1,881,948	2,665,673	3,367,914
cash & eq end	2,176,308	1,688,360	1,881,948	2,665,673

 

in the q4 17 letter they said energy storage sales would triple in 2018. not sure if they mean deployment or revenue, but for reference i estimated in 2017 they deployed 410 mwh of storage and generated 145.5m in revenue.

3x that would be 1230 mwh and 457m of revenue. in q1 2018 i estimated they deployed 373 mwh for 185m in revenue. to meet q4 guidance the rest of the year should be ~860 mwh with 272m in revenue.

i chose to flatline the storage vs q1 as i didn't have great visibility, other than to know q2 will lose a big australia deployment. that would be a nearly 5x increase year over year in storage revenue, which i think might be aggressive.

i limited margin improvement to -10% -5% and 0% in q2-q4 and i don't feel great about that because the last 5 quarters i estimate storage margin to have ranged from -90% to -18% with no consistent trend. it's possible there would have been a developing trend if 17q4 storage margin didn't drop to -72%, which i think is due to donations for hurricane maria victims in puerto rico. 18q1 storage gross margin i estimated at -18% and that is the best that has been seen. so it is possible it trends towards zero from here: price increases and dropping free puerto rico work etc. will help.

in summary, i think i have modeled a perhaps overly adequate increase in the storage business and i have given them gross margins that have not been observed since sept 2016.

you're welcome. the cost cutting would most likely go to tesla r&d or tesla sg&a. which shows me an error: i don't have the opex totaling properly. i meant to have opex creep higher but i left the opex line items the same, i'll fix that in the next revision.

i didn't give them cost reductions in sg&a because opex has only gone down once in the last 4 years. maybe elon will clean up some excess and it will flatline or even go down, would be a little upside surprise if that happened.

 

fwiw i had estimated using inside ev's data about 177k deliveries thru march end: 164k s/x + 10k 3's and i added 3k for roadsters.

​

the line item in my model should say % of s/x deliveries leased (will change that).

my interpretation may be incorrect but "8% of our deliveries in q1 were subject to lease accounting" to me means 8% of total deliveries. total deliveries were 29,997 (s/x + 3). 8% of that amount is ~2400. and 2400 / 21,815 (s/x deliveries) = 11%. that's why you see 11% up there.

on the credit revenue, i do have zev credits split, the remaining amount of other credits has been small enough that i didn't bother to do the split - but this does have the effect of perhaps artificially boosting calculated asp.

 

thanks for the comments from everyone. have already helped find/fix a few errors and inconsistencies.

i had to put out q3 and q4 b/c q2 looks so much worse than i expected. had to do work further out than i feel comfortable modeling just to get a sense of how they could hold this thing together.

on the model 3 margin, my q2 margin is a goose-egg up from around -20%. on the call they mentioned there would be 2k of depreciation per vehicle at 5k per week of production. i assume that to mean 2k x 5k x 13 weeks = 130m of depreciation is spread across the production.

in q1 you'd have 130m / 8,182 units = 15.9k depreciation per model 3 which i assume is in cogs.
in q2, i'd get 130m / 23,000 units = 5.6k depreciation per vehicle.
on a 56k asp the 10.3k depreciation per unit difference is exactly 20%, hence gross margins going to zero.

sorry can't share the excel version.

on the accts payable and accrued liabilities cash flow line, it's driven by the balance sheet projections for payables and accrued liabilities. note that this indirect method of estimating the cash flow line item has not been historically exact to cash flow, but it's the best i felt i could do. as i am reviewing the data again, i can see that balance sheet changes in payables + accrued liabilities has been recently slightly over-estimating the cash flow line. so i can make a 50m adjustment lower just for that difference.

now reviewing the balance sheet line items of interest:

…	luv q4-18	luv q3-18	luv q2-18	Mar-18
accts payable	4,767,023	4,691,598	3,637,155	2,603,498
accrued liabs+other	1,972,157	1,900,064	1,828,971	1,898,431
total cost of rev	5,649,232	5,285,288	3,927,697	2,952,225
dpo*	77.00	81.00	84.50	80.47

you can see the entire driver to the cash flow line is an increase in accts payable from the balance sheet. #2 in your thoughts above.

while the "delayed payments" seem enormous as an absolute quantity, if you check it through days payables outstanding (dpo), you'll actually find for 18q2 estimates i modeled a year-over-year decrease. basically as the sales and production pick up pace, the payables naturally grow from the "float" of materials that were invoiced but not paid. from 2015-2016 dpo averaged 96. from mar 17-mar 18 the range of dpo is 76-101.

i modeled a slight uptick in dpo in q2 only because i felt there are deliveries that would be withheld (so revenue would be low relative the amount of materials that had been invoiced). in q3 and q4 i am actually bringing dpo down each quarter to ~18q1 and 17q4 levels. in my model the 18q4 dpo is the lowest level seen since 2014.

do you think maybe you're underestimating payables growth based on how production / sales scale?

*dpo = days payables outstanding = payables / (quarterly cost of revenue / (365/4))

 

chart of dpo over last few years:


if the payables were unsustainable, i would have expected days payables outstanding to be growing over time, and perhaps near a record high. this is clearly not the case. from 2014-2017 days payables was routinely in the 90-100 range and we are well below that in the most recent period (see chart above, note axis is backwards showing most recent projections first). it would seem like payables could be even higher than i have modeled just to get into recent ranges. i don't know how else to intelligently model payables except vs cogs, inventory, or revenue. simply guessing that it's too high or too low would be inadequate for me without some quantitative support.

qualitatively you could say suppliers would demand stricter payment terms due to tesla's [deteriorating] credit rating, but i don't see that happening as long as production is growing. those guys want to sell their parts!

i went through and did some calculations on canada based on known orders from troy's spreadsheet. canada has approximtely 10% of usa reservations. so if assuming 250k usa reservations figure 25k in canada. the take rate for the current configuration has been running around 60%. so there are potentially 15k canadians who would configure with the current config. also 60% of canadian reservations are in ontario, and the take rate there is 70%. even haircutting these numbers a bit there could easily be 8-10k canadians willing to buy the current config. i don't have anything better to work with.

 

on suppliers wanting to collect, i may be looney but my modeling is showing 1b+ of operating cash flows in q4. so really they only need to keep people happy until then when the financials take over.

i didn't model draws on other liquidity lines (for example against the fremont factory), which they could use to show higher cash balances to allay fears. from the 2017 10k:

In addition, we had $2.04 billion of unused committed amounts under our credit facilities and financing funds, some of which are subject to satisfying specified conditions prior to draw-down.​

you may have noticed klarman getting long tesla mar 2019 bonds: he too must believe there's little risk of not getting paid to march 2019.

on model 3 asp, this quarter came in 4k higher than i had thought previously (at 56k). this means option take rates are a bit higher than i thought. i believe in q3 they will still only be making long range + premium upgrades but they will add awd (+$5k) and performance models (+$29k). hold other option take rates constant to start at the 56k base of q1, and then figure 10% take rate on performance and 30% on awd together boost asp by 4k. i think they will mix in lower option vehicles in q4 which will start to drop the asp again.

as far as how sustainable the asp will be going forward, it could stay elevated as they start doing international deliveries. as long as the order book is deep, they can blend higher and lower option vehicles to maintain a higher asp for a short time. i expect that longer run the asp will drop into the 40k-50k range, but probably not until mid 2019.

on s/x numbers, assuming they game the tax credit runoff, they will be running the entire auto business flat out. we've seen when one of these credits runs off that there is a demand spike ahead of the expiration and then a lull. we've seen tesla comfortably produce 25k in a quarter, and with 4k "in-transit" and a little extra effort, i think 27-28k of production can happen. tesla will want to sell every single s&x they possibly can into the usa in q3 & q4 because the orders may cancel if they can't get in ahead of the credit expiration. i'd expect a clearing of the decks for domestic new s/x inventory goes in q4. so both demand and supply seem to be there to meet my projections.