Many times we see clients struggle to create reports that pull the correct data from their accounting system or corporate performance management solution. The data has been loaded into the system correctly, but building queries or reports by hand can cause headaches if data is suddenly coded to new GL accounts or new departments that did not exist previously.
This can lead to frustration when your team relies on these monthly finance reporting packages, and the purpose of a top-notch solution is to produce accurate reports with the click of a button. The functionality examples below can be found in top-rated Adaptive Suite cloud solution.
Here are some tips and tricks to help you avoid those frustrating report reconciliations.
1. Build reports that call out the following elements:
- Organization structure
- Time period
- Currency—if relevant
These may seem like simple rules to follow, but many users create reports by just pulling in accounts and a time period. They are not aware that if they fail to specify the version, the solution will default to the current version, when maybe instead they wanted the report to pull actuals. Being clear about which specific elements you bring into the report will ensure that you are bringing in data that aligns with your expectations and will help validate the data you are viewing.
2. When creating a P&L or bill of sale report, pull in the account rollup groups rather than cherry-picking individual accounts.
Sometimes we see clients individually grabbing GL accounts, then using a subtotal line to sum the accounts above. They verify that the subtotals tie out for the current period, but in future months there may be amounts that hit other GL accounts that were not previously captured. Three months down the line, their report subtotal is missing thousands of dollars in expenses.
3. Reference level elements rather than level dimensions or level attributes when possible.
Many times, level dimensions or level attributes are needed to create a customized view for specific reporting purposes. This requires maintaining level dimension or level attribute tags on all of your levels, to make sure all of your data is captured. I’ve often seen this approach work for the client when their Adaptive Planning implementation was recently set up and the team is diligent about making sure all levels were tagged with values. Fast-forward to a year later, however, and there may be 50 new levels in the structure, with only 30 tagged with a level dimension by the administrator. Now when the report is run, 20 levels of data are missing or are showing up in the “uncategorized” line item. At this point, you need to go back into your organization structure and make sure all of the levels are tagged with an appropriate dimension or attribute.
4. Use check figures.
These are key to confirming that your report data is correct, especially when using Adaptive Planning for external reporting. Here is one trick we have:
1. First, run a trial balance report in your enterprise resource planning system and paste the data for your specified time period into a blank tab on your OfficeConnect workbook. This can be used to come up with net income, total assets, total revenue, total opex, or whichever metric you wish to use as your check figure.
2. Next, create a check figure at the bottom of each tab of the OfficeConnect report tabs that refers to trial balance data tab. This allows for an easy confirmation each month after you refresh your report book and paste in the new monthly trial balance data. You can flip through each tab and verify that your check figure equals zero, and you are ready to distribute the report.
3. You can also utilize other check figures to validate that the same accounts across multiple tabs always equal the same value. Additionally, if you did utilize manual subtotals for expenses, you can pull in the total opex rollup line at the bottom of your report and do a check figure between your manual subtotals and your total opex rollup.
5. Be mindful of how you distribute your reports.
My advice is to have one person responsible for the entire reporting package and data validation. Once that person has refreshed the data, run the reports, and validated the amounts, he or she can then distribute the reports to the audience or even create the workbook as a protected book that does not allow for user edits. Avoid having multiple users creating their own individual reports and then compiling them into one master report book. That can lead to a lot of human errors and inaccurate data.
This article was originally published on the Armanino blog.