The Ledger : reconciliation

Make Sure Your Database Stays in Balance!

Jay Wright — Wed, 10 Jun 2009 14:59:00 GMT

To make sure your bank registers balance to your cash account in GL, there is a business rule that you have to understand so that reconciliations run smoothly. Business rules configure how the database is able to operate according to your business office policy.

This important business rule, located in GL, Configuration, Business Rules, Journal Entry, is called Allow journal entries to cash accounts without adjusting the bank register. When marked, a corresponding bank adjustment is not created in the bank register when the cash account is used in a manual journal entry. Using this option, the bank register has no record when the cash account balance is changed making reconciliation more difficult.

When this rule is unmarked, a corresponding bank adjustment is created in the bank register when the cash account is used in a manual journal entry. The rule makes it easy for the cash account and the bank register to communicate without any manual adjustments. No one wants to do manual adjustments; your day is too busy as it is!

Now you can make the decision on how you want to set this up. I suggest you don’t mark this rule so all parts of the database are in balance. Now you are armed with the knowledge to keep your reconciliations simple and in balance. And knowing is half the battle! Go Joe!

The Ledger Lowdown: Get it in Balance

Jennifer Maroney — Wed, 03 Jun 2009 12:39:00 GMT

Nothing is more frustrating than when you run your Balance Sheet and the bottom line is out-of-balance . So here you are with an out of balance Balance Sheet that you need to have to your board in an hour, or, even worse, an hour ago. What should you do? Well, the first thing is don't, or try not to, panic. A deep breath usually works for me. Then take a look at these common causes of out of balance financial statements and reports; you may be able to resolve the issue and be a hero:

An account is marked inactive and the report is marked to not include inactive accounts
The chart template is missing accounts
The financial statement is filtering out information
Fund Balance account is not defined on all fund records
The user does not have sufficient rights
Unposted journal entry batches are out of balance

So now that you know what the common causes are, the next step is to start narrowing them down to find your issue. First, create and preview a new Trial Balance Report for the same period and fund(s) as the out of balance financial statement. The report should always be in balance when including all accounts and their balances. If the Trial Balance Report is incorrect with no filters or Visual Chart Organizer (VCO) selected, create a case.

If your Trial Balance is correct, here are some other steps to help you find and resolve the issue:

Run the report as Supervisor or as a user without account level security defined. Financial statements will exclude accounts that a user does not have rights to view.
Mark the option on the General tab to include inactive accounts and rerun the report. Inactive accounts with activity will cause the report to be out of balance.
- If the report totals correctly, create a query of inactive accounts and change the account(s) from inactive to active.
Check your VCO template. As new accounts are added or new templates created, accounts can be left off or duplicated causing the report to be out of balance or total incorrectly.
- If you find there are missing or duplicate accounts, add or remove the appropriate accounts from your custom VCO.
- If the chart validation does not pinpoint the problem, run the report using the default template.
Check the filters on the report to ensure you're not filtering out accounts or activity. A combination of advanced filters on a VCO and report filters can cause you to filter your way out of balance. If the report is still out of balance, remove all filters from the report to determine if the filters are the cause; don't forget the column filters.
Run the report at a level 9. Running reports at a lower level will summarize activity. Increasing the level is an easy way to ensure no accounts are excluded from the report.

For more troubleshooting steps, take a look at Knowledgebase solution BB63184.

Talk to you soon!

Reconciliation Recommendations

Jennifer Maroney — Tue, 19 May 2009 12:33:00 GMT

At some point we all need to perform the dreaded reconciliation process. It can go smoothly, especially if you reconcile on a regular basis, or can be a headache if you don't reconcile regularly and have inconsistent data entry. The Transaction Business Rules, implemented in version 7.77, can help with inconsistent data entry, but you still need to get in the habit of reconciling regularly; have you caught my theme yet ?

Sometimes, no matter how careful you are, your subsidiary ledger does not balance to the GL. Here are some of the common causes of balancing issues:

Voided or deleted transactions
Voided checks
Transactions where the transaction date and post date are in different fiscal periods
Remitting Payroll taxes, deductions, or withholdings from the incorrect account
Journal entries made directly to the subledger summary account

You can find these sneaky transactions by creating a query or running additional reports.

I've put together a list of recommendations for reconciling subsidiary ledgers, but they can also help when reconciling balance sheet and income statement line items.

Find a reliable starting point. This may be your annual audit or another period when your subledger and general ledger were in agreement. If you cannot find a period, you may need to make a one-time adjustment at your reconciliation start date. Without a good starting point in the past, none of the subsequent beginning balances will be reliable.
Avoid manual journal entries to the summary accounts (e.g., Accounts Payable Summary, Accounts Receivable Summary, or the Accumulated Depreciation accounts). Make corrections to items such as invoices or checks in the subsidiary ledger, rather than making a manual journal entry to General Ledger. If you must make journal entries, consider creating a separate account for them to simplify subledger reconciliation.
Use journal codes to indicate the source of transactions. When posting journal entries, do not use the journal code used by the subsidiary ledger. Clearly and consistently identifying the journal codes helps you trace a transaction to its origin.
Reconcile monthly . Perform reconciliations at least monthly. Finding and correcting items regularly may prevent additional errors. If you have several periods to reconcile, start with the oldest and reconcile one period at a time.
Be aware of how your organization uses post dates and transaction dates. Items such as invoices may be dated in one fiscal period, posted in another, and paid in yet another fiscal period. When running reports, determine whether you want to see the transaction date or the post date. Ensure you have established guidelines for determining the correct transaction post date. Inconsistency can cause reconciliation issues and possible financial statement misrepresentation.
Ensure transactions have the correct post status.
Update your policies and procedures manual regularly. Include detailed instructions for correcting reconciling issues. Create a reconciliation schedule as part of your policies and procedures manual, detailing which accounts should be reconciled with what frequency, and by whom.

For detailed reconciliation information, check out the Subsidiary Ledger Reconciliation Guide.

The Balancing Act

Gregory Bingel — Wed, 06 May 2009 11:21:00 GMT

The ending balance on the Trial Balance does not match the Balance Sheet. What is going on here? Have any of you ever experienced this frustration? I’m sure you have. There is a valid explanation for this discrepancy: mock close. As a refresher, mock close is the term used to refer to the rollover of the net surplus/deficit for the year into the fund balance account for reporting purposes only. In essence, the mock close allows us to see our retained earnings as of the report date.

Both reports mock close differently, which causes the ending balances not to match. Let me explain. The Balance Sheet mock closes both the prior year's net surplus/deficit and the current year's net surplus/deficit as of the report date, where the Trial Balance mock closes only the prior year's net surplus/deficit. So when you run the two reports, the ending net asset balance on the Trial Balance should match the difference between the total net assets and net surplus deficit on the Balance Sheet. This value is called the 'beginning balance with current year adjustments'.

I hope this information helps when reconciling!

The Ledger Lowdown: Breaking Bank Reconciliation

Jennifer Maroney — Mon, 13 Apr 2009 16:55:00 GMT

A pain point with a lot of our customers with the optional module, Cash Management, is the dreaded bank reconciliation...dunh DUNH DUNH. Here's the thing, this is an easy process when you balance , but can be a huge headache when you don't. For those of you who are new to FE or Cash Management world, the process compares the GL cash account, bank register, and your bank's monthly statement to ensure they all have the same balance.

Balancing your bank register to your bank's monthly statement and GL cash account can get challenging and often complicated, especially if multiple modules use the same bank. Some common reconciliation issues are:

The reconciliation statement date or ending balance are incorrect
Checks created in a fiscal period other than the one in which they were voided.
Transactions marked as Do Not Post or have not yet been posted.
Manual journal entries made to the cash account and not posted or imported to Bank Accounts as an adjustment.
Bank adjustments not created when the journal entry batch was posted to the cash account, so a manual bank adjustment was entered as Do Not Post.
The debit account on the adjustment is not the cash account.
The bank is not set to track cash in General Ledger.

If your bank register and bank statement don't balance to your GL cash account, try the following:

Verify the Statement date and ending balance are correct by selecting Reconciliation, Go to Reconciliation Input from the Bank Account Reconciliation menu bar.
Ensure the bank is set to track cash in General Ledger.
On the Bank tab, verify the account numbers used for Accounts Payable and Cash Management are correct.
Post to General Ledger.
Post any unposted transactions that show in the bank account but not the GL cash account. Also, make sure all journal entries to the GL cash account are posted.
Note timing issues.
If you voided checks in a month other than the one in which they were created, the bank account and cash account will remain out of balance until the reversals post to the cash account. If you must correct this in the current period, you can create an automatically reversing journal entry to your GL cash account.
Determine how you will treat Do Not Post transactions.
Run a transaction register or transaction list to find transactions marked as Do Not Post. The account will remain out of balance until the transactions are posted, or manual journal entries are posted to balance to the bank register.
Check the debit account used on adjustments.
An account, other than the cash account, used as the debit account on an adjustment will cause the bank account and GL cash account to be out of balance. If the adjustment is unposted, edit the debit account. If the adjustment is posted, enter another adjustment to correct the original adjustment.

Do you have any other tips and tricks for fellow bank reconcilers? Let us and others know by leaving a comment.

Talk to you later!

The Ledger Lowdown: Before You Close Your Fiscal Year...

Jennifer Maroney — Wed, 11 Feb 2009 12:01:00 GMT

After your audit is complete, there are some things you need to do before actually closing the fiscal year. The process includes creating, printing, and comparing the Trial Balance, Income Statement, Balance Sheet, Statement of Activities, and the General Ledger reports. I recommend using a new default template for these reports. This will remove any VCO issues like missing or duplicate accounts. Here is how to run each of the reports:

Trial Balance:
1. On the General tab, select Balance as the Report Format.
2. Select <specific fiscal year> in the Date field and select the fiscal year you are closing.
3. On the Format tab, highlight Detail and mark the Show account category totals checkbox.
4. Click Preview and print the report.

Income Statement:
1. On the General tab, select the new default template and a level of 1.
2. Mark the Include accounts with no activity and Include inactive accounts checkboxes.
3. On the Columns tab, open the Actual column.
4. On the column screen, select the Date Range tab, <specific fiscal year> in the Date field, the fiscal year you are closing, and then click OK.
5. Click Preview and print the report.

Balance Sheet:
1. On the General tab, select the new default template and a level of 2.
2. Mark the Include accounts with no activity and Include inactive accounts checkboxes.
3. On the Columns tab, open the Actual column.
4. On the column screen, select the Date Range tab, <specific fiscal year> in the Date field, the fiscal year you are closing, the last fiscal period of your fiscal year, and then click OK.
5. On the Format tab, highlight Detail and mark the Print change in Net Assets checkbox.
6. Click Preview and print the report.

Statement of Activities:

On the General tab, select the new default template and a level of 1.
Mark the Include accounts with no activity and Include inactive accounts checkboxes.
On the Columns tab, open the Unrestricted column.
On the column screen, select the Date Range tab, <specific fiscal year> in the Date field, the fiscal year you are closing, and then click OK.
Repeat step 4 for the Temporarily Restricted, Restricted, and Total columns.
Click Preview and print the report.

General Ledger Report:
1. On the General tab, select the new default template.
2. Select <specific range> in the Date field and enter the day after the last day in the fiscal year you are closing. For example, if your fiscal year ends 6/30/2008, enter a start and end date of 7/1/2008.
3. On the Filters tab, change the Category filter from All to Include, select Net Assets, and click OK.
4. On the Format tab, highlight Detail, and mark the Include Grand Total checkbox.
5. Click Preview and print the report.

For all step-by-step instructions for the end-of-year process, review The Financial Edge End of Year Guide.

Talk to you soon.

Payroll Post Notes: Reconcile Your Payroll Taxes Before the End of the Year

Jennifer Maroney — Thu, 11 Dec 2008 15:33:00 GMT

Tax season is never a fun relaxing time whether you are an employer preparing W-2s or an employee filing your taxes. I can't help with filing your individual taxes, but I can help save you a headache or two when preparing your W-2s. My key advice...don't wait until January 26 (the last week in January) to start the process. If you do, good luck with Murphy's Law . Another step you can take is to reconcile your 4th quarter 941 tax report. This can be done before the end of the year and will help ensure smooth W-2 processing in January.

Oh, one quick thing about reconciliation before we get to the good stuff. If you don't reconcile regularly, add this important step to your policies and procedures manual. OK, let's get started.

The first step, of course, is to create a 941 Tax Report. Now that you have the report, refer to the table below to reconcile each line.

941 Line Number	Report Name	Report Options	Reconciliation Process
Line 1	Payroll Register	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report. Mark the Create an output query of checkbox and select Employees.	In Query, open the query created from the Payroll Register. Select the Results tab to view the number of employees.
Line 2	Adjusted Gross Wage Report	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report.	Verify the Federal Withholding (Federal W/H) Grand Total.
Line 3	Payroll Register	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report. Under Include calculation detail for, unmark all checkboxes except Taxes. In the Print a field, select Summary. Mark the Summarize activity by: checkbox and select Pay stub ID. On the Filters tab, select to filter Taxes by Federal tax.	Verify the Federal amount under Total Deductions/Employee Taxes.
Line 5a	Adjusted Gross Wage Report	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report.	Verify the Social Security (Soc. Sec.) Grand Total.
Line 5c	Adjusted Gross Wage Report	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report.	Verify the Medicare Grand Total.
Line 8	Payroll Register	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report. Under Include calculation detail for, unmark all checkboxes except Taxes. In the Print a field, select Summary. Mark the Summarize activity by: checkbox and select Pay stub ID.	Using the amounts under Total Deductions/Employee Taxes, calculate: Federal W/H column + [(Soc Sec + Medicare columns) * 2].
Line 9	Payroll Register	On the General tab, select to include paid calculations. Select <Specific date> for Payment date and enter the date range used on the 941 report. Under Include calculation detail for, unmark all checkboxes except Taxes. In the Print a field, select Summary. Mark the Summarize activity by: checkbox and select Pay stub ID. On the Filters tab, select to filter Taxes by Advanced EIC.	Verify the Advanced EIC amount under Total Deductions/Employee Taxes.

Want more Payroll end-of-year information? Take a look at the Payroll Post special edition: Payroll 7 End-of-Year Process. Talk to you soon!

How and Why Did We Come Up With Transaction Control Business Rules?

Charles Soderlund — Mon, 20 Oct 2008 19:26:00 GMT

Here in Financial Edge support, one of our biggest challenges is helping our clients reconcile their databases. This topic puts us in what you could call a gray area of our scope of support. The scope of support basically outlines what we can and can’t help you with. Traditionally, our response is very much geared to giving you the right reports to run and letting you knock it out. However, if you’ve ever been in a situation where you’ve inherited the books from a previous bookkeeper, you will understand that from the outside looking in, this is quite a daunting and time consuming task, especially if you have no idea what happened in the past.

That being said, we also realize that no software is perfect and that not all issues that can contribute to reconciliation issues stem from poor accounting practices or data entry. So how can your support representative prove that the reason that you are not able to balance your ledgers is something that you did or a software issue without reconciling the database with you? That is our dilemma, because our Support culture dictates that we do everything to help a client in need. So to try to help pry our Financial Edge support staff and you reconcilers out there out from in between that rock and hard place, we have introduced several new Transaction Controls in Financial Edge 7.77 that, once you have balanced your books, will greatly help you keep them that way with minimal effort on your part.

These features were mostly suggestions that you gave to us over the years that Financial Edge and its predecessor Accounting for Nonprofits have been in the field. A cross-departmental task force here at Blackbaud got together to see how we could improve the transaction entry and management processes to avoid some of the common pitfalls that cause reconciliation issues.

First, we made a list of the most commonly encountered situations that can make a sub-module out of balance to the General Ledger. Once we had the list, we ranked them in order of how hard it is to identify and correct these issues. The task force’s next step was to come up with solutions for as many of those scenarios that we could. In terms of solutions, we tried to take a proactive approach and provide features that would prevent you from creating these discrepancies going forward. Basically, we have provided options that dial back some of the flexibility of transaction entry so that there is less chance for transactions to be entered inconsistently causing unexpected differences in your control accounts. Like Uncle Ben said in Spiderman, “With great power comes great responsibility.” Well, we are helping you to exercise that responsibility and/or take some that power away from your end-users.

Keep in mind that these new rules will not magically make your books balance, but they will provide a way for you to control transactions going into system, so that all users are forced to enter data consistently. Here is a link to a previous post that covers the new rules: New Transaction Business Rules. Also, check out Knowledgebase solution BB499077.

Please let us know what you think!

Happy Reconciling!

Sr. Product Support Lead, The Financial Edge