Excel Solver / Math Problem

[denton]

I have a general ledger account that is out of a balance. Auditors tomorrow
will force a large write off or other fines if this account is not
reconciled. In order to find where the account is off I am trying to solve
mathematically where it is right. Large wire transfers (credits) are made of
many debits but I don’t know which debits are making up the wire transfers.
In other words, I have a number X and some grouping of other numbers equals
X.

I set up an excel solver model that that attempts to test every combination
by using binary “switchesâ€. The numbers 0 or 1 multiply by each test value
to give either 0 or the value itself. All of these result values are added
up and if the resulting sum matches number X then we found are match. The
problem is the Excel solver model is not really solving using a binary
method. It is testing all values from 0 to 1 and telling me if I get a
solution that meets the binary constraints.

Here is small sample data table and the constraints:
Changing cells: B2:B6
Constraint: Difference = 0
Constraint: B2:B6 = binary

Test Values Binary # Result
$456,136.59 1 $456,136.59
$456,136.59 1 $456,136.59
$197,181.09 0 $0.00
$195,487.11 1 $195,487.11
$521,298.96 0 $0.00
$130,324.74 0.0002 $31,767.42
$65,162.37 0.25047 $16,321.22
$390,974.22 0.30657 $119,862.06
$252,198.01 0.231078 $58,277.46

Sum of Results: $1,333,988.45

Number X: 3,359,333

Difference (Target of zero): ($2,025,344.55)


What is the fastest way to setup a solution in Excel or custom programming
(I can program C#,VBA) to solve this problem?

 

[Gary''s Student]

You are almost there:

You need three constraints on the B's

1. they must be int
2. they must be >= 0
3. they must be =<1

 

[Dana DeLouis]

Hi. A Binary Constraint that ends up being 0.3065 usually indicates
that Solver aborted early because it got confused, and gave up.
For this type of problem, the usually technique is to set B1:B6 as
binary constraints as you did.

The third column is usually not used. The Sum formula is
=Sumproduct (A1:A6, B1:B6)

The Target Cell is as you have done.

The Target Cell will almost never be Zero.
So, one usually minimizes the Target Cell with the constraint that
Target Cell >= -.005 (or something similar close to zero depending on
what you are doing)

You should also adjust Solver's options for Precision, Tolerance, and
Convergence.

HTH
Dana DeLouis

 

[Tushar Mehta]

This is probably too late to help you if you haven't already solved your
problem...

You are on the right track. The approach Solver uses is a 'standard' one
for problems with binary variables. Unfortunately, the default version of
Solver has some limits on the size of the problem it can solve.

For a Solver based template or for VBA code that lists all (or as many as
desired) combinations, see
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/templates/match_values/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions