Aged Payables

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 Payables Table:

The columns at the end of this Payables 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 Payables due

These columns show the:

(i) V.A.T. Due; (ii) Payables Due (excluding V.A.T.); and then (iii) Total Payables 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)

Payable 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 Payable Due Formula

MS Excel 
=[@[VAT Due '@ EoM]]+[@[Payable 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 Payables

These columns show how old the Payables 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 Payable '@ EoM]],0)

0-30 Days

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

30-60 Days

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

60-90 Days

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

Older

MS Excel 
=IF(TODAY()-[@[Cash Due Date]]>90,[@[Total Payable '@ 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 Payables

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

The Formula used in this column has been shown below:

Unbilled Payables 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]], payable_due_at_EoM, [@[Total Payable '@ 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_payable, IF(AND(invoice_cash_date > recognition_date, invoice_cash_date > TODAY()), payable_due_at_EoM,  0),
result, IF( OR(transaction = "S/L- Invoice", transaction = "P/L - Invoice"), unbilled_payable, 0), result)

Other comments on the Payable 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.