This blog introduces the Sage 300 ERP A/R Aged Trial Balance Report, and presents solutions to address challenges like adding more Aging Periods, grouping by National Account, filtering by the Account Set (AR control account) at transaction level, and performance issue - using PowerPivot. PowerPivot is a free add-in to Excel 2010 and is a built-in functionality of Excel 2013.
The Sage 300 ERP A/R Aged Trial Balance Report is a very sophisticated and flexible report that provides quite a few parameters. There is a dedicated aging dll, a.k.a the processing view to processing all types of transactions and documents and finally populated the ARAGED table which serves as the data source for the report.
Here is a sample output of the Sample Company Inc. in Sage 300 ERP 2012.
It is possible to use PowerPivot to reproduce the same result.
In addition, the PowerPivot report allows users to easily add a new aging period, group by national account, filter by account asset at transaction level and much more. You might want to try it out here.
In terms of performance, it can take a long time for the report to process data. I tested for a Sage 300 ERP company that has over 600K invoice documents and 1.4m payments. It took about eight minutes for the Crystal report to complete. Using PowerPivot in 64-bit Excel 2013, it takes five to six seconds to respond to a selection in slicers. To make a fair comparison, we need to add the time needed to load the data into the Excel data model, which takes about two minutes and depends on things like network speed. Once the data is loaded, PowerPivot allows you to quickly slice the same data. Using Crystal reports, you have to wait another eight minutes for each change of the parameters.
In summary, PowerPivot can help answer hard questions in a timely fashion.
Your company might have an ERP system that has accumulated very large data over the years; you might know your most valuable customers and bestselling products very well. But what if you were asked these questions by week, by population of the city, by pay grade of the sales representative, and by color of the product, together with the corresponding product cost and marketing expenses? One single PowerPivot report to help you stay on top of the game by helping you answer these questions -- and quickly.
Want more? Read my blog post on creating an AP Aging Report.
866.647.2611