PAYMENT RECORDS
This
sheet is used to maintain Payment Records and to reconcile payments against
Submitted Records.
The sheet includes two report generators:
·
Generate
Reconciliation Table creates a table of combined submitted and Payment Records
for reconciliation purposes
·
Generate
Accountants Workbook creates a macro-free unprotected copy of your T1 model for
year-end accounting
Payment
Records are generated by the Submit Records program. By default, the program
sets all expense records to PaidOK and all sales records to NotPaid. You can
change the default settings in the Setup sheet if you wish. Click Help in the
Setup sheet for further details
This
sheet also includes a Lock Records program which enables records to be locked
by financial year. This feature is designed to prevent records from being
inadvertently amended or deleted. Periods can be unlocked if required
Payment
Records are sorted in Payment Date order. Unpaid records appear at the bottom
of the Payment Records table without dates and with amounts set to zero
After
a sales invoice has been paid, go to the Payment Records table in the PayRecs
sheet and carry out the following steps:
1.
Using the record number, locate the unpaid
record at the bottom of the Payment Records table
2.
Enter the Paid Date
into the Paid Date field, the amount into the Money In field and any comments
into the Comments field
3.
Click the
ReCalculate Payment Records button
Every
payment record has a payment status which is calculated in column H
Updating Part
Payment Records
Where
a part payment has been made, carry out the following steps:
1.
Enter the part paid
amount and date against the initial payment record
2.
Click the
Recalculate Payment Records button. The payment status changes from NotPaid to
PaidAmountDiffers
Add
any further payments as follows until the record is paid in full:
1.
Locate the first
payment record and note down the record number
2.
Add a new record by
entering the amount, original record number (noted from above), paid amount,
Paid Date and comments on a blank line at the bottom of the Payment Records
table
3.
Click the
Recalculate Payment Records button
The
payment status of further Payment Records will be set as FurtherPymnts. The
status of the initial record will remain as PaidAmntDiffers until all paid
amounts add up to the original Submitted Record amount when the status will
change to PaidOK
Closing Balance
For
new businesses, the closing balance (PayRecs Sheet cell D10) should be set to
zero and any monies introduced (i.e. startup loans or savings amounts) should
be submitted in the SubRecs sheet using Transaction Type ‘Capital Introduced’
Credit Notes
A
Credit Note or Credit Memo is issued to indicate a return of funds in the event
of an invoice error, incorrect or damaged products, purchase cancellation or
otherwise specified circumstance
When
you issue a Credit Note, you must submit a new record in the SubRecs sheet.
Enter
the date that the credit note was agreed in the transaction date field
Enter
code S-Credit Note in the Transaction type field
Enter
the appropriate negative amount in the Due / Paid In field
Select
the correct VAT percentage in the VAT Rate field
In
the description field enter the reason for the Credit Note, together with the
invoice and record number that the Credit Note applies to
In
the Client Name field select the appropriate Client Name
Example
Submitted Records – Invoice & Associated Credit Note
|
Transaction Type |
Due or |
Owed
or |
VAT |
Purchase Order / Reference Number |
Comments |
Client Name / Company Name |
||
|
06-Aug-24 |
S-Non EU Sales |
£2,985.00 |
£0.00 |
20% |
PO-Number-012 |
Agreed
Work - 012 |
|
Mr P Bear |
|
17-Aug-24 |
S-Credit
Note |
£485.00 |
£0.00 |
20% |
Agreed Work
- 012 Credit Note Record: Reduced
Requirements Agreed to
reduce invoice from £2,985 to £2.500 (-£485) Rec No.
65 INV0012 ****************" |
Mr P Bear |
After
you have submitted the record, go to the Payment Records table in the PayRecs
sheet and locate both the original Payment Record and the associated Credit
Note record.
Tie
both records together by entering record numbers and invoice numbers into each
comment field.
Make
sure that both records are set to paid in full as per the example below:
Example
Payment Records – Invoice & Associated Credit Note
|
Paid Date |
Description |
Money in |
Money Out |
Rec No |
Comments |
|
|
21-Aug-24 |
Agreed
Work - 012 |
£2,985.00 |
|
65 |
"Invoice
Record Reduced
Requirements Agreed
to reduce invoice from £2,985 to £2.500 (-£485), See Rec No. 68 ****************" |
PaidOKInYear |
|
21-Aug-24 |
"Agreed
Work - 012 Credit Note Record: Reduced
Requirements Agreed
to reduce invoice from £2,985 to £2.500 (-£485) Rec
No. 65 INV0012 ****************" |
£485.00 |
|
68 |
Credit
Note Record: Reduced
Requirements Agreed
to reduce invoice from £2,985 to £2.500 (-£485) See
Rec No. 65 INV0012 ****************" |
PaidOKInYear |
Finally go to the invoice sheet,
select the Credit Note Number from the drop-down selector and generate the
Credit Note before emailing it to your client
If
you prefer, you can make the Credit Note payment record appear immediately
below the original payment record in the Payment Table as follows:
1. Set the Payment Date for the
Credit Note to be the same as the Payment Date for the original record
2. Add a time to the end of the Credit
Note Paid Date (i.e. 01/07/24 00:00:01 = 1st July 2024 + 1 second)
3. Clicking ReCalculate Payment
Records and the older record will be sorted below.
Note that for VAT
registered businesses, Sales VAT calculations are based on Submitted Record
amounts and not on paid amounts. If a client underpays or overpays, adjustments
to VAT calculations are applied via the Credit Note.
Reconciling Payment
Records
Assuming
that every business transaction appears in your bank account, the balances in
your Payment Records table should be matched to the balances in your bank
statements. This can be achieved as follows:
1.
Download your bank statement record for the required period
(oldest entries first) from your online bank portal
2.
Open your live T1 Business Model and go to the PayRecs sheet
3.
Click ReCalculate Payment Records and ensure that the T1 Status
equals OK
4.
Click Generate Reconciliation Table to produce a comparison
between Submitted Records and Payment Records
5.
Make sure that missing Submitted or Payment Records do not
appear in column T in the Reconciliation Table.
If there are missing records it means that a record number
exists in one of the tables but there is no corresponding record number in the
other table. There should be a Submitted Record for every Payment Record and at
least one Payment Record should exist for every Submitted Record
Missing Submitted Records can only occur if a new payment record
number has been manually into the Payment Records Table. If this has occurred
you will need to submit a new record to match the payment record. After you
have submitted the record, note down the new Submitted Record Number before
amending the record number in the Payment Table to match. Finally click
ReCalculate Payment Records
Missing Payment Records can only occur if a payment record
number has been cleared or overwritten in the Payment Records Table. If this
has occurred you will need to identify the missing record number before adding
a new record (including the missing record number) to the bottom of the Payment
Records table before clicking ReCalculate Payment Records
6.
Compare the Payment Records table against the bank statement for
the period. Identify if any further records are missing and if they are, submit
them before amending the Payment Record Table as needed
7.
If Payment Records are sorted differently on the bank statement,
you can change the change the order to match by following the steps below:
Check that the Payment Dates in the Payment Records table are
set to the same date as shown in the bank statement. Make any changes needed
and then click ReCalculate Payment Records
If there are transactions with the same date, you can change the
sort order by adding a time to the end of the date field in column B before
clicking ReCalculate Payment Records (i.e. 01/07/24 00:00:01 = 1st
July 2024 + 1 second)
Where Credit Notes have been issued, you can make the Credit
Note Payment Record appear immediately below the original Payment Record. See
the Credit Note section above for further details.
You can temporarily reverse the sort order of the Payments
Records Table by clicking the Reverse Sort Payment Records button
8.
After you have made sure that every bank statement transaction
has a corresponding transaction in the Payments Table you will need to check
account balances. First check that the opening balance from your bank statement
matches the opening balance in your Payments Table, cell N15. If it doesn’t,
check the closing balance (cell D10) see previous section on Closing Balance
9.
Finally check that the closing balance for the bank statement
period matches the closing balance shown in the Payments Table in column N. If
necessary, check each balance in turn making corrections as needed to the
Payments table before clicking ReCalculate Payment Records
Generate
Accountants Workbook
1.
This program generates a macro-free unprotected workbook, which
is suitable for accountants to produce year end accounts from
2.
If you wish, you can encrypt the workbook with a password before
sending it to your accountant. You can do this by opening it in Excel before
selecting File, Info, Protect Workbook, Encrypt with password.
3.
When you email the workbook to your accountant you should advise
them to click on the link in the PayRecs sheet ‘Notes for Accountant’ in order
to gain an understanding of the different reports and tables in the workbook
After
your accounts have been filed, you can lock the records for the filed year as
follows:
1.
Go to the Lock Records table in the PayRecs sheet (column AR)
2.
Select the appropriate date from the selector in the ‘Locked To’
field (cell AR13)
3.
Click the Lock Records button
4.
You can unlock all records again if needed by changing the
selector (cell AR13) to ‘Unlocked’ before clicking the Lock Records button