Weekly Dynamic: Solve for Values - Bank Rec Example

Let's say for example that someone has done a poor job of entering deposits in Dynamics GP. Instead of selecting appropriate receipts and processing a deposit, they selected receipts that don't match the way the physical deposit was made. Now you have a mess trying to balance the bank account. Having to add multiple items to match a single number commonly comes up in Bank Rec. (I don't just mean GP bank rec either. I've seen cases where you have to combine items differently for bank recs by hand, with Quickbooks, Peachtree and Oracle financials. It's a bank rec thing.)

Excel can be a big help when trying to combine transactions to match so we're going to work through an example. One caveat, this method finds one match. It may not be the only combination that can match. You'll need the Solver add in that comes with Excel. Use File-Options-Add Ins to see if it's turned on and activate it if it's not.

1. Key amounts into Excel that look like this:

The Amounts column contains the amounts that you will ultimately use to try to find a match. Indicator is just the number 1 all the way down. We use it in the calculation.

2. In cell C1 type SumProduct(A2:A7,B2:B7). This will show the sum of A x B.
3. In cell D1 type 27.62. This is the amount we are trying to combine items to find. It's here just for reference.

Excel is going to add up the items in multiple combinations until it finds a combination that matches 27.62.

4. Click the Solver icon
5. In Set Objective enter or select $C$2
6. Check Value Of and enter the amount you want to find - 27.62
7. Set By Changing Value of Cells to $B$2:$B$7
8. Click Add. Set Cell Reference equal to  $B$2:$B$7 and the middle field to bin.
9. Click OK. Click Solve.

Excel changes the 1's to 0's except for the lines that add up to $27.62.

To reset to search for another number, change column B back to all 1's, click the Solver icon and change the amount.

Many times when working through a bank statement there is a pattern. Items are off by a day for example. These are pretty easy to find by hand. Sometimes it's a lot harder an you a tool like this.