Analyze Sales Receivables with NAV and Power Pivot
Recently we added a new business to our Power Pivot portfolio and we would like to share the path and some of the techniques that we used.
The idea is to provide a means to answer questions around the accounts receivable area – some questions that are not easy or impossible to answer directly in NAV such as:
What percentage of all receivables or the receivables of a certain customer group are outstanding?
How do the days sales outstanding develop over time – differentiated by salesperson, subsidiary, etc.?
How many days does it take on average until invoices are paid with the possibility to see the average of a certain customer, the average of a certain responsibility Group, etc.?
Everything should not only be reported on a certain date but the development over time should be visualized as well.
Let’s start with the part in NAV since we are using NAV’s capability to publish pages and queries as oData-Webservices. First, we create a query that will later be used as the source of our fact table. It is based on the combination of customer ledger entries and detailed customer ledger entries, which is why queries are very suitable here.
The query is not overly complex:
Most information comes from the detailed customer ledger entries because we need to know all transactions (invoices, credits, payments) – both the initial entries and all applications, realized losses, etc. in Power Pivot.
Since we want to use a separate Customer Dimension in Power Pivot (it is always best practice to use separate lookup tables in Power Pivot), we just need the Customer No. field here.
We use the Customer List page (Page 22) as the source of our Customer Lookup. Alternatively we could have created another query that returns the customers but normally it is preferable to re-use an existing page. The dimension set page is another custom page that gives us the 2 global and 8 shortcut dimensions to make them available in Power Pivot.
To summarize the NAV-part, the following screenshot shows the objects that we need to publish:
The resulting data model in Power Pivot looks as follows in the diagram view:
We are using two date tables that we import from SQL-Views. Dim Reporting Date is the main date table that will be used to report on different periods.
Based on this data model, we created a number of measures that shed light on different aspects of the receivables area. The first measures would also be (more or less) available in NAV and are just used as the base of the more advanced measures:
Amount Receivables: this is calculated based on the Balance end of Year/Month/Day filter. All customer ledger entries prior to (and including) that date are summarized to show the receivable balance at that date
Amount Outstanding: Amount of receivables that are overdue at the selected reporting date
Sales: This is the sales value of the customer ledger entries
Monthly Average of Receivables: Average of the end of month receivables balances.
Amount Customer Ledger Entries: Sum of Amount (LCY) of all customer ledger entries in segment.
Using these base measures Power Pivot allows us to create the following measures:
Days Sales Outstanding: Average number of days that it takes to collect revenue after a sale has been made. It is calculated based on the average monthly receivables divided by the customer sales times the number of days in the reporting period
% Share Outstanding: This measure shows the share of receivables that are outstanding. It is calculated as follows: Amount Outstanding divided by Amount Receivables as per reporting date
Receivables turnover rate (year): The receivables turnover rate tells how many times during the year the company collects the average amount of money its customers owe. This measure is calculated as follows: total yearly sales revenue divided by the balance of the accounts receivables. If the measure is analyzed on the month level, the value is extrapolated in order to reflect the yearly turnover rate
Avg Days Payment Terms (Weighted): Average number of days according to payment terms (i.e. the period allowed to a customer to pay off the amount due)
Avg Days until Closed (Weighted): Average number of days from the invoice date until invoices are closed (i.e. entirely paid or otherwise applied); the average is weighted by invoice amount in order to take the different value of invoices into consideration
Avg Days Overdue until Closed (Weighted): Average number of days from the due date until invoices are closed
To see it in action we created a pivot table for example showing the key figures per salesperson:
I may drill down into the first Salesperson’s customers to compare their payment behaviour:
Using our addon, it is possible to jump from each measure into the trend view (this screenshot was taken from another dataset):
It is also possible to compare the trend of any combination of dimensions – for example to compare the number of days it took on average until invoices were closed between different branches: