Production Planning Optimization

Optimization
Operations Research
Linear Programming
Choose how many boats to build in regular time and in overtime each quarter, and how many to hold in stock, so every quarter’s demand is met at the lowest total cost. Change the quarterly data and read the plan, the limits that bind, and the computed value of extra capacity.

Overview

Production Planning Optimization decides how many boats to build in regular time and in overtime each quarter, and how many to hold in stock at the end of each quarter, so that every quarter’s demand is met at the lowest total cost. A regular-time boat costs $400, an overtime boat costs $450, and each boat held in stock at the end of a quarter costs $20. Demand in a quarter is met from that quarter’s production or from stock carried in from the quarter before.

The workbook ships with the Sailco inventory problem from Wayne L. Winston’s Operations Research: Applications and Algorithms, Chapter 3, Example 14. Under the default settings the plan costs $78,450, which is the published minimum. It builds 40 regular-time boats in each of quarters 1 to 3 and 25 in quarter 4, builds 10 overtime boats in quarter 2 and 35 in quarter 3, and holds 10 boats in stock at the end of quarter 1.

The source is cited from a secondary online copy of the textbook text (see Limitations and when to use this).

Workbook tour

Sheet and range What it holds Kind
Model, B4 to B6 Three single inputs: initial inventory, holding cost per boat per quarter, and minimum ending inventory (blank means 0). Editable input
Model, A9 to F29 The quarterly table, one row per quarter, up to 20 quarters. Columns: Quarter, Demand, Regular-time capacity, Regular-time unit cost, Overtime capacity (blank means no limit), Overtime unit cost. The four Sailco quarters are filled in; rows 14 to 29 are free. Editable input
Results, A5 to C14 Optimization summary with a Status column: Status, Message, Total cost, Regular-time cost, Overtime cost, Holding cost, Quarters in plan, Largest limit violation, and Duality gap. The last two show PASS or FAIL in column C and are blank when no plan is solved. Output (Python)
Results, A17 to E26 Suggested limit changes, filled only for an infeasible plan: Limit, Type, Current, Suggested, and Change, with the total absolute change in row 26. Row 18 gives a note instead when the plan is optimal or not yet solved. Output (Python)
Results, A29 to F49 Production plan by quarter: Demand, Regular-time units, Overtime units, Ending inventory, and Period cost including holding. Output (Python)
Results, A52 to H72 Capacity limits by quarter: Regular-time capacity, the percentage of each limit used, whether each limit is at its maximum, and the computed regular-time and overtime prices. Output (Python)
Validation, A5 to E13 Expected values from the published source and the model’s own rules, with a tolerance for each. The notebook uses its own fixed copies of these numbers, so editing these cells changes nothing. Reference
Validation, F5 to H13 Actual value, difference, and PASS or FAIL status for each check. Output (Python)
Guide How to type the data, what the notebook reads, and how to adapt the model. Notes here never block a run. Reference

Blue cells are inputs. Green cells are published by Python. Do not type over the Results or Validation sheets.

Using the template

1. Run the sample problem

  1. Open the workbook and select the Results sheet. The summary shows Status Optimal and a Total cost of $78,450.
  2. Review the plan table. Regular-time units are 40, 40, 40, and 25. Overtime units are 0, 10, 35, and 0. Ending inventory is 10, 0, 0, and 0.
  3. Review the limits table. Regular-time production is at its limit in quarters 1 to 3, so those rows show Yes. Quarter 4 uses 25 of its 40 units.

2. Test a demand change

  1. On the Model sheet, change quarter 2 demand (cell B11) from 60 to 70.
  2. Return to Results. The status stays Optimal, and the Total cost becomes $82,950.
  3. The plan now uses overtime of 20 units in quarter 2 and 35 in quarter 3. Regular-time production is unchanged at 40, 40, 40, and 25.
  4. On Validation, rows 1 to 4 now show n/a (model edited), because they compare against the published numbers. Rows 5 to 8 still run and show PASS.

3. Observe an infeasible plan

  1. Restore cell B11 to 60.
  2. Clear the overtime capacity in cells E10 to E13 and type 0 in each. Overtime is then limited to nothing.
  3. The status becomes Infeasible. The message names the first quarter that cannot be met, here quarter 2: demand through quarter 2 is 100 units, but initial inventory plus regular-time capacity through quarter 2 is 90 units.
  4. Look at Suggested limit changes on Results (rows 17 to 26). The table lists the limits to change, with the current value, a suggested value, and the change for each. The total in row 26 is the total absolute change, here 45. That equals the largest shortfall, which is in quarter 3: 175 units are needed through quarter 3 and 130 are available. You choose which limits to change; the table is a starting point, not an instruction to follow exactly.
  5. Restore the overtime cells (clear them to remove the limit) to return to the optimal plan.

4. Clear the model

Clear the three inputs and every quarterly row. The Results show Ready with no numbers, and the Validation rows show not run.

Inputs and settings

Cell or column Meaning Allowed values Default
Model!B4 Initial inventory at the start of quarter 1 (boats) Number, zero or more 10
Model!B5 Holding cost per boat per quarter ($) Number, zero or more 20
Model!B6 Minimum ending inventory in each quarter (boats) Number, zero or more; blank means 0 blank
Model!A10:A29 Quarter name (label only) Any text Quarter 1 to 4
Model!B10:B29 Demand in the quarter (boats) Number, zero or more 40, 60, 75, 25
Model!C10:C29 Regular-time capacity in the quarter (boats) Number, zero or more 40 each
Model!D10:D29 Regular-time cost per boat ($) Number, zero or more 400 each
Model!E10:E29 Overtime capacity in the quarter (boats) Number, zero or more; blank means no limit blank
Model!F10:F29 Overtime cost per boat ($) Number, zero or more 450 each

Fill the quarterly table from the top down with no gaps. A completely blank row is skipped. A used row needs a quarter name, a demand, a regular-time capacity, a regular-time cost, and an overtime cost. The planning horizon is the number of quarters you fill in, up to 20.

How results are calculated

The model chooses, for each quarter, how many boats to make in regular time, how many in overtime, and how many to keep in stock at the end of the quarter.

  • Stock balance. Stock carried in from the previous quarter, plus regular-time and overtime boats made in the quarter, equals demand plus the stock carried out. Quarter 1 starts from the initial inventory.
  • Limits. Regular-time boats cannot exceed the regular-time capacity. Overtime boats cannot exceed the overtime capacity, and a blank overtime capacity means there is no limit.
  • Minimum stock. Ending inventory cannot fall below the minimum ending inventory (0 when blank).
  • Cost. The model adds the regular-time cost, overtime cost, and holding cost ($20 per boat per quarter on every quarter’s ending stock, including stock left after the last quarter). It finds the plan with the lowest total.

Worked example (sample data). Total demand is 200 boats, and the 10 boats in stock at the start cover part of quarter 1, so 190 boats must be made. Regular time can make up to 160 boats, but the lowest-cost plan uses 145 of them: full capacity of 40 in quarters 1 to 3, and 25 in quarter 4, which needs only 25. That is 145 boats at $400 = $58,000. Overtime makes the other 45 boats at $450 = $20,250, in quarters 2 and 3. The 10 boats held at the end of quarter 1 cost $20 each, or $200. The total is $58,000 + $20,250 + $200 = $78,450.

Computed prices. The source does not publish capacity prices. The Results sheet computes them: for each quarter’s capacity, the price is the change in total cost for one more boat of capacity in that quarter. A negative price means extra capacity lowers the total cost by that amount. For the sample data, regular-time capacity is worth -$30 per boat in quarter 1 and -$50 per boat in quarters 2 and 3. Quarter 4 shows 0 because its regular time is not fully used. The notebook re-solves the model with each limit raised by one boat to confirm each price. Validation row 8 shows the largest gap between a shown price and its re-solve. If a re-solve does not confirm a quarter’s price (a one-sided price at a limit of zero), that price shows not confirmed and row 8 shows n/a (price one-sided: quarter N) naming the quarter; the notebook does not publish an unconfirmed number. Overtime capacity is unlimited in the sample data, so its prices are blank. A blank overtime price means that quarter has no overtime limit.

Reading the results

  • Status. Optimal means a plan was found and checked. Infeasible means the limits cannot meet demand; the message names the first quarter that fails, and Suggested limit changes lists the limits to change. Unbounded should not occur with this model. Time limit, feasible only means the solver ran out of its 60-second limit after finding a feasible plan that it did not prove optimal; no plan is shown. Time limit, no plan means no plan was found in time. Neither is expected at this size.
  • Plan. Each row is a quarter. Period cost includes the holding cost of that quarter’s ending stock.
  • Checks. Largest limit violation is the largest amount by which the plan breaks a limit; Duality gap is the difference between the primal and dual objective values of the LP. Each shows PASS or FAIL in column C of the Results summary. Both are blank when no plan is solved.
  • Limits. Used % is production divided by the limit. At limit is Yes when production equals the limit. A binding limit with a negative computed price shows how much one more boat of that capacity would lower total cost.
  • Validation. PASS means the check holds. FAIL means the sheet no longer matches the rules. n/a (model edited) means the numbers differ from the published example, so rows 1 to 4 are not applicable. not run means there is no plan to check.

Saving, sharing and App mode

Your inputs are saved with the workbook like any other cells. Results and Validation are recalculated by Python from the Model sheet and are saved with the workbook. In App mode, only the Model sheet is meant to be edited; Results and Validation are outputs.

Errors, empty states and limits

Message or state Cause What to do
Input error naming a cell A required cell is blank, or holds a negative number, or text that is not a plain number. Text is accepted only as plain digits with an optional sign and decimal point, with spaces around it allowed: “40” and “2.5” work, but “1e3”, “1_000”, “nan”, “inf”, “1,5” and “84O” are errors naming the cell. Type a number of zero or more in the named cell.
Input error: a number in a label cell A typed number sits in a title or label cell (Model!A1 to A8, or the header row B9 to F9). Labels hold text only. Clear the number, or move it into an input cell.
Input error: a value outside the input cells Something was typed in Model outside the input cells (for example in column H), other than titles, section labels, and input labels. Clear the named cell.
Infeasible Cumulative capacity through a quarter cannot cover cumulative demand plus minimum ending inventory. Use Suggested limit changes on Results (rows 17 to 26) to see which limits to change, by how much, and the total change. Or raise capacity in the named quarters, lower demand, or lower the minimum ending inventory.
Largest limit violation or Duality gap shows FAIL The solver’s answer did not pass its independent check. Status is error when the feasibility or objective check fails; a Duality gap FAIL shows next to Status Optimal. Read the Message on Results, which says what failed. Report it with the Message; the plan is not trusted until the checks pass.
Largest limit violation and Duality gap are blank No plan was solved (Infeasible, Ready, or an input error). Expected. The checks appear once a plan is solved.
Ready No input or quarterly data has been entered. Enter the three inputs and the quarterly rows.
n/a (model edited) Validation rows 1 to 4 compare against the published numbers, which the model no longer matches. Expected after you change numbers. Rows 5 to 8 still apply.
n/a (price one-sided: quarter N) Row 8 skips a quarter whose computed price a one-unit re-solve does not confirm. The price shows not confirmed on Results. Expected for a one-sided price. A confirmed price that is wrong still shows FAIL.
not run There is no optimal plan to check (rows 1 to 4 show FAIL instead if the unedited benchmark model gets no optimal plan). Fix the input or the infeasibility first.

Limits. Up to 20 quarters. The notebook reads Model!A1 to AZ60, so columns beyond AZ and rows beyond 60 are not read. Blank rows in the quarterly table are skipped, but rows must be filled with no gaps.

Customizing as an author

  • Change the horizon. Add or remove quarters in the quarterly table, up to 20 rows.
  • Change the numbers. Edit the blue cells. Validation rows 1 to 4 will show n/a (model edited); this is expected.
  • Rename labels. Quarter names and input labels can be renamed without changing the answer.
  • Change the model structure. The Guide sheet describes the engine builder and the notebook re-sync step. Adding a new cost type or limit type is a developer change, not a worksheet edit.

Limitations and when to use this

  • Continuous plan. The model allows fractional boats. It does not enforce whole boats, so the plan may show decimals for other data.
  • One product. The model has one product, no setup costs, no lead times, and no sales revenue. It does not optimize prices or demand.
  • No backorders. Demand that cannot be met in its quarter must be met from stock or it is infeasible.
  • Deterministic. Demand and costs are known in advance. There is no uncertainty or risk.
  • Source. The problem and its published optimum are taken from a secondary online copy of Winston’s text, not from the printed book. The optimum of $78,450 was reproduced independently with a linear-programming solver.
  • Computed prices are not published values. The capacity prices on Results are computed by this template and are labelled as computed.

Use this template to learn the production and inventory linear program, to test how demand or capacity changes affect cost, and to see which limits bind. Do not use it as a production schedule without checking the real constraints.

Free alternatives

Excel Solver, built into desktop Excel (it is not available in Excel for the web), and the free OpenSolver add-in for Windows Excel (a COIN-OR CBC add-in with no stable release since 2020) can both solve this same production and inventory model. The four-quarter sample is small enough to fit within Excel Solver’s Simplex LP limit of 200 decision variables and 100 constraints. This workbook solves the linear program with HiGHS through SciPy’s scipy.optimize.linprog, running in the browser. What it adds is the quarterly layout with live recalculation when demand, capacity or cost changes, capacity prices computed by re-solving with each limit raised by one boat (labelled as computed, not published), independent checks on the plan, and a Validation sheet against the Sailco optimum of $78,450 from Winston’s Operations Research, Chapter 3, Example 14. When a plan is infeasible, the workbook also lists suggested limit changes in plain words: each limit, its current value, a suggested value, and the change, with a total change equal to the largest shortfall.

Developer reference

Notebook implementation

notebook.py is a reactive Marimo application that runs in the Python for Excel task pane.

  • Engine. # engine-modules: repair, production_plan copies engine/core.py, the shared repair module, and engine/forms/production_plan.py into the notebook through sync_engine.py. The notebook carries the generated engine cells between its marker lines.
  • Inputs. bf.inputs(...) reads the three single inputs from Model!B4:B6, the quarterly table from Model!A9:F29 with headers, and several probe ranges that detect stray content in the cells around the table. The overflow probe for Model!A30:AZ60 is required by the workbook validator.
  • Reading the model. A strict reader converts the table to numbers. Text is accepted only as a plain number (digits with an optional sign and decimal point), so text such as “1e3” or “1_000” is an error rather than a silent conversion. Blank rows are skipped, and a row with a gap or a non-number raises an input error that names the cell. Label cells (Model!A1:A8 and B9:F9) hold text only; a typed number there is an input error. Stray content outside the input cells is reported with the first cell address.
  • Solving. solve_production_plan builds the production and inventory linear program and solves it with HiGHS. It returns an empty result with status infeasible when cumulative capacity cannot meet cumulative demand, and it checks the solver answer against an independent evaluation before reporting a plan. Computed prices are confirmed by one-unit re-solves. When the model is infeasible, the shared elastic repair finds suggested changes to the capacity and demand limits whose total change equals the largest shortfall (the Suggested limit changes table). Each solve reports Largest limit violation from check_solution and, for an optimal LP, the Duality gap.
  • Benchmark constants. The notebook holds the published benchmark values (cost, regular and overtime units, ending inventory) as constants in its edit-these-rules cell. These duplicate the engine fixture and must be updated together if the source changes.
  • Publish. bf.publish(outputs=...) writes the five output anchors: summary, repair, plan, limits, and validation.

How the calculation/model works

The problem is a linear program with three variables per quarter (regular-time, overtime, and ending inventory). Each quarter has one stock-balance equality and bounds for its limits, and the minimum ending inventory is a lower bound on ending stock. Costs are linear, so the solver returns the unique optimum for the sample data. The reduced costs of the upper bounds give the computed capacity prices, which are then confirmed by re-solving with each limit raised by one.

Validation / expected results

The Validation sheet checks four published values and four live checks.

Check Expected outcome
Published optimum, total cost $78,450
Published regular-time units 145
Published overtime units 45
Published total ending inventory 10
Inventory balance error (live) 0
Capacity breach (live) 0
Minimum ending inventory shortfall (live) 0
Largest gap between a shown price and its one-unit re-solve ($ per unit) 0 (a not-confirmed quarter is n/a, not a failure)

Scenario checks run against the workbook with these expected results:

  • Demand in quarter 2 raised from 60 to 70: total cost $82,950, overtime 0, 20, 35, 0.
  • Overtime capacity set to 0 in every quarter: Infeasible, naming quarter 2 (100 units needed, 90 available), and naming the sheet cells to check. Suggested limit changes total 45, the largest shortfall (quarter 3: 175 units needed, 130 available). Both checks are blank.
  • Overtime capacity set to 0 in every quarter, quarter 2 demand set to 50 and quarter 3 demand set to 40: Optimal, Suggested limit changes total 0, and both checks PASS.
  • Every input and quarterly row cleared: Ready, with no numbers and Validation rows not run.
  • A value typed outside the input cells: Input error, naming the cell.
  • Quarter and input labels renamed: the plan and all benchmark checks still pass.
  • Quarter 3 overtime capacity set to 0 (a one-sided price): the price shows not confirmed, and Validation row 8 is n/a naming quarter 3.
  • Text “84O” or “1,5” typed in a quantity cell: Input error naming the cell. A typed number in a label cell: Input error naming the cell.

The expected plan and cost were computed independently with a linear-programming solver, and they match the published optimum.

Architecture

The engine builder is in engine/forms/production_plan.py, with its fixture in engine/forms/production_plan.json and tests in engine/forms/test_production_plan.py. The workbook layout is in workbook.py, the notebook in notebook.py, and the scenario expectations in scenarios.py. The infeasibility repair comes from the shared repair module through the same builder, and the notebook includes it with # engine-modules: repair, production_plan. The checks use check_solution and duality_gap from the shared core module.

Changelog

2026-10-28

  • Added suggested limit changes for infeasible plans: the limit, its current and suggested values, the change, and a total change equal to the largest shortfall.
  • Added Largest limit violation and Duality gap checks to the Results summary, marked PASS or FAIL and blank when no plan is solved.
  • Added the overtime-zero-suggested-demand scenario and updated the infeasible-plan steps, the Errors table, and the developer reference.

2026-10-26

  • Rebuilt the template as a multi-quarter production and inventory model using the Sailco example from Winston, Chapter 3, Example 14 (issue #240).
  • Added a quarterly table of up to 20 quarters, with overtime limits that can be left blank.
  • Added Results for summary, plan by quarter, and capacity limits with computed capacity prices.
  • Added a Validation sheet with published checks and live model checks, including the infeasibility message.
  • Added a Guide sheet describing the inputs, blank and stray-content rules, and how to adapt the model.
  • Review fixes: a one-sided computed price is published as not confirmed instead of a wrong value, and row 8 names that quarter; time-limit wording says no plan is shown; text numerals must be plain numbers; label cells reject typed numbers; input errors say how many more problems there are; infeasible messages name the sheet cells to check.