I recently developed a spreadsheet to check the domestic allowances I was being paid. The process was educational, to say the least. This initial version only checks domestic allowances – I will develop further to facilitate the checking of rostered vs actual international allowances as well.
Note :
– This spreadsheet required VBA programming code in the background to calculate the Allowances out correctly against each Payslip. Therefore it will not run on Mac or iPad.
- Version 2.0 (25Feb23) : Revised for the Domestic model of allowance calculation (full Incidental per day, etc) as well as Amounts and Payslip Dates from Jul/21 onwards.
- Version 1.4 (02Apr16) : Added a column on the RHS of the calculator to allow back paid allowances to be entered and checked.
- Version 1.3 (10Jul15) : Added allowances for post 01Jul15 (to be updated); unlocked the green allowances section for user edit.
- Version 1.2 (14-Apr-15) : Bugfix Release (Dinner using Lunch Allowance in Duty Periods sheet).
- Version 1.1 (05.Apr.15) : Initial Issue
Background
Having only recently come to the EBA, I’ve started seeing the additional payments that come into my salary associated with Overtime, Callout and Allowances. The first of these two I track and check using my EBA Overtime Calculator, which you can get from the linked post. Certainly the comparison of what I believe I should be paid as compared with what I have been paid has been an educational process, illuminating for me both the detail of the EBA and inner working of the rostering and payroll systems, occasionally requiring follow up redress. I encourage everyone to check their overtime/callout when it’s paid every 8 weeks.
Allowances however are a different story. The process of calculating how much should be paid across a series of meal windows and an associated incidental period is relatively simple and I used such a process extensively in my Crew Allowance Tax Calculator (the latest of which can be downloaded from the link).
As such I figured it would be easy to develop a sheet to check my domestic allowances. To make sure I was doing it right, I started with the EBA, which is where I struck my first problem. The paucity of detail in this document covering a moderately complicated issue such as crew allowances at domestic and international ports was manifestly inadequate for my purposes. I fired off a couple of emails with comprehensive questions to the responsible line management, so far without a reply. In the end I reverted to the Short Haul EBA for the basis of calculation, the premise of which is basically that from the time you sign on at home Base for the purpose of operating Duty(ies) which include an overnight away from home base, you’re continuously paid Meal/Incidental Allowances until you sign off again back at home Base. This happens irrespective of whether meals are provided associated with your duties or not – or so my fellow domestic pilots advise.
I would like to give credit here to Dean Young, who provided the original formulae for the Allowance Checker. He was looking at this issue at the time as I was, and developed the formulae to assess the presence of a duty period over a meal window.
Dean was using a spreadsheet to fact check paid allowances, I used it for a different purpose (Tax) but the formula requirement was the same. His solution was elegant and with his permission I used it in my own sheet. Dean was responsible for much behind the scenes work in the early days of V Australia (we didn’t call it Volunteer Australia for nothing …), for which many will never appreciate.
Thanks Dean.
In any case I thought I had a handle on it so I started a sheet based on my Tax Calculator. It took me an afternoon to get a sheet I was happy with, which looked substantially like the following:
As always with my spreadsheets, the green cells are where you enter your information, the other cells are where you shouldn’t change things unless you want the calculations to go wrong. The Clear button deletes your entries in the green cells.
Basically you enter the periods away from home in green. On the 15th March I reported at 15:55 for a flight to BNE, to undertake a few days of simulator training, before returning to Melbourne, signing off at 18:50 on the 17th March after the flight home. The calculation is relatively simple where Incidental is purely based on hours away from home at a fixed rate; Breakfast, Lunch and Dinner meal allowances paid when your time away from Base touches any of the allocated meal windows. Or so I thought.
However when it comes to checking the allowance you were paid – it’s a different story.
You see we are paid on a two week basis, covering a two week period, and we receive that pay about 4 working days after the close of that two week window. This payroll fortnight takes no account of your comings and goings and as such you can be paid for half of your time away in one pay check, and the rest of your time away in the next.
As such I was now confronted with the requirement to turn the fairly easy to enter blocks of time away from home base to a meal/date specific result that would be easy to check against the payslip. After much effort – I finally realised I couldn’t do it without code.
I therefore built a custom function in Visual Basic (Applications) for Excel, called:
Payslip (PaySlipDate As Date, Meal As String, Payrate As Currency) As Currency
This is certainly not my best piece of programming, it’s pretty quick and dirty, but it does the job. This formula requires three variables:
- A Date – the date during the payslip period which is to be examined for possible allowances;
- a Meal – the name of the meal band (“Breakfast”, “Lunch”, “Dinner”) which the calculation is to look for; and
- a Rate – the rate of the particular meal allowance to be paid.
The value returned is either Zero (no allowance paid); in the case of Brekky, Lunch or Dinner the PayRate of the particular allowance if there is one on the date requested; or in the case of Incidentals – how much is to be paid in the way of an Incidental amount for that date.
This formula is incorporated into another sheet that looks like this:
- The Payslip Date is Entered/Selected at the top.
- The Start/End date of the related period fill in at the top, and down the LHS of the calculating area
- Based on these dates, each Meal Period (and Incidental) for each date is reviewed in the context of duty periods away from base entered previously on the other sheet. If you were on station during a meal period on a particular date – this sheet detects that and fills in the amount. The incidental amount depends on how many hours you were away from Base (up to 24) and calculates it accordingly.
- Once the sheet has calculated where you should have been paid something – the green cell next to the required payment value turns yellow.
- The yellow cells are where you are required to enter something (one of the following):
- Yes : You were paid the full amount. (enter the word “Yes”)
- No : You were paid nothing against this meal/incidental allowance (enter the word “No”); or
- $##.## : The amount you were paid (this is reserved for the Incidental figure as printed on your payslip).
The result?
As can be shown here, there is a missing meal, and the incidentals are rarely paid in full. From a review of the last 6 months of allowances calculated vs paid:
- No incidentals are being paid on the first day of a duty (neither positioning up to BNE for simulator nor positioning up the day prior to simulator)
- Incidentals are never paid in full for full days away from Base (my suspicion is that when I sign on to teach simulator in BNE, I stop being paid the incidental allowance).
- The odd occasional meal drops out of the Company’s calculation. I originally postulated that when I signed on to teach Simulator, I was no longer being paid a meal allowance, hence the odd meal on most days there would be a meal allowance missing. However this turns out not to be the case – I can’t see any rhyme or reason as to why I’m missing a meal every now and then. Perhaps they’re not paying me when I skip meals?
Over the past 6 months the difference I’ve calculated comes to over $600. I have yet to claims these and will be doing so early next week. Many years ago I detected an error in the way I was being paid an allowance at Emirates, my previous employer. This error affected a small number of pilots several times a week (depending on them operating a particular flight pairing); but went back a couple of years. The solution was for the company to change the wording of our “contracts” so that the manner in which the allowance had previously been paid was correct. I don’t think that’s going to happen here .. but it will no doubt be interesting, especially given the vague wording of our agreement.
If you find my content useful and are in a position to do so – I would appreciate a contribution to my PayPal account (ken.pascoe@gmail.com) – If you use the Friends and Family feature in PayPal it reduces the charges to the transfer. Please note that when sending money in this way you are removing any form of purchase protection, which is not relevant to a contribution of this type anyway.
Regards, Ken Pascoe