CODEWITHSUNDEEP
excellinear-programmingsolver
ahmad noori
ahmad noori

Reputation: 127

Excel Solver - price per serving change after unit 5

i have created following excel file that uses the solver to come up with the cheapest possible diet based on the constraints on calories, fat, protein, and carbs. File here

i want to add another constraint where the price for potatoes changes to 0.40 instead of 0.15 after the fifth unit. How is this constraint modeled in a linear way in excel?

thanks

Upvotes: 0

Views: 239

Answers (1)

Erwin Kalvelagen
Erwin Kalvelagen

Reputation: 16762

This can be modeled as:

 potatoes = cheap_potatoes + expensive_potatoes
 cheap_potatoes <= 5
 potatoes, cheap_potatoes, expensive_potatoes >= 0

In the objective function, replace 0.15*potatoes by 0.15*cheap_potatoes + 0.4*expensive_potatoes. As cheap_potatoes are cheaper, the objective (cost minimization) will automatically try to use these first.

This should not be too difficult to implement in your spreadsheet.

PS. The other way around (first 5 are expensive, after that cheaper) is also common (volume discount). This is a bit more difficult to model (requires binary variables).

Upvotes: 1

Related Questions