Solver is the tool for one kind of production question: given limited hours and several products, how much of each should we make to leave the most money? It's called a product mix problem, and it's a linear program. You lay out three things on a sheet: the cells Solver may change (batches of each product), the number to make as large as possible (what's left), and the limits (hours on each machine, what will sell). Solver tries combinations until it finds the best one. In the example below, a made-up bakery planning by instinct, croissants first because they leave the most a tray, makes $1,128 in a day. Solver's plan, from the same mixer, the same ovens and the same orders, makes $1,400, with no croissants in it at all. Below is the model, the steps in Excel, why the answer looks the way it does, and the workbook.
The short version
- Decide what Solver may change: batches of each product.
- Work out the objective: total left = batches × left a batch, added up.
- Write the limits: hours used on each machine ≤ hours available; batches ≤ what will sell.
- Make the batches whole numbers.
- Run Solver with Simplex LP, and read which limits are used up.
- The best product isn't the one that leaves the most. It's the one that leaves the most for each hour of whatever runs out.
- TaroStack's planning, on Growth, works from real capacity and stock. More below.
The problem
| Product | Left a batch | Mixer hours a batch | Oven hours a batch | Most that will sell |
|---|---|---|---|---|
| Sourdough loaves, rack of 40 | $62.00 | 0.50 | 1.50 | 12 |
| Baguettes, rack of 60 | $48.00 | 0.40 | 1.00 | 10 |
| Croissants, tray set of 96 | $118.00 | 1.50 | 0.75 | 6 |
| Cinnamon rolls, tray set of 72 | $84.00 | 1.00 | 0.75 | 8 |
| Sandwich bread, rack of 36 | $44.00 | 0.60 | 1.25 | 10 |
Available in a day: 14 hours at the mixer station (two bakers, seven hours each) and 22 oven hours (two decks, eleven hours each).
"Left a batch" is the price of the batch less its ingredients and packaging. "Mixer hours" here means hands-on time at the mixing and shaping bench, which for croissants includes laminating: an hour and a half a tray set.
The plan by instinct
Croissants leave $118.00, the most by far, so make all six that will sell. That's nine mixer hours. Cinnamon rolls are next at $84.00: five batches use the last five mixer hours.
| Batches | Left | Mixer hours | Oven hours | |
|---|---|---|---|---|
| Croissants | 6 | $708.00 | 9.0 | 4.5 |
| Cinnamon rolls | 5 | $420.00 | 5.0 | 3.75 |
| Total | $1,128.00 | 14.0 of 14 | 8.25 of 22 |
The bench is full and the ovens are empty for most of the day.
Setting it up for Solver
The workbook's sheet has a row for each product. Column F is the batches to make. Three cells add things up: J2 is total left, K2 is total mixer hours, M2 is total oven hours. L2 and N2 hold the hours available.
- Turn Solver on, once: File, Options, Add-ins, Manage Excel Add-ins, Go, tick Solver Add-in. It then appears on the Data tab.
- Set Objective: J2. Choose Max.
- By Changing Variable Cells: F2:F6.
- Add the constraints:
- K2 <= L2: mixer hours used can't exceed mixer hours available.
- M2 <= N2: the same for the ovens.
- F2:F6 <= E2:E6: don't make more than will sell.
- F2:F6 = integer: whole batches.
- Tick "Make Unconstrained Variables Non-Negative", so it can't plan a negative batch.
- Select a Solving Method: Simplex LP. Every formula here is a plain multiply-and-add, which is what makes the problem linear.
- Solve, and keep the solution. If it stops a few dollars short of $1,400, open Options and set Integer Optimality to 0, then solve again.
Google Sheets and LibreOffice Calc have their own solvers, with the same four ingredients: an objective, cells to change, constraints, and a method.
Solver's plan
| Product | Batches | Left | Mixer hours | Oven hours |
|---|---|---|---|---|
| Sourdough loaves | 4 | $248.00 | 2.0 | 6.0 |
| Baguettes | 10 | $480.00 | 4.0 | 10.0 |
| Croissants | 0 | $0.00 | 0.0 | 0.0 |
| Cinnamon rolls | 8 | $672.00 | 8.0 | 6.0 |
| Sandwich bread | 0 | $0.00 | 0.0 | 0.0 |
| Total | $1,400.00 | 14.0 of 14 | 22.0 of 22 |
$272 more in a day than the plan by instinct, 24%, from the same people and the same ovens. Both limits are used up exactly.
Why no croissants
Look at what each product leaves for an hour of each resource.
| Product | Left for a mixer hour | Left for an oven hour |
|---|---|---|
| Sourdough loaves | $124.00 | $41.33 |
| Baguettes | $120.00 | $48.00 |
| Croissants | $78.67 | $157.33 |
| Cinnamon rolls | $84.00 | $112.00 |
| Sandwich bread | $73.33 | $35.20 |
Croissants are the best use of an oven and nearly the worst use of the bench. On this floor the bench runs out first, so a product that eats bench time has to earn a lot for each hour of it, and croissants earn $78.67 where baguettes earn $120.00. Solver doesn't know anything about baking. It found that six trays of croissants cost nine bench hours, and that each of those hours earns more in bread.
This is the thing to take from the exercise, with or without Solver. Rank products by what they leave for each unit of the resource that runs out, not by what they leave a batch.
Reading the answer like an owner
- Zero croissants is the answer to the question asked, not a business plan. A wholesale bakery whose customers expect croissants adds a constraint: F4 >= 3. Solver then finds the best plan that includes them, and the drop in the total is the price of keeping that customer happy.
- A limit that's used up is worth money. Raise mixer hours from 14 to 15 and solve again. The increase in the total is what one more bench hour is worth, and that's the most you'd pay for it in overtime.
- A limit with slack isn't. In the instinct plan the ovens had nearly fourteen hours spare. A third oven would have added nothing.
- The model is only as good as its hours. Take them from your own production records: see what is a production log and how to calculate production capacity.
Once the mix is chosen, laying it out across the week is a different job: see production plan format in Excel and master production schedule template. A bakery's own version of the whole process is in bakery production planning.
Get the workbook
Download the Solver production mix workbook. One sheet and a read-me: the five products with their figures, the batches column Solver changes, and the totals and limits it needs. It's saved with the answer for the example. Type zeros in the batches column and run Solver to watch it get there, then put in your own products. It's an ordinary Excel file with no macros.
Where this stops working
The model assumes flour, butter and boxes are all in the building, that every hour is as good as every other, and that the numbers in the table are right. On a real morning the butter delivery is short, one deck is down until ten, and the "most that will sell" column was typed last month. Solver gives the best answer to the problem on the sheet, and the sheet has to be rebuilt by hand whenever the floor changes.
How TaroStack does it
TaroStack keeps the facts a plan has to respect, and plans from them.
Planning, on Growth: what to make, what to buy and when, in plain words, as drafts you approve.
Work centers and capacity, so a week's plan doesn't promise more hours than you have.
Exceptions named ahead of time: what will run short, arrive too late, or expire before it's used.
"What could I make right now" checks a plan against the stock actually on hand.
Actual against standard on every batch, so the hours and yields in the plan come from what happened.
| The input | Solver sheet | In TaroStack |
|---|---|---|
| Hours available | Typed in | Work centers and capacity |
| Is the butter here | Assumed | What could I make right now |
| What will sell | A column, typed last month | Forecasts from your own history |
| Hours a batch really takes | An estimate | Actual against standard |
Planning is on Growth at $39 a month (50 recipes). Recipes and batches alone are on every plan, from $9.99 a month (5 recipes). The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.
