Tax Time : Crew Allowances (v3.4) [14Sep18]

It’s Tax time again and as since I’m one of those lazy people who does all the work at the end, instead of keeping up with it as it goes along – the first thing I need to do is update my Allowance calculator spreadsheet. I’m posting a copy of the sheet here for you guys to download because each year more and more crew ask for a copy and I can’t remember who’s asked for it and who hasn’t. In this post, I’ll discuss the basis for the tax refund, and how to use the spreadsheet.

Disclaimer

Sorry – but this Excel spreadsheet is all care and no responsibility on my part. I’m using it myself and so I have certainly checked it as best I can to determine if there are any errors – but I can’t promise there aren’t any. Feel free to look through all the tabs at the tables of values I’ve taken from the current TD to make sure there aren’t any entry errors – please let me know if there are via the comments at the bottom of this post and I’ll fix it and re-upload.

Background

Essentially the Australian Tax Office produces a Tax Determination each year which covers this issue. There’s a new one each year and it’s urbanely titled “Income Tax : What are the reasonable travel and overtime meal allowance expense amounts for the 20XX-YY income year?“.

The TD determines the maximum reasonable amount for overnight allowances (without receipts). The amount is determined by location (separate listings for Australia Cities vs Overseas Countries/Currencies) and are also affected by salary level – the concept being that if you earn a lot more, you are allowed to spend a lot more when you’re on a trip. I wish.

Salary cutoff’s for the salary bandings are as follows (all values are AUD) :

Low : Less than $110,650 AUD
Mid : Between $119,650 and $212,950
High : Greater than $212.950

Typical ATO maximum claim values are values for our trips are:

LAX (Short)  => Lo: $660 / Med: $860 / Hi: $1,020
AUH (Long) => Lo: $1,075 / Med: $1,400 / Hi: $1,725

As you can see – the ATO values are in excess of the allowances typically paid to crew, irrespective of salary band.

I would encourage you to read through the TD in detail, but basically it says that if your company pays less than the ATO allowance for overnight expenses (Meals and other Incidentals) you are entitled to claim the difference between what the Company pays and what you actually spend, up to a maximum threshold which is the ATO allowance.

Notice that you can claim the difference up to what you actually spend – which may be less than the ATO allowance. That said – the TD says that while you can’t claim what you don’t actually spend – you don’t have to provide receipts either.

For Australian Stations, how much the ATO allows is a combination of a meal allowance (eg: if you’re “on station” anywhere between 0600am-0800am, you get a breakfast allowance) and an “incidental allowance”. The incidental allowance is paid for each day you touch while you are on station. Note that the actual meal time bands are not published anywhere in TD 017/2011. I’m still hunting down an ATO reference, but apparently the ones the Company uses are based on the ATO time bands.

For International Stations, while there is a concept of Meals – time bands are not applied and instead for each day (or part thereof) you are “on station” a combination of all the meal allowances (brekky, lunch, dinner) and the incidental allowances is paid.

In the past there’s been a lot of too-ing and fro-ing about this (as you can imagine). My accountant has allowed me to claim this for four years now and since he worked for the ATO for many years – he’s is extremely conscious of what is and isn’t kosher when it comes to income tax deductions. owever I’ve had a number of pilots and cabin crew tell me their accountant has said it’s not kosher.

If your accountant has issues, and you want to persist with it – I suggest you talk to my accountant about it. He’s had extensive discussions with the ATO and two years ago obtained a judgement/ruling in this area. He can be reached via his web site. As an aside – he’s been my accountant for about 9 years now and I have no hesitation in recommending him.

Geoff Taylor http://www.majenda.com/ +61 (2) 9904 6933

Cash Allowances – Report from Payroll

The cash allowances we now receive down route complicates things a little. You will need to provide the details of this money to your accountant and the ATO. This can be done in one of two ways.

  • [CASH] Go back in time and note down how much you were paid (in local currency) on each layover; or
  • [REPORT] Get a report from Payroll (payroll.queries@virginaustralia.com) which will give you a total in Australia dollars.

The former method has the advantage of being highly accurate. The latter (report from payroll) is difficult to check for accuracy. Please note I have labelled the two methods above CASH and REPORT – this is because the spreadsheet allows you to do either of these methods, and later I’ll show you how.

The Spreadsheet

Ok, so onto the spreadsheet. As an overview:

  • Enter some basic parameters in the Summary sheet (approximate Salary, etc);
  • Enter the Payslip and Cash Allowances paid to you via Salary in the Summary sheet;
  • Enter the details of your layovers (both domestic and international) in the DutyLog sheet; then
  • The Summary sheet will advise the totals – but I just give the whole sheet to my accountant.

Summary Page – Company Payslip (domestic) Allowances

The spreadsheet is protected against accidentally overriding the formula’s – the green cells are where you can enter values.

Basically, you need to log onto the portal and run V-Claim and look at your past payslips. For each two week pay period – take the value you were paid in allowances by the company (“Meal/Incidental Allowances”) and enter it against the correct date in the spreadsheet. Where you were docked overpayments (or paid extra) – these are to be entered too, even if it means entering negative allowances for that payslip.

Note this amount will NOT include allowances paid in cash over the hotel check-in desk. That’s handled elsewhere.

Summary Page – Basic Variables.

Now there are some global numbers to enter on the Summary Sheet. The major one is Gross Salary, which is used to determine which Salary Range you are in and therefore which allowance band will be used. All of the following values are required:

  • Enter your approx Gross Salary into the green box.
  • If you are using the [REPORT] method to determine cash allowances paid down route – enter the total from the report provided by Payroll into the Hotel Cash Allowances (Report) green box. Otherwise – leave it blank.
  • ATO Tax Rate : when you look at how much tax you paid last year, divided into your Gross Income – you can get an approximate percentage figure. Based on this, the spreadsheet can estimate what you’re allowance refund should be – which is Allowance Difference x (1 – %Approx Tax Rate). This will give a “best guess” at how much you can expect to get back on your allowances.

Once you have completed the Duty Log section of the sheet, you’ll get the following values on the Summary Sheet.

  • Payslip Allowances : Summed from the table (on the left) you entered them into.
  • Hotel Cash Allowances (as paid) : If you enter all the local currency cash paid down route onto the Duty Log sheet – it’ll be summed here.
  • Total Company Allowance : The addition of the Payslip plus Hotel Allowances.
  • ATO Allowance : This is how much the ATO calculates Crew Allowances at for Tax Purposes – based on your roster entered on Duty Log.
  • Difference – the gap that you can claim.
  • Approx Tax Refund : Based on your ATO Tax Rate this is a general stab at what you should get back.

The Duty Log (Where it All Happens) Example : SYD – MEL – LAX – BNE – SYD

The Duty Log tab is where you enter in all the details of the flights you have operated during 01.Jul.2011 -> 30.Jun.2012

While the data you enter is based on the flights you operate – in fact for the most part is is the off duty periods between flights while away from home that you are claiming. This is an important distinction when entering the information. For the trip pictured below, you’re claiming:

  1. The time from getting on the flight SYD to MEL until the next day when you sign on for the MEL-LAX flight; and
  2. The time between arriving (sign off) into LAX and departing (sign on) LAX for BNE; and
  3. The time between arriving (sign off) into BNE and arriving back into SYD after the domestic flight.

Note : It doesn’t matter where your domicile is – all these calculations are to be Sydney based, as your roster is.

Note : All Dates/Times are Local Time, wherever you are.

Remember that you are only addressing periods of time between flights or during ground duties such as SEP or CRM, which are NOT at your home base. Thus you can’t claim anything for SEP in Sydney – but you can if you are flown to BNE for SEP training. In which case you would claim against the time away from SYD : From the time you go on the flight in SYD; until when you got off the plane again back in SYD.

The headings of the spreadsheet are reasonably self explanatory and if you place the mouse cursor over each of them, a popup comment (cells with little red triangle in the corner) provides additional detail … However … using the picture here as a sample :

(A) Date : The Date column is pre-filled with a Date for each day of the tax financial year. For each FLT or GND duty – you place it on the date that the allowance claim period started. So you start claiming an LAX layover period on the day the LAX flight departed Australia. However if you land after midnight after flying into AUH – you would start your claim on the day after the flight departed SYD – does that make sense?

(B) Duty Type : Duty Type is either FLT (Flight); GND (Ground Duty – Meetings, SEP, etc); or SIM (Simulator Training). You can enter it or choose it from a dropdown box.

(C) Station : This is the three letter code for the layover airport. This determines whether the station is Domestic or Overseas, as well as linking into the exchange rate later on. You can enter it or choose it from a dropdown box.

(D) Start Time : This is a time (entered in 24 hours time with a colon, eg 23:40) which denotes the start of the period for which allowances are to be paid. For LAX/AUH flights – this is the Sign Off time after you arrive into LAX/AUH.

(E) Start Date : The date is automatically filled in from the first date column and is only repeated for convenience. Again – watch out for AUH flights that get in late after midnight.

(F) Stop Time : This is the time at which the paid layover period ends. For LAX/AUH flights – this should be the Sign On time for the return flight.

(G) Stop Date : Since our layovers will end on a different date to the arrival time – a separate date is entered alongside the Stop Time to help with the calculation of how many days you were on station. Dates can be entered into Excel in a number of ways (12.3.12   12.mar.12  12.mar  12.3  etc) but always check after you’ve entered one that it’s worked correctly. Also look in the Days column to see if the calculation has worked. Remember that you lose a full day on the way back from LAX (which you can’t claim for – nice try!)

(H+I) Start / Stop Date/Time : These two columns are the calculated start and stop date/time based on what you entered. Have a quick look at these two after you enter in your values to make sure you’ve done it correctly.

(J) Cash Allowances Hotel Paid (Local $) : If you are going to use the [CASH] method for down route allowances, enter the amount in local currency you were given here. This will be converted to AUD using the RBA exchange rate on that day.

(K) Cash Allowances Hotel Paid (AUD $) : If for some strange reason you want to enter your [CASH] Hotel paid allowances in Australian Dollars (Why? Why?) – you can do that here. If you enter both $Local and $AUD – the sheet will get grumpy at you. Be warned.

(L) Total Cash Allowances (AUD$) : The sheet will calculate the AUD amount of allowance paid over the counter by the hotel based on what you entered into the previous twe columns. But you only used one of those columns – correct?

(M) Done : If the spreadsheet has enough information to calculate – there will be an Ok in this column. Otherwise it will be blank and you should go back and see what has been done wrong.

(N) Days : This is the number of days you were on station, rounded up to the next whole day. This number forms the basis for the Incidental Allowance calculation, and the Day of Meals calculation for International Layovers.

Ground Duty not in SYD.

Here is an example of how a ground duty somewhere other than SYD is claimed. Basically you claim from the time you board the flight in SYD, until the time you get off the flight back into SYD after ground training in BNE, three days later, or whatever.

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

General Use Allowance Calculator

I have recently been looking at the allowances paid down route to us in LAX, with a view to developing and easy way to identify a discrepancy in what we should be paid; and to re-calculate what the difference should be in the event of an early arrival or delayed departure. Accordingly, I have developed this spreadsheet to be used for this purpose.

Note that you can run this sheet on MS Excel for iPad (as well as PC/mac, etc) although the “Clear” and “Sort” buttons won’t work on IOS.

When complete, the sheet looks like the image below. The blue sections are where the user enters information. The port is entered at the top (currently supporting BNE/LAX/MEL/SYD) and you can compare scheduled with actual to see changes in the allowances.

Note …

  • The On Blocks Date/Time (arrival) and Off Blocks Date/Time (departure) values must be entered in the same time zone so a meaningful total days/hours value can be calculated.
  • While the ATO pays a per day allowance based on meals/incidentals (so if you go 1 minute into a day, you get the full days allowance) the company only pays meal windows and incidental hours actually you touch with your off duty down route time.
  • Note that early arrivals and therefore early sign off’s should generate additional allowances (where relevant).
  • Delayed departures that do not result in delayed sign on do not incur additional allowances – you need to have your return sector sign on delayed to achieve additional allowances.
  • While the CSP’s (A1) specify 80 minutes between Sign On and Off Blocks for all international departures (from Oz and elsewhere); the company has increased this to 90 minutes for LAX departures.
  • The values in the current sheet are relevant for Pilots under the current EBA; Cabin Crew will need to amend the values in the data sheet.

GenOver7

There is a second data sheet you can use to update/amend the values the sheet uses to calculate:

GenOver6

Tax Time : Crew Allowances 2012/2013

It’s Tax Time again 2012/2013 and as since I’m one of those lazy people who does all the work at the end, instead of keeping up with it as it goes along – the first thing I need to do is update my Allowance calculator spreadsheet. I’m posting a copy of the sheet here for?you guys to download because each year more and more crew ask for a copy and I can’t remember who’s asked for it and who hasn’t. Note the Taxation Determination for FY2013-14 can be found here.

Note : My accountant is now saying that after discussions with the ATO, he is not recommending claiming more than the Company pays in allowances?without receipts. Therefore while I’ve still prepared

Note that this article is a follow on from the original article which covers the basics of the relevant legislation – and more importantly, how to use the spreadsheet.