In my previous post I mentioned that we could create a race strategy using nothing but a spreadsheet software like Excel or Google Sheets. And I am well aware that the cynic in us will ask: can we really make something as complex as a race strategy using such simple tools and nothing more? Truth be told, for an engineer this is the bread and butter of what we do. We take a complex problem and simplify it enough using logical assumptions such that the problem can be solved using simple addition or multiplication. But these solutions come at a cost. It requires an understanding of the underlying assumptions. Without this understanding, we can be mistaken into thinking that the solution is valid for all conditions when in actuality it is only valid under certain conditions.
For today's piece we will attempt to create a 30 minute race simulation at Misano using Google Sheets which takes into account the effect of fuel load. I will go over how we would go about collecting the data required, the assumptions we are going to make, and how we would populate these data into our spreadsheet. In the next entry, we will expend on this first version by considering tire wear before moving onto adding pit stops into the mix. To create our race simulation spreadsheet, we are going to refer to this racing strategy guide from Mclaren Racing Team. I will use the same terms introduced in the guide. In case some things aren't clear please refer back to the guide or ask in the comment section for clarification.
Since we are dealing with a virtual race, we have a few benefits in terms of collecting data for our tool. Firstly, we can turn off tire wear and fuel consumption completely and remove all influences of tire wear and consumption of fuel in our data run. Secondly, we can run in Practice mode in AC:C to get the fastest lap time possible with only 1 lap worth of fuel. We could also use the AC:C's Hotlap mode to get this fastest lap value, but we will have 10 litres instead of the minimum amount. In Practice mode, we are able to change the fuel quantity to any value that we want. Lastly, we can, at a click of a button, add and remove fuel loads. Doing this gives us a starting point to evaluate our weight effect, E. But before we start collecting data, lets take a look at the assumptions we are going to make.
The first assumption is that the amount of fuel will be in a linear relationship towards our lap time. That is a fancy way of saying the more fuel we put in, the slower we will be. From the Mclaren PDF, all we really need is this factor E. Once we have this value we can start doing our simulations. Another important assumption we are going to make is that we are going to only be using one engine mode. Adding multiple engine modes will only complicate things for now, but we will look at how this can be incorporated into our race sim worksheet at a later stage. The final assumption we are going to make is that there is a minimal effect on our fuel consumption against the ambient temperatures. This last assumption is important especially if you are racing in an event with high time factor (x6 and above) where crossing into the day time or night time will change the ambient air temp significantly and thus changing the fuel consumption of our vehicle. For now let's stick to only normal time to keep things simple.
With that out of the way, let's start with our data collection.
1st run - Fastest lap, low fuel
We could go about this in two ways: either taking the fastest lap achieved during the session as recorded in the game or taking the theoretical fastest lap that could have been achieved by taking the fastest time for each sector from the session. There is no right or wrong approach here as it is a personal preference. If you feel that you can achieve the theoretical fastest lap then go ahead and use it, but if you feel that the theoretical fastest lap is too hard, then use the fastest lap that you managed. Either way this fastest lap value will become our t0.
2nd run - Fastest lap, 60 litres of fuel
As before we want to take the fastest time achievable. For consistency I would use the same approach that we opted for in our first run to determine the fastest lap with 60 litres of fuel.
Other data to collect
Since in Practice mode with tire and fuel consumption turned off, we cannot measure our fuel consumption. We could use the value given to us in the setup menu, or run a couple of laps with the tire and fuel consumption option turned on. I recommend collecting this value after doing at least 5 consecutive consistent laps.
Pit stop entry/exit duration. To do this, note down the time required to travel the whole pit along with doing a stop an go e.g. coming to a full stop at a pit box and immediately driving away.
Pit stop duration. Since we are considering only refuelling for our pit stop, we can record the time needed to refuel our car with 1 litre of fuel and the time needed to fully refuel the car when we only have 1 litre remaining (we need this because if our car already have 20 litres and that a full tank holds 120 litres, even if we set the refuelling amount anywhere between 100 and 120 litres, the time will not change as the car cannot hold any more fuel)
We will not be using item 2 and 3 for today's entry, but we might as well collect it now. And with that we are ready to start creating our first race simulation tool. Here is a screenshot of my best times recorded in my practice mode for Misano at 38°C track temp and 24°C ambient
The fastest lap with low fuel was: 1:35.2 (best recorded in session)
The fastest lap with 60 litre fuel was: 1:36.4
Fuel consumption: 2.55 litres per lap
With these information, the calculated E is = 0.0207 s/litre of fuel
Our Fuel Laps Weight Effect,W is then = 0.0528 s/ lap lap of fuel
Tabulating all of these we get the following table (note that I've written out the formula in the second row in case you are interested). Also note that for our first simulation, I am using 60 litres of fuel as a start.
Now that all the data has been inputted, congratulations, you have just created your first race sim spreadsheet! Let's now try to understand what this sheet is trying to tell us.
The first important bit to understand is that Column B is showing to us the theoretical fastest lap that we could do given the fuel load that is within the car. If during our data run with low fuel, we decided to take the fastest possible time during the session and that we felt that doing such a time is relatively easy, we could then safely assume that achieving the times in Column B would also be relatively easy. However, if during the data run, we only managed to do 1 miracle fast lap or that we took the fastest sector times to be our fastest laps, it is quite likely that these time indicated in Column B might be difficult to achieve in race conditions. This can be improved by either changing the setup of the car so that we can be a bit more comfortable with the car or by practicing the track more.
Another thing to notice is that the first lap doesn't seem to make sense. It's expecting us to be on pace immediately even though we have to do a rolling start procedure as well as avoiding the carnage in turn 1. How could we do that and still be anywhere near the indicated lap time? Even an alien would be hard pressed to do such a thing. We can fix that by adding a Lap 1 Delay Factor. This delay factor is something that changes every race. For today's exercise, we will use 8 seconds as a starting point. You can change this value to anything that suits you and your racing environment e.g. league races with clean T1 you can go as low as 5 seconds; open lobbies on the other hand can vary wildly.
Next on our radar is to notice that our 1st lap pace should be quite similar to what we achieved as our fastest lap with 60 litres worth of fuel during our data run. It shouldn't be different as we are running this first lap at the same fuel loads. This is a good check to make to ensure that our calculations is not wrong.
One final thing to notice, is Column C where we have our elapse race time. Since we know our race is at least 30 minutes long, our final lap would be on lap 19 which is the first instance where we cross the start/finish line after the 30 minute mark. From here we can then read Column D which indicates our fuel remaining at the end of the race. Notice how it indicates a positive value? This means that we have put too much fuel at the start of the race and is something we can consider changing. If, however, at the end of the race, Column D indicates a negative value, this would mean that we do not have enough fuel. Here we must add more fuel or consider doing fuel saving measures during our stint.
Now that he have added our Lap 1 Delay Factor, and determined our fuel quantity needed, let's review our race sim table again:
Notice now a few things have changed. Firstly, if we remove the delay factor, our fastest lap would be a little bit faster compared to our fastest lap with 60 litre fuel. This should be expected as we are slightly lighter compared to our data collection run. Coming to the end of the race, we should be closer to our fastest time with low fuel compared to before. Since on average our race pace is faster compared to our first calculations, our total race duration has decreased from 30 mins 39 seconds to just 30 mins 6 seconds even with the added 8 seconds 1st Lap Delay factor.
However as a real race will have tire wear, we need to expend our race simulation further so that we can also take into account the effects of tire wear in our race simulation tool. We will have a look at how this can be achieved next week. I hope you find today's entry interesting and helpful. If you have any questions, comments or suggestions, please feel free to comment below. Thanks so much for your time!
If you would like download a copy of this spreadsheet, click on the link here.
Faiz
Comments