12. Solver: a Bakery Production Plan Model

The bakery at a coffee shop decides how much to bake per shift. Resources are limited: the baker has 420 minutes, there are 2,400 g of butter, and you cannot sell more than the demand. The goal is the maximum profit for the day.

A model for Solver is built like this:

• Variables — the production plan in B7:D7 (plain numbers);
• Objective — =SUMPRODUCT(B2:D2,B7:D7): profit per piece × quantity;
• Resource usage — also SUMPRODUCT: rate per piece × plan. Next to it are "Available" and the check =IF(E3<=F3,"OK","Violated");
• Demand — a check for each product: the plan is no more than demand.

In Excel the next steps would be: Data › Solver › Set Objective B10 › To: Max › By Changing Variable Cells B7:D7 › constraints E3:E4 <= F3:F4, B7:D7 <= B5:D5, B7:D7 = int › method "Simplex LP" › Solve.

There is no Solver here, so the last step is yours. A hint for your reasoning: work out the profit per baker minute (the scarcest resource) and start with the best product, then check the butter. The site checks that the plan is feasible and that the profit equals the best possible one, which we found by trying every whole-number plan.