Monthly Transactions
Overview
The monthly transaction Tabs are where (almost) all daily and weekly transactions should be recorded. There are 12 of them, one for each month.
January example
In January, (following the example set of accounts in this guide), 15 separate transactions were recorded in the month.
These are shown in the pictures below:
Click to view large-sized image
Click to view large-sized image
Click to view large-sized image
Info: The red headed columns: V.A.T., Net, and Gross are calculated by the formulae that have been created, and do not need to be filled in. Any Monetary amount should be filled in the column called Inv Amount / Cash Amount.
Transaction table
Although there are other tables shown in the sheet (such as the ones that show a summary of the Monthly V.A.T., and Cash Movements in the month), only one table needs to be completed - the main Transaction Table shown in the middle part of the sheet, as shown below:
Click to view large-sized image
Transactions
Below, we go through a sample of the transactions that were made in January, to better explain what is going on.
Jan - 1:
As shown from the screenshots above, this transaction is for the purchase of a 6-month subscription of 1000 G.B. on data storage space from A.W.S. (Amazon Web Services). Field inputs were set as follows:
| Field | Description |
|---|---|
| Invoice / Cash Date | This is 18/01/2020 – the date on the invoice |
| Tax Point Date | This is 18/01/2020 – this is calculated by the created formula |
| Start Date | This is 18/01/2020 - the date the service starts |
| End Date | This is 17/07/2020 - the date the service ends |
| Transaction | This is a purchase Invoice, so: ‘P/L – Invoice’ is selected (to indicate this is an Invoice from a Supplier) |
| Contract Type | This is Non-Cancellable, meaning that the Amount, if the Due Date has passed, cannot be refunded. |
| Cash Due Date | This is 45 days after the date of the Invoice (and is when the Cash should be paid) |
| Account | This is Purchasing Agent, to indicate who the amount needs to be paid to (in this case to a Purchasing Agent, who paid for the amount, on behalf of the Company) |
| Ledger | This is set as ‘P/L’, as the transaction goes to the Purchasing Ledger (the company now owes money to the Purchasing Agent) |
| Description | This is “Subscription of 1000 G.B. on A.W.S.” |
| Analysis | The expense relates to ‘I.T. costs’ to show what the amount was spent on (in this case an Information Technology related cost) |
| Taxable Benefit? | This is to indicate whether the amount is a Taxable Benefit or not - which here it is not. |
| Status | This is to indicate whether the Amount has been paid or not - (but it is not linked to anything) |
| Ref | This is where the Invoice Reference Number should be recorded. |
| V.A.T. Rate | This is 20%, as the expense is 20% V.A.T. chargeable |
| Invoice / Cash Amount | This is entered as £15.99, as the total cost, including V.A.T., of the service |
| V.A.T. Amount | This is calculated by the formula created, as £2.67 |
| Net Amount | This is calculated as £13.33 |
| Gross Amount | This is calculated as £15.99 |
| Type | As the expense related to work that is carried out over several months, the option: ‘Acc/Def Expense’ should be selected, so the Accrued / Deferred aspects properly show in the P&L, and B/S |
Jan - 2:
This is payment by the Purchasing Agent, of the previous year’s Annual Confirmation Statement (for 2019, but paid by the Purchasing Agent in January 2020). Field inputs were set as follows:
| Field | Description |
|---|---|
| Invoice / Cash Date | This is 01/01/2020 – the date on the invoice |
| Tax Point Date | This is 01/01/2020 – this is calculated by the created formula |
| Start Date | This is Not Applicable - (as this is a one-off payment) |
| End Date | This is Not Applicable - (as this is a one-off payment) |
| Transaction | This is a Purchase Invoice, so: ‘P/L – Invoice’ is selected |
| Contract Type | Nothing needs to be selected, as this is a one-off transaction |
| Cash Due Date | This is 30 Days after the Due Date |
| Account | This is set as Purchasing Agent, as the invoice was paid by the Purchasing Agent, with details then forwarded by the Agent to the Company |
| Ledger | This is set as ‘P/L’, as the transaction goes to the Purchasing Ledger (the company now owes the Purchasing Agent) |
| Description | This is “2019 Annual Confirmation Statement” |
| Analysis | This is ‘Legal and Professional’ to show what type of cost this expense was for |
| Taxable Benefit? | This is to indicate whether the amount is a Taxable Benefit or not - which here it is not. |
| Status | This is to indicate whether the Amount has been paid or not - (but it is not linked to anything) |
| Ref | This is where a reference if available should be recorded. |
| V.A.T. Rate | This is 0%, as the expense is not V.A.T. chargeable |
| Invoice / Cash Amount | This is entered as £13.00, as the total cost, including V.A.T., of the service |
| V.A.T. / Net / Gross Amounts | These are automatically calculated from the other fields in the record |
| Type | Not applicable as a one-off charge. |
Jan - 3:
This is a Sales Invoice raised to Toyota, for work done over 2 weeks in January. Field inputs were set as follows:
| Field | Description |
|---|---|
| Invoice / Cash Date | This is 01/01/2020 – the date on the invoice |
| Tax Point Date | This is 15/01/2020 – and calculated from the created formula |
| Start Date | This is 02/01/2020 – the date the work started |
| End Date | This is 14/01/2020 – the date the work ended |
| Transaction | This time, this is a sales Invoice, so: ‘S/L – Invoice’ is selected |
| Contract Type | This is Non-Cancellable, meaning that the Amount, if the Due Date has passed, cannot be refunded. |
| Cash Due Date | This is 13/07/2020 – about 6 months after the Invoice Date |
| Account | This is Toyota, as the invoice was raised and sent to them |
| Ledger | This is set as ‘S/L’, as the transaction goes to the Sales Ledger (Toyota now owes the company) |
| Description | This is “Toyota Sales” |
| Analysis | This is ‘Sales’ to indicate the line relates to Sales Income |
| Taxable Benefit? | This is to indicate whether the amount is a Taxable Benefit or not - which here it is not. |
| Status | This is to indicate whether the Amount has been paid or not - (but it is not linked to anything) |
| Ref | This is where the Invoice Reference Number should be recorded. |
| V.A.T. Rate | This is 20%, as the service was 20% V.A.T. chargeable |
| Invoice / Cash Amount | This is entered as £144 (inclusive of V.A.T.) |
| V.A.T. / Net / Gross Amounts | These are automatically calculated from the other fields in the record |
| Type | As the expense related to work that is carried out over several months, the option: ‘Acc/Def Expense’ should be selected, so the Accrued / Deferred aspects properly show in the P&L, and B/S |
Jan - 7:
This is the payment of N.I.C.s due to H.M.R.C., following the payment of Salary in the month (shown as a separate transaction in the Table). Field inputs were set as follows:
| Field | Description |
|---|---|
| Invoice / Cash Date | This is 20/01/2020 – the date the cash payment was made |
| Tax Point Date | This is 15/01/2020 – and calculated from the created formula |
| Start Date | This is Not Applicable - (as this is a one-off payment) |
| End Date | This is Not Applicable - (as this is a one-off payment) |
| Transaction | This is set as ‘Co. Bank Payment’, as the transaction was done by cash (there was no invoice) |
| Contract Type | Nothing needs to be selected, as this is a one-off transaction |
| Cash Due Date | As a Cash Transaction itself, nothing needs to be selected |
| Account | This is set as H.M.R.C. N.I.C.s to show what the payment relates to |
| Analysis | This is ‘HMRC PAYE / NIC’ |
| Taxable Benefit? | This is to indicate whether the amount is a Taxable Benefit or not - which here it is not. |
| Status | This is to indicate whether the Amount has been paid or not - (but it is not linked to anything) |
| Ref | This is where the Invoice Reference Number should be recorded, but it is not applicable in this case. |
| Invoice / Cash Amount | As this is a cash payment, this is entered into as a negative number |
| V.A.T. / Net / Gross Amounts | These are automatically calculated from the other fields in the record |
| Type | Not applicable for Cash Payments. |
Jan - 9:
The is the receipt of cash for the work that had been done, and when the sales invoice had been raised and sent to Toyota. Field inputs were set as follows:
| Field | Description |
|---|---|
| Invoice / Cash Date | This is 20/01/2020 – the date the Cash was received |
| Tax Point Date | This is 20/01/2020 – and calculated from the created formula |
| Start Date | This is Not Applicable - (as this is a one-off payment) |
| End Date | This is Not Applicable - (as this is a one-off payment) |
| Transaction | This is set as ‘Co. Bank Receipt’, as the transaction was done by cash (there was no invoice) |
| Contract Type | Nothing needs to be selected, as this is a one-off transaction |
| Cash Due Date | As a Cash Transaction itself, nothing needs to be selected |
| Account | This is Toyota, as the cash is received from them |
| Ledger | This is set as ‘S/L’, as the Transaction now goes to the Sales Ledger, to cancel out what was previously owed by Toyota |
| Description | This is “Toyota payment” |
| Analysis | This is ‘Sales Payment’ to indicate the line relates to the receipt of Cash relating to Sales Income |
| Taxable Benefit? | This is to indicate whether the amount is a Taxable Benefit or not - which here it is not. |
| Status | This is to indicate whether the Amount has been paid or not - (but it is not linked to anything) |
| Ref | This is where the Invoice Reference Number should be recorded, but it is not applicable in this case. |
| V.A.T. Rate | This is ‘Non VAT able’ – as this is a cash receipt, V.A.T. is not relevant |
| Invoice / Cash Amount | This is entered as (£144.00) – the actual amount of cash received. All cash transactions (whether receipt or payment) should be entered in as a negative number. |
| V.A.T. / Net / Gross Amounts | These are automatically calculated from the other fields in the record |
| Type | Not applicable for Cash Receipts. |
Jan - 15:
The is the payment of cash to purchase a Laptop (Fixed Asset) from the Apple Store.
The record-keeping of Fixed Assets is done in a separate tab in the workbook, but the details of the Fixed Asset Purchase needs to be recorded here.
Field inputs were set as follows:
| Field | Description |
|---|---|
| Invoice / Cash Date | 10/01/2020 – this is the date the Cash was paid |
| Tax Point Date | This is 10/01/2020 – and calculated from the created formula |
| Start Date | This is Not Applicable - (as this is a one-off payment) |
| End Date | This is Not Applicable - (as this is a one-off payment) |
| Transaction | This is set as ‘Co. Bank Payment’, as the transaction was done by cash (there was no invoice) |
| Contract Type | Nothing needs to be selected, as this is a one-off transaction |
| Cash Due Date | As a Cash Transaction itself, nothing needs to be selected |
| Account | This is Apple, as the cash is paid to them |
| Ledger | This is set as ‘P/L’, as the Transaction now goes to the Purchase Ledger, to cancel out the amount recorded from the line, where the Purchase Invoice had been recorded |
| Description | This is “Laptop Payment” |
| Analysis | This is ‘Office Equipment’ |
| Taxable Benefit? | This is to indicate whether the amount is a Taxable Benefit or not - which here it is not. |
| Status | This is to indicate whether the Amount has been paid or not - (but it is not linked to anything) |
| Ref | This is where the Invoice Reference Number should be recorded, but it is not applicable in this case. |
| V.A.T. Rate | This is ‘Non VAT able’ – as this is a cash payment, V.A.T. is not relevant to this line |
| Invoice Amount | This is entered as (£1,952.40) - the actual amount of cash paid. All cash transactions (whether receipt or payment) should be entered in as a negative number. |
| V.A.T. / Net / Gross Amounts | These are automatically calculated from the other fields in the record |
| Type | Not applicable for Cash Payments. |
Hopefully, now you get the gist of how to fill in the Transaction Table!
Cash Date and Cash Amount
Note that the last two columns in each Transaction table try to show the Cash Date, and Cash Amounts paid or received for each Invoice. That is, through M.S. Excel formulae, they try to match up for each Invoice Line, the amount of Cash that has actually been paid or received, to that particular Invoice.
The formula will only work, if the reference to the Invoice that is shown in the ‘Ref’ column has been recorded in the Cash Payment or Receipt lines, that the Invoice relates to - so the Cash Amounts can actually be linked back.
The formula will also only work if a Cash Amount wholly pays an Invoice. or when a Cash Amount wholly pays an Invoice and other Invoices, where the other Invoices aren’t partially paid by other Cash Amounts on other lines.
The Cash due date shown on the Invoice line will also only show the first Cash date, if for example, there happen to be two separate Cash payments that pay the same Invoice.
If in doubt, the Formulas that have been created can always be manually overridden, with the most appropriate Dates and Cash Amounts.
The respective formulae used have been show below:
Cash Date Formula
= LET(cash_date, [@[Invoice Date / Cash Date]], transaction, [@Transaction], reference, [@Ref], account,[@Account],
cash_date_matched, XLOOKUP(1, ((Table_Combined[Transaction]="Co. Bank Receipt") + (Table_Combined[Transaction]="Co. Bank Payment")) * (ISNUMBER(SEARCH(reference,Table_Combined[Ref]))) * (TRIM(account)=TRIM(Table_Combined[Account])), Table_Combined[Invoice Date / Cash Date], "not found", 1, 1),
cash_date_found, IF(ISBLANK(reference), "", cash_date_matched ),
IF(NOT(OR(transaction="Co. Bank Receipt", transaction="Co. Bank Payment")), cash_date_found, ""))
Cash Amount Formula
= LET(cash_date, [@[Invoice Date / Cash Date]], transaction, [@Transaction], reference, [@Ref], account,[@Account],
cash_amount_exact_match, ABS(SUMPRODUCT(Table_Combined[Inv Amount / Cash Amount],--((Table_Combined[Transaction]="Co. Bank Receipt")+(Table_Combined[Transaction]="Co. Bank Payment")), --(Table_Combined[Ref]=reference), --(TRIM(Table_Combined[Account])=TRIM(account)))),
cash_amount_partial_matches, ABS(SUMPRODUCT(Table_Combined[Inv Amount / Cash Amount],--((Table_Combined[Transaction]="Co. Bank Receipt")+(Table_Combined[Transaction]="Co. Bank Payment")), --((ISNUMBER(SEARCH(reference,Table_Combined[Ref])))), --(TRIM(Table_Combined[Account])=TRIM(account)))),
cash_amount_other_matches, cash_amount_partial_matches - cash_amount_exact_match,
cash_row_match,IF( ((Table_Combined[Transaction]="Co. Bank Receipt")+(Table_Combined[Transaction]="Co. Bank Payment")) * ISNUMBER(SEARCH(reference,Table_Combined[Ref])) * (TRIM(account)=TRIM(Table_Combined[Account])),ROW(Table_Combined[Ref])-ROW(Table_Combined[[#Headers],[Ref]]),0), cash_row_match_filtered, FILTER(cash_row_match, cash_row_match<>0), cash_row_reference_match, UNIQUE(INDEX(Table_Combined[Ref], cash_row_match_filtered)), cash_reference_match_joined, TEXTJOIN("; ",,cash_row_reference_match),
unique_cash_ref_match, UNIQUE(TRIM(MID(SUBSTITUTE(cash_reference_match_joined,";",REPT(" ",100)),SEQUENCE(10,,1,100),100))),
other_linked_invoice_references, FILTER(unique_cash_ref_match, (unique_cash_ref_match<>"")*(unique_cash_ref_match<>reference)),
invoice_amount, [@[Inv Amount / Cash Amount]],
invoice_amounts_linked_to_other_cash_matches, SUM(SUMIFS(Table_Combined[Inv Amount / Cash Amount], Table_Combined[Transaction], "<>Co. Bank Receipt", Table_Combined[Transaction], "<>Co. Bank Payment", Table_Combined[Ref], other_linked_invoice_references, Table_Combined[Account], TRIM(account))),
left_over_invoice_amount, invoice_amount - cash_amount_exact_match,
apportioned_cash_to_this_invoice_from_other_cash_matches, (left_over_invoice_amount)/(invoice_amounts_linked_to_other_cash_matches + left_over_invoice_amount) * cash_amount_other_matches,
total_allocated_cash, cash_amount_exact_match + IFERROR(apportioned_cash_to_this_invoice_from_other_cash_matches, 0),
total_allocated_cash_display, IF(ISBLANK(reference), "", total_allocated_cash ),
IF(NOT(OR(transaction="Co. Bank Receipt", transaction="Co. Bank Payment")), total_allocated_cash_display, ""))
V.A.T. table
For every Monthly Transaction Table, a summary of the Input and Output V.A.T. is also shown at the bottom of the sheet, like shown below for January:
Click to view large-sized image
The figures are calculated through formula from the other tabs in the WorkBook. The table is needed to complete the information in the ‘V.A.T. Returns’ Tab, to correctly submit any V.A.T. returns.
Bank table
A summary of the Opening and Closing Cash Positions, and Cash Payments and Cash Receipts in the month, are also shown at the top of the sheet, as shown below for the month of January:
Click to view large-sized image
Tax Point Date
Important:
Rules: Tax Point Date: There are specific Tax rules from H.M.R.C. about what this date should be set as, depending on whether a service or good has been provided, and on whether the invoice has raised in advance (before providing the good or service) or arrears (after providing the good or service).
In general, the Tax Point Date should be set as the earlier of: the Date of Cash Payment, Date of Invoice, or Date of Supply (whichever is earlier). If an Invoice is issued in arrears, after the Date of Supply, the Tax Point Date can be the Date of the Invoice (that is, when it is after the Date of Supply) but only when the Date of Invoice is 14 or less days after the Date of Supply. If it is more, the Tax-Point Date becomes the Date of Supply (or Date of Cash Payment if that was even earlier).
Tax Point Date Rules
The rules (as of the time of writing this article!) are summarised here:
In any supply, there are four relevant stages to bear in mind: 1) Making goods available, 2) Completion of a service, 3) Invoicing, and 4) Payment:
| Situation | Tax Point |
|---|---|
| No invoice needed | Date of supply |
| V.A.T. invoice issued | Date of invoice |
| V.A.T. invoice issued 15 days or more after the date of supply | Date supply took place |
| Payment in advance of supply and no V.A.T. invoice yet issued | Date payment received |
| Payment or invoice issued in advance of supply | Date of payment or invoice (whichever is earlier) |
The Date of Supply:
- For goods – the date they are sent, collected, or made available (e.g. installed in the customer’s house)
- For services – the date the work is finished
Additional Points:
- As a buyer, you cannot reclaim input V.A.T., without a valid V.A.T. invoice
- If you pay a supplier invoice in advance, you cannot reclaim the V.A.T. element of the payment without a valid V.A.T. invoice
- If you reclaim input V.A.T. on a supplier’s invoice, but fail to pay the supplier within 6 months, you usually have to repay the V.A.T.
H.M.R.C.:
But check the latest HMRC guidance here: V.A.T. Record Keeping
Purchasing Agent
Note:
If you’re buying services and goods yourself for the company (e.g. as a Director), we’ve considered these to be (having read through some H.M.R.C. guidance on this) to be transactions made in the capacity of a Purchasing Agent.
Buying Agent
Buying as an agent simply means you have your own account with the company, so settlements can be properly recorded and tracked.
Any cash payments made by the company to settle such agent purchases, would be recorded as a ‘Co. Bank Payment’, with the ‘Expense Reimbursement’ option chosen under the field called ‘Analysis’.
More information
More information about buying agents can be found on the Government’s own website here: Agents and VAT - H.M.R.C.
Accruals and Deferrals
If you would like to find out more about how the workbook calculates Accruals and Deferrals for accrued or deferred transactions, please see here:
Feedback
Submit and view feedback