RealDesk for Accountants and B...
Section 6: Reconciliations
21min
This section explores the process of account reconciliations using RealDesk and QuickBooks Online (QBO). Bank reconciliations involve matching your accounting records against your bank's records to ensure accuracy and uncover any discrepancies. This section provides step-by-step instructions on how to reconcile your brokerage's bank accounts in QBO, and how to reconcile bank account balances to liability ledgers in RealDesk. You'll learn how to identify and resolve common issues, such as bank fees, timing differences, and transaction mismatches.
Additionally, we'll guide you through reconciling trust accounts in RealDesk and managing the General account in QBO. By mastering these steps, you'll maintain accurate financial records and ensure your brokerage's accounts are balanced.
Learning Objectives for this section:
- Understand the importance and process of bank account reconciliations.
- Learn to identify and resolve common discrepancies during reconciliations.
- Gain skills in reconciling trust and general accounts using RealDesk and QBO.
A bank reconciliation is a matching exercise that involves cross-checking your accounting entries against what the bank has processed.
Bank reconciliations can surface a number of issues to resolve, including:
- Items processed by the bank but not included in your accounting — such as bank fees, service charges, interest, recurring pre-authorized payments not yet entered, or other debit or credit memos
- Transaction issues — deposits or payments returned due to insufficient funds, incorrect payment amounts manually entered into your accounting, deposits received into the wrong account (General instead of Trust, or vice-versa) or not added to RealDesk
- Timing issues — deposits or payments entered into RealDesk in a previous month that cleared the bank in a subsequent month
None of these are difficult to resolve, but all are easier to handle when less time has passed and your team's memories are fresher. For this reason, we encourage you to reconcile your bank accounts as soon as possible after the end of the month, ideally as soon as your bank statement is available.
Currently, bank account reconciliations occur in QBO, although bringing this process into RealDesk is on our roadmap. QBO has a number of helpful features that make reconciliations simple to accomplish.
- Ensure you have your bank statement and access to both QBO and RealDesk
- In QBO, go to Transactions --> Reconcile
- Choose the account you wish to reconcile
- Add the ending balance and statement ending date from your statement
- By default, we will assume that all transactions listed have cleared the bank. Ensure that all items are checked / shaded, to represent that they are included in the reconciliation calculation.
- Compare the balance QBO reports to the statement balance. If it matches, you may be good to go, although it's still a good idea to quickly scan the list of transactions in QBO vs. the bank statement.
- If there is a mismatch, begin to compare the bank statement against the transactions listed in the reconciliation:
- As well as the overall balance, QBO will report the number and volume of deposits, as well as the number and volume of payments. Typically your bank statement will contain the same information, which can help to narrow down the potential mismatches.
- For example, if the volume of deposits in QBO matches the statement, but the volume of payments is higher in QBO than on the statement, you can deduce that there are payments entered into QBO that did not clear the bank in the month you are reconciling.
- Matching the number of deposits is generally less important than the volume, as many banks will aggregate multiple same day deposits into one entry. For example, QBO may show three deposits of $5,000, $4,000, and $2,000 on June 15, while the bank statement will show one deposit of $11,000.
- Payments should never be aggregated, so you should be able to match both the number and volume between QBO and the bank statement.
- As above, most reconciliation discrepancies come from timing issues. There are two types:
- Payments entered into RealDesk and QBO that have not yet cleared. Paper cheques, in particular, often can take 1-2 weeks (sometimes longer) from when you prepare and send them to when they clear the bank. (Pro tip: use RealDesk's embedded EFT feature wherever possible; it's much quicker!)
- Deposits entered into RealDesk and QBO that have not yet cleared. This is usually only an issue on the last business day of the month, or sometimes in cases where deposits are entered into RealDesk on a weekend.
- Most of the time, simply unchecking these uncleared items will allow you to match the QBO reconciliation against the bank statement.
- If there still appear to be discrepancies, you should scan the bank statement for deposits or payments that you cannot match in QBO:
- Bank fees and service charges: these should be paid from General, so the solution is usually to transfer funds from General into your trust account to offset them
- NSF payments or deposits: you will want to follow up on these to ensure the issue is corrected and the transactions are reissued. You may also need to create entries in QBO to account for the additional withdrawals and deposits that occur when these transactions are pulled back and subsequently corrected
- Deposits received into bank but not entered into RealDesk: this can be fixed by simply creating the missing deposit in RealDesk and running the QBO sync
- You can also do the reverse and scan the list of transactions in QBO for items that you cannot locate in the bank statement:
- In most cases, these are simply uncleared items and would have been accounted for earlier in the process
- Outside of timing issues, it is possible, though rare, that a deposit or payment exists in QBO but not in the bank. Here are a few examples:
- A payment was created and subsequently voided/deleted in RealDesk, but has already synced with QBO. In such cases it is not always possible for RealDesk to fully delete its entries in QBO. The solution is to void or delete the QBO entries manually.
- In some cases, when a deal collapses but a buyer subsequently makes an offer on a new property, their deposit from the initial deal is simply "rolled over" to the new deal. This is perfectly acceptable if the deposit from the original deal is deleted from RealDesk / QBO and a deposit for the equivalent amount created in the new deal. However, we have seen cases where the original deposit was not deleted, and therefore RealDesk / QBO will (incorrectly) show two deposits where the bank will (correctly) only show one. This is easily resolved by deleting the deposit from the original deal.
- At this point you should have QBO reconciled to the bank statement. You can complete the reconciliation by clicking the green Finish button.
- The reconciliation report will be available in the History by Account section. You should also upload a copy of the bank statement by clicking Attach next to the month you have reconciled.
- You may also want to attach a copy of the RealDesk Monthly Reconciliation Report for the account once you have completed this; see below for instructions on how to do so.
- Ensure you have your bank statement and access to both QBO and RealDesk
- In QBO, go to Transactions --> Reconcile
- Choose the General account
- Add the ending balance and statement ending date from your statement
- By default, we will assume that all transactions listed have cleared the bank. Ensure that all items are checked / shaded, to represent that they are included in the reconciliation calculation.
- Compare the balance QBO reports to the statement balance. If it matches, you may be good to go, although it's still a good idea to quickly scan the list of transactions in QBO vs. the bank statement.
- The General account is a special case since not all of its transactions will be populated through RealDesk's sync to QBO. RealDesk will handle most deposits into the General account, via deductions (splits and deal fees) and expense recoveries from agents.
- Payments out of the General account occur outside of RealDesk and could cover a wide range of potential expense types:
- Staff payroll
- Office rent and expenses
- Professional expenses (Board dues, MLS fees)
- Bank service charges and payment processing fees
- Insurance
- Marketing and advertising expenses
- Legal and accounting fees
- Technology and software
- Entries for these and other expenses need to be added manually to QBO. While it may be possible (depending on which bank you use) to download and sync your online banking transactions with QBO, in most cases this will only add debits and credits for the correct amounts. It may not categorize these expenses and bills in the way that your accounting practices require.
- As discussed in the Payments and Entries section of this manual, realtor expenses recovered outside of deal payments (via automated credit card processing, or realtors making out cheques to the office) are by default debited to Undeposited Funds in QBO.
- When these payments are deposited into the General account (typically in bulk, in the cases of credit card deposits), it is necessary to create a Transfer from Undeposited Funds to General in QBO to account for this.
- This will allow you to reconcile the individual expenses recovered with the bulk deposits into your General bank account. Keep in mind that, because it can often take 4-5 business days between processing a credit card and the funds being deposited into your account, Undeposited Funds may not "zero out" at the end of each month.
- As with the Trust accounts, if there is a mismatch between the QBO balance and your bank's reported end of month balance, begin to compare the bank statement against the transactions listed in the reconciliation.
- Since RealDesk and QBO only populate deposits into General, the most common timing issue relates to deposits created on the last few business days of the month. These may not yet have cleared the bank.
- Most of the time, simply unchecking these uncleared items will allow you to match the QBO reconciliation against the bank statement.
- At this point you should have QBO reconciled to the bank statement. You can complete the reconciliation by clicking the green Finish button.
- The reconciliation report will be available in the History by Account section. You should also upload a copy of the bank statement by clicking Attach next to the month you have reconciled.
Reconciling RealDesk against your bank account follows a similar process to the QBO bank reconciliation, but serves a slightly different purpose.
As discussed above, the QBO reconciliation is on a transaction-by-transaction basis and allows you to demonstrate that each transaction on the bank statement has a matching accounting entry.
The RealDesk Trust reconciliation is meant to provide a full breakdown of your current Trust balance. It will show you which clients / deals make up the money you are currently holding in trust. It will also provide full traceability for deals where there is no outstanding balance; i.e. where money was received into trust and subsequently disbursed when a deal completed or collapsed.
Completing this reconciliation is typically very quick and follows similar steps to the bank reconciliation.
- The report can be found under the Accounting tab --> Monthly Reconciliation Report
- Choose the report date — in most cases, you will want to generate this on the last day of the month you are reconciling
- Choose the account (Trust, or Commission Trust if applicable) that you want to reconcile
- In most cases it is helpful to check the "Detailed Report" box -- this will show details of each deposit and payment for any deals with balances
- Click Run Report
There are three sections to the report:
- Trust Liability Ledger
- This will show each deal with a current balance, and the sum of all balances, representing your total trust liability
- It is important to note that all deposits and payments created on or before the date of the report will display in this section. This includes cheques that have not yet cleared the bank. These are accounted for in the next section.
- Outstanding Cheques
- This is a list of all cheques that have not been marked as deposited in RealDesk. These payments (credits) are reported in the Trust Liability Ledger, but because they are uncleared, the balances are added back (debited) in this section.
- Additional Line Items
- This section allows the creation of temporary adjusting entries to account for additional uncleared items, fees debited or credited in error, NSFs not yet resolved, or other mistakes not yet corrected.
- At the bottom of the report, you will see a summary line "Balance in trust account as of {report date} should be:" This is RealDesk's balance based on the information currently in the system.
- On the line below, enter the balance as of the report date from your bank statement.
- The final line in this section will report the discrepancy betweeen the two balances, and will update in real time as you clear outstanding cheques or add line items.
- Review the list in the second section of the report against your bank statement. If some of the cheques listed have cleared the bank, you can update this in one of two ways:
- Click on the cheque number, which will take you to the Send/Receive Payments page for the deal — find the payment where the cheque was used, click the button under Actions, then select Deposit and enter the date
- Go to the Outstanding Cheques report (under Accounting in the left navigation), find the cheque number and enter the deposit date
- You can add debit or credit entries to adjust RealDesk's reported balance if required to address temporary issues, such as NSFs, incorrect deposits, or fees added or withdrawn in error.
- Adding a debit increases the balance that RealDesk reports. For example, if a $5,000 payment to an agent was made on March 28, but returned to your Trust account the following day because the agent's bank information was wrong, you can add a debit entry for $5,000 and an explanatory comment to your March reconciliation.
- Adding a credit decreases the balance that RealDesk reports. For example, if you entered a $50,000 deposit into RealDesk on February 28, but the bank received it on March 1, you can add a credit entry for $50,000 and an explanatory comment to your February reconciliation.
- Unlike entries added to QBO, which remain until they are reversed or deleted, additional line items added to your RealDesk reconciliation for a given month do not automatically "roll forward" to subsequent months. This is deliberate, as it is assumed that these are temporary adjustments that will be corrected at the earliest opportunity.
- In our two examples above, the agent payment will likely be reissued in the first few days of April. And we already know that the deposit was received by the bank on March 1.
- Accordingly, both of these issues "self-correct" in the following month, eliminating the need for these adjusting entries.
- At this point, the balance reported in RealDesk should equal the bank balance.
- You can export the completed reconciliation report in PDF or Excel formats.
- Optionally, you may wish to add the RealDesk reconciliation to the "Reconciliation History by Account" section of QBO.
Completing regular account reconciliations is essential for maintaining accurate and reliable financial records. By following the steps outlined in this section, you can effectively reconcile your brokerage's accounts in QuickBooks Online (QBO) and RealDesk, ensuring that all transactions are accounted for and any discrepancies are promptly resolved. This process not only supports accurate financial reporting but also helps safeguard the financial integrity of your brokerage operations.
Updated 24 Aug 2024
Did this page help you?