The Basics
The Accountants
Workbook is made up from a set of unprotected macro-free worksheets which were
generated from a client’s business model
The
PayRecs contains all financial information needed for producing year end
accounts, other sheets are provided as support information only.
Data
is held in a Payment Records table and a Reconciliation table. The
Reconciliation table includes VAT calculations and provides a comparison
between all submitted and payment records
Tables
can contain up to 5 years of accounting data.
Payment Records are sorted by Payment Date, Reconciliation Records are
sorted by Transaction Date
Clients
have been asked to highlight the appropriate financial period using the Record
Highlighter above the tables in the PayRecs sheet. Periods will be highlighted
grey in columns J & U.
Payment
Status (PaidOKInYear, PaidOKOutYear,
NotPaid, PaidAmntDiffers etc..), Transaction Types (Sales, Expenses, Credit
Notes, Capital Expenditure etc..) and Financial Periods can be highlighted by
selecting the required status, type or period code from the green drop-down
selectors above the two tables
Opening
and closing balances are found in column N, closing balance positions are
flagged in column Q
Years
can be filtered in column J:
·
*D-YR1* filters all
records with payment due in year 1
·
*P-YR1* filters all
records with payments received in year 1
Clients
have been asked to send a PDF copy of their bank statement to their accountant
for the appropriate filing period. They have also been asked to reconcile their
bank statement against the balance shown in their Payment Records table
Transaction Types
Every
record has a transaction type made up from one of 4 main categories:
·
S-(Sales)
·
X-(Expenses)
·
I-(Other Money In)
i.e. bank interest payments
·
O-(Other Money Out)
i.e. loan repayments
A
further sub category has been setup to record all capital expenditure:
·
X-CPX(Capital
Expenditure)
Due and Paid dates are translated
into period codes which can be found in column J in the Payment Records table
and column U in the Reconciliation table
Example filter codes are shown
below:
·
D-YR1-Q1-M01* =
Due in year 1, quarter 1, month 1
·
*P-YR2-Q3* =
Paid in year 2, quarter 3
·
*P-YR2* =
Paid in year 2
·
D-YR1*P-YR2* =
Due in year 1, paid in year 2
T1 Reconciliation
The
T1 Status shown in the SubRecs sheet cell J4 should be equal to OK. If it does
then everything should add up correctly in all sheets
If
the status doesn’t equal OK you may wish to alert your client to the fact
before continuing.
The
Totals report can be used to reconcile all amounts. Transaction types can be
filtered in the green cells in row 8. Financial periods can be filtered in
column B. The totals shown in row 9 are calculated against visible filtered
cells only
Payment Status
Every
record has a payment status calculated in the Payment table in columns H and in
the Reconciliation table in column T. Status descriptions are listed below:
·
NotPaid - Payments
are outstanding (Owed or Due)
·
PaidOKInYear
– Correct amount paid within the financial year
·
PaidOKOutYear
– Correct amount paid outside of the financial year
·
PaidAmntDiffers –
An overpayment or underpayment has been made
·
DeletedRec – A record has
been deleted (See Change Control sheet)
·
FuturePayment
– A payment date has been entered in the future. This can apply to direct debit
or monthly payments
·
FurtherPymnts –
More than one payment has been made against a transaction. When multiple
payments appear in the Payments table, all subsequent payments will have their
status set to FurtherPymnts. The combined record status is calculated against
the first occurrence of the record. Where payments are outstanding, the first
record status will be equal to PaidAmntDiffers. When the sum of all combined
payment records adds up to the total amount due, then the first record status
will change to PaidOK
If
any record calculates to a status listed below, the overall status will equal
“ISSUES”:
·
MissingSubRec
·
MissingPayRec
·
InvalidDate
·
InvalidDesc
·
InvalidRecNo
·
InvalidDate
·
InvalidBankIn
·
InvalidBankOut
Clients
have been asked to rectify any issues before sending the workbook to their
accountant
Other Worksheets
The
workbook includes copies of the following sheets:
·
SubRecs (Submitted
Records) - Contains all records input by the client. Records are sorted by
transaction date. For expenses, this date should match the date shown on the
purchase invoice or other paperwork. For sales records, this is the date the
record was entered into the business model and is usually the same date as the
invoice was raised
·
VAT (VAT Records) -
For VAT registered clients using MTD third party Excel bridging software, this
sheet contains a record of all VAT submissions to HMRC
·
Totals (Totals by
period code and by transaction type) - Totals are calculated against filtered
cells only. Records can be filtered by transaction date, transaction type and
financial period
·
CC (Change Control)
- This sheet includes a record of every amendment or deletion made to the
Submitted Records table
·
Other Information
·
Client input cells
are coloured green.
·
Descriptions of
fields can be found by clicking inside header records
·
Spare sheets are provided
and available for use if required
·
For all new
businesses, the PayRecs sheet opening balance field (cell E10) should be set to
zero. Startup businesses have been advised to submit all records individually
for any capital introduced using transaction code ‘I-Capital Introduced’. The
Opening Balance field should only contain a value for businesses following on
from a previous accounting period