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
Highlighted
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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies