Aged Receviables

Overview

This Tab shows the net movement in Receivables over the year. The total movement shown should agree with that shown in the ‘Customer & Suppliers’ Tab.

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Columns at end of Receivables Table:

The columns at the end of this Receivables table show:

Cash Amounts & Cash Dates

These columns try to match up Cash Amounts to Invoices (if matching references have been given to both the Invoice line, and relevant Cash Flow line).

The Cash Dates shown here are the first Dates of any Cash Payments or Receipts that might have been made (e.g. if there are two separate Cash lines that pay towards the same Invoice.)

The Invoice Amount O/S

This column shows what amounts are outstanding - that is how much of each Invoice has not been paid.

Total Receivables due

These columns show the:

(i) V.A.T. Due; (ii) Receivables Due (excluding V.A.T.); and then (iii) Total Receivable Due, at the End of the Month (E.o.M.)

Formulae used in these columns have been showed below:

V.A.T. Due Formula

MS Excel 
    =LET(
    amount_vat,[@VAT], tax_date, [@[Tax Point Date]], transaction, [@Transaction],
    start_date_month, EOMONTH(TODAY(),-1) + 1, end_date_month, EOMONTH(start_date_month, 0),
    date_matrix_vat,(tax_date<=end_date_month), amount_matrix_vat, amount_vat*date_matrix_vat,
    amount_total, IF(OR(transaction= "S/L- Invoice", transaction = "P/L - Invoice"), amount_matrix_vat, 0),
    amount_total)

Receivables Due Formula

MS Excel 
    =LET(
    amount_net, [@Net], due_date, [@[Cash Due Date]], start_date, [@[Start Date]], end_date, [@[End Date]], invoice_cash_date, IF([@[Invoice Date / Cash Date]] = 0, 2958465,[@[Invoice Date / Cash Date]]), contract, [@[Contract Type]], transaction, [@Transaction], start_date_month, EOMONTH(TODAY(),-1) + 1, end_date_month, EOMONTH(start_date_month, 0), start_date_previous_month, EOMONTH(start_date_month, -2) + 1, end_date_previous_month, EOMONTH(start_date_month, -1),
    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)),
    recognition_date_matrix, (recognition_date <= end_date_month),
    amount_matrix_net, amount_net*recognition_date_matrix,
    amount_matrix_net_pro_rated, amount_net*((end_date_month >= start_date)*1*IF(end_date <= end_date_month, end_date, end_date_month) - start_date + 1) / (end_date - start_date + 1) * recognition_date_matrix,
    amount_result, IF(OR(transaction = "S/L- Invoice", transaction = "P/L - Invoice"), IF(contract = "Cancellable", amount_matrix_net_pro_rated, amount_matrix_net), 0),
    amount_result)

Total Receivables Due Formula

MS Excel 
=[@[VAT Due '@ EoM]]+[@[Receivable Due '@ EoM]]

Cash Due Date

This column shows the Cash Due Date for each Invoice. This will vary depending on whether the Contract is ‘Cancellable’, or ‘Non-Cancellable’

The Formula used in this column has been showed below:

Cash Due Date Formula

MS Excel 
=IF([@[Contract Type]] = "Non-Cancellable", MIN([@[End Date]], [@[Due Date]]), IF([@[Contract Type]] = "Cancellable", [@[Due Date]], [@[Invoice Date / Cash Date]]))

Aged Receivables

These columns show how old the Receivables are based on their Cash Due Date.

Formulae used in these columns have been showed below:

Not Yet Due

MS Excel 
=IF(TODAY()-[@[Cash Due Date]]<=0,[@[Total Receivable '@ EoM]],0)

0-30 Days

MS Excel 
=IF(AND(TODAY()-[@[Cash Due Date]]<=30,TODAY()-[@[Cash Due Date]]>0),[@[Total Receivable '@ EoM]],0)

30-60 Days

MS Excel 
=IF(AND(TODAY()-[@[Cash Due Date]]<=60,TODAY()-[@[Cash Due Date]]>30),[@[Total Receivable '@ EoM]],0)

60-90 Days

MS Excel 
=IF(AND(TODAY()-[@[Cash Due Date]]<=90,TODAY()-[@[Cash Due Date]]>60),[@[Total Receivable '@ EoM]],0)

Older

MS Excel 
=IF(TODAY()-[@[Cash Due Date]]>90,[@[Total Receivable '@ EoM]],0)

Total Cash Amounts

This column shows all the Cash Receipts or Cash Payments that have gone through the Sales or Purchase Ledger.

The Formula used in this column has been shown below:

Total Cash Amounts

MS Excel 
=IF(AND(OR([@Transaction] = "Co. Bank Receipt", [@Transaction] = "Co. Bank Payment"), OR([@Ledger] = "P/L", [@Ledger] = "S/L")), [@[Inv Amount / Cash Amount]], 0)

Unbilled Receivables

This column shows all Receivables that have been recognised in the Accounts, but where no Invoice has yet been raised (e.g. for any Invoice that might have been raised much after the end date of the Work Period specified in that Invoice).

The Formula used in this column has been shown below:

Unbilled Receivable Formulae

MS Excel 
=LET(
transaction, [@Transaction], contract, [@[Contract Type]], invoice_cash_date, IF([@[Invoice Date / Cash Date]] = 0, 2958465,[@[Invoice Date / Cash Date]]), due_date, [@[Due Date]], receivable_due_at_EoM, [@[Total Receivable '@ EoM]], start_date, [@[Start Date]], end_date, [@[End Date]],
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)),
unbilled_receivable, IF(AND(invoice_cash_date > recognition_date, invoice_cash_date > TODAY()), receivable_due_at_EoM,  0),
result, IF( OR(transaction = "S/L- Invoice", transaction = "P/L - Invoice"), unbilled_receivable, 0), result)

Other comments on the Receivable Table

The financial data in this Table is linked from the other sheets in this Workbook, so there should be nothing additional you have to do in this sheet.

You may also have to occassional reset the filter (i.e. de-select, and reselect it), as new transactions are added, as the table will not automatically do this - you need to refresh the ‘Ledger’ column filter.