Bank Account Reconciliation

Excel bank account reconciliation for financial accounting

Excel Bank Account Reconciliation

Rate this solution: (3.4/5 from 5 votes)

The Excel Bank Account Reconciliation template can be used as an cash accounting book and allowing the preparation of your bank reconciliation at the press of a button. The Excel Bank Account Reconciliation template comprises the following: One sheet containing the Bank Reconciliation. 12 receipts and 12 payments sheets - one of each for each month. (Note These are called Rec01, Pay01 etc so that it does not matter which month your fiscal year starts in). One sheet called 'B Fwd' - enter in here your opening cash book balance for the fiscal year, plus the details of previous year uncleared items. The format required will allow you in future years easily to copy and paste in such items. One sheet called 'Data Sheet' where you enter (once only each year) the name of first month in your fiscal year (e.g. 2001, or 2000/01), your company / division / bank account name. Having set up the details on the last two Bank Account Reconciliation templates you are then ready to start entering your cash book detail into the 12 receipts and 12 payments sheets. Any sheet that already contains a total line can either have lines inserted above that line, or better still, you should delete the totals line. At any time you can automatically have a totals line added just by clicking on the button at the top of the sheet - the software will insert the formulae for however many columns you have used (which can vary from sheet to sheet). On the 12 sheets you must not change the format of columns A - E: Column A contains the date. Column B is for a reference eg cheque number, paying-in number, direct debit, etc. Column C is for marking when each item clears the bank (uncleared items are the ones used to reconcile the bank). We recommend you enter the name or number of the month when each item clears. (NOTE: This also applies to the brought forward items. Don't forget to mark these once cleared). Column D is for some text e.g. payee for payments. Column E is a formula that will automatically be inserted when you use the 'Total this page' button. You do not need to enter anything in column E. Column F onwards can be used as you wish, and it is here that you enter the amount(s). At any time you can create the bank reconciliation simply by pressing the button on the first worksheet, and entering the current bank balance. You should observe these limitations for using the Bank Account Reconciliation workbook: 1. Do not change the worksheet tab names as supplied in the file. You can add your own new worksheets though this is not recommended. You can move the sheets around e.g. put all the 12 receipts sheets together, and similarly the payments sheets. 2. Do not alter the format of the worksheets except you may use columns F onwards on the 12 receipt and 12 payments sheets as you wish. 3. The 12 receipts and 12 payments sheets are limited to 32,750 usable rows. If you use more then the automatic Bank Account Reconciliation will not work properly. Try It  Get It (Pricing is available on the next page) (Updated on 2024-03-01)

Related Excel Solutions for Bank Account Reconciliation General LedgerFinancial Accounting  Accounts Receivable TemplateFinancial Accounting  Web Reporting ToolPresentation and Reporting  Budget PlanningBusiness Planning

Share your thoughts and opinion with other users: Create Review

Browse Main Excel Solution Categories Business Finance Financial Markets Operations Management Excel Productivity

Additional Excel business solutions are categorized as Free Excel solutions and the most popular. Further solutions proposed for specific user requirements can be either found in the Excel Help Forum.