I recently blogged about using Excel’s PowerPivot functionality to create more flexible AR aging reports, and AP aging reports using Sage 300 ERP data. In this blog, I will cover how to create a cash flow forecast report based on the aged receivable and the aged cash requirements.
Let’s take a look at the aged cash requirement first. Unlike the AP aging report which “groups outstanding transactions into a current period and other aging periods by document date or due date”, the Aged Cash Requirements report “highlights the cash amounts that will be required to meet obligations as they become due, including the amount needed to pay invoices that are overdue, current, and due in each of four future periods you define.”
As you can tell from the screenshots below, they present the same data in different perspective.
With Aged Cash Requirements ready, I can get the cash forecast for the current and future periods at a given cut-off date and age-as-of date, as circled blow for example. This is achieved by applying the same slicers for Cut-Off and Age-As-Of, and the same Aging Period definition to the Excel data model, and return the calculated data with Excel formula.
As you might have noticed, the cash accounts balance is listed as well. They are returned by using the Excel CUBEVALUE function which gives you the flexibility to format your report beyond what PivotTable allows.
For example, the G/L account 1020 represents “Bank account, operating” in the sample company; I created a measure named “CY BAL” to calculate the account balance. Then, I can type in the formula below to any cell I want to return the account balance for G/L account 1020. As you see in the screenshot below, the Excel formula autocomplete worked well.
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[CY BAL]","[COA].[1020]",Slicer_CalendarYear,Slicer_MonthOfYear)
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 would like to connect the worksheet 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!
866.647.2611