Wide Body EBA 2017 Calculator [UPDATE 27Nov18]

With the implementation of the 2017 Wide Body EBA – it’s time to revise the spreadsheet I developed to track and check Overtime, Callout, etc for the 2011 EBA. Part and parcel has been seeking clarification from the AIC/CP on certain aspects of the EBA’s implementation. Most particularly clarification over the MCG, how RP changeovers are going to be implemented and how they impact the credit hours associated with Duty Periods and Flight Duty Sequences (MCG) that take place over the RP changeover to the next RP. I believe I have the answers now, the spreadsheet is in beta; welcome to the test program!

Note : Some the images here may look a little small – but if you click on them they expand to a decent size over the text.

Note : The Allowance Calculator requires Visual Basic. This means it does not run properly on MAC or IOS (iPad). You can use your Mac/iPad to enter the list of duties, but the Payslips tab requires a custom function I wrote to work. You can use the Excel in Citrix, or if you have a PC you can get Office Pro as a Virgin Employee quite cheaply – ask me how.

Version History

27Nov-2018 : RP13/01 has just been published with RP01/2019 starting on 31DEC18. Fowl Ball! How can you start an RP on the 31st of the year before? It should have been RP1314. I think they did it just to annoy me. Anyway – manual fix required in this version to accurately reflect RP1301/2018 (and RP0203/2019, etc). Download HERE.
18-Oct-2018 : Updated for ATO Domestic and International Allowance Changes.
21-Mar-2018 : Whoops – previous upload had my roster in it!
09-Mar-2018 : This release fixes a coding error that I missed in my review of the EBA. When called out for a Non (Flight, Sim, Standby) duty – you are paid the Callout but not creditted any overtime value. The sheet now allows this and inserts “OT+CO” where the normal credit for a Non (Flight, Sim, Standby) would be paid. There are a few other changes so – “Positioning” has become “Position Only” and “Away Day” is now “Away/Blank Day”. I’ve also highlighted the use of “Positioning (FRMS)” in the positioning area to show no credit … 
22-Feb-2018 : Made some minor formatting corrections, and changed “Away Day” to “Away/Blank Day” (Thanks Tim)
29-Jan-2018 : Added the ability to enter duties/periods and calcluate allowances to see what you’re getting on an overnight, and to crosscheck your payslip. There are some other minor changes to the OT/Callout sheets (both fleets) as well that do not affect the calculation result. I’ll add some instructions and a few videos for this at some point
26-Jan-2018 : Found some bugs in the new A330 sheet. Basically it wasn’t calculating Carry-Out Flight Pairings correctyly.
22-Jan-2018 : Updated 2 Operating sectors for the A330. Tidies up the conditional formatting in cells to more appropriately highlight cells that should have data (Green) and cells that really should have data (Yellow). Also Combined Position/Operating route into a single cell eg: “MEL-BNE” – note this means that the instructions below aren’t quite matching the spreadsheet for the moment.
20-Jan-2018 : Update for the A330. There’s now an A330 tab in the sheet. Please advise if it’s working ok!
09-Jan-2018 : Initial Issue.

Video Tutorials

The following video covers Entering Your Roster.

This next one covers entering Actual Hours for Positioning/Flights and entering Callouts and Changes …

This next one covers Carry Out Flight Pairings (including MCG) and Carry-In Flight Pairings.

Finally – the A330 Differences in the sheet.

Late addition – Entering Duty Periods and Checking Payslips for Domestic and International Allowances.

Overview

As usual, the sheet copes with Ranks, Salary Levels and Years in terms of the changes to the various values of Overtime, Callout, Ad Hoc Training Pay and more through the life of the EBA (noting that the last 3 year EBA lasted about 7 years). If you peek into the Data tab, you’ll see the A330 stuff in there as well. An RP calculating sheet in the same Excel file for the A330 is my next task. If you’ve used this before – you’re in for a mix of the familiar and the startlingly new …

Note : While the sheet does both the A330 and the B777 (on the 2017 Wide Body EBA) only the 777 is described here. At the moment the A330 sheet only handles one sector per day but I will fix that shortly. Other than that – the instructions are valid across both fleets (I hope).

  • Ranks : It does Captains, First Officers, and Second Officers. The 2017 EBA has levelled the playing field in terms of Checkers/Trainers and OT and Callout, so there’s no longer the need to differentiate.
  • Levels : Whether you’re Level 5 or Level 1 – you select and the sheet does the rest.
  • Level Changes : On 01 July each year, your Pay Level increments. Therefore the Overtime/Callout rates increase also. The sheet copes with this.
  • A330 Odd/Even SN : The A330 has a different number of min days off each RP, depending on whether you are an odd or even staff number. Seriously? Anyway I’ve added this to the A330 sheet so the min DDO’s calculates correctly.
  • Duty Select : You don’t have to know credit hours – just select the duty for each duty day and the sheet will use the relevant EBA Credit Hours.
  • Leave : Leave impacts the Overtime Threshold, as well as the minimum number of days off required in the month – the sheet copes with this.
  • Positioning : Positioning changes a bit, with the introduction of a 1 hour sector minimum, 50% basis for credit hours and 2 hour minimum for position only duty periods. As always the maximum of Scheduled vs Actual is the basis for calculation so you’ll need to track and enter both. I’ve also added the ability to Position (FRMS) after going fatigued so the credit hours won’t count.
  • Ad Hoc Training : When you’re not a Check/Training Captain, but conducting Ad Hoc Training as an Instructor (eg : NTS) – there’s a credit and payment. The sheet tracks this as well.
  • Data Filtering / Validation : As much as possible, cell entries are checked from lists for validity (Duties, Airports, Yes/No’s, etc). Any time this is done – there’s a list box you can click to drop and choose from.
  • MCG : The Minimum Credit Guarantee of 5 hours for each day of a sequence of Duty Periods that takes you away from Home Base and includes an operating crew Flight Duty Period (FDP). This was a little tricky to implement, and I’m not sold on my method – suggestions welcome!
  • Carry-Out : Individual Carry Out Duties are no longer necessary – any single duty period that has a credit value (ground or flight) that commences prior to midnight (Crew Local Base Time) on the last day of an RP – the credit hour value for that duty period is paid in that originating RP. Also…
  • Carry-Out DPs/FDPs (including MCG) : Any series of duties (must include an FDP) that carries into the following RP are credited as a whole into the could well result in an MCG based additional payment in the originating RP. Therefore in order to track and calculate this, you’ll need to enter all the Duty Periods past the end of the current RP at the bottom of the sheet in order to ensure the DP/FDP credit values are added and the any applicable MCG calculation is checked.
  • Comments : So you can stir the memory every 8 weeks without having to come back here, many of the cells include comments to remind you how to fill them in or the function they perform. These comments are indicated by the small red rectangle, and pointing your mouse at them causes them to popup.
  • Cell Color : Cells are color coded to assist in entry. Generally only cells coloured GREEN are where you should (can) enter data. Sometimes a cell will be coloured YELLOW to indicate that either no entry has been made (and one is required); or to highlight the use of that cell to you (such as to override a calculated value).
    In the example show, because Positioning has been selected, the Sector field is yellow indicating an entry required, and the Scheduled and Actual Block Times fields are green, also awaiting entry. Note the EBA Credit field is white and is a calculated field you cannot enter data into. Meanwhile, the Over Ride field is also green in case you want to over ride the calculation. Similarly, on the Flight line, the FromToScheduled and Actual Block fields are yellow, awaiting the details of your flight duty.
  • Error Checking : There is a comprehensive cross check built into the sheet to try and ensure you have completed it correctly. If an error is detected (such as a day you forgot to fill in) the “Error” box in the top LH Corner of the sheet. Further, there will be an error flag at the end of the row that contains the error. Finally, the cell in error (might) also be red/yellow to indicate a problem.

1. Primary Selections

  • Choose your Rank (Capt, FO, SO). This influences rates for Callout, Overtime, etc.
  • Choose the Pay Level/Year applicable at the end of the RP. You’re paid after the RP completes, so if there’s a changeover of rates (01 July each year) then the rate applicable after the change will be the ones used,
  • Select the Roster Period.
  • A330 Only : You need to select whether you are an Odd or Even Staff Number (SN). This impacts the calculation of your Min Days Off for the RP.

Having made these basic suggestions, the Overtime Threshold, Effective Overtime Rate (paid after the RP completes) and the Callout Rate are displayed. Note that the OT/Callout rates are based on your Pay Level/Year selection, and the OT Threshold includes the vLearn credit in each RP. The Error flag is shown because the selections against the days of the RP are not yet complete.

2. Daily Duty Types

So the main sheet is where all the work happens, of course. You must select a duty type for every day in the RP – even days where you didn’t do anything, or days that don’t exist (such as the one between takeoff and landing on the way back from LA).

Select the cell, then click the little drop-down arrow to see the list. You can also just type in the entry you want (you must spell it correctly!). Note that having selected it once, the next time you can just start typing the entry, and Autofill will quickly work out what you are after. This seems to be the best way.

Strictly speaking, the days in the column on the LHS of the sheet are based on Crew Member Base Local Time. So when you assign a duty or flight down route in LAX – you should be selecting the Australian Date for this duty. In practice, this only comes into play for carry-out flights/duties at the end of the RP – and this area has been tidied up immensely since EBA 2011.

Shown here is the current list of duty types in use. These may change from time to time, but so far these have worked from the previous EBA.

  • Admin Duty : Admin duties that come with the standard 5:00 hours credit
  • Admin – No Credit : Used when doing an admin duty that does not credit you towards Overtime (0:00)
  • Away Day : Basically days away down route that are not days off. Also the day between departing LAX and arriving back into Australia.
  • Day Off : This is your DDO’s and ADO’a. Note that sometime Crew Control will convert a duty to a Blank day as the result of a change. This is basically a Day Off that won’t generate a callout if you subsequently work it.
  • Flight : Select for any Flight Duty Period (FDP).
  • Gnd/Trg Duty : Basically any type of Ground or Training Duty that’s not Admin – SEP, NTS, etc. This includes running such courses.
  • Leave (All) : All types of leave that reduce your OT threshold, including Annual, Parental, Carers, etc.
  • Open Day : This is the 12 hour notice, 3 hour credit standby day that came in with the 777 2011 EBA. Love these.
  • Positioning : Any form of positioning, where the positioning is the only duty undertaken that day. The sheet automatically handles the 50%, minimum 1 hour per sector; minimum 2 hours for position only duty days.
  • Position (FRMS) : Positioning in the event of going fatigued may not attract credit. Check the EBA whether this applies to your positioning after going fatigued and if so, select this duty to calculate your credit hours correctly without the credit for FRMS Positioning.
  • Sick : Sick days do not attract credit and do not reduce your credit target.
  • Sim Instructor / Sim Student : These two duties have different credit values.
  • Standby/Reserve : This is applicable to both Standby at home and Hotel Standby.

3. The Simple Duties – as well as Callout, Ad Hoc Training and Cancelled Accommodation.

So some of the duties (Flight, Positioning) are clearly more complex than others. Let’s get the simple ones done first.

  • Day Off ; Admin (both); Gnd/Trg Duty; Leave; OpenDay; Sick, Simulator, Standby/Reserve : Once selected, that’s it for that day … except if …
  • Callout : If a callout is applicable, you need to Enter or Select Yes in the callout column. Note there is no longer  Day One, DayOne+ callout rate – it’s all one rate.
  • Ad Hoc Trg : If you’re performing a Gnd/Trg Duty as an Ad Hoc Instructor – enter Yes in this column to have the sheet calculate your Ad Hoc Instructors payment to the breakdown/total.
  • Canc Accom : If you have cancelled company accommodation for one or more duties, select Yes here to have the sheet include these in the breakdown/total.

4. Positioning

Positioning requires additional detail to calculate correctly. The (basic) EBA 2017 rules for calculating Positioning Credit are as follows:

  • Positioning typically gains a 50% credit of the larger value of (Scheduled or Actual Block Time). Once again – prior to positioning the Flt Time value on your roster is the Scheduled Block Time; once you’re positioned your roster will show the Actual Block Time.
  • There’s a minimum 1 hour credit per sector (applied after the 50% factor).
  • If the only thing you are doing in the duty period is Positioning – then there’s a minimum 2 hour credit.
  • Remember that if you commence a Duty Period with Positioning (or anything else) that goes over midnight Crew Base Local Time of the last day of the RP and into the next RP – the credit (Scheduled/Actual/EBA) goes into the RP in which the Duty Period commenced.
  • There are some very specific instance where International Positioning in Economy comes with 100% of the (Scheduled/Actual) hours as Credit. In this case – the EBA Override cell is used to credit yourself with the full sector’s credit.
  • Clear as mud?

Here you can see a typical series of duties. Day One is positioning up to BNE for Simulator. Day Two is the Sim Session, and (yet to be entered) the positioning sector home. Note the Yellow cell showing a required entry (the sector) and the three green cells for Sched BlockActual Block, and Over Ride. The EBA Credit is already filled in at 2 hours minimum since that’s all you are doing during the duty period.

Now the first line is basically complete. The sector (MEL-BNE) has been entered. The Sched Block (from roster publication) has been entered. The Actual Block (from the roster after “flown”) has also been entered. Since 50% of both these values is less than 2 hours – the EBA Credit remains at 2 hours. Note that any value you enter in the Over Ride cell will over-ride the EBA Credit cell. Any value you enter in this cell turns the cell Orange to indicate that an over-ride is in use.

But we have to get home from BNE after sim. This positioning is part of the Duty Period applicable to the Sim Student duty. To apply this, click on the Sector cell and enter the BNE-MEL sector (even though the cell is not green). Once you’ve done this, the Sched BlockActual BlockEBA Credit and Over Ride cells will activate for further entry. You can see that in this case the 50% credit value is calculated (since there is no 2 hour minimum) and that Actual is great than Scheduled. If this was SYD-MEL, the EBA Credit value would show 1:00 since 50% of SYD-MEL is less than the one hour per sector EBA minimum.

Finally – in the event that we go fatigued after a duty and are not safe to drive home, any positioning sector that follows the rest may not incur a credit value. In this case, show the duty as Position (FRMS) and whatever you choose to enter in the Sector,  Sched BlockActual Block cells – the  EBA Credit cell will show 00:00. Note however that the Over Ride cell still functions to override all calculation and uses the entered Over Ride value to credit your hours.

One case the Over Ride cell could be used is when positioning internationally on a sector length above 7 hours where the rest period after the positioning, prior to operating, is less than the minimum proscribed in the EBA. In this case – manually enter the maximum of (Scheduled ActualBlock Time into the EBA Over Ride cell – there is no factoring here, any value you enter over-rides all other positioning considerations. In this case the EBA Over Ride cell shows in orange to indicate it’s over-riding all the other positioning calculations.

Note : Occasionally you have to do mental maths to work out the Scheduled or Actual Block time from your roster. If you have a Start and Stop time for this, you can do the maths in your head (remember the two times are Local Time so between MEL and BNE there could be a missing (or extra) hour you have to account for in your formula) – then you can use the following formula in Excel (say the Actual Block is between 14:37 and 16:58) in the cell where you need the result. After you’ve used the formula to get an answer – it’s easiest just to type the answer in over top of the formula.

=Time(16,58,0)-Time(14,37,0)

4. Flights

Flights also require more details in order to calculate the correct credit value. This includes tracking both Scheduled and Actual Block Times (Pushback to Park). Note that the Flt Time value that appears on your Sabre roster at publication (or before you fly the trip) is the Scheduled Block Time applicable for the trip. Shortly after you have flown the trip – this time becomes the Actual Block Time you flew – and the Scheduled value is no longer accessible. I therefore strongly recommend taking screenshots of your roster after publication so you can retain the scheduled values.

MCG : Minimum Credit Guarantee

More complicated is the implementation of the Minimum Credit Guarantee. Basically when you head off from Home Base on a series of duties that includes a Flight Duty Pairing where you are operating (not positioning) – you get a minimum credit of 5 hours per day until you complete the Duty Period that signs you off at Home Base. This includes any Duty Types (Simulator, Admin, Ground Training, Open, Standby, Day Off, etc) as long as one of them is a Flight (Operating).

At the end of the duty series – if the 5 hour per day credit exceeds the credit from the Duty Period/Types – you are paid the MCG. I’ve implemented this methodology using the MCG # column to the immediate right of the Duty Type column. Anytime you start a series of flight-related duties (1 or more) where the MCG is applicable (because of a Flight) – place a number in the MCG # cell for each date of the sequence of duty periods. Keep that number identical for subsequent duties until complete the series ends back at Home Base. Use a new number for the next series of duties that includes an FDP. It sounds more complicated than it is … Mostly.

Firstly select Flight from the Duty Type list box on the left against the departure date of your flight. Note that technically this is the departure date based on your Crew Base Local Time. Which for evening LAX departures means the day after that shown on your Sabre roster.

  • Now enter the MCG # value. While the specific number actually doesn’t matter (I’ve limited it to 1…9 for now) – I suggest using 1-1-1-1 for your first trip; then 2-2-2-2 for your second trip, etc. Note that when you select Flight the MCG # cell next to that selection will turn yellow to highlight you need an entry there. When you choose a non-flight for the next day (say, Away Day) the cell will not be yellow – but this cell still needs the MCG # since it’s part of a sequence of duties.
  • Next, scroll across and you’ll see the Flight Details area has some yellow areas for entry. Enter the From airport and the To airport, then enter the Scheduled Block time (shown on your Sabre roster as Flight Time at publish). This will give you an initial estimate of the credit hours that will come from this duty. For the moment I have not provided for multiple sectors on the 777 – I’ll do it with the 330 and then decide if the solution should be rolled back. Further to the right, you can see the EBA Credit has your scheduled block time value.
  • Once you’ve operated the flight, look back at your Sabre roster and enter the actual Flight Time into the Actual Block time in the spreadsheet. If Actual was longer than Scheduled – you’ll see the new, larger value in the EBA Credit cell.
  • Remember that if you’re called off one or more days off for a trip – enter Yes into the Call Out cell to the immediate right of your Duty Type selection.
  • Finally, once again remember that if you commence a Duty Period with Positioning (or anything else) that goes over midnight Crew Base Local Time of the last day of the RP and into the next RP – the credit (Scheduled/Actual/EBA) goes into the RP in which the Duty Period commenced.

After the entries are complete, scrolling further across to the right (see below) you can see the Duty Type Credit (Max of Sked/Actual Block); MCG Cumulative Credit (5 Hrs/Day), the Pairing Cumulative Credit (based on Max of Sked/Actual Block); and MCG Additional – in the case above the credit from the flying exceeds the credit from the MCG, so there’s no addition. Let’s hit all the highlights with the next one …

Final Example – Flight with Callout, Positioning, Standby and MCG

The following is an example where the MCG applies – in this case, the shorter BNE/LAX flight times (after Standby), along with an extra day in LAX brings the MCG into play. Since the MCG Accumulation (7 x 5:00) comes to 35:00 while the Sequence of FDP related duties comes to 33:00 – there’s a 2:00 in the MCG Addition column at the end of the MCG sequence, and this 2:00 extra has been added into the EBA (& MCG) column on the far right. The MCG is paid by the spreadsheet against the last day of the sequence – either as the only credit (if the last day has no credit, like an Away Day) or added to any credit on that day – like in this example where the 2:00 is added to the Positioning Credit of 1:10.

This sequence is positioning to MEL-BNE for Standby, then heading off on a 5 day trip to LAX (you never know …) then coming back to BNE and positioning BNE-MEL home after arrival. Note in this instance I was called out for this sequence as indicated by the Yes in the Callout column.

  • The MCG # (2) is selected for the entire set of the MCG related days. Because the Positioning and Standby days are associated with the Flight Duty Period sequence – they count towards MCG. The “2” probably means this is the second trip on my roster.
  • Positioning on 28/Jan was 2:00 because that’s the minimum. Although the MCG of 5 hours on the day exceeds this value – MCG applies across the total sequence of duties, so you can’t tell if MCG is going to pay until it’s all over (including Actual vs Schedule for both Positioning and Flights).
  • Standby on 29/Jan has a 5:00 hour credit.
  • The Flights on 30/Jan and 02/Feb are credited based on the highest of (Sked/Actual) Block Time.
  • During the sequence, the highest of the MCG Cumulative Credit or Pairing Cumulative Credit is highlighted (subtle yellow).
  • At the end of the sequence – the MCG accumulation comes to 35:00; the duty related credit 33:00 – so the MCG adds 2:00 hours to the EBA (& MCG) Credit for the series of Flight related duties.

Note that while the EBA may not be clear – the Payroll system is coded to look only for an FDP as part of a series of duties; then it looks for the first Duty Period that took you away from Home Base at or before the FDP; and the last Duty Period/DFP that brought you back to Home Base. MCG is applied across the entire series of duties – FDPs and non-FDPs alike.

In the example below, I choofed off to Sydney on 02/Jan to teach two days of Ground School. Then I headed off SYD-LAX and came back the next day. On the last day (after arriving into SYD) I positioned home to MEL. The MCG calculation is done across all these duties. In this instance MCG Cumulative Credit is less than Pairing Cumulative Credit.

[Read more…]

EBA Overtime Calculator (v2.6) [Update 27Oct17]

I’ve watched the EBA with some envy for the last three years. Between Overtime, Callout, Domestic Allowances, Leave and Days Off, the position of Manager hasn’t quite kept pace with the EBA. I’ve also been watching the need for a spreadsheet to calculate the Overtime, Callout and Accommodation Cancellation monies, but resisting the temptation to build one. I didn’t quite want to know how much I was missing out on.

Current Version : V2.6 27.Oct.17 : Down HERE.

– Corrected for leaving the wrong cells locked in 2.5 – no other changes.

I really would have thought we’d be on the new EBA by this RP, but apparently not? Anyway – 2.5 extends the sheet for a couple more RP’s just in case. Esp since there’s still seems to be a lot of overtime going around, even with an aircraft in maintenance for most of this RP …

– Ok, so after some discussion at a recent EBA meeting, positioning credit/before after duties (1 hour) is going to be paid for the time being. Meanwhile the lack of any payscales after 01Jul17 has broken my spreadsheet. For some reason I didn’t plan for the 3 year EBA of 2011 to still be in force …  fixed in 2.4

– After years of paying it, the company is now no longer paying a 1 hour credit for positioning before/after any other duty. Until recently, the 6 hours or so added to my day that is positioning up to BNE for Sim/Admin/etc is not worth Zero. The interpretation is that this event is “travelling” before/after a duty and not positioning under the EBA. Yeah right.- Positioning BNE/SYD/MEL no longer carries a 1 hour credit unless it’s completed as it’s own duty.
– Corrected adding error in Block Hours Total (Top RHS)
– Thanks to PM for spotting to bugs that I’ve corrected in V1.5
– Thanks to TH removed Super from Salaries to better compare Overtime with payslips in V1.6
– Now deals with Carry In/Out Flights that wrap from one RP to the next (see below) – V1.7
– Bug correction in Carry In/Out Flights & Updated to clarify that Push/Park are for Sked; Actual (was User) is for Actual Block (Tks TD) – now in V1.8
– Corrected Calc Blocks Time not showing 00:00 for midnight V1.9
– Added BNE/MEL positioning; change to allow for zero credit positioning on same day of Duty V2.0
– Added CCA/TCA/TFO Rank Selectors to incorporate appointment pay in overttime V2.1

The combination of recently updating my Tax Allowance Claim Calculator for the 2011/2012 Tax Year as well as seeing someone else’s overtime calculation sheet come past my inbox, I decided it was time to get off my tail and build one. I also decided to see how much I could test the data validation and conditional formatting functionality of Excel and turn it into a custom form like entry interface that would test and indicate both incompleteness as well as validity of entries. In the past I’ve always tried to maintain compatibility with Excel 2003, forgoing the really cool features of Excel 2007/2020 – not anymore.

VAI 777 EBA Overtime Calculation Spreadsheet.

I wanted my sheet cope with the following aspects, all in a single spreadsheet.

  • Ranks : It does Captains, First Officers, Relief First Officers; Check Captains, Training Captains and Training First Officers under the EBA.
  • Levels : Whether you’re Level 5 or Level 1 – you select and the sheet does the rest.
  • Level Changes : On 01 July each year, your Level increments. Therefore the Overtime rate increases also. The sheet copes with this.
  • Duty Select : You don’t have to know the credit hours – just select the duty for each duty day and the sheet will use the relevant EBA Credit Hours
  • Leave : Having Leave impacts the Overtime Threshold, as well as the minimum number of days off required in the month – the sheet copes with this.
  • Positioning : There are two types of positioning – EBA (MEL-SYD-MEL or BNE-SYD-BNE) and All Others. The EBA has the standard credit hours – the rest you have to enter. The sheet uses the default credit for EBA positioning; facilitates your entry of the Block hours for non EBA positioning (SYD/KUL/SYD).
  • Ad Hoc Training : When you’re not a Check/Training Captain, but conducting Ad Hoc Training as an Instructor (NTS) – there’s a credit and payment. The sheet tracks this as well.
  • Data Filtering / Validation : As much as possible, entries are checked from lists for validity (Duties, Airports, Yes/No’s, etc). Anytime this is done – there’s a list box you can click to drop and choose from.
  • Summaries and Analysis : Once complete, quick reference summary at the top for Days Off; Leave; Sims; Admin; Standby/Open; Ground Duties; Credit hours (in relation to the Overtime Threshold); Callouts; Block Hours; Cancelled Accom and Ad Hoc Trainer. There’s also a list of duties with a count on the far right, and I’m playing with Pivot Tables and Charts in this one too.
  • Variables ?: I’ve coded as much as I can as variables that can be changed should I need it to. I can’t see anything changing in the EBA in the next 12 months – but just in case …

Let me run you through how to use it:

1. Basic Entries.

The first things the sheet really needs to know is your Rank (Capt, FO, CRFO); Pay Level (1 … 8); and which Roster Period you are looking at (currently only from RP 2012 3/4 onwards). Note that Rank now includes choosing Check/Training Captain/First Officer since this impacts some values.

Note that the Pay Level is the one at the start of your target RP. For most of the original VAI pilots that will be level 5 from RP 9/10 2012 onwards.

Note that when you point your mouse at a cell with a little red triangle in the corner – a hint pops up. Also not that when you click into a cell for data entry – if there’s validation on the cell (such as the requirement to select from a previously established list) a small down arrow shows to the bottom right of the cell – click the example here to see.

Having chosen these variables, the initial credit threshold (it will updated as Leave days are later selected) and Initial Overtime Rate should be checked. All the Dates down the LHS should also fill in for the 56 day RP.

The?Clear Button – clicking this will remove ALL entries into the sheet (From Rank through to all the duties and Positioning/Flight entries – and There’s No UNDO!

[Read more…]