Excel template for creditors reconciliation
Excel template for creditors reconciliation
This article provides details of Excel template for creditors reconciliation that you can download now.
At the end of each month a statement is received from creditors. The statement demonstrations the transactions that have taken place during the month. What does this tell us? It shows us the transactions that have taken place in that specific month. So it’s information that the creditor has sent us telling us about the transactions that have ensued in that particular month. Creditor ReconciliationWhat do we do with this statement? We take the statement should be compared to the creditors ledger account to confirm that the details of all invoices and other transactions imitated on it are accurate before payment can be made. Why? Once we can confirm the correctness of that information we are now in the position to make the essential payments, so clearly we receive the statement, we confirm the accurateness and now we go on to make the payments.
Microsoft Excel software under a Windows environment is required to use this template
These Excel templates for creditors reconciliation work on all versions of Excel since 2007.
Examples of a ready-to-use spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.
To be able to use these models correctly, you must first activate the macros at startup.
The file to download presents tow Excel template for creditors reconciliation
- Simple Excel template for creditors reconciliation
- Advanced Excel template for creditors reconciliation
Instructions:
Part 1: Reconciliation of deposit accounts included in the deposit system(s) to general ledge
"The purpose of this section is to reconcile all accounts in the deposit system(s) to the unconsolidated general ledger ('GL') for the bank. In columns B-E, input the system name, account type code, account description, and balance, respectively, for each account type in the deposit system. In columns G-I, input the corresponding GL account number, GL description, and GL balance, respectively. In columns K and L, please indicate if the account is subject to the requirements of the LBDIDM Rule and if provisional holds have been placed. The first row is provided as a sample for your reference.
In the event that there is a reconciliation difference between the system balance and the corresponding GL account, please include the description and amounts in the ""Adjustments"" section. After considering all adjustments, we would expect the final reconciliation balance to be $0 between the deposit system(s) and the GL."
Part 2: Listing of additional deposit accounts on GL but outside the deposit system(s)
Often, there are accounts that are not included in the deposit system but are shown as deposits on the general ledger and/or Call Report. These "GL only" accounts should be listed in part 2. After totaling the various GL account balances in parts 1 and 2, the total should tie to the principal amount reported as deposits on the general ledger.
Part 3: Final reconciliation of GL to Call Report (RC-O line 1)
By using a quarter-end date for GL and core system data, the reconciliation should tie to same quarter's Call Report totals (section RC-O line 1). This comparison is meant to ensure that all deposit sources making up the gross deposits balance on the Call Report have been accounted for above.
Tab 3. Materials and Data Request
Reconciliation Support
The items requested allow FDIC to review RC-O, line 1, insurable deposits, and provides broad insight into the Banks volumes andcomplexity. Thank you for your assistance.
Creditors’ Transactions and Creditors’ Accounts
When an enterprise buys goods on credit from another enterprise or supplier, the transaction is recorded in the creditor’s journal. Any purchases returned are recorded in the creditor’s allowances journal and payments of outstanding creditors’ accounts are recorded by making an entry in the cash payments journal. Errors that are corrected, interest charged on overdue accounts and transfers between debtors’ and creditors’ accounts are all recorded through entries in the general journal.
The following journals are used:
- · CJ – for recording credit purchases
- · CAJ – for recording purchases returns
- · CPJ – for making cheque payments to creditors
- · PCJ – for making cash payments to creditors
- · CRJ – for recording R/D cheques
- · GJ – for sundry transactions relating to creditors, e.g. interest on overdue accounts
An individual account is kept for each creditor in the enterprise’s creditors ledger. This individual account is a summary of all the transactions between the enterprise and the creditor and shows the (total) amount due to (or outstanding balance of) the creditor. The entries in the journals as books of first entry are posted daily or monthly to the individual account of the creditor.
At month-end, an account is rendered by the creditor. It gives a summary of the transactions concluded with the creditor during the month, and the total amount that the enterprise owes the creditor.
Creditors Reconciliation Statement
When the enterprise receives an account statement from the creditor, the statement must be compared with the entries of the creditor concerned in the creditors ledger. The purpose of this check is to trace any errors and/or omissions. For this purpose a creditors reconciliation statement is prepared.
Poodles Pet Shop Poodles
Pet Shop received an account statement (statement 114) dated 28 May 2009 from Ray-Bees Vets. According to an agreement with Ray-Bees Vets, Poodles Pet Shop receives 20% trade discount on all invoices and an additional 5% cash discount if payment is made within 30 days of the account date. Statement 114 shows a debit balance of R27 888. The accounting officer compared the statement with Ray-Bees Vets’ account in the creditors ledger, which showed a credit balance of R10 712, and found the following differences:
- · Invoice 189 was correctly recorded in the creditors ledger as R11 200. Statement 114 reflects the invoice amount as R14 000. The trade discount was not taken into account.
- · Credit note 73 for R320 was correctly entered into the creditors ledger, but is shown as an invoice on statement 114.
- · Ray-Bees Vets’ account in the creditors ledger was added up incorrectly – it is undercast by R7 200.
- · Invoice 194 for R6 280 has not yet been recorded in the creditors journal because the goods are in transit.
- · Invoice 208 included eight items that were on special and invoiced for the gross price of R160 per item. The trade discount was not taken into account on these items.