Combined

Overview

This tab combines the datasets from the ‘Accrual’ and ‘Deferral’ Tabs. This is so that both Deferrals, and Accruals, even though calculated in different ways, can be seen in one Financial Dataset.

Click to view large-sized image

Trade Debtor / Creditor, and V.A.T. movements are also shown, in this Tab:

Click to view large-sized image

Click to view large-sized image

Formulae used

Trade Debtor / Creditor movements

The following logic is used to calculate the movement in Trade Debtors / Creditors

MS Excel 
=LET(
    amount_net, Table_Combined[@[Net]:[Net]], due_date, Table_Combined[@[Cash Due Date]:[Cash Due Date]], start_date, Table_Combined[@[Start Date]:[Start Date]], end_date, Table_Combined[@[End Date]:[End Date]], invoice_cash_date, IF(Table_Combined[@[Invoice Date / Cash Date]:[Invoice Date / Cash Date]] = 0, 2958465, Table_Combined[@[Invoice Date / Cash Date]:[Invoice Date / Cash Date]]), contract, Table_Combined[@[Contract Type]:[Contract Type]], ledger, Table_Combined[@[Ledger]:[Ledger]], start_date_month, AN$3, 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 >= start_date_month)*(recognition_date <= end_date_month),
    amount_matrix_net, amount_net*recognition_date_matrix,
    recognition_date_matrix_pro_rated, recognition_date <= end_date_month,
    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_pro_rated,
    recognition_date_matrix_pro_rated_previous_month, recognition_date <= end_date_previous_month,
    amount_matrix_net_pro_rated_previous_month, amount_net*((end_date_previous_month >= start_date)*1*IF(end_date <= end_date_previous_month, end_date, end_date_previous_month) - start_date + 1) / (end_date - start_date + 1) * recognition_date_matrix_pro_rated_previous_month,
    amount_matrix_net_pro_rated_movement, amount_matrix_net_pro_rated - amount_matrix_net_pro_rated_previous_month,
    amount_result, IF(OR(ledger = "S/L", ledger = "P/L"), IF(contract = "Cancellable", amount_matrix_net_pro_rated_movement, amount_matrix_net), 0),
    -amount_result)

V.A.T. Account movements

The following logic is used to calculate the movement in the V.A.T. Account.

MS Excel 
=LET(
    amount_vat,Table_Combined[@[VAT]:[VAT]], tax_date, Table_Combined[@[Tax Point Date]:[Tax Point Date]], ledger, Table_Combined[@[Ledger]:[Ledger]],
    start_date_month, AZ$3, end_date_month, EOMONTH(start_date_month, 0),
    date_matrix_vat,(tax_date>=start_date_month)*(tax_date<=end_date_month), amount_matrix_vat, amount_vat*date_matrix_vat,
    amount_total, IF(OR(ledger= "S/L", ledger = "P/L"), amount_matrix_vat, 0),
    amount_total)

Next

Click next to see the working set of example Management Accounts.