Deferrals
Overview
As with the ‘Accruals’ Tab, the ‘Deferrals’ Tab workbook tries to make the task of calculating and showing any Deferred Revenue or Deferred Expense as hassle free as possible.
Below are some screen shots:
Click to view large-sized image
There are quite a few deferred expenses (for supplier invoices received and paid in advance of the given service). For example, you can see that a Rent invoice was raised for £1,050, sometime in February, but which applies to Rent that covers the period from between the middle of February to the middle of March – so some of this expense needs to be deferred into March.
This is exactly what the Profit & Loss columns show:
Click to view large-sized image
An amount of £289.66, has been charged in February, and the remaining £760.34 has been charged in March.
As also expected, there is a Deferred Expense balance at the end of February, of £760.34 in February.
Click to view large-sized image
You have to be careful that the Table doesn’t run out of rows, as the number of transactions grow. If this happens, just drag and expand the Table from the bottom in order to add more rows.
Formulae used
Deferred Revenue / Expense
The following logic is used to calculate the Revenue / Expense Charge to the Profit & Loss. It can easily be customised for your own specific accounting logic, if they depart from the normal rules:
=IFERROR(LET(
start_date, Table_Deferrals[@[Start Date]:[Start Date]], end_date, Table_Deferrals[@[End Date]:[End Date]], days, MAX(end_date-start_date+1, 0), date_array, SEQUENCE(1,days,start_date,1), amount,Table_Deferrals[@[Net]:[Net]], invoice_cash_date, IF(Table_Deferrals[@[Invoice Date / Cash Date]:[Invoice Date / Cash Date]] = 0, 2958465, Table_Deferrals[@[Invoice Date / Cash Date]:[Invoice Date / Cash Date]]), accrual_type, Table_Deferrals[@[Type]:[Type]],
start_date_month, AB$16, end_date_month,DATE(YEAR(start_date_month),MONTH(start_date_month)+1,0),
date_matrix, (TRANSPOSE(date_array)>=start_date_month)*(TRANSPOSE(date_array)<=end_date_month),
days_in_month, TRANSPOSE(MMULT(TRANSPOSE(date_matrix),SEQUENCE(ROWS(date_matrix),1,1,0))),
pnl_amount, days_in_month/days*amount,
rev_point_in_time_test, AND(invoice_cash_date >= start_date_month, invoice_cash_date <=EOMONTH(start_date_month,0)),
acc_def_test, OR(accrual_type ="Acc/Def Revenue", accrual_type ="Acc/Def Expense"),
pnl_result, SWITCH(TRUE(), acc_def_test, pnl_amount, rev_point_in_time_test, amount, 0), pnl_result), "")
Deferred Balance
The following is used to calculate the Deferred Revenue / Deferred Expense Balance for the Balance Sheet. This can also be customised for your own specific accounting logic, if they depart from the normal rules:
=IFERROR(LET(
amount,Table_Deferrals[@[Net]:[Net]], due_date, Table_Deferrals[@[Cash Due Date]:[Cash Due Date]], contract, Table_Deferrals[@[Contract Type]:[Contract Type]],
invoice_cash_date, IF(Table_Deferrals[@[Invoice Date / Cash Date]:[Invoice Date / Cash Date]] = 0, 2958465, Table_Deferrals[@[Invoice Date / Cash Date]:[Invoice Date / Cash Date]]), deferral_type, Table_Deferrals[@[Type]:[Type]], start_date, Table_Deferrals[@[Start Date]:[Start Date]], end_date, Table_Deferrals[@[End Date]:[End Date]], start_date_month, AN$16, end_date_month, EOMONTH(start_date_month, 0),
recognition_date, IF(contract = "Non-Cancellable", MIN(end_date, due_date), IF(contract = "Cancellable", MAX(MIN(end_date, invoice_cash_date), start_date), invoice_cash_date)),
deferred_amount, (end_date_month < end_date)*1*(end_date - (IF(end_date_month < start_date, start_date, end_date_month + 1) ) + 1) / (end_date - start_date + 1) * amount,
initial_recognition_result, IF(recognition_date >= end_date_month + 1, 0, deferred_amount),
deferred_test, OR(deferral_type = "Acc/Def Revenue", deferral_type = "Acc/Def Expense"),
deferred_BS, SWITCH(TRUE(), deferred_test, IF(contract = "Cancellable", 0, initial_recognition_result), 0), deferred_BS),"")
New Revenue Rules
Important:
IFRS 15 vs IAS 18
In 2018, there was a slight change to the accounting recognition of revenue
I.F.R.S. 15 replaced I.A.S. 18, as the main revenue recognition standard.
The standard has more complex rules around when revenue can be recognised. For example, performance obligations to a contract have to be separately identified, and unlike IAS 18, any contract liability (deferred revenue) and trade receivable, needs to be shown net until there is an unconditional right to payment. This is usually the earlier of either:
- The date the payment becomes due (i.e. when the ‘receivable’ is recognised), or
- The date the goods or services are delivered (i.e. when a ‘contract asset’ (accrued revenue) is recognised).
Whether or not an invoice has been issued, does not dictate whether or not an entity has an unconditional right to receive payment.
For cancellable contracts, an unconditional right to receive payment usually arises when all performance obligations have been met and an invoice has been raised. This is when a receivable will usually be recognised.
But an invoice doesn’t always have to be raised for there to be an unconditional right to payment. For example, if an invoice is sent much after all performance obligations have been completed, an unbilled receivable can still be recorded. Equally, if an invoice is raised in advance of the start of any performance obligation, a receivable should only be recognised if there was an unconditional right to receive payment.
The I.F.R.S. 15 rules further stipulate that in the case of non-cancellable contracts, a receivable can be recognised even if performance obligations have not been met, or even if they have not even started. In such cases, a receivable can be recognised on the cash payment due date stipulated in the contract.
Under I.A.S. 18, the rules were simpler, and deferred revenue and trade receivables could be recognised ‘Gross’ on the balance sheet. Under I.F.R.S. 15, they need to be recognised ‘Net’, until there is an unconditional right to payment.
Example
To explain the differences between I.A.S. 18 and I.F.R.S. 15 further, if as a company, you invoice in full at the start (say 1 January 2020), for a 2 year maintenance service contract, totalling £24,000, which is payable in full on 31 March 2020, you would make the following entries:
I.A.S. 18
Under I.A.S. 18, the following entries would be made:
1 January:
Dr Receviable £24,000
Cr Deferred Revenue £24,000
31 January:
Dr Deferred Revenue £1,000
Cr Revenue £1,000
28 February:
Dr Deferred Revenue £1,000
Cr Revenue £1,000
31 March:
Dr Deferred Revenue £1,000
Cr Revenue £1,000
I.F.R.S. 15
Under I.F.R.S. 15, the following entries would be made:
1 January:
Dr -
Cr -
31 January:
Dr Contract Asset (Accrued Revenue) £1,000
Cr Revenue £1,000
28 February:
Dr Contract Asset (Accrued Revenue) £1,000
Cr Revenue £1,000
31 March:
Dr Contract Asset (Accrued Revenue) £1,000
Cr Revenue £1,000
Dr Receivable £24,000
Cr Contract Asset (Accrued Revenue) £3,000
Cr Contract Liability (Deferred Revenue) £21,000
Important:
The example was taken from here: B.D.O. I.F.R.S. 15 - Revenue Example - non-cancellable contracts
How do the New Rules affect this workbook?
This Tab has been designed to accommodate the new rules.
Cancellable Contracts:
For Cancellable Contracts, amounts get recognised based on the Invoice Date.
-
If an Invoice gets raised before the Period of Work, then a Receivable/Payable will recognised from when the Period of Work Starts, and build up each month to the Total Amount till the end of the Period of Work.
-
If an Invoice gets raised some time during the Period of Work, then when the Period of Work Starts an Accrued Revenue/Expense will get recognised, uptil the date of the Invoice gets raised. On this date the Accrual will then get reversed to a Receivable/Payable, which will then build up each month to the Total Amount till the end of the Period of Work.
-
If an Invoice gets raised after the Period of Work, then an Accrued Revenue / Expense will get recognised till the end of the Period of Work. On the end date of the Period of Work, the full amount of the Accrual will then get reversed to an Unbilled Receivable / Payable.
Non-Cancellable Contracts:
For Non-Cancellable Contracts, amounts get recognised based on the Cash Due Date.
-
If the Cash Due Date is before the Period of Work, then the Full Amount of Receivable / Payable will get recognised on this date, along with a Deferred Revenue / Expense. The Deferral will then gradually reduce to Nil each month till the end of the Period of Work.
-
If the Cash Due Date occurs at some time during the Period of Work, then when the Period of Work Starts, an Accrued Revenue / Expense will get recognised, uptil the date of the Cash Due Date. On this date the Accrual will then get reversed to the Full Amount of the Receivable / Payable, along with any Deferred Revenue / Expense. The Deferral will then gradually reduce to Nil each month till the end of the Period of Work.
-
If the Cash Due Date occurs after the Period of Work, then Accrued Revenue/Expense will get recognised over the Period of Work. At the end of the Period of Work, this will get reversed to the Full Amount of the Receivable / Payable resulting in an Unbilled Receivable / Payable.
More information
More information about the IFRS 15 and IAS 18 standards can be found here:
Feedback
Submit and view feedback