On 2002-03-23 12:43, Aladin Akyurek wrote:
On 2002-03-23 10:51, Chris Davison wrote:
I have VBA which will find any permutation of numbers given a target value
so if your target is $ 5,995.37 it will find the 4 or 5 or 6 or however many numbers from a list add up to this total
emial me and I'll send it, I find it very useful at bank recs
it's currently configured for any 6 numbers from a sample, but in theory can be augmented to look for any amount, at the trade-off for time : one of the previous posters pointed out the 1million+ permutations which is absolutely correct
any 6 from a sample of 100 takes around 1/2 hour
let me know and I'll send it
Chris
novulari@hotmail.com
Chris,
Just curious: Did you often do this task manually? If so, how did you go about?
The reason I ask is that people (accountants?) who do this might be using heuristics (shortcuts or rules, say) that must beat brute force approaches in most cases. If those heuristics can be made explicit, maybe a system of formulas might be found to implement them in a spreasheet.
Any comments?
Aladin
Sorry for the delay... I must have missed the follow ups...
I use this each Monday morning and once a month. The commenest problem area is the grouping of sales via credit cards or debit cards. So we have 7 sales hitting the bank statement vs only 2 sales processed on the accounting system.
(Obviously, the flaw here is that the person who processes them needs to align their processing to what it actually happening out there on the sales terminals, but this is outside my control!)
Luckily, my problem revolves around the date : the grouping of the sales by date, so I have an indirect help in so far as I can use my judgement (via a pivot table) with the dates.
From the project's inception, I demanded that the processing and banking always include a narrative at the end : "MMDD" so I could later interrogate and group by it.
Heuristically : I sum amounts based on identical MMDD endings
so VISA0126 $ 1450 = VISA0126 $ 1450
and "assume" that :
VISA0323 $ 5500 = AMEX0323 $ 5500
(operator input error on card type)
Also there is the "transposition" rule :
Sales is $ 358
Operator keys $ 538
difference = $180
if the difference is divisible by "9", there's a strong possibility someone has transposed 2 digits somewhere.
Translated to formula : hmmmm...!
if(mod(suspect1-suspect2,9)=0) then proceed
I also, if still stumped, assume net values have been keyed rather than gross (after TVA). Sometimes this helps.
Formula along the lines of above but (/117.5)*100
Also as a sub-layer to all of these, I sometimes carry out the above 1st on credits then on debits, on the basis that operators often process in batches : batches of credits in the morning, batches of debits in the afternoon (for example)
(batches of rejected credit cards, batches of subscription payments, by date)
I *do* have spreadhseets which deal with the *type* of problems for each bank.... each attempts to replicate that heuristic logic of the problem.... it's interesting that you can sort of second-guess the mathematics of the problem based on what kind of environment you are trying to reconcile within... mine have very definable borders : cheque accounts, income accounts, credit card accounts, membership database accounts - each seems to throw up different kind of imbalance scenarios, and thusly, maybe, manageable imbalance solutions
The perfect answer is to *ensure* that what is processed is the same as what hits the bank : but with different companies, different staff, different priorities, systems, skills and different commitment levels, it's practically impossible
Hence the fire-fighting !
I use all these, plus the VBA code from this board.