Skip to content

Production Scheduling with Excel Solver: A Worked Example

Production scheduling with Excel Solver, step by step: five products, mixer and oven hours, what will sell, and the mix that leaves the most. Free workbook.

By Koa Sterling. Product specialist at TaroStack and small business owner. Yes, I am a real human, and I actually sit in front of a computer and write these articles. Reviewed November 21, 2026 · 8 min read

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.

  1. Turn Solver on, once: File, Options, Add-ins, Manage Excel Add-ins, Go, tick Solver Add-in. It then appears on the Data tab.
  2. Set Objective: J2. Choose Max.
  3. By Changing Variable Cells: F2:F6.
  4. 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.
  5. Tick "Make Unconstrained Variables Non-Negative", so it can't plan a negative batch.
  6. Select a Solving Method: Simplex LP. Every formula here is a plain multiply-and-add, which is what makes the problem linear.
  7. 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.

Questions people also ask

How do I use Excel Solver for production planning?

Put the batches of each product in cells Solver can change, add up what's left as the objective, add up the hours used on each resource, and tell Solver to maximize the objective while hours used stay within hours available and batches stay within what will sell. Use Simplex LP.

What is a product mix problem?

Choosing how much of each product to make, with limited resources, to get the most profit. It's a linear program when profit and resource use rise in proportion to the amount made.

Why does Solver leave out my most profitable product?

Because it uses too much of the resource that runs out first. In the example, croissants leave the most a batch and the least for each hour at the mixing bench, which is the bottleneck.

We use AI to help with the research for these articles. Every one is read, checked against its sources and edited by Koa before it's published. Spot a mistake? Tell us and we'll fix it and say so. How we write these.

Let’s get your stack in order.

Get TaroStack

Sign up now for your free 14 day trial. No credit card required.