Allocating Debtor payments
For my sins, I’m the one who has to allocate debtor payments as we are sticklers for payment and it’s worth running this at partner level. But so often a debtor makes a lump payment against an account with lots of open items and without giving us a remittance advice. So which invoices did they pay?
I love a challenge and I tried all sorts of techniques to determine the combination of invoices that fits the total paid, but I fell flat.
Then I Googled “How can I find a subset of numbers that add up to a particular total” and Bingo!
This is what came up http://superuser.com/questions/204925/excel-find-a-subset-of-numbers-that-add-to-a-given-total
Now, when you try it, it seems a bit complicated, but it is well worth the effort. The writer missed out just two little things that gave me trouble.
1) When entering the formula in the Objective (Target) cell, you type (using his example) =SUM(A1:A100*B1:B100) then to get the curly brackets you must press <Ctrl><Shift><Enter>. You can’t type them in as he suggests. This sets up an Array formula, something that I still haven’t fully got my head around in Excel, but it amounts to a shorthand way of writing =SUM(A1*B1,A2*B2,A3*B3,………..A100*B100)
2) To add the constraints, you first click on Add, Then in the cell reference select the range that he indicates (or your equivalent), then from the <= dropdown select bin and click OK
Everything else is explained very well (the conditional formatting is a nice touch) and you can set up a master solver for use over and over again.
If you get stuck, give me a call on 011 656 2945 or drop me an email and I’ll send you the master that I set up for my use.
Should you wish to make an appointment, please feel free to visit derek’s diary and book a time that suits you.
0 comments