Shift Scheduling

Operations Research

Optimization

Management

Optimize 5-days-on / 2-days-off cyclic shift patterns to meet daily staffing requirements at minimum headcount and payroll cost.

Overview

Shift scheduling determines the minimum number of employees to hire and schedule across a recurring weekly cycle while ensuring adequate coverage on every day of the week. In continuous operations—such as corporate security, building facilities, data center monitoring, and hospital support units—staff must be on duty seven days a week. However, workplace guidelines and fair scheduling rules require that full-time staff work five consecutive days followed by two consecutive days off.

Because each worker’s weekly schedule is governed entirely by the day they begin their five-day stretch, staffing decisions reduce to determining how many people should begin their work week on each day. This template formulates the scheduling challenge as an integer linear programming problem (MILP). It solves for the lowest-cost headcount allocation that meets or exceeds daily staffing requirements, checks daily surplus coverage, and generates an employee roster.

The pre-populated baseline model uses the benchmark staffing requirements from Wayne L. Winston’s classic operations research post-office problem, restyled as a 24/7 security and operations staffing unit. Daily staffing requirements range from 11 to 19 personnel across Monday through Sunday. The template proves that a minimum of 23 whole employees are required to guarantee uninterrupted coverage, verifies that the theoretical continuous LP lower bound of 67/3 (≈ 22.33) cannot be scheduled with whole workers, and produces a complete 23-person roster.

Before you start

Prerequisites: Excel with the Boardflare Python for Excel add-in. The workbook runs locally in Python via WebAssembly and requires no external databases or network services.
Workflow roles: Operational schedulers and department managers can adjust daily staffing numbers in cells B15:H15 or update wage rates in K22:K28 without writing code. Template developers can customize shift rules, bounds, or solver parameters in the underlying Python notebook.
Cycle wrapping: The 7-day schedule wraps seamlessly from week to week. An employee assigned to a Friday-start pattern works Friday, Saturday, Sunday, Monday, and Tuesday before taking Wednesday and Thursday off.

Workbook tour

The workbook organizes operational inputs, scheduling grids, published rosters, validation benchmarks, and customization guides across four dedicated worksheets:

Sheet	Key Ranges	Description	Type
Schedule	`A4:C12` `B15:H15` `B16:H18` `A21:M28` `L21:L28`	Operational cockpit. Displays the solver status, summary KPIs, and per-solve checks (`A4:C12`), the editable daily staffing requirements (`B15:H15`), daily scheduled, surplus, and coverage formulas (`B16:H18`), the cyclic shift pattern matrix with editable pay rates (`A21:M28`), and the solver-assigned hires (`L21:L28`).	Input & Live Cockpit
Roster	`A4:E11` `A14:J54`	Management publication sheet. Displays a clean daily coverage summary table (`A4:E11`) and a generated individual staff shift roster (`A14:J54`) assigning each employee to their specific pattern with daily on/off status.	Published Report
Validation	`A4:F9`	Scientific benchmark sheet. Independently evaluates the integer MILP optimum (23) and continuous LP relaxation (67/3 ≈ 22.33) against Winston’s published operations research literature.	Method Validation
Guide	`A4:B9`	Reference and customization guide. Explains how to adapt the template for non-uniform wage differentials, fluctuating demand spikes, and alternative shift rotations.	Authoring Guide

Using the template

1. Review the baseline staffing schedule

Open the Schedule sheet. The baseline model opens pre-loaded with standard 24/7 staffing demand: Monday 17, Tuesday 13, Wednesday 15, Thursday 19, Friday 14, Saturday 16, and Sunday 11. The summary card confirms that the solver has identified an optimal schedule requiring 23 total staff members at a baseline cost of $23.00 (with flat $1.00 relative cost per hire). The two Results checks, Largest limit violation and MIP gap, both show PASS.

2. Adjust daily coverage requirements

To reflect your department’s upcoming staffing requirements, edit the numbers in the blue input cells Schedule!B15:H15: - Increasing demand on any day causes the solver to re-allocate shift starts. - For example, if weekend traffic increases and Saturday demand rises from 16 to 25, the solver recalculates total headcount to 28, adjusting pattern hires accordingly.

3. Add pattern cost differentials (optional)

By default, every shift pattern has a relative cost of 1.0 (headcount minimization). If weekend-start patterns or anti-social shifts carry wage premiums or overtime differentials, enter the respective cost rates in Schedule!K22:K28. For example, setting Saturday and Sunday start costs to 1.5 shifts the solver’s allocation toward weekday starts while keeping total headcount minimal.

4. Review the published roster

Switch to the Roster sheet to view the generated staff roster. Each employee (Staff-01 through Staff-23) is assigned a specific shift pattern, displaying their exact working days (“ON”) and scheduled rest days (“OFF”).

Inputs and settings

Daily staffing requirements (`Schedule!B15:H15`)

Range: Seven integer values representing required staff on duty from Monday to Sunday.
Constraints: Values must be finite numbers from 0 to 1,000,000 ($0 \le r_i \le 10^6$). Negative, non-finite, or larger values are rejected before solving, and the error names the cell (for example Schedule!B15). A non-numeric value is also rejected, with an error that does not name the cell.
Default values: Mon 17, Tue 13, Wed 15, Thu 19, Fri 14, Sat 16, Sun 11.

Pattern cost per hire (`Schedule!K22:K28`)

Range: Seven numeric values representing the relative cost or wage rate for each pattern start day.
Constraints: Values must be strictly positive ($c_j > 0$) and no larger than 1,000,000. Other values are rejected before solving, and the error names the cell (for example Schedule!K22).
Default values: 1.0 for all patterns (headcount minimization).

Shift pattern matrix (`Schedule!B22:H28`)

Fixed binary structure: A $7 \times 7$ cyclic permutation matrix where 1 indicates that the pattern is on duty and 0 indicates a scheduled day off.
Workweek rule: Exactly 5 consecutive days on, followed by 2 days off.

How results are calculated

The optimization model is formulated as a mixed-integer linear program (MILP):

Decision variables: Let $x_j \in \mathbb{Z}_{\ge 0}$ be the number of employees assigned to start their 5-day shift on day $j \in \{0, \dots, 6\}$ (Monday = 0, …, Sunday = 6).
Objective function: Minimize total payroll cost: \[\min \sum_{j=0}^6 c_j x_j\] where $c_j$ is the cost per hire for pattern $j$ (defaults to 1.0).
Coverage constraints: For each day of the week $i \in \{0, \dots, 6\}$, the total staff working must meet or exceed required staffing $r_i$: \[\sum_{j=0}^6 A_{ij} x_j \ge r_i\] where $A_{ij} = 1$ if pattern $j$ works on day $i$, and $0$ otherwise.
Integrality: All $x_j$ must be integers because fractional employees cannot be scheduled to work full shifts.

The solver executes via HiGHS (through scipy.optimize.milp), guaranteeing a proven global optimum.

Reading the results

Solver status (Schedule!B5): Displays optimal when a globally minimal staffing allocation is found, or error if inputs are invalid. Sheet values in the allowed range (0 to 1,000,000) cannot make the model infeasible: the model stayed feasible for every requirement and cost in the allowed range (checked with random trials), so infeasible does not occur from the workbook.
Total headcount hired (Schedule!B6): The total number of personnel hired across all seven patterns ($\sum x_j$). In the baseline, this equals 23.
Total weekly cost (Schedule!B7): Total cost $\sum c_j x_j$. When all $c_j = 1.0$, this equals total headcount.
Largest limit violation (Schedule!A10:C10): The largest violation of any coverage limit, variable bound, or integrality condition in the solution, computed by check_solution. Column C shows PASS when the value is at most 1e-6 and FAIL otherwise.
MIP gap (Schedule!A11:C11): The relative gap HiGHS reports when it stops. Column C shows PASS when the gap is at most 1e-4, the same tolerance as the LP bound check on Validation. HiGHS stops at 1e-4 by default, so this check passes on a normal optimal solve. A MILP has no duality gap, so none is shown.
The two checks are blank in both value and status when the solver status is not optimal.
Scheduled staff on duty (Schedule!B16:H16): Dynamic =SUMPRODUCT formulas computing scheduled staffing on each day.
Surplus staff (Schedule!B17:H17): Difference between scheduled staff and required staff. A surplus of 0 indicates a tightly binding constraint.
Coverage status (Schedule!B18:H18): Displays Met if surplus $\ge 0$ and Shortage if surplus $< 0$.

Errors, empty states and limits

Empty input cells: If the requirements row is blank, the notebook falls back to the baseline post-office requirements (17, 13, 15, 19, 14, 16, 11) and displays an informational notice in the task pane.
Invalid requirements: If a user enters negative, non-finite, or larger-than-1,000,000 staffing, the notebook displays a danger callout naming the cell (for example Schedule!B15) and sets the summary status to error. Non-numeric staffing also gives an error callout, without the cell name.
Invalid costs: Cost per hire must be strictly positive ($> 0$) and no larger than 1,000,000. Other values trigger an immediate input validation error that names the cell (for example Schedule!K22).
Spill range limits: The employee roster spill accommodates up to 40 individual staff members. For very large facilities requiring $> 40$ employees, expand the roster spill reference in workbook.py.

Customizing as an author

Template authors can modify and extend this template:

Modifying shift rules: To model 4-days-on / 3-days-off (e.g. 10-hour shifts) or 6-days-on rotations, update the binary coverage matrix A_MATRIX in notebook.py and the corresponding pattern rows in workbook.py.
Adding maximum headcount caps: To model budget ceilings, add an upper bound constraint $\sum x_j \le \text{budget}$ in notebook.py.
Synchronizing the engine: The engine block in notebook.py is managed by sync_engine.py. If modifying engine utilities:
```
uv run python templates/optimization/engine/sync_engine.py templates/optimization/shift-scheduling/notebook.py
```

Rebuilding the workbook artifact:

uv run python .agents/skills/python-template/scripts/generate-workbooks.py templates/optimization/shift-scheduling

Running scenario validation:

uv run python .agents/skills/python-template/scripts/run-scenarios-offline.py templates/optimization/shift-scheduling

Limitations and when to use this

Daily vs. interval granularity: This template operates at the daily aggregate headcount level. It is ideal for security guard posts, facility maintenance, hospital portering, and retail store staffing. Call centers and quick-service restaurants requiring 15-minute or hourly staffing curves require multi-period tour scheduling.
Fractional vs. integer staffing: The continuous LP relaxation yields an unachievable lower bound of 67/3 ≈ 22.33 employees. Real managers cannot hire 0.33 of a full-time worker for five days; only the integer solution (23 employees) is realistic.
Non-unique optimal solutions: The optimal headcount of 23 is mathematically proven, but the allocation across start days is not unique. Multiple different combinations of integer hires total exactly 23 while satisfying daily coverage. Do not assume a specific start-day distribution is the only optimal plan.
Homogeneous workforce: The model assumes interchangeable staff skills. If specialized credentials or certifications (e.g., canine handling, CPR certification, supervisor badges) are required, individual skill constraints must be added.

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 staffing model. The seven start-day hire counts fit within Excel Solver’s Simplex LP limit of 200 decision variables and 100 constraints. This workbook solves the integer headcount model with HiGHS through SciPy’s scipy.optimize.milp, running in the browser. What it adds is the daily schedule layout, live recalculation when coverage or pattern costs change, daily coverage checks, an integer-total check, per-solve checks on the Results summary (largest limit violation and MIP gap), and a Validation sheet against Winston’s post-office problem (integer optimum 23, LP bound 67/3).

Developer reference

Notebook implementation

notebook.py is authored as a reactive Marimo application structured into modular cells:

Dependencies cell: Imports boardflare, marimo, numpy, pandas, and scipy.optimize.
Engine cell: Synchronized from engine/core.py via sync_engine.py. Declares # engine-modules: core and imports solve, check_solution, and Solution.
Model geometry & constants: Defines the cyclic coverage matrix $A \in \{0, 1\}^{7 \times 7}$, pattern names, days of the week, weekend burden metrics, and default vectors.
Inputs cell: Reads workbook data via bf.inputs(requirements=bf.ref("Schedule!A14:H15", headers=True), patterns=bf.ref("Schedule!A21:K28", headers=True)).
Validation and parse cell: Sanitizes input matrices, verifies data types, and implements empty-state fallbacks.
Solver cell: Invokes core.solve for the primal MILP and continuous LP relaxation using HiGHS. After an optimal MILP solve, the summary cell calls check_solution for the largest limit violation and reads sol.mip_gap for the MIP gap; each gets PASS or FAIL.
Publication cell: Constructs structured DataFrames for summary, hires, coverage, roster, and validation, publishing them via bf.publish.
Task pane UI cell: Renders responsive KPIs and callout elements.

How the calculation/model works

The mathematical structure leverages the cyclic circulant matrix property of consecutive-days scheduling:

\[A = \begin{bmatrix} 1 & 0 & 0 & 1 & 1 & 1 & 1 \\ 1 & 1 & 0 & 0 & 1 & 1 & 1 \\ 1 & 1 & 1 & 0 & 0 & 1 & 1 \\ 1 & 1 & 1 & 1 & 0 & 0 & 1 \\ 1 & 1 & 1 & 1 & 1 & 0 & 0 \\ 0 & 1 & 1 & 1 & 1 & 1 & 0 \\ 0 & 0 & 1 & 1 & 1 & 1 & 1 \end{bmatrix}\]

Because every column contains exactly five 1s and two 0s, each employee works 5 days. Summing all seven daily constraints yields: \[5 \sum_{j=0}^6 x_j \ge \sum_{i=0}^6 r_i = 17 + 13 + 15 + 19 + 14 + 16 + 11 = 105\] Dividing by 5 gives the theoretical lower bound: \[\sum_{j=0}^6 x_j \ge \frac{105}{5} = 21\] However, because days-off are consecutive and daily demand varies (peaking at 19 on Thursday and 16 on Saturday), the LP relaxation optimum is $67/3 \approx 22.3333$, and the integer optimum requires $\lceil 22.3333 \rceil = 23$ employees.

Validation / expected results

Validation is verified against four published benchmark metrics:

Winston MILP Optimum: Solved with $c = \mathbf{1}$ and baseline requirements; asserts exact objective $z^* = 23$.
Winston LP Relaxation: Solved without integrality; asserts $z^*_{LP} = 67/3 \approx 22.3333$ within $\pm 0.0001$.
Coverage Feasibility: Asserts zero shortages ($\min(\text{Scheduled} - \text{Required}) \ge 0$).
Integrality Verification: Confirms that all decision variables $x_j$ are exact integers ($\max \lvert x_j - \text{round}(x_j) \rvert = 0$).

Historical citation and problem background

This problem originated in workforce operations research: - Primary citation: Wayne L. Winston, Operations Research: Applications and Algorithms, 4th ed., Chapter 3, Section 3.5, “A Work-Scheduling Problem”, Example 7 (the post office problem). - Academic references: Academic coursework problem statements at Columbia University IEOR 3608; S. Bartholdi, J.B. Orlin, and H.D. Ratliff, Cyclic Days-Off Scheduling, Operations Research, 1980.

Changelog

2026-10-28

Added per-solve checks to the Schedule summary: Largest limit violation (from check_solution, covering coverage limits, variable bounds, and integrality) and MIP gap (from HiGHS). Each shows PASS or FAIL, and both are blank unless the solve is optimal.
Added an input range check: requirements and costs must be finite and at most 1,000,000. Errors name the cell.
Moved the Schedule sections down: summary A4:C12, requirements B15:H15, coverage B16:H18, pattern matrix A21:M28, hires L21:L28. Roster, Validation, and Guide ranges are unchanged.
Did not add infeasibility repair. Sheet inputs inside the allowed range always give a feasible model, so there is no infeasible case to repair.

2026-10-25 (Issue #240)

Rebuilt shift scheduling template from generic skeleton into a hand-crafted, field-authentic operations staffing model.
Designed interactive Schedule sheet featuring a 0/1 pattern matrix, editable daily requirements row, live =SUMPRODUCT coverage formulas, and summary KPI cards.
Added Roster sheet with daily coverage summary and generated employee shift assignments.
Added Validation sheet with independent verification against Winston’s published post-office problem ($z = 23$, LP relaxation $z = 67/3$).
Added Guide sheet with practical adaptation instructions.
Switched notebook optimization solver from generic table parser to direct core.solve MILP execution.
Removed legacy spec.py.