Sage 100 Contractor Bank Reconciliation with PDF Bank Statements Converted to Excel

How do I do a bank reconciliation in Sage 100 Contractor?

Open 1-5 Bank Reconciliation, enter the bank account number in the Account# box and the statement ending date in the Statement Cutoff Date box, then click Display Items. Select each item that appears on the bank statement and click Clear. When the Statement Ending Balance box agrees with the bank, click File, Save and choose Final, or Trial if you plan to finish later.

The window itself is simple. The hard part for a contractor is the statement. An operating account that pays forty subcontractors, a dozen suppliers and weekly payroll can carry several hundred lines a month, and the bank lists them in posting order, not check number order. Clearing that from a PDF means scrolling back and forth for every check. Converting the statement first puts every line in a spreadsheet you can sort by check number, filter by amount and total, so you clear the 1-5 grid in one pass.

1. Upload the PDF statement to BankXLSX and download Excel. Check that the opening balance plus deposits minus withdrawals equals the ending balance on the last row.
2. In 1-5 Bank Reconciliation, enter the account, type the statement period end in Statement Cutoff Date and click Display Items. Sage 100 Contractor does not show transactions entered after that date.
3. Sort the converted sheet by the check number column and clear checks in order. Then sort the remaining debits by amount for ACH payments, payroll batches and tax debits.
4. Sort credits by amount and clear deposits. Where the bank shows one deposit for several receipts, total the receipts in Sage until they equal the bank line.
5. Whatever is still unmarked in the sheet is missing from Sage: usually bank fees, interest, returned items or a deposit nobody recorded. Enter those, clear them, and save as Final when the balances agree.

Can Sage 100 Contractor import a bank statement?

Not as a file, according to its help. Sage 100 Contractor documents two ways to reconcile: download transactions through Sage Bank Feeds and match them, or clear items by hand in 1-5 Bank Reconciliation. Its help does not describe importing a CSV, OFX or PDF statement into the reconciliation. If your account is not on a feed, the statement is your source, and a converted Excel copy is the fastest way to work from it.

That gap matters more than it sounds. Sage notes that connecting a bank can take a few days for the bank to authorize, sometimes with paperwork your company has to complete, and that the historical download covers only the start date you choose. A contractor who switched banks mid-year, opened a new job account, or hired a new controller who has to reconcile last year before the CPA arrives will still be working from PDFs for some accounts and some months.

How do Sage Bank Feeds work in Sage 100 Contractor?

You select a bank account in 1-5 Bank Reconciliation, choose Bank Feeds, Connect to Bank Feeds, and complete a wizard with an admin email, your banking credentials and a start date for historical transactions. Depending on the bank, the connection runs through Sage Direct Bank Feeds or an aggregator such as Plaid or Yodlee. Once the account is active, Display Items retrieves bank transactions and opens Match Banking Transactions.

Matching is strict. Sage matches a bank line automatically only when it is unique, has the same payee and amount, and the Sage transaction is dated earlier than the bank transaction. Duplicates and look-alikes stay open for you to match by hand, and the transaction type (deposit or payment) has to agree. Bank fees and interest that Sage does not have yet are posted with Add to Sage, then Confirm Matches sends the result back to the 1-5 grid with a double check mark. For contractors with many same-amount payroll checks or repeated supplier payments, a fair share of lines still ends up manual, and a sorted spreadsheet of the statement helps there too.

Why is my Sage 100 Contractor bank reconciliation out of balance?

Almost always because an item is on the statement and not cleared in Sage, or cleared in Sage and not on the statement. For contractors the usual suspects are a voided check that was reissued and both got cleared, a payroll direct deposit batch cleared as individual lines that the bank posted as one, a progress payment recorded for the gross amount when the bank received it net of retainage, a joint check entered against the wrong account, and bank fees or interest nobody posted.

Working from the converted sheet you compare totals instead of rereading. Sum the debits and credits in the workbook, compare them with what you cleared, and the gap tells you which side to search and how much you are looking for. Two quick filters catch most of the rest: amounts that appear more than once (where double clears hide) and the last three days of the period, where a deposit made on the 30th that posted on the 1st belongs to next month.

Which contractors get the most out of converting statements?

The ones with the most lines and the least bank access. A general contractor paying subs and suppliers by check, a specialty contractor with a separate payroll account, a builder with a construction loan whose draws post as single deposits, and any company whose controller is catching up months or years at once. Outside bookkeepers and CPA firms with several Sage 100 Contractor clients see the same pattern across every engagement: a stack of PDFs and a deadline.

Against a controller's hourly cost, the converter is small money. BankXLSX Starter is $49 a month, or $24 a month billed yearly, for 2,500 pages with unlimited users. Plus is $149 a month, or $74 a month billed yearly, for 10,000 pages, batch upload of up to 50 files at once, saved templates and transaction categorization. New accounts can run a one-time 15 page demo on a real statement before paying, and the output can be watermarked until you pick a plan.

What does BankXLSX give you for a Sage 100 Contractor 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 start. You can keep one signed amount column or split debits and credits, choose ISO dates, and download Excel or CSV on every plan. Scanned statements and phone photos go through OCR, and the running balance check flags a misread line before it reaches your reconciliation. Nothing connects to your bank or to your Sage company file: you upload the statement, download the workbook, and can delete the upload whenever you like.