Hi there,
I have 38 receipts from a client funded travel, the budget was $6000, and actual spending is $6777.49, therefore I need to find a combination of receipts that the total amount is as close to $6000 as possible to recover from the client and put the rest in company expenses.
I've tried Solver to solve this problem, but the solution is different every time I ran the Solver. There is once it came up with the perfect combination that total amount is exactly $6000 while other times not. How do I set up the Solver case to make sure I have the best result every time?
Budget Cell B1=6000
Actual Cell B2 =SUMIF(Table1[Recoverable],1,Table1[Amount])
Difference Cell B3 = B1-B2
Table1[Recoverable] = 0 or 1, 1 means the expense will be recovered
Solver parameters:
Set Objective: $B$3 (Min)
Changing cells: $E$6:$E$43 (which is the Table1[Recoverable])
Constrains:
$B$2<=$B$1
$B$2>=0
$B$3>=0
$E$6:$E$43 = binary
and I used Evolutionary solve method.
I've also attached file with detailed data.
If any one can provide possible solution or let me know how do I improve the constrains is much appreciated.
Thank you.
