Depending on your Sage business solution, you may or may not have the Missing Accounts feature in your Financial Report Designer report. Even if you have this functionality, you are still not automatically alerted that account level detail may be missing from your layout. In this tip, I’m going to share a way to identify missing accounts using account categories/classes and conditional formatting.
The first step is to create a report using account categories/classes and account level detail. If you’re interested in finding out more on how to do this, take a look at our Learning Portal.
Here is a report where account categories/classes have been used for the subtotal rows, and account level detail is shown for the Revenue group. You can see that adding up the values for the account level detail does not match the subtotal for the group. The subtotal is correct, as a formula that is based on a category/class will automatically include any new GL accounts. This implies that accounts are missing at the detail level.
Here is a way you can identify that there are missing accounts in your report:
1. Insert a row between your subtotal row and above the account level detail.
2. Then, sum the values for accounts together so you have a total to compare with the subtotal.
3. Select the subtotal cell and on the Home tab on the menu ribbon, click Conditional Formatting.
4. Select the Greater Than option and reference the cell which has the sum of the accounts in it. You can then format the cell by clicking Custom Format.
5. Then, click on Conditional Formatting under the Home tab again, this time select the Less Than option, reference the cell which has the sum of the accounts in it, and format appropriately.
You will now easily see that you have accounts missing, as if you do, the subtotal will appear according to the select formatting. You can also then hide the row containing the sum of the accounts to keep your layout tidy.
To find any missing accounts you can use the Missing Accounts tool if your product has it, or you will need to identify the account manually and add it.
To find out more on how to add missing accounts, check out this short video:
And If you would like more information on the subject, check out the following webcast which was done for Sage Enterprise Management: