Excel FIFO Calculator

After 6 years on the outskirts of Parramatta, our Boeing 777 simulator is moving to Brisbane airport. For our small fleet of 5 aircraft and 150 pilots, it’s a big change. As part of the process I had a look at the simulator slot timings, especially since we would be moving to 24×7 operation for a disparate workforce spread mostly up and down the East Coast. Coupled with this is the implication of Fly In And/Or Fly Out for single simulator sessions, which we do quite a lot of. I freely admit the project got away from me somewhat – the spreadsheet attached is the result.

FIFO Analysis Spreadsheet

The premise of the sheet was to analyse various simulator session start times and determine how many domestic sectors a start time for a particular session facilitates for either (a) fly in before the session; (b) fly out after the session; or (c) fly in and out for a single session in a day. Early sessions don’t facilitate fly in (unless they’re “very” early and you’re talking about flying in late the night before); similarly late (or early) finishing sessions don’t facilitate fly out, other than sessions that finished before the first departures of the day from BNE.

Broadly speaking, the east coast domestic network is an 18-hour-a-day pattern, with a dearth of flights between eleven pm and six am. Not that anyone really wants to be doing sim that late at night anyway.

Sim Slot Timings

A simulator session is a four hour block of time, preceded by a 90 minute briefing period and followed by a 30 minute debrief, which are fixed. In terms of FIFO, place before this a minimum period of transit between arriving in from a flight and signing on (basically airport transfer time) and follow it with another minimum transit period after the session to catch a flight home. Note that while there’s a minimum transit time between flight arrival and the briefing period, there’s also a reasonable maximum value – you can’t have crew arriving in to the airport 4 hours before they’re due to present for training.

Between each simulator session is a gap period (nominally 10 minutes) which gives the engineers time to service the simulator (software resets, oxygen mask replacement, etc.) and allows some handover time for the crews. These vary from 15 minutes, 10 minutes, to nothing at all in some operations. I settled on 10 minutes, which we are using at the moment, and a couple of the other BNE simulators are also using.

Our simulator will have the company of 5 other simulators in BNE and this introduces an additional constraint. Ideally, we want to avoid having the 10 minute break between sessions at the same time as occurs for the other simulators. While there’s more than one team of engineers on duty at any time, overlapping handovers places a strain on everyone involved. At least two of the existing simulators have the same slot schedule already.

It can be seen that the basic simulator slot time, with the brief/debrief and transit periods, the flight themselves and the EBA sign on/off periods outside the flights, sets up a basic pattern. While it certainly makes for a long day (sign on, flight to BNE, airport transit, brief, sim, debrief, transit, flight home, sign off) – it’s certainly feasible with the right flight connections – or the right sim slot timings to meet the existing east coast flight schedule. By the way this is of course all impacted by the change of daylight savings – or more accurately the lack of change in Queensland.

Finally, the use of 4 hour simulator slots with 10 minute breaks means you lose the use of the full 6 simulator slots per day. Basically you end up with a 3 hour break somewhere along the line. Engineering require a period of two hours a day anyway (not always to be used) for regular maintenance and complete the power down/power up cycles. Typically this takes place in the middle of the night when the sim is not being used.

Flight Schedule

I wanted the sheet to automatically select from a flight schedule for SYD/BNE/SYD and MEL/BNE/MEL when different sim slot timings were selected. I cast around for some kind of data set I could use but there really wasn’t anything readily to hand. I ended up simply entering into a sheet all the flights for a particular (weekday) in late October after daylights savings started in (most of) the east coast states. Strategic updates to schedule would need to be done to continue to use the sheet, but I determined this wasn’t necessary for my purposes. I had a quick look at the weekend flights as well, but rather than code in day-based schedules, I included the basic weekday pattern (MTWTFSS) in the data so that it was clear when certain flights didn’t operate.

Variables

I wanted the sheet to be able to account for certain variables. Quite apart from simulator slot start time, I wanted the user to be able to change sim slot gap, sign on/signoff periods, min/max transit times and limitations on duty periods.

Changing these variables in the spreadsheet changes the flights that appear to the left (fly in) and right (fly out) of the simulator sessions.

Counting Flights

At this point I now had a sheet that responded to changes in variables, selecting available flights based on the constraints in the variables. Flights that arrived too early for the max transit before a simulator slot would not show. Flights that arrived too late for the min transit after a simulator slot would not show. Flights that exceeded the parameterised min transit, max duty, etc. are either hidden or flagged in the sheet, depending on how bad they exceed.

At this stage the sheet can be used to tweak the parameters to match the work rules, then try a range of first session simulator start times to see the sheet update the slots (and the breaks) and see how the flights propagate across the simulator slots. I added columns on the right to count how may fly-in and fly-out and fly-in-out simulator slot/flights there were. These numbers are not a true measure of the specific availability of a simulator slot for Fi/Fo – but it facilitates a metric across which different sim slot patterns can be measured.

The various lines are summed at the top of each SYD/BNE/SYD and MEL/BNE/MEL section, then summed at the top of the sheet into an overall FIFO table to show a summary of numbers. Once again, these specific numbers aren’t necessarily valid in terms of the number of crew flying in and out – but taken overall allow you to see how the sim slot times compete with each other. Except …

Except that I’d now built a one shot system which allowed you to evaluate different sim slot times, but not really facilitating the comparison of multiple scenarios. I played with the What-If feature and the Excel Scenario Manager for a while (never been a big fan of these, but they have their uses) – before deciding to move onto something more ambitious. By this time I’d played with a number of simulator slot start times all through the day and had realised the results were not quite as I’d expected.

Automation

I decided to automate the sheet. This consisted of constructing an analysis table to track the results across various time slots. I decided since the basic gap is usually no less than 10 minutes, working through all of a 24 hour period. Then some automation code to make Excel increase the first simulator slot session start time by 10 minutes, then copy the results of the FIFO table into the analysis table.

Graphing The Numbers

Having got the table sorted, I then set about graphing the result. The graph is a combined line/area graph differentiating between fly-in, fly-out and fly-in and fly-out across MEL/BNE/MEL and SYD/BNE/SYD. The coloured areas indicate FIFO for MEL, SYD and both. The lines indicate FI or FO for MEL, SYD and Both. Depending on what your focus is, you can see that certain simulator slot start times, remembering the simulator slot start time at the bottom is that of the FIRST session – different levels of FIFO are facilitated.

The Analysis

The analysis indicated that in fact the peak Fi/Fo simulator start time is late afternoon. This is because when you start the simulator at this time – the back of the clock sessions (which as it turns out are the best for facilitating flight access for FIFO) – are available. Meanwhile the maintenance period takes place mid morning, when it’s difficult to fly in for the simulator because of the 18 hour a day nature of Australian East Coast services.

Of course regularly scheduling back of the clock simulator training has some other considerations. Quite apart from the crappy nature of these slots for training and particularly checking – engineer maintenance shifts are already focussed on the early morning period for down time maintenance (not that they would mind shifting, I suspect).

Video-Coolness

At one point I ended up with the sheet open twice, clicking the run button, and watching the spreadsheet populate and repopulate with the changing simulator slot times, building the graph as it made its way across as each simulator slot start time – which was pretty cool. The two videos here show (most of) the analysis sheet and graph during automation, and then just the graph.

Video not supported.

 

Video not supported.

 

Now, back to work.

The Result

As it turns out, practicalities override the analysis. The maintenance period needs to be back of the clock; and one of the highest productive sessions (in terms of Fi/Fo) is back of the clock and undesirable for training. That just left me with about a 90 minute window to finesse the start time to ensure maximum flights. Still, it was an interesting exercise… now all we need is a simulator.

Boeing 777 Bne 01 Boeing 777 Bne 02 Boeing 777 Bne 03

Shortt URL for this post:

Leave a Reply

Your email address will not be published. Required fields are marked *