Home

Excel Solver finding min value of the difference between budget and actual expenses

%3CLINGO-SUB%20id%3D%22lingo-sub-650941%22%20slang%3D%22en-US%22%3EExcel%20Solver%20finding%20min%20value%20of%20the%20difference%20between%20budget%20and%20actual%20expenses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650941%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3EI%20have%2038%20receipts%20from%20a%20client%20funded%20travel%2C%20the%20budget%20was%20%246000%2C%20and%20actual%20spending%20is%20%246777.49%2C%20therefore%20I%20need%20to%20find%20a%20combination%20of%20receipts%20that%20the%20total%20amount%20is%20as%20close%20to%20%246000%20as%20possible%20to%20recover%20from%20the%20client%20and%20put%20the%20rest%20in%20company%20expenses.%3C%2FP%3E%3CP%3EI've%20tried%20Solver%20to%20solve%20this%20problem%2C%20but%20the%20solution%20is%20different%20every%20time%20I%20ran%20the%20Solver.%20There%20is%20once%20it%20came%20up%20with%20the%20perfect%20combination%20that%20total%20amount%20is%20exactly%20%246000%20while%20other%20times%20not.%20How%20do%20I%20set%20up%20the%20Solver%20case%20to%20make%20sure%20I%20have%20the%20best%20result%20every%20time%3F%3C%2FP%3E%3CP%3EBudget%20Cell%20B1%3D6000%3C%2FP%3E%3CP%3EActual%20Cell%20B2%20%3DSUMIF(Table1%5BRecoverable%5D%2C1%2CTable1%5BAmount%5D)%3C%2FP%3E%3CP%3EDifference%20Cell%20B3%20%3D%20B1-B2%3C%2FP%3E%3CP%3ETable1%5BRecoverable%5D%20%3D%200%20or%201%2C%201%20means%20the%20expense%20will%20be%20recovered%3C%2FP%3E%3CP%3ESolver%20parameters%3A%3C%2FP%3E%3CP%3ESet%20Objective%3A%20%24B%243%20(Min)%3C%2FP%3E%3CP%3EChanging%20cells%3A%20%24E%246%3A%24E%2443%20(which%20is%20the%20Table1%5BRecoverable%5D)%3C%2FP%3E%3CP%3EConstrains%3A%3C%2FP%3E%3CP%3E%24B%242%26lt%3B%3D%24B%241%3C%2FP%3E%3CP%3E%24B%242%26gt%3B%3D0%3C%2FP%3E%3CP%3E%24B%243%26gt%3B%3D0%3C%2FP%3E%3CP%3E%24E%246%3A%24E%2443%20%3D%20binary%3C%2FP%3E%3CP%3Eand%20I%20used%20Evolutionary%20solve%20method.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20attached%20file%20with%20detailed%20data.%3C%2FP%3E%3CP%3EIf%20any%20one%20can%20provide%20possible%20solution%20or%20let%20me%20know%20how%20do%20I%20improve%20the%20constrains%20is%20much%20appreciated.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-650941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESolver%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Tina_Ho
Occasional Visitor

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.