Sage 300 CRE Bank Reconciliation from PDF Statements to Excel and CSV

How do I do a bank reconciliation in Sage 300 CRE?

Open Cash Management, choose Tasks, then Reconcile, and select the bank account. Enter the Statement date, check the Statement opening balance Sage fills in, and type the Statement ending balance. Mark each item that appears on the bank statement on the Checks, Deposits, Misc or All tab. When the Difference reaches zero, Finish becomes available.

That is Sage's documented routine, and the slow part is not the clicking. It is reading the PDF: finding check 4518 on page 9, working out which deposit is the owner payment and which is the loan draw, and adding up what cleared when the Difference refuses to reach zero. A converted statement turns that into a sorted list with totals.

1. Upload the PDF statement to BankXLSX and download Excel or CSV. Check that the opening balance plus credits minus debits equals the ending balance on the last row.
2. Filter the sheet to rows with a check number and build the Import Cleared Checks file (the layout is below).
3. In Cash Management, run Tools, Import Cleared Checks, review the journal, and fix anything in the rejected records file.
4. Open Tasks, Reconcile, enter the statement date and balances, select Reconcile cleared entries, then mark deposits, transfers and fees from the deposits tab of your sheet.
5. Finish when the Difference is zero and print the Bank Reconciliation report for the file.

Can Sage 300 CRE import a bank statement?

Only the checks. Sage 300 CRE Cash Management has one built in import for reconciliation, Tools, Import Cleared Checks. It reads a text file of cleared checks and marks matching open checks as Cleared, so Reconcile can pick them up in one pass. Sage does not document a CSV, OFX or BAI2 statement import for deposits, transfers or fees in 300 CRE, and a Sage staff answer on the Sage community describes fuller bank imports as a custom setup through Sage Expert Services.

One naming trap catches a lot of people searching for this. Sage 300 (the former Accpac product) has an Import OFX Statements function and bank feeds in its Bank Services module. That is a different product. If you run Sage 300 Construction and Real Estate, the former Timberline, those help pages do not apply to you.

What file format does Import Cleared Checks need?

Four fields per check, in this order: the bank, the check number, the cleared amount and the cleared date. Sage ships two templates in the Accounting Template folder. Cm_comma.tpl reads a comma delimited file, and Cm_fixed.tpl reads fixed width columns. Sage's own example for the comma layout is OPERATING,1550,79.00,06262015, with the date written as MMDDYYYY and no separators.

Here is the quickest way we know to build the comma file from the Excel download. Filter the check number column to non blank values and copy those rows to a new sheet. In column A type your Cash Management bank ID and fill it down. In column B put the check number, in column C the debit amount formatted to two decimals with no dollar sign or thousands separator, and in column D use =TEXT(date,"mmddyyyy") and paste it back as values. Delete the header row so the file starts with data, like Sage's sample, and save as CSV. Sage's help says the import file can carry any extension up to three characters and adds .txt when there is none; partners note some 300 CRE imports want a .csv renamed to .txt, so rename it if the file picker does not show it.

If your bank's check lines carry extra information you want to keep in the file, Sage's guidance is to copy a template and edit it in Notepad; custom templates must sit in the same Template folder, start with Cm_ and end in .tpl. For most companies the four column file above is all you need.

Why are some checks rejected by Import Cleared Checks?

Import Cleared Checks only clears checks that already exist as open entries in the Cash Management register for that bank. The usual reasons a row lands in the rejected records file are a check written outside Cash Management (entries posted directly in General Ledger do not reach the bank rec), a bank ID that does not match the one set up in Cash Management, an amount that differs from the register by a cent or a stop payment, and a date written in the wrong order. Fix the row in the rejected file and import that file again.

Why is my Sage 300 CRE bank reconciliation out of balance?

Nearly always an item marked that is not on the statement, or the reverse. For contractors the common ones are a voided check that was reissued and both versions cleared, a deposit entered in Cash Management as several receipts while the bank shows one lumped deposit, a transfer marked on one account and not the other, and fees the bank charged that nobody entered. Sage also notes that the Bank Statement Ending Balance on its report is the bank balance less entries with a cleared date after the statement date, so a wrong cleared date on an import row moves the number. With the statement in Excel you can SUM what the bank cleared and compare it with the reconciled balance to see the size of the gap before you hunt for it.

Which companies get the most out of converting statements for Sage 300 CRE?

The ones with the most checks and accounts. A general contractor paying hundreds of subcontractor and supplier checks a month, a specialty contractor with a separate payroll account, a developer or real estate company with an account per project or property, and the outside CPA who rebuilds a year of reconciliations before an audit or a surety review. Sage 300 CRE is sold through Sage and its partners on quote, so the companies running it are already paying for a serious system. The time their staff spend reading PDF statements is the cost that is easiest to cut.

What does BankXLSX give you for a Sage 300 CRE bank reconciliation?

One row per transaction with date, description, check number, debit, credit and running balance, plus the opening and ending balance so you can prove the statement before you open Sage. Excel and CSV come with every plan, scanned statements go through OCR, and Plus converts up to 50 PDFs in one batch for catch-up months or year end. Nothing connects to your bank or to your Sage company. Starter is $49 a month, or $24 a month billed yearly, for 800 pages with unlimited users, and a new account can convert 15 pages once with no credit card. If you also keep a smaller entity on Sage 100 Contractor, our Sage 100 Contractor bank reconciliation page covers its 1-5 Bank Reconciliation routine, and the bank statement converter for contractors covers job costing and draw tracking from the same files.