Near-future quarterly financial projections

[luvb2b]

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.

…

s deliveries

x deliveries

s+x deliveries

3 deliveries

ls veh % total

avg price s+x

avg price model 3

revenue

auto sales ex 3

auto sales mod 3

auto leasing

1 time autopilot

zev credits

total auto

energy storage

solarcity

grohmann

services/other

total revenue

cost of revenue

auto sales ex 3

auto sales mod 3

auto leasing

total auto

energy storage

solarcity

grohmann

services & other

total cost of rev

gross profit

auto gaap ex 3 gm

auto-zev ex 3 gm

model 3 gm

auto-zev incl 3 gm

storage gm

scty gm

grohmann gm

services gm

opex

tesla r&d

tesla sg&a

1time acq cost

solarcity r&d

solarcity sg&a

total opex

op income

interest inc

interest exp

scty interest

other income exp

1time scty gain

pretax income

income tax

net income

non-cont int.

net inc to common

basic shares

diluted shares

diluted gaap eps

gaap net income

- stock based comp

- one time scty

non-gaap net income

non-gaap diluted eps

balance sheet

current assets

cash & eq.

restricted cash

accts rcvbl

inventory

prepaids+other

total current assets

op lease vehicles

solar energy sys

pp&e

intangible assets

goodwill

mypower rcvbls

restricted cash

other assets

total assets

current liabiliites

accts payable

accrued liabs+other

deferred revenue

resale value guar

cust deposits

curr debt+leases

curr solar bonds

total current liabs

lt debt+leases

solar bonds

rel party conv debt

deferred revenue

resale value guar

other lt liabilities

comm stk warrants

capital lease oblg

total liabilities

commits/contings

rdmbl ncis in subs

conv senior notes

nci in subsidiaries

common equity

cash flow statement

cash flows from ops

net loss

dep/amortization

stock-based comp

am of debt discount

inv write-down

loss on disposals

forex loss (gain)

loss on acq scty

non-cash int/other

chgs in op as/lb

accts rcbl

inv / op leases

prepaids/other ca

mypower rcvbls + other

accts pybl/accr liabs

deferred revenue

customer deposits

other lt liabs

net cash from ops

cash flows from inv

pp&e purchases

purchase solar sys

net cash from inv

cash flows from fin

stock issued

debt issued

debt repayments

rel pty solar repaids

coll lease borrowing

stock option excrs

capital lease paids

stock+debt issue cost

investment by nci in subs

dist to nci in subs

buyouts of nci in subs

net cash from fin

forex effect

net change in cash

cash & eq start

cash & eq end

[TD2] luv q4-18 [/TD2][TD2] luv q3-18 [/TD2][TD2] luv q2-18 [/TD2][TD2] Mar-18 [/TD2] [TD2]15,000[/TD2][TD2]15,000[/TD2][TD2]11,500[/TD2][TD2]11,738[/TD2] [TD2]13,000[/TD2][TD2]12,000[/TD2][TD2]10,500[/TD2][TD2]10,077[/TD2] [TD2] 28,000 [/TD2][TD2] 27,000 [/TD2][TD2] 22,000 [/TD2][TD2] 21,815 [/TD2] [TD2] 60,000 [/TD2][TD2] 50,000 [/TD2][TD2] 23,000 [/TD2][TD2] 8,182 [/TD2] [TD2] 0.11 [/TD2][TD2] 0.11 [/TD2][TD2] 0.11 [/TD2][TD2] 0.11 [/TD2] [TD2] 106.00 [/TD2][TD2] 106.00 [/TD2][TD2] 106.00 [/TD2][TD2] 105.76 [/TD2] [TD2] 58.00 [/TD2][TD2] 60.00 [/TD2][TD2] 56.00 [/TD2][TD2] 56.00 [/TD2] [TD2]2,641,520[/TD2][TD2]2,547,180[/TD2][TD2]2,075,480[/TD2][TD2]2,053,375[/TD2] [TD2]3,480,000[/TD2][TD2]3,000,000[/TD2][TD2]1,288,000[/TD2][TD2]458,192[/TD2] [TD2]186,903[/TD2][TD2]180,913[/TD2][TD2]175,192[/TD2][TD2]173,436[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]100,000[/TD2][TD2]100,000[/TD2][TD2]0[/TD2][TD2]50,314[/TD2] [TD2] 6,408,423 [/TD2][TD2] 5,828,093 [/TD2][TD2] 3,538,672 [/TD2][TD2] 2,735,317 [/TD2] [TD2]180,000[/TD2][TD2]180,000[/TD2][TD2]164,500[/TD2][TD2]185,022[/TD2] [TD2]220,000[/TD2][TD2]275,000[/TD2][TD2]275,000[/TD2][TD2]225,000[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]300,000[/TD2][TD2]300,000[/TD2][TD2]275,000[/TD2][TD2]263,412[/TD2] [TD2] 7,108,423 [/TD2][TD2] 6,583,093 [/TD2][TD2] 4,253,172 [/TD2][TD2] 3,408,751 [/TD2] [TD2]1,973,415[/TD2][TD2]1,903,004[/TD2][TD2]1,553,374[/TD2][TD2]1,540,031[/TD2] [TD2]2,836,200[/TD2][TD2]2,490,000[/TD2][TD2]1,288,000[/TD2][TD2]551,366[/TD2] [TD2]119,618[/TD2][TD2]115,784[/TD2][TD2]112,123[/TD2][TD2]104,496[/TD2] [TD2] 4,929,233 [/TD2][TD2] 4,508,789 [/TD2][TD2] 2,953,497 [/TD2][TD2] 2,195,893 [/TD2] [TD2]180,000[/TD2][TD2]189,000[/TD2][TD2]180,950[/TD2][TD2]217,863[/TD2] [TD2]154,000[/TD2][TD2]192,500[/TD2][TD2]192,500[/TD2][TD2]157,500[/TD2] [TD2]10,999[/TD2][TD2]10,999[/TD2][TD2]11,000[/TD2][TD2]11,000[/TD2] [TD2]375,000[/TD2][TD2]384,000[/TD2][TD2]365,750[/TD2][TD2]369,969[/TD2] [TD2] 5,649,232 [/TD2][TD2] 5,285,288 [/TD2][TD2] 3,703,697 [/TD2][TD2] 2,952,225 [/TD2] [TD2] 1,459,191 [/TD2][TD2] 1,297,805 [/TD2][TD2] 549,475 [/TD2][TD2] 456,526 [/TD2] [TD2]28.5%[/TD2][TD2]28.6%[/TD2][TD2]26.0%[/TD2][TD2]27.8%[/TD2] [TD2]26.0%[/TD2][TD2]26.0%[/TD2][TD2]26.0%[/TD2][TD2]26.1%[/TD2] [TD2]18.5%[/TD2][TD2]17.0%[/TD2][TD2]0.0%[/TD2][TD2]-20.3%[/TD2] [TD2]21.9%[/TD2][TD2]21.3%[/TD2][TD2]16.5%[/TD2][TD2]18.2%[/TD2] [TD2]0.0%[/TD2][TD2]-5.0%[/TD2][TD2]-10.0%[/TD2][TD2]-17.7%[/TD2] [TD2]30.0%[/TD2][TD2]30.0%[/TD2][TD2]30.0%[/TD2][TD2]30.0%[/TD2] [TD2]-100.0%[/TD2][TD2]-100.0%[/TD2][TD2]-100.0%[/TD2][TD2]-100.0%[/TD2] [TD2]-25.0%[/TD2][TD2]-28.0%[/TD2][TD2]-33.0%[/TD2][TD2]-40.5%[/TD2] [TD2]335,000[/TD2][TD2]335,000[/TD2][TD2]335,000[/TD2][TD2]332,096[/TD2] [TD2]545,000[/TD2][TD2]545,000[/TD2][TD2]545,000[/TD2][TD2]546,404[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]35,000[/TD2][TD2]35,000[/TD2][TD2]35,000[/TD2][TD2]35,000[/TD2] [TD2]140,000[/TD2][TD2]140,000[/TD2][TD2]140,000[/TD2][TD2]140,000[/TD2] [TD2] 1,090,000 [/TD2][TD2] 1,080,000 [/TD2][TD2] 1,070,000 [/TD2][TD2] 1,053,500 [/TD2] [TD2] 369,191 [/TD2][TD2] 217,805 [/TD2][TD2] -520,525 [/TD2][TD2] -596,974 [/TD2] [TD2]6,000[/TD2][TD2]6,000[/TD2][TD2]6,000[/TD2][TD2]5,214[/TD2] [TD2]-107,000[/TD2][TD2]-107,000[/TD2][TD2]-107,000[/TD2][TD2]-102,546[/TD2] [TD2]-53,000[/TD2][TD2]-53,000[/TD2][TD2]-53,000[/TD2][TD2]-47,000[/TD2] [TD2]-12,000[/TD2][TD2]-12,000[/TD2][TD2]-12,000[/TD2][TD2]-37,716[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2] 203,191 [/TD2][TD2] 51,805 [/TD2][TD2] -686,525 [/TD2][TD2] -779,022 [/TD2] [TD2]19,999[/TD2][TD2]19,999[/TD2][TD2]20,000[/TD2][TD2]5,605[/TD2] [TD2] 183,192 [/TD2][TD2] 31,806 [/TD2][TD2] -706,525 [/TD2][TD2] -784,627 [/TD2] [TD2]-50,001[/TD2][TD2]-50,001[/TD2][TD2]-50,000[/TD2][TD2]-75,076[/TD2] [TD2] 233,193 [/TD2][TD2] 81,807 [/TD2][TD2] -656,525 [/TD2][TD2] -709,551 [/TD2] [TD2]170,000[/TD2][TD2]170,000[/TD2][TD2]170,000[/TD2][TD2]169,146[/TD2] [TD2]170,000[/TD2][TD2]170,000[/TD2][TD2]170,000[/TD2][TD2]169,146[/TD2] [TD2] 1.37 [/TD2][TD2] 0.48 [/TD2][TD2] -3.86 [/TD2][TD2] -4.19 [/TD2] [TD2]233,193[/TD2][TD2]81,807[/TD2][TD2]-656,525[/TD2][TD2]-709,551[/TD2] [TD2]140,000[/TD2][TD2]140,000[/TD2][TD2]140,000[/TD2][TD2]141,639[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]373,193[/TD2][TD2]221,807[/TD2][TD2]-516,525[/TD2][TD2]-567,912[/TD2] [TD2] 2.20 [/TD2][TD2] 1.30 [/TD2][TD2] -3.04 [/TD2][TD2] -3.36 [/TD2] [TD2]2,176,308[/TD2][TD2]1,688,360[/TD2][TD2]1,881,948[/TD2][TD2]2,665,673[/TD2] [TD2]150,000[/TD2][TD2]130,000[/TD2][TD2]100,000[/TD2][TD2]120,194[/TD2] [TD2]1,168,508[/TD2][TD2]1,082,152[/TD2][TD2]745,762[/TD2][TD2]652,848[/TD2] [TD2]4,457,476[/TD2][TD2]4,633,677[/TD2][TD2]3,490,608[/TD2][TD2]2,565,826[/TD2] [TD2]342,700[/TD2][TD2]339,358[/TD2][TD2]336,017[/TD2][TD2]379,379[/TD2] [TD2] 8,294,992 [/TD2][TD2] 7,873,547 [/TD2][TD2] 6,554,334 [/TD2][TD2] 6,383,920 [/TD2] [TD2]2,519,505[/TD2][TD2]2,439,234[/TD2][TD2]2,359,070[/TD2][TD2]2,315,124[/TD2] [TD2]6,345,887[/TD2][TD2]6,349,381[/TD2][TD2]6,352,910[/TD2][TD2]6,346,374[/TD2] [TD2]12,420,083[/TD2][TD2]11,695,516[/TD2][TD2]11,061,438[/TD2][TD2]10,519,226[/TD2] [TD2]361,502[/TD2][TD2]361,502[/TD2][TD2]361,502[/TD2][TD2]346,428[/TD2] [TD2]60,237[/TD2][TD2]60,237[/TD2][TD2]60,237[/TD2][TD2]61,284[/TD2] [TD2]428,754[/TD2][TD2]435,754[/TD2][TD2]442,754[/TD2][TD2]449,754[/TD2] [TD2]440,000[/TD2][TD2]440,000[/TD2][TD2]440,000[/TD2][TD2]433,841[/TD2] [TD2]273,123[/TD2][TD2]273,123[/TD2][TD2]273,123[/TD2][TD2]415,478[/TD2] [TD2] 31,144,084 [/TD2][TD2] 29,928,294 [/TD2][TD2] 27,905,368 [/TD2][TD2] 27,271,429 [/TD2] [TD2]4,767,023[/TD2][TD2]4,691,598[/TD2][TD2]3,429,725[/TD2][TD2]2,603,498[/TD2] [TD2]1,972,157[/TD2][TD2]1,900,064[/TD2][TD2]1,828,971[/TD2][TD2]1,898,431[/TD2] [TD2]604,681[/TD2][TD2]585,416[/TD2][TD2]542,586[/TD2][TD2]536,465[/TD2] [TD2]600,000[/TD2][TD2]600,000[/TD2][TD2]600,000[/TD2][TD2]629,112[/TD2] [TD2]965,000[/TD2][TD2]965,000[/TD2][TD2]965,000[/TD2][TD2]984,823[/TD2] [TD2]1,500,000[/TD2][TD2]1,500,000[/TD2][TD2]1,800,000[/TD2][TD2]1,915,530[/TD2] [TD2]100,000[/TD2][TD2]100,000[/TD2][TD2]100,000[/TD2][TD2]82,500[/TD2] [TD2] 10,508,862 [/TD2][TD2] 10,342,078 [/TD2][TD2] 9,266,282 [/TD2][TD2] 8,650,359 [/TD2] [TD2]9,415,700[/TD2][TD2]9,415,700[/TD2][TD2]9,415,700[/TD2][TD2]8,761,070[/TD2] [TD2]100[/TD2][TD2]100[/TD2][TD2]100[/TD2][TD2]100[/TD2] [TD2]2,519[/TD2][TD2]2,519[/TD2][TD2]2,519[/TD2][TD2]2,556[/TD2] [TD2]881,827[/TD2][TD2]853,732[/TD2][TD2]825,674[/TD2][TD2]818,250[/TD2] [TD2]650,000[/TD2][TD2]670,000[/TD2][TD2]700,000[/TD2][TD2]756,800[/TD2] [TD2]2,806,088[/TD2][TD2]2,726,344[/TD2][TD2]2,647,600[/TD2][TD2]2,561,886[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2] 24,265,095 [/TD2][TD2] 24,010,473 [/TD2][TD2] 22,857,875 [/TD2][TD2] 21,551,021 [/TD2] [TD2]402,943[/TD2][TD2]402,943[/TD2][TD2]402,943[/TD2][TD2]405,835[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]2[/TD2] [TD2]900,000[/TD2][TD2]900,000[/TD2][TD2]900,000[/TD2][TD2]863,876[/TD2] [TD2] 5,576,045 [/TD2][TD2] 4,614,878 [/TD2][TD2] 3,744,550 [/TD2][TD2] 4,450,695 [/TD2] [TD2]183,192[/TD2][TD2]31,806[/TD2][TD2]-706,525[/TD2][TD2]-784,627[/TD2] [TD2]484,383[/TD2][TD2]456,125[/TD2][TD2]442,458[/TD2][TD2]416,233[/TD2] [TD2]140,000[/TD2][TD2]140,000[/TD2][TD2]140,000[/TD2][TD2]141,639[/TD2] [TD2]35,000[/TD2][TD2]35,000[/TD2][TD2]35,000[/TD2][TD2]39,345[/TD2] [TD2]46,337[/TD2][TD2]34,906[/TD2][TD2]25,658[/TD2][TD2]18,546[/TD2] [TD2]45,000[/TD2][TD2]45,000[/TD2][TD2]45,000[/TD2][TD2]52,237[/TD2] [TD2]25,000[/TD2][TD2]25,000[/TD2][TD2]25,000[/TD2][TD2]47,661[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]-3,984[/TD2] [TD2]-86,356[/TD2][TD2]-336,391[/TD2][TD2]-92,914[/TD2][TD2]-169,142[/TD2] [TD2]95,930[/TD2][TD2]-1,223,234[/TD2][TD2]-968,727[/TD2][TD2]-419,277[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]-50,001[/TD2] [TD2]-15,000[/TD2][TD2]-15,000[/TD2][TD2]-15,000[/TD2][TD2]-57,583[/TD2] [TD2]147,519[/TD2][TD2]1,332,966[/TD2][TD2]756,767[/TD2][TD2]317,983[/TD2] [TD2]75,000[/TD2][TD2]65,000[/TD2][TD2]50,000[/TD2][TD2]45,795[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]-19,823[/TD2][TD2]67,359[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]-60,560[/TD2] [TD2] 1,176,005 [/TD2][TD2] 591,179 [/TD2][TD2] -283,107 [/TD2][TD2] -398,376 [/TD2] [TD2]-900,000[/TD2][TD2]-800,000[/TD2][TD2]-700,000[/TD2][TD2]-655,662[/TD2] [TD2]-60,000[/TD2][TD2]-60,000[/TD2][TD2]-70,000[/TD2][TD2]-72,975[/TD2] [TD2] -960,000 [/TD2][TD2] -860,000 [/TD2][TD2] -770,000 [/TD2][TD2] -728,637 [/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2] [TD2]100,000[/TD2][TD2]200,000[/TD2][TD2]100,000[/TD2][TD2]1,775,481[/TD2] [TD2]0[/TD2][TD2]-300,000[/TD2][TD2]0[/TD2][TD2]-1,389,388[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]-17,500[/TD2] [TD2]100,000[/TD2][TD2]100,000[/TD2][TD2]100,000[/TD2][TD2]-87,092[/TD2] [TD2]75,000[/TD2][TD2]75,000[/TD2][TD2]75,000[/TD2][TD2]94,018[/TD2] [TD2]-30,000[/TD2][TD2]-30,000[/TD2][TD2]-30,000[/TD2][TD2]-18,787[/TD2] [TD2]-12,000[/TD2][TD2]-12,000[/TD2][TD2]-12,000[/TD2][TD2]-2,913[/TD2] [TD2]75,000[/TD2][TD2]75,000[/TD2][TD2]75,000[/TD2][TD2]73,704[/TD2] [TD2]-50,000[/TD2][TD2]-50,000[/TD2][TD2]-50,000[/TD2][TD2]-52,942[/TD2] [TD2]0[/TD2][TD2]0[/TD2][TD2]0[/TD2][TD2]-2,921[/TD2] [TD2] 258,000 [/TD2][TD2] 58,000 [/TD2][TD2] 258,000 [/TD2][TD2] 371,660 [/TD2] [TD2]13,944[/TD2][TD2]17,232[/TD2][TD2]11,382[/TD2][TD2]10,102[/TD2] [TD2] 487,949 [/TD2][TD2] -193,589 [/TD2][TD2] -783,725 [/TD2][TD2] -745,251 [/TD2] [TD2] 1,688,360 [/TD2][TD2] 1,881,948 [/TD2][TD2] 2,665,673 [/TD2][TD2] 3,367,914 [/TD2] [TD2] 2,176,308 [/TD2][TD2] 1,688,360 [/TD2][TD2] 1,881,948 [/TD2][TD2] 2,665,673 [/TD2]

 

[neroden]

Care to take a stab at the management-guided increase in the storage business? That is the only thing which jumped out at me.

 

[mershaw2001]

Thank you for posting this, it's very helpful to many of us. When Elon talks about cutting out contractors and getting authorization on anything over 1 million dollars, what aspect of the model is that supposed to reduce? Total auto cost of revenue, tsla SG&A, or something else?

 

[generalenthu]

Thanks @luvb2b. Can see the amount of effort that went it into this. Couldn't have been easy.

One of the biggest variable is of course the model 3 GM. I constructed a separate model here with sources for some of the assumptions. Feel free to play with the numbers or point out where it could be improved.

 

[luvb2b]

Care to take a stab at the management-guided increase in the storage business? That is the only thing which jumped out at me.

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.

Thank you for posting this, it's very helpful to many of us. When Elon talks about cutting out contractors and getting authorization on anything over 1 million dollars, what aspect of the model is that supposed to reduce? Total auto cost of revenue, tsla SG&A, or something else?

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.

 

[schonelucht]

With 23k model 3 deliveries and 22k S/X the 200k limit would already be breached in June. Assuming a very generous 5k deliveries in Canada you can have at most 15k total Model 3 deliveries. Tthere is no sign of Tesla rerouting Model S or Xes to countries elsewhere so those will soak up the usual 10k units per quarter. Since we are roughly 180k at the start of the quarter that’s what’s left over.

 

[brian45011]

Just starting to review:

Two quick possible refinements:

With the adoption of the new revenue recognition standard starting January 1, 2018, lease accounting generally applies only to vehicles directly leased by us without using bank partners. As a result of this change, only 8% of our deliveries in Q1 were subject to lease accounting. We are not expecting to offer a leasing option on Model 3 this year as we continue to focus on cash sales.

Revenue by source
The following table disaggregates our revenue by major source (in thousands):
....
Automotive regulatory credits 80,329



​

 

[bonaire]

For storage to blossom like that, they just need to do that 1GWh single project along with a few others in Australia and USA sample projects at some utilities. They need to move cells as the # of cars destined to be built is well short of their 500,000/yr by 2018 that was given as "guidance" in the past. The factory will be putting out cells fast and storage markets could absorb them if price is right. Governments are putting up more money for storage than EVs, it seems. CA, NY and Australia are favorites. CA will pay you a large sum for a powerwall installation versus buying an EV, for example. NY offers over $2/Watt for storage incentive. If Australia is similar, then the storage business is ripe for picking (as Australia's pricing market for MWh which pays fast-responders a high amount - higher than in the USA moderated markets). I see CapEx slowing, as do OpEx. Possible Solar City staff reduction too. I don't see GF1 being built-out further until after the Model 3 is well over 5000/wk every week at a sustainable, provable rate. This should include at least 1700 additional S+X per week. The 5000/wk Model 3 may not be the same rate as incoming reservations and so once the full backlog is reaching it's fulfillment, there needs to be color of the incoming order rates of all models. By the time Model 3 is nearing backlog 50% depletion, Model Y will be unveiled and deposits taken.

Key is Model 3 take-rate. Since S+X is not going to match 2017 total deliveries (it looks like) Model 3 is the make or brake (heh) situation. If the DWD and P models don't have serious service issues, they should be able to do 100k or more Model 3 in the year. If not, could be daunting.

 

[luvb2b]

With 23k model 3 deliveries and 22k S/X the 200k limit would already be breached in June. Assuming a very generous 5k deliveries in Canada you can have at most 15k total Model 3 deliveries. Tthere is no sign of Tesla rerouting Model S or Xes to countries elsewhere so those will soak up the usual 10k units per quarter. Since we are roughly 180k at the start of the quarter that’s what’s left over.

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.

Just starting to review:
Two quick possible refinements:

With the adoption of the new revenue recognition standard starting January 1, 2018, lease accounting generally applies only to vehicles directly leased by us without using bank partners. As a result of this change, only 8% of our deliveries in Q1 were subject to lease accounting. We are not expecting to offer a leasing option on Model 3 this year as we continue to focus on cash sales.

Revenue by source
The following table disaggregates our revenue by major source (in thousands):
....
Automotive regulatory credits 80,329

​

​

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.

 

[Reality]

Yes! Was thinking yesterday that im excited for your Q2 projections.


I kind of wish you didnt put out Q3 and Q4 yet though.

 

[Reality]

2 comments so far, i think we just have to structurally disagree on the Q2 gross margin of the model 3, and i think ur fudging a point or 2 on S and X margins, but its close enough I guess.

I will do a more thorough review later, no chance you share the excel version of this with the assumptions by chance? I dont blame you if you want to keep it.


My biggest beef though is this line:

From cash flow

accts pybl/accr liabs

[TD2]147,519[/TD2][TD2]1,332,966[/TD2][TD2]756,767[/TD2][TD2]317,983[/TD2]

I want to know how you think this is possible, the only way would be 3 ways


1) HUGE RVG increases which are not showing in your COGS above, so it cant be that

2) Enormous amounts of delayed payments, i mean 2B increase from the already unreal levels? No way suppliers let that fly imo.

3) Enormous amounts of deposits to not only offset declining deposits from deliveries but enough to inflate deposits another 500m? Doesnt seem reasonable.



great model!

 

[luvb2b]

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

Yes! Was thinking yesterday that im excited for your Q2 projections.
I kind of wish you didnt put out Q3 and Q4 yet though.

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.

2 comments so far, i think we just have to structurally disagree on the Q2 gross margin of the model 3, and i think ur fudging a point or 2 on S and X margins, but its close enough I guess.

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.

I will do a more thorough review later, no chance you share the excel version of this with the assumptions by chance? I dont blame you if you want to keep it.
My biggest beef though is this line:

From cash flow

accts pybl/accr liabs

[TD2]147,519[/TD2][TD2]1,332,966[/TD2][TD2]756,767[/TD2][TD2]317,983[/TD2]

I want to know how you think this is possible, the only way would be 3 ways
1) HUGE RVG increases which are not showing in your COGS above, so it cant be that
2) Enormous amounts of delayed payments, i mean 2B increase from the already unreal levels? No way suppliers let that fly imo.
3) Enormous amounts of deposits to not only offset declining deposits from deliveries but enough to inflate deposits another 500m? Doesnt seem reasonable.
great model!

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:

…

accts payable

accrued liabs+other

total cost of rev

dpo*

[TD2] luv q4-18 [/TD2][TD2] luv q3-18 [/TD2][TD2] luv q2-18 [/TD2][TD2] Mar-18 [/TD2] [TD2]4,767,023[/TD2][TD2]4,691,598[/TD2][TD2]3,637,155[/TD2][TD2]2,603,498[/TD2] [TD2]1,972,157[/TD2][TD2]1,900,064[/TD2][TD2]1,828,971[/TD2][TD2]1,898,431[/TD2] [TD2] 5,649,232 [/TD2][TD2] 5,285,288 [/TD2][TD2] 3,927,697 [/TD2][TD2] 2,952,225 [/TD2] [TD2] 77.00 [/TD2][TD2] 81.00 [/TD2][TD2] 84.50 [/TD2][TD2] 80.47 [/TD2]

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))

 

[bdy0627]

Awesome thread! Thanks to all of those contributing. These estimates and critical questions are really helpful.

 

[Reality]

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))

I get that you are using cost of revenue as a driver to the payables balance, and im glad to see it modeled in that way, but the current levels of DPO are likely not sustainable imo, i dont have a complex model built out like you do (but i do have a QoQ blance sheet runout that i use for more in depth stuff, the P&L is basic and not linked).

I will look over against and respond some more.


I love these threads! Seriously the best thread in the investor forum imo.

 

[Zhelko Dimic]

With 23k model 3 deliveries and 22k S/X the 200k limit would already be breached in June. Assuming a very generous 5k deliveries in Canada you can have at most 15k total Model 3 deliveries. Tthere is no sign of Tesla rerouting Model S or Xes to countries elsewhere so those will soak up the usual 10k units per quarter. Since we are roughly 180k at the start of the quarter that’s what’s left over.

Thanks Luv.
To add to @schonelucht, I expect at most 18-19K M3s in Q2.
Mostly because production so far has not been stelar. Couple of slowdowns and interruptions to production so far (the best I can tell). That 5 day stoppage seems to have been 7 days, etc... Focus seems to be on achieving good exit run-rate and not optimizing for how many cars are produced in Q2.
Further, I expect that Q2 turns into a bit of the dog's breakfast, any expense that can be thrown into it will be. It's a write-off quarter anyhow and cleans the slate for Q3 and Q4. The only thing Tesla will try to protect is cash at the end of the quarter, as otherwise that number can cause panic. Of course, this is all my speculation.
I also expect more S+X in transit as they are trying to get away from optimizing for a quarter. Which alligns with attempts to delay onset of 200K limit.

 

[luvb2b]

I get that you are using cost of revenue as a driver to the payables balance, and im glad to see it modeled in that way, but the current levels of DPO are likely not sustainable imo, i dont have a complex model built out like you do (but i do have a QoQ blance sheet runout that i use for more in depth stuff, the P&L is basic and not linked).

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!

With 23k model 3 deliveries and 22k S/X the 200k limit would already be breached in June. Assuming a very generous 5k deliveries in Canada you can have at most 15k total Model 3 deliveries. Tthere is no sign of Tesla rerouting Model S or Xes to countries elsewhere so those will soak up the usual 10k units per quarter. Since we are roughly 180k at the start of the quarter that’s what’s left over.

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.

 

[Zhelko Dimic]

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.

I don't believe 60% conversion rate is correct. 60% is correct for people that have bothered to report. Most people that are waiting for something before ordering (me included), wouldn't bothered to report to Troy. I wouldn't be surprised if real conversion rate of invites is only 20-30%
Having said that, Ontario is exception and may surprise positively. There are elections coming and serious chance of $CAD14K credit going away. I have 0% reason to convert at this point (1 year new Model S), yet I've given it some thought, to the point of actually advertising my Model S.
So, one + and one -, your numbers may turn out to be correct

 

[Reality]

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. 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!

Ya i get it, you are making reasonable assumptions on known information, but i also think you hit the nail on the head that you are looking a company that has severe liquidity concerns. Suppliers love sales but you know what they like more than sales? Collecting on sales.


I think this might be a big reason for Elon's recent lunatic rants about the media. Before he would have a somewhat weak balance sheet, but he could easily explain away any risk with his growth and visions to purchasing managers.


Nowadays, there is so much information about Tesla and their missed deadlines, questionable build quality, deteriorating financials that they are likely being pressed much harder by their vendors when going on credit. Basically, as you said, Tesla's credit isnt very good right now.

Musk, as with most narcissists, believes this has to be somebody elses fault. So when hit with all these new demands that dont go away with a cool PR video of tunnels that will neer be built, and he sees the same comments being made by journalists, makes the connection that one must cause the other. Guy is a total loon.


I think your model is very reasonable, 2 questions:

How sustainable do you think that ASP is for the model 3, AND

How much risk do you see in those Q3/Q4 S and X numbers?

 

[luvb2b]

Ya i get it, you are making reasonable assumptions on known information, but i also think you hit the nail on the head that you are looking a company that has severe liquidity concerns. Suppliers love sales but you know what they like more than sales? Collecting on sales.
...
I think your model is very reasonable, 2 questions:

How sustainable do you think that ASP is for the model 3, AND

How much risk do you see in those Q3/Q4 S and X numbers?

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.

 

[Reality]

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 didnt mean to call you looney at all, just Musk. I think we both can agree he is probably on thin ice right now with both vendors and creditors


ONce financials take over its a different story, but then again thats why debating this is fun!