Following my previous blog about creating AR aging reports using PowerPivot in Excel, I’d like to cover AP (Accounts Payable) in this blog.
The Excel data model for AP aging is similar to that of AR. The caveat is, unlike AR where you can use an invoice and/or document number to join the invoice and receipts tables, in AP you have to combine vendor and invoice to join the invoice and payment tables since you could potentially have the same invoice number from different vendors.
Let’s take a look at the out-of-the-box AP aged payable report first. As the screenshot below shows, it is very sophisticated report.
When you use an Excel data model using PowerPivot to this table, you get the benefits of flexibility to let you look at things such as aging bucket and filter by a host of different factors. You also get an improvement in the report performance. As circled in the screenshots below, users can edit or add the aging bucket as needed. In the same worksheet, there is a statistics by document types update when you change the Cut-Off year and period.
Such a report can be uploaded to Office 365 and shared with others in your organization. In this example, I shared it with everyone.
Click here to get a static version of the report to get a sense of the look and feel. However, you cannot interact with the slicers, etc. from this version.
If you are interested in playing with the report, you can download a copy from this link. Please note, this report is developed in Excel 2013 Professional Plus and you need the same Excel version to interact with the worksheet. Also note that this worksheet is protected, so you'll be unable to modify it or connect with your Sage 300 ERP instance.
If you would like to get an unprotected version of the worksheet and/or connect to your Sage 300 ERP system, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!