Near-future quarterly financial projections

[Fact Checking]

Pre-Raven S/X inventory is still a mystery to me. It stood at 13.7k on 3/31 (includes demos, loaners, etc.). T

How did you arrive at the 13.7k pre-Raven S/X inventory number?

To expand a bit more on my question to @Doggydogworld, here's the S+X production and delivery figures of the last 5 quarters:

S+X production:

  2018/Q1 = 24,728
  2018/Q2 = 24,761
  2018/Q3 = 26,903
  2018/Q4 = 25,161
  2019/Q1 = 14,150

S+X deliveries:

  2018/Q1 = 21,800
  2018/Q2 = 22,300
  2018/Q3 = 27,660
  2018/Q4 = 27,550
  2019/Q1 = 12,100

I.e. 115,703 units made, 111,410 units delivered - an increase in inventory levels over end of 2017 of +4,293 units - 2,050 units came in during Q1, which is the seasonal pattern as well, the international delivery pipelines are usually restarted in Q1, with an uptick in in-transit S+X units at the end of Q1.

(Also note that my delivery figures are likely too low by 100-200 units per quarter, as the figures come from the P&D reports, which under-count deliveries.)

I picked Q1'18 as a starting point, because while they obviously had some S+X inventory at the end of 2017 as well, they also had a blow-out quarter with 28,320 deliveries (!) on 22,140 of production - so I assume inventory levels were really low end of 2017 - I'd be surprised if they were over 2k globally at that point.

I.e. I don't see where your 13.7k pre-Raven inventory figure comes from - and it's difficult IMHO to estimate inventory levels from the finished goods reported figures.

I.e. end of Q1 they should have had at most around 5k-6k pre-Raven units in inventory - but maybe as low as 4k units if the record quarter in Q4'2017 reduced inventory levels particularly strongly.

TL;DR: your pre-Raven S+X inventory mystery is a mystery to me!!

 

[EVNow]

I picked Q1'18 as a starting point, because while they obviously had some S+X inventory at the end of 2017 as well, they also had a blow-out quarter with 28,320 deliveries (!) on 22,140 of production - so I assume inventory levels were really low end of 2017 - I'd be surprised if they were over 2k globally at that point.

I.e. I don't see where your 13.7k pre-Raven inventory figure comes from - and it's difficult IMHO to estimate inventory levels from the finished goods reported figures.

I.e. end of Q1 they should have had at most around 5k-6k pre-Raven units in inventory - but maybe as low as 4k units if the record quarter in Q4'2017 reduced inventory levels particularly strongly.

TL;DR: your pre-Raven S+X inventory mystery is a mystery to me!!

The inventory level of 13,997 is from Troy's spreadsheet that tracks S, X production & delivery from beginning - Q3 '12.

As you can see Q4 '16 had this 13k level of inventory. It went all the way to ~17k in Q2 '17, down to 9k in Q4 '17.

May be, a lot of these are loaners, floor models or scrapped ? This would be a good question for Q2 ER. Let us submit it and upvote it.

 

[EVNow]

I.e. while the Q4->Q1 drop was pretty much the worst-case scenario to drain cash, Q1->Q2 is pretty much the best case scenario. If we go by the ~75% figure then the net cash improvement could be 0.75*624=+$468m.

Payables is related to production and receivables to deliveries.

Between Q1 '19 and Q2 '19, we'll see production go up by ~15% and deliveries by ~40%. So, I expect the net effect on cash flow to be not big, if we go with 1:3 ratio. The actual changes will probably be much bigger - about $400M each. Esp., because the month-end is a weekend again, in Q2.

But the precise timing of the transactions has a big effect on how much, plus there was the curious case of slower than expected payables expansion in Q4 that I remarked on at the time in this thread: I believe Tesla intentionally paid suppliers faster than required in Q4, to help the Q1 payables effect.

In Q4 there was 8% production increase - so, we could have expected a payable increase of about 240M, but we only saw 94M. Or perhaps we should check more quarters to figure out the payables to production relationship better.

BTW, in Q4 receivables went down, even though deliveries increased by 8%. May be because Dec 31st was a Monday.

 

[Fact Checking]

Payables is related to production and receivables to deliveries.

Between Q1 '19 and Q2 '19, we'll see production go up by ~15% and deliveries by ~40%. So, I expect the net effect on cash flow to be not big. The actual changes will probably be much bigger - about $300M each.

Payables are related to the value of production, and the timing of it.

+15% unit count from Q1 to Q2 is not the right figure in this sense: both the S/X and 3 production delta in the final two months have to be considered. Q1 was mainly about a 40% drop in the value of production (revenue). If Q2 recovers +30% of that, then there will probably be a strong payables boost.

Edit:

See the modified DIO (Days Inventory Outstanding) model I posted in this thread previously, based on @luvb2b first approximation, which correctly predicted Q3 finished goods inventory:

Near-future quarterly financial projections

This can be used to estimate payables as well.

Receivables are harder (because the delay is shorter and more sensitive to small quarterly variances and the intensity of the push), and both cash flow figures are also discretionary to a certain extent: Tesla can choose to pay certain suppliers faster, to shift cash flows between quarters.

Also, this is not criticism of your work, I'm trying to contribute. The DIO parameters worked very well in Q3, and cash flow is notoriously hard to model.

 

[Fact Checking]

The inventory level of 13,997 is from Troy's spreadsheet that tracks S, X production & delivery from beginning - Q3 '12.

As you can see Q4 '16 had this 13k level of inventory. It went all the way to ~17k in Q2 '17, down to 9k in Q4 '17.

May be, a lot of these are loaners, floor models or scrapped ? This would be a good question for Q2 ER. Let us submit it and upvote it.

The discrepancy might be explained by writedowns they take, and by the handling of used cars:

• Does a sale of a returned leased or traded in Tesla count as a new delivery? I don't think so.
• Are the writedowns of value, which are significant sums, reflected in the P&D report? I don't think so, they only include new production and deliveries of newly produced cars.
• Are new leased units accounted as deliveries?

 

[EVNow]

The discrepancy might be explained by writedowns they take, and by the handling of used cars:

​

​

• Does a sale of a returned leased or traded in Tesla count as a new delivery? I don't think so. : No.
• Are the writedowns of value, which are significant sums, reflected in the P&D report? I don't think so, they only include new production and deliveries of newly produced cars. : No.
• Are new leased units accounted as deliveries? : Yes.

Basically, the difference between production and delivery needs to be accounted as
- Loaners/floor models
- Scrapped

Only other thing I can think of is - if loaners with large mileage are sold as used (because Tesla already took tax credit for them ?). Normally they would still sell as new, so buyers can get tax credits.

 

[Fact Checking]

Basically, the difference between production and delivery needs to be accounted as
- Loaners/floor models
- Scrapped

Only other thing I can think of is - if loaners with large mileage are sold as used (because Tesla already took tax credit for them ?). Normally they would still sell as new, so buyers can get tax credits.

So if loaners have so much mileage on them that they are sold as CPO units, I don't think they count as new, and CPO units are not eligible for the federal tax credit AFAIK.

If we take the last ~5 years and 20 quarters, just 250 such cars every quarter would account for a 5k discrepancy.

Do we know how many loaners they have? After some time the aging of the loaner fleet forces a writedown.

If the loaner fleet has 2 years average age, and there are 2k loaner units, then that's about 250 new units added, and 250 units written off into the CPO fleet, per quarter, to keep the age of the loaner fleet constant.

I.e. I think this could account for an about ~5k over-counting in Troy's inventory estimates.

 

[EVNow]

+15% unit count from Q1 to Q2 is not the right figure in this sense: both the S/X and 3 production delta in the final two months have to be considered. Q1 was mainly about a 40% drop in the value of production (revenue). If Q2 recovers +30% of that, then there will probably be a strong payables boost.

Yes, its related to value of production rather than quantity of production. But, value of production is not related to revenue, because of inventory changes. It is related to COGS - but for all of production (not just sales). So, it is related to COGS + change in inventory - or we can use ASP and margin to estimate value of production.

 

[Fact Checking]

But, value of production is not related to revenue, because of inventory changes.

Note that while this is true (I was simplifying), it is helping the payables argument I'm trying to make: the inventory plus revenue fluctuations were even larger, so payables expansion in Q4, contraction in Q1 and re-expansion in Q2 will be even higher than the first order approximation I gave.

I'm pretty sure payables expansion will be significantly higher than the $25m figure you estimated, and this is going to help gross cash position at the end of Q2: wouldn't be surprised if "cash and cash equivalents" stood beyond $5b.

 

[EVNow]

I'm pretty sure payables expansion will be significantly higher than the $25m figure you estimated, and this is going to help gross cash position at the end of Q2: wouldn't be surprised if "cash and cash equivalents" stood beyond $5b.

Yes, the payables will go up significantly - by about $400M. But, I also think receivables will go up by similar $400M amount - like it did in Q3 when it went up by $587M.

 

[Fact Checking]

Yes, the payables will go up significantly - by about $400M. But, I also think receivables will go up by similar $400M amount - like it did in Q3 when it went up by $587M.

I think there are three arguments that point against that:

• The historic seasonal pattern is in the opposite direction: in Q2'18 accounts receivable improved by about $83m, despite most of the Model 3 deliveries happening in June. With growing production accounts receivable usually go up, but this is not necessarily so.
• An additional factor is that Q1'19 was anomalous in that the final two weeks brought in half of the revenue, much of which was overseas - which is probably why accounts receivable still remained at the historically very high levels of $1,046m. Q2'19 probably won't be nearly as lopsided, plus it will be more weighted towards the U.S., allowing for customer payments to clear faster by end of quarter. (International payments will clear in a couple of days (T+3, sometimes longer) - in the U.S. it can clear immediately or overnight. Every day delay is ~$100m of cash flow that doesn't clear and gets added to accounts receivable.)
  
• Finally, Q1'19 ended on a Sunday, which is the worst possible timing for payments to clear - this adds up to 2.5 days of cash flow - and from the strongest days of the quarter. Q2'19 is going to end on a Sunday too, but it appears Tesla is intent on letting the wave crest in Europe and in China a couple of days before the end of the quarter, plus the cash delay effect is shorter in the U.S.

So unless there's like thousands of Ravens delivered in the final week, I'd expect accounts receivable to stay roughly constant in Q2 - which allows payable expansion to have its maximum effect.

 

[EVNow]

If the loaner fleet has 2 years average age, and there are 2k loaner units, then that's about 250 new units added, and 250 units written off into the CPO fleet, per quarter, to keep the age of the loaner fleet constant.

I.e. I think this could account for an about ~5k over-counting in Troy's inventory estimates.

Given the inventory level of 13k in Q1 '17 and ~10k in Q4 '17 - I think they only had about 4k S+X actual new inventory at the end of Q1. Rest (10k) are loaners/floor models - a major part already written / sold off.

If they have about 200 stores, the actual loaners/floor models are probably not more than 2k - definitely not more than 4k. So, we can say t the end of Q1, they had about 4k new inventory and 2k to 4k in floor models/loaners. 6k to 8k of the 14k "inventory" has already been sold off.

 

[Fact Checking]

Given the inventory level of 13k in Q1 '17 and ~10k in Q4 '17 - I think they only had about 4k S+X actual new inventory at the end of Q1. Rest (10k) are loaners/floor models - a major part already written / sold off.

If it's sold off it's not inventory anymore. Hence my questioning of the 13.7k figure.

 

[EVNow]

I think there are three arguments that point against that:

• The historic seasonal pattern is in the opposite direction: in Q2'18 accounts receivable improved by about $83m, despite most of the Model 3 deliveries happening in June. With growing production accounts receivable usually go up, but this is not necessarily so.
• An additional factor is that Q1'19 was anomalous in that the final two weeks brought in half of the revenue, much of which was overseas - which is probably why accounts receivable still remained at the historically very high levels of $1,046m. Q2'19 probably won't be nearly as lopsided, plus it will be more weighted towards the U.S., allowing for customer payments to clear faster by end of quarter. (International payments will clear in a couple of days (T+3, sometimes longer) - in the U.S. it can clear immediately or overnight. Every day delay is ~$100m of cash flow that doesn't clear and gets added to accounts receivable.)
  
• Finally, Q1'19 ended on a Sunday, which is the worst possible timing for payments to clear - this adds up to 2.5 days of cash flow - and from the strongest days of the quarter. Q2'19 is going to end on a Sunday too, but it appears Tesla is intent on letting the wave crest in Europe and in China a couple of days before the end of the quarter, plus the cash delay effect is shorter in the U.S.

So unless there's like thousands of Ravens delivered in the final week, I'd expect accounts receivable to stay roughly constant in Q2 - which allows payable expansion to have its maximum effect.

Thinking a bit more about it - the receivables is proportional to value of cars delivered in the final N number of days (not entire quarterly deliveries). That N depends on whether the quarter end is a weekend or not. Let us say it takes 2 days to clear i.e. Tesla will get the money on Wednesday for all the cars delivered on day Monday. So, the best case is last day of the month is Wed, Thu or Friday. In this case only cash from last 2 days of delivery will be in receivables. For other days, it will be more than 2 days, up to 4 days when the last day is a Sunday.

In terms of international orders - I don't know what they do - but usually they will have local bank accounts and the funds need to just get transferred to those local accounts. They will transfer to US at the time of their choosing. So, I expect the number of days to transfer to be similar to US in EU (or slightly better) and not sure about China.

So, AR for Q1 was $1,046M and Q4 was $949M - even though the Q1 deliveries were 2/3rd of Q4 deliveries. This is because Q1 would have had 4 days worth of sales not being cashed and Q4 would be only 3. There may have been some international effect too here.

So, I should probably bump up Receivables by about $100M to make it similar to the $1.15B as in Q4 - but a little less because the ASP is lower.

Now Payables are about 80% of the COGS associated with production in Q3, Q4 and Q1 '19 - calculated using known production numbers, margin and estimated ASP. So, in Q2 it should be about $3.4B for a production of 88k and assumed ASP, Margin or $175M more than Q1.

Net effect is about $75M more cash in Q2.

 

[EVNow]

If it's sold off it's not inventory anymore. Hence my questioning of the 13.7k figure.

That's right. We are saying real inventory at the end of Q1 is probably 4k + 4k = 8k instead of 14k.

 

[SPadival]

China numbers for May. Source not mentioned - so take it with a grain of salt

Sydney squinter (@spudheadcapital) Tweeted:
$TSLAQ China May numbers are in...
3: 1903
S: 104
X: 682

Sydney squinter on Twitter

 

[Spacemanspliff]

China numbers for May. Source not mentioned - so take it with a grain of salt

Sydney squinter (@spudheadcapital) Tweeted:
$TSLAQ China May numbers are in...
3: 1903
S: 104
X: 682

Sydney squinter on Twitter

If you see that citi bank report for May floating around twitter that would be helpful. Last time it came out on April 21st so maybe late this week early next.

 

[EVNow]

China numbers for May. Source not mentioned - so take it with a grain of salt

Sydney squinter (@spudheadcapital) Tweeted:
$TSLAQ China May numbers are in...
3: 1903
S: 104
X: 682

Sydney squinter on Twitter

Along with 2300 Model 3 in April that Citi put out, we are now at 5k+ for 2 months in China. So, looks like 8k for the quarter is likely. Along with 22k in EU + ROW would make it possible for Q2 numbers to be 90k+, if NA manages 60k+.

 

[Spacemanspliff]

That being said using Insideevs 24k model 3’s + 4,275 S/X and Fred’s quoted 2,512 so far in June we get 30,787.

33,000 NA vehicles-30,787= 2,213

Which could mean Canadian deliveries thus far are around 2,213.

2,512 for the first 5 days of June isn’t blistering given its a pace of 15k cars of S/X/3 over 30 days (granted I expect this to ramp up the last two weeks). He does state they have 10k deliveries scheduled and another 6k matched with VINS which is usually at most 2-3 weeks till delivery, but again this is S/X/3 so 18.5k of the trio won’t cut it. But say they hit 33k this month in the US of S/X/3:

S/X/3
63k US/Canada (I took out the double counting of 2,512 for this)
7k China
1 Japan car carrier with who knows how many cars
22k Europe

33k June would put them in the ballpark of 92k+ for Q2.

This seems like the sweet spot right now.

 

[Doggydogworld]

That's right. We are saying real inventory at the end of Q1 is probably 4k + 4k = 8k instead of 14k.

Troy's numbers (and mine, which are similar) track Finished Goods Inventory very well from quarter to quarter. This contradicts the theory that Tesla has secretly sold 5k+ aged loaners as used cars without counting them as deliveries.

If Tesla keeps a loaner that long they are supposed to transfer it to PP&E and depreciate it. In fact, they did this not too long ago for ~70m (from memory) of cars. That'd be ~1000 S/X, which we should subtract from Troy's numbers when doing balance sheet inventory calculations. Tesla will eventually sell almost all of these cars. I really don't know if they'll count those sales as deliveries.

Accounts receivable seem to represent the final 3-4 business days worth of sales in the quarter.

Accounts payable include capex payables, which can really throw off calculations during periods of high and/or rapidly changing capex. That shouldn't be a big problem right now.