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.

 

Memberships

South African Institute of Chartered Accountants. Designation CA(SA)  Internationally Accredited Financial MediatorsIndependent Regulatory Board for Auditors. Designation RA<Engineering Council of South Africa. Designation Pr(Eng) Member of MENSA, the High IQ Society

 

 

 

Find Us Here

Address: 1st floor, Gallagher School Building, 537 Lupton Drive, Midrand, Gauteng