Pitstop spreadsheet

Andrea_Moda_Rules

Podium Finisher
Wowzers looks like a quite a bit of info there. Are you trying to get a job has an F1 statergist??

Take it the yellow cells is where you change the input. It would be intresting to come up with some theoretical stratergies though with it lol
 

jez101

Bookies drive nice cars because of people like me
Contributor
Are you trying to get a job has an F1 statergist??

I can think of worse outcomes ;)

I always wanted to work in F1 when I was a kid (30+ years ago), but spreadsheets are more of a hobby than anything else. Sad, isn't it :rolleyes:. One of my workmates once termed me an excel junkie :1st:
 

Sarinaide

Banned
I looked at it briefly and will say that I don't really understand it....I am a lawyer there is a reason I didn't do economics........is there a simpleton explaination for the numerically challenged?
 

jez101

Bookies drive nice cars because of people like me
Contributor
Concentrate on the yellow cells if you are struggling to follow the formulas and look at the chart for the results. It is pretty basic but it gives you an idea I think of what teams might do and why they might do it. If you can get the assumptions right!
 
It's a good basic picture of the cost of the tyres assuming only a few variables
Imagine the granular level the teams can achieve using their Crays and more inputs such as track condition change and incremental weather changes.

And still mess up
 

Galahad

Not a Moderator
Valued Member
Nice work jez! You certainly know your formulae. I've tried to do something similar on several occasions down the years, but this is far better.

I suppose the obvious extensions would be to include a non-linear tyre wear model. Does it help not having to factor in fuel loads?:)
 

jez101

Bookies drive nice cars because of people like me
Contributor
Cheers folks, glad you like it. As I say, it's pretty basic, leaving aside as it does all sorts of variables. I think one thing it has reminded me is that you only have 3 sets of option tyres - if you want to stop more than 3 times, you are going to see more than one stint on the prime tyre.

This might be ok if both degrade really quickly (eg China), but otherwise you are not really giving yourself maximum time on the faster tyre.

Imagine the granular level the teams can achieve using their Crays and more inputs such as track condition change and incremental weather changes.

I wish I could get a live data feed from the FIA, honestly, I haven't asked...:whistle:

And still mess up

That'll be the human factor and what makes it great entertainment LOL

I suppose the obvious extensions would be to include a non-linear tyre wear model. Does it help not having to factor in fuel loads?:)

If you can capture non linear tyre wear in a formula, I could add it in, but personally I don't understand the shape of the curve. I don't think it is exponential, but adding a cliff could esily be incorporated.

Fuel loads are easily ignored from the timing because you are just doing relative time and yes, it is a cheat because it is probably true that a lighter car degrades the tyre at a slower rate than a heavy car. Again, I wouldn't be sure how to define that in a formula... Any ideas?
 

sushifiesta

Champion Elect
Contributor
Nice effort, I have a secret habbit of making massive spreadsheets like this on occasion as well lol

I'm having a look at some lap times of Vettel, Alonso and Button at Monaco to see if I can notice a pattern in the degradation. I'm struggling to find a comprehensive list of times from any other races though, there must be a web-site that has them somewhere?

It's hard to pick out anything really because there are so many variables during the race, e.g. backmarkers, yellow flags, overtaking etc. etc. etc. In fact it might be better to use times from long stints done in practice sessions.
 

f1fansp

Points Scorer
I'm having a look at some lap times of Vettel, Alonso and Button at Monaco to see if I can notice a pattern in the degradation. I'm struggling to find a comprehensive list of times from any other races though, there must be a web-site that has them somewhere?

They do get posted on the FIA website after each session and after the race, but get wiped at some point, definitely before the next race.

The link to the FIA timings is below, but empty at present:
http://www.fia.com/en-GB/mediacentre/f1_media/Pages/timing.aspx

The archive on their website is only for registered users. As you say, there must be another website out there somewhere, but I haven't found it.

I've downloaded most of the 2011 files in PDF format. I'm not sure how easy it is to upload such files using the link below, but I can try later. Its a bit of a pain transposing from PDF -> MS Excel, but for comparing a few drivers, its reasonably easy using Alt+Left click.
 

sushifiesta

Champion Elect
Contributor
Well I've spent quite a lot of time looking at the Monaco times this afternoon and not really found anything conclusive.

After loking at the lap times for Alonso, Hamilton and Vettel and fiddling with averages, removing dodgy lap times etc. etc. I've come up with a couple of graphs that at least seem to show what was going on but there's no nice pattern that emerges. I've stuck the two graphs below anyway so you can have a look. The "gap" I'm using is basically the difference to the first lap time in the stint - a negative gap means the lap time id quicker than at the start, and a positive gap means the lap time is slower than the start of the stint.
supersoft stints.PNG
soft stints.PNG


The supersoft tyre graph gives the sort of picture you might expect to see. Lap times are fairly consistent or improving for the first few laps and then beginning to degrade fairly linearly, typically 2 or 3 tenths a lap. The soft tyre graph I can't really explain. The first 15 laps seem to mirror the supersoft graph but then they seem to get a new lease of life before slowly degrading linearly again.

There are all sorts of factors that could be influencing this picture and Monaco is a bit of an anomaly as far as the F1 calendar goes so a lot more races should really be considered, but maybe someone can extract something useful from this.....
 

jez101

Bookies drive nice cars because of people like me
Contributor
Did you adjust for fuel coming out making the car faster? I haven't done this level of detail but with the naked eye, it looked like the fuel weight saving was negated by the tyre degredation (or so) during the FP stints at least.
 

sushifiesta

Champion Elect
Contributor
I just used lap times so these gaps include the effect of fuel being burned off, which probably explains why the first few laps either stay around zero or are negative for both tyres. It doesn't take into account that their might be different degradation rates for stints at different points in the race with different fuel loads etc. though.
 

Brogan

🦶 Leg end
Staff Member
Nice work guys, just the kind of stuff we nerds on here like :D

If anyone needs them, I have all the FIA documents for this year.
 

jez101

Bookies drive nice cars because of people like me
Contributor
The James Allen UBS report has Monaco consuming 1.55kg fuel per lap and 10 kg costing 0.28s / lap

I would make the cost per lap as 0.28 / (10/1.55) = 0.0434 secs / lap, or put a way that is easier to understand, you would expect a car to go 1.302s faster on the 30th lap than they would on the first.

Can you bump each of these data points up by 0.0434 secs / lap to give us a more pure view of the tyres?
 

sushifiesta

Champion Elect
Contributor
Ok, I've put that in:
supersoft fuel adjusted.PNG

soft fuel adjusted.PNG
Note on the soft tyre graph I've now included all of Vettel's long stint which I didn't before because I can't average the times with Button/Alonso. So after around lap 30 the points are more sensitive to individual problems experienced by Vettel during the race.

I think this does make the picture a bit clearer, although I still don't understand the improvement in the soft times after the initial peak. Thinking back to the Bridgestones though the tyres used to go through a period of wear and then stabilise so maybe that's what's happening with the Pirellis also.

EDIT: Also, just to make clear, the laps I'm referring to are the number of laps in the stint not the laps in the race.
 

jez101

Bookies drive nice cars because of people like me
Contributor
Ok, I've put that in:

It looks like the option was degrading at around 0.1s / lap (1.4 secs slower after 14 laps), while the prime was losing about half that (2 secs down after 40).

It also looks like the performace from them both degrades much slower than this at first and is pretty consistent for 8 laps on option and 12 in Monaco. I wonder what it will be like in Canada this weekend? I might steal your method sushi and have a look at this in more detail next week.
 

sushifiesta

Champion Elect
Contributor
You can have a look at the spreadsheet I used if you like. It's a bit messy.... The 3 or 5 lap averages I'm using are to smooth out variations in lap times due to mistakes or backmarkers etc.

At some point later I'm going to try and post probably the most ridiculously geeky thing I've done ever :embarrassed:. I've tried using ideas from your spreadsheet to write some python code that will determine the optimum strategy based on the input variables you used...

I think I'm going to have to ban you from making more threads like this because I have spent so much time playing with it lol :dizzy:!
 

Attachments

  • monaco tyre analysis 2011.xlsx
    54.1 KB · Views: 99
Top Bottom