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-18luv q3-18luv q2-18Mar-18s deliveries15,00015,00011,50011,738x deliveries13,00012,00010,50010,077s+x deliveries28,00027,00022,00021,8153 deliveries60,00050,00023,0008,182ls veh % total0.110.110.110.11avg price s+x106.00106.00106.00105.76avg price model 358.0060.0056.0056.00revenueauto sales ex 32,641,5202,547,1802,075,4802,053,375auto sales mod 33,480,0003,000,0001,288,000458,192auto leasing186,903180,913175,192173,4361 time autopilot0000zev credits100,000100,000050,314total auto6,408,4235,828,0933,538,6722,735,317energy storage180,000180,000164,500185,022solarcity220,000275,000275,000225,000grohmann0000services/other300,000300,000275,000263,412total revenue7,108,4236,583,0934,253,1723,408,751cost of revenueauto sales ex 31,973,4151,903,0041,553,3741,540,031auto sales mod 32,836,2002,490,0001,288,000551,366auto leasing119,618115,784112,123104,496total auto4,929,2334,508,7892,953,4972,195,893energy storage180,000189,000180,950217,863solarcity154,000192,500192,500157,500grohmann10,99910,99911,00011,000services & other375,000384,000365,750369,969total cost of rev5,649,2325,285,2883,703,6972,952,225gross profit1,459,1911,297,805549,475456,526auto gaap ex 3 gm28.5%28.6%26.0%27.8%auto-zev ex 3 gm26.0%26.0%26.0%26.1%model 3 gm18.5%17.0%0.0%-20.3%auto-zev incl 3 gm21.9%21.3%16.5%18.2%storage gm0.0%-5.0%-10.0%-17.7%scty gm30.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%opextesla r&d335,000335,000335,000332,096tesla sg&a545,000545,000545,000546,4041time acq cost0000solarcity r&d35,00035,00035,00035,000solarcity sg&a140,000140,000140,000140,000total opex1,090,0001,080,0001,070,0001,053,500op income369,191217,805-520,525-596,974interest inc6,0006,0006,0005,214interest exp-107,000-107,000-107,000-102,546scty interest-53,000-53,000-53,000-47,000other income exp-12,000-12,000-12,000-37,7161time scty gain0000pretax income203,19151,805-686,525-779,022income tax19,99919,99920,0005,605net income183,19231,806-706,525-784,627non-cont int.-50,001-50,001-50,000-75,076net inc to common233,19381,807-656,525-709,551basic shares170,000170,000170,000169,146diluted shares170,000170,000170,000169,146diluted gaap eps1.370.48-3.86-4.19gaap net income233,19381,807-656,525-709,551 - stock based comp140,000140,000140,000141,639 - one time scty0000non-gaap net income373,193221,807-516,525-567,912non-gaap diluted eps2.201.30-3.04-3.36balance sheetcurrent assets cash & eq.2,176,3081,688,3601,881,9482,665,673 restricted cash150,000130,000100,000120,194 accts rcvbl1,168,5081,082,152745,762652,848 inventory4,457,4764,633,6773,490,6082,565,826 prepaids+other342,700339,358336,017379,379total current assets8,294,9927,873,5476,554,3346,383,920op lease vehicles2,519,5052,439,2342,359,0702,315,124solar energy sys6,345,8876,349,3816,352,9106,346,374pp&e12,420,08311,695,51611,061,43810,519,226intangible assets361,502361,502361,502346,428goodwill60,23760,23760,23761,284mypower rcvbls428,754435,754442,754449,754restricted cash440,000440,000440,000433,841other assets273,123273,123273,123415,478total assets31,144,08429,928,29427,905,36827,271,429current liabiliites accts payable4,767,0234,691,5983,429,7252,603,498 accrued liabs+other1,972,1571,900,0641,828,9711,898,431 deferred revenue604,681585,416542,586536,465 resale value guar600,000600,000600,000629,112 cust deposits965,000965,000965,000984,823 curr debt+leases1,500,0001,500,0001,800,0001,915,530 curr solar bonds100,000100,000100,00082,500total current liabs10,508,86210,342,0789,266,2828,650,359lt debt+leases9,415,7009,415,7009,415,7008,761,070solar bonds100100100100rel party conv debt2,5192,5192,5192,556deferred revenue881,827853,732825,674818,250resale value guar650,000670,000700,000756,800other lt liabilities2,806,0882,726,3442,647,6002,561,886comm stk warrants0000capital lease oblg0000total liabilities24,265,09524,010,47322,857,87521,551,021commits/contingsrdmbl ncis in subs402,943402,943402,943405,835conv senior notes0002nci in subsidiaries900,000900,000900,000863,876common equity5,576,0454,614,8783,744,5504,450,695cash flow statementcash flows from opsnet loss183,19231,806-706,525-784,627 dep/amortization484,383456,125442,458416,233 stock-based comp140,000140,000140,000141,639 am of debt discount35,00035,00035,00039,345 inv write-down46,33734,90625,65818,546 loss on disposals45,00045,00045,00052,237 forex loss (gain)25,00025,00025,00047,661 loss on acq scty0000 non-cash int/other000-3,984 chgs in op as/lb accts rcbl-86,356-336,391-92,914-169,142 inv / op leases95,930-1,223,234-968,727-419,277 prepaids/other ca000-50,001 mypower rcvbls + other-15,000-15,000-15,000-57,583 accts pybl/accr liabs147,5191,332,966756,767317,983 deferred revenue75,00065,00050,00045,795 customer deposits00-19,82367,359 other lt liabs000-60,560net cash from ops1,176,005591,179-283,107-398,376cash flows from invpp&e purchases-900,000-800,000-700,000-655,662purchase solar sys-60,000-60,000-70,000-72,975net cash from inv-960,000-860,000-770,000-728,637cash flows from finstock issued0000debt issued100,000200,000100,0001,775,481debt repayments0-300,0000-1,389,388rel pty solar repaids000-17,500coll lease borrowing100,000100,000100,000-87,092stock option excrs75,00075,00075,00094,018capital lease paids-30,000-30,000-30,000-18,787stock+debt issue cost-12,000-12,000-12,000-2,913investment by nci in subs75,00075,00075,00073,704dist to nci in subs-50,000-50,000-50,000-52,942buyouts of nci in subs000-2,921net cash from fin258,00058,000258,000371,660forex effect13,94417,23211,38210,102net change in cash487,949-193,589-783,725-745,251cash & eq start1,688,3601,881,9482,665,6733,367,914cash & eq end2,176,3081,688,3601,881,9482,665,673
Care to take a stab at the management-guided increase in the storage business? That is the only thing which jumped out at me.
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?
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.
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.
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.
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
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.
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.
Yes! Was thinking yesterday that im excited for your Q2 projections. I kind of wish you didnt put out Q3 and Q4 yet though.
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 liabs147,5191,332,966756,767317,983I 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!
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-18luv q3-18luv q2-18Mar-18 accts payable4,767,0234,691,5983,637,1552,603,498 accrued liabs+other1,972,1571,900,0641,828,9711,898,431total cost of rev5,649,2325,285,2883,927,6972,952,225dpo*77.0081.0084.5080.47you 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))
Awesome thread! Thanks to all of those contributing. These estimates and critical questions are really helpful.
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.
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.
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.
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
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?
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.
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!