Wide Body EBA 2017 Calculator [STOP PRESS – UPDATE 01May18]

STOP PRESS:

To the surprise of many (including me), a provision of the Domestic EBA made its way into the Wide Body EBA of 2017. Specifically – when you are called out off leave to perform a duty – your overtime threshold remains at the level it was at publish time.  This is change from the previous Long Haul EBA where your OT threshold was based on the leave you actually had based on the flown/completed roster – so if you worked on your leave, the OT threshold increased proportionally to the number of leave days you worked. This is no longer the case – and makes working on leave more punitive towards the company. Note the leave you have lost by working on your leave is still returned to the bank.

I am working on an update to the sheet to correct for this but I have a lot on at the moment so it will be a few days. In the meantime – if you have taken callout from leave it is likely that you have been underpaid – the company is aware of this and working towards re-imbursement.

[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…]