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?
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.
Review the total row of the Working Trial Balance and note exactly which columns do not equal $0.00.
If it is the budget column, as we mentioned above, you may not have a problem.
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.
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.
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.
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:
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.