Everyone responsible for financial reporting wants as fast and as simple a quarter-end or year-end as possible. Your organization uses CaseWare Working Papers specifically to achieve this goal. It has many excellent tools to prevent mistakes and reduce the time invested by your finance team. It is THE tool that makes finance look good and prevents them from having to spend their evenings and weekends in the office.
An obvious prerequisite for this faster financial reporting is starting off with good data. No system, no matter how amazing, is going to help if we start off with a GL that doesn't balance.
As a result, another of our CaseWare Tips and Tricks is to immediately confirm the GL in CaseWare Working Papers foots to zero when you have completed importing your account balances.
To do this, click on the Trial Balance button on the Navigation toolbar.
Now look at the very last line (note it may take a moment to calculate if you have a very large GL). It should appear like this:
Depending on your budgeting practices, it may be the case that your budget values may not foot to zero so in this case, a non-zero balance in the budget column could be acceptable. But what if one or more of the "actual" columns do not foot to zero?
Step 1 - Repair File
On occasion - typically due to either a crash of a work station or a network interruption - your Working Paper's file may need a recalculation. This is essentially what the Repair File feature does. 
On the Tools tab of the Ribbon, select Repair File. Click all the boxes and select OK. Working Papers will prompt you to make a back up of your data file. We strongly recommend you to say "YES" to this prompt.
The repair file may take a few moments, and you will see a number of progress bars appear on your screen. Once completed, go back and review the totals at the bottom of the Working Trial Balance and see if they are now set to zero.
If they are now all set to zero, you are able to carry on with the month / quarter / year end. If not, continue on to step 2.
Step 2 - Determine What is Out of Balance
Review the total row of the Working Trial Balance and note exactly which columns do not equal $0.00.
Budget Column
If it is the budget column, as we mentioned above, you may not have a problem.
Adjustments Column
If the Adjustments column does not foot to zero, it means one or more of your adjusting entries are not in balance.
You can quickly find these entries by choosing Diagnostics from the Tools tab of the ribbon. Set the Report type to "Out of Balance Entries," choose the 1st option and click the OK button.
Any entries that are out of balance will be listed and you can now review and correct them.
Opening Balance Column
If the column that is out of balance is the Opening Balance column, there could be a number of causes and further investigation is warranted.
One thing to be aware of is that this column is editable. Meaning we can not exclude the possibility that someone modified one or more accounts intentionally or otherwise. With this one point in mind, we encourage our clients to see if there are other discernible causes.
Step 3 - Sum balance columns in Excel
Most of our clients import their end-of-period balances into the Opening Balance column of Working Papers from a Microsoft Excel file, and the remainder of this guide is written based on that common scenario. If you're out of balance after Working Papers generated an opening balance as part of the year-end close process, or if you are importing the Opening Balance directly from an accounting package, contact us to help in troubleshooting your file.
When you have populated the Opening Balance column via an import from Excel, it makes sense to check the Excel file to confirm that the balances being imported were good to begin with (i.e. sum to zero).
If the Excel file is also out of balance, there could be a number of causes. To start, determine if there is a setting in your ERP that excludes some accounts from the export to Excel. Even scarier, perhaps the GL in your ERP system does not balance to zero.You will want to check on that too.
If the balance columns in Excel do balance to zero, move on to Step 4.
Step 4 - Look for duplicate accounts in Excel
By default if CaseWare encounters two accounts with the same entity and account number it behaves in a very specific way. To understand, let's consider the following example:
In the above example, on import, CaseWare would update the Opening Balance column of account 101 Petty Cash to $200. Then when it comes across the second instance of account 101 (with the same entity) on the fourth row, it will update the balance to $1,000. The result is your Working Trial Balance will be out out of balance by $200.
Now you must determine why you are getting the same account multiple times in your Excel file. This will likely require some investigation back in your ERP system.
In the event that you determine that it is appropriate to have the same account / entity combination presented multiple times with different values, you will need to modify the import layout file to accumulate balances. On the last page of the Excel File Import wizard titled "Advanced Specifications," you need to enable the checkbox for "Accumulate Balances."
With this setting enabled, in the above scenario CaseWare will add together the $1,000 balance and the $200, keeping the Working Trial Balance footing to zero.
If you could not find any duplicate accounts in Excel, we are now left with three possible causes:
- Manual over-ride. Intentionally or unintentionally a balance was changed in the Opening Balance column in CaseWare some time after import.
- Changed Entity - The entity assigned to an account was changed in CaseWare after import.The reason this is a problem may not be immediately apparent however. Consider the following example:
- On import account, account 107 was associated with Entity GG.
- Some time after to import, the entity was changed. You may have done this to move a balance from one of your consolidation entities to another. Imagine you opened the "Assign Entities" interface from the Account Ribbon and dragged 101 from the "GG" entity to the "PS" entity.
- On re-import, if the Excel file still has the account being assigned to GG, CaseWare will not update the manually changed PS-107. It will instead ADD a "new" account GG-107, thereby throwing the GL out of balance.
- Omitted Accounts - One of the accounts that has a balance in CaseWare is no longed included in Excel file you are importing. This is a problem because by default, CaseWare only updates account values that are in the Excel file. Thus, if an account is not in the Excel file, the old balance will be retained, throwing your GL out of balance.
Step 5 - Delete Opening Balance column and Re-Import
If you have gotten this far, the final step is to re-import your Excel file. But first, we will eliminate existing balances (Note - now is a great time to take a backup of your CaseWare file!).
In the Working Trial Balance, right-click on the Opening Balance column header. Choose "Select Column". Then press the Delete key on your keyboard. This will set every account balance to $0. Now you are ready to re-do your import.
99.9% of the time these steps will guarantee you a balanced GL after the import completes.
