Payment arrears calculation help

[macfuller]

I'm having trouble bending my head around developing a formula to show average amounts of outstanding payments by the number of months they are in arrears.

Let's say we have fairly constant purchasing - $1 million / month. But we don't pay our bills immediately - delays in vendors sending invoices, invoice exceptions, net 45 day payment etc. So how much on average are our obligations after 1 month, 2 months, etc.

So to average how much outstanding we have after (say) 3 months we would see how much was outstanding on our January purchases in April, February purchases in May, March purchases in June etc. We would show those averages for each month "bucket" from 1 -12.
Then we could have a report sliced by vendor, equipment type, or other criteria with a payment profile of outstanding accruals

	Unpaid After
	1 month	2 months	3 months
Widget	$5,000	$3,000	$1,000
Sprocket	$3,000	$2,000	$1,000
Gizmo	$100	$60	$30

<tbody>
</tbody>

Relevant fields

Table	Field
Calendar	Date
Purchase Order	PO Number PO Date PO Amount
Voucher	PO Number Check Date Paid Amount

<tbody>
</tbody>

So the measure would first get the unpaid amount within the filter context e.g. SUM(PO Amount) - SUM(Paid Amount) then filter that into the month buckets e.g. FILTER(UnPaid, Check Date - PO Dispatch Date > 90), and finally give the average of those unpaid values within the month bucket.
This could also be adapted to our Accounts Receivable information as well. I just can't get the SUMMARIZE, FILTER, SUM and AVERAGE concepts into the right framework.

Thanks for your help!

 

[Matt Allington]

He a read of my old blog article here. It may help you. Collections Reporting Using Power Pivot - Excelerator BI

 

[macfuller]

Thanks Matt. That's on the right track but about halfway to my goal. Looking at your totals matrix in the post, each cell in the grid has an implied monthly aging bucket (as below) as well as the total amount and/or percentage, underlying your result as you explained. ( The grid is a mirror image of yours of course, just threw it together.)

Issued		Paid
	Jan	Feb	Mar	Apr	May
Jan		1	2	3	4
Feb			1	2	3
Mar				1	2
Apr					1
May

<tbody>
</tbody>

I need two additions to your method


1) Instead of total paid within each cell I need the total in arrears. So for a cell representing a 2-month bucket I need SUM(PO Amount) for the issued month minus cumulative SUM(Paid Amount) for what's been paid against those POs in just those 2 months.

2) I need to turn your matrix into a single line of values by averaging the cells in each aging bucket to give me a single value for each bucket. So for my 2-month value I would take the average of all the values in the 2-month cells of your matrix.

Then I can filter multiple lines to get a comparative graph of accrual aging by facility, vendor, payment method, etc. As an example of what I'm trying to solve, in 2017 Q1 we had $2.1MM of payments related to 2017 purchase orders and $2.3MM related to 2016 POs! We recently switched accrual methods - not effectively it seems - so it looks like we had $2.3MM of unbudgeted expense in 2017 Q1! So I need to quickly slice 'n' dice the aging profiles to see if certain vendors or facilities are failing to invoice us in a timely manner, and to highlight where the new accrual policy is breaking down. I can either poke thru a matrix like yours trying various facility and vendor combinations at random and try to remember what everyone's numbers were, or I can have a graph with our top x vendors or facilities and immediately see based on the slope who's out of whack.

We also can't track backorders in our system, and this aging report would help us there. If MedicalWidgets Inc. has POs issued in January and 25% is invoiced in March, 25% in May etc. the aging report gives us a very good idea that they have a problem with fulfillment, and again if we can see that they're consistently 3 months late in shipping based on the average monthly profile that's a huge help for us.

I looked here (which is a grid similar to yours) and here, but they all seem to stop short of being able to average the buckets to create a single aging line, and they're all going with just the payment totals rather than the amount in arrears.