Build a Capacity Planning Report

A capacity plan answers a simple question that is surprisingly hard to answer from a spreadsheet of orders: can we make all of this by when it is due? It needs two numbers for every work centre and every week — the hours of work the orders require and the hours the work centre can actually deliver — and a comparison of the two. Required hours come from the order book and a routing table that says how long each product spends on each machine. Available hours come from the shift pattern, reduced by efficiency, public holidays and planned maintenance. The comparison shows which weeks are overloaded, and a simple simulation shows whether moving work into earlier weeks with spare capacity would be enough, or whether overtime, a second shift or new due dates are needed. This page builds that report with pandas and writes it to Excel with a utilisation heatmap. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl holidays

The input workbook has four sheets: open orders by product and due week for the next twelve weeks, a routing table (hours per unit for each product on each work centre), the work centres with machine counts, shift hours, working days and efficiency, and planned maintenance. Public holidays come from the holidays package for England.

How the report fits together

Keeping the two sides separate until the very end matters. Demand changes every day as orders arrive and move; capacity changes when shifts, staffing or machines change. When they are calculated independently, either side can be updated or questioned without touching the other, and a planner who disagrees with a utilisation figure can see immediately whether the disagreement is about the load or about the hours.

Step 1 — Required hours per work centre and week

import pandas as pd

orders = pd.read_excel("capacity_inputs.xlsx", sheet_name="Orders")
routing = pd.read_excel("capacity_inputs.xlsx", sheet_name="Routing")
centres = pd.read_excel("capacity_inputs.xlsx", sheet_name="Work centres").set_index("Work centre")
maint = pd.read_excel("capacity_inputs.xlsx", sheet_name="Maintenance")

load = orders.merge(routing, on="Product", validate="many_to_many")
load["Hours"] = load["Qty"] * load["Hours per unit"]
required = load.pivot_table(index="Work centre", columns="Due week", values="Hours", aggfunc="sum", fill_value=0)
print(required.round(0).astype(int).iloc[:, :6].to_string())

Each order line is multiplied out through the routing: a product that visits two work centres creates load on both. The merge is deliberately many-to-many — one order has several routing steps, one routing step serves many orders — which is the rare case where duplicating rows is exactly right, and saying so with validate documents the intent. The result is a grid of required hours, work centres by week. For simplicity all of an order's load falls in its due week; a finer plan would offset earlier routing steps by their lead time.

Step 2 — Available hours, net of efficiency, holidays and maintenance

import holidays
import numpy as np

weeks = required.columns
uk = holidays.country_holidays("GB", subdiv="ENG", years=[2026, 2027])
def working_days(week_start, days):
    dates = pd.date_range(week_start, periods=days, freq="D")
    return sum(d.date() not in uk for d in dates)

available = pd.DataFrame(index=centres.index, columns=weeks, dtype=float)
for wc, c in centres.iterrows():
    for w in weeks:
        hours = c["Machines"] * c["Shift hours per day"] * working_days(w, int(c["Days per week"])) * c["Efficiency"]
        down = maint.loc[(maint["Work centre"] == wc) & (maint["Week"] == w), "Hours"].sum()
        available.loc[wc, w] = hours - down * c["Efficiency"]
util = required / available
print(available.round(0).astype(int).iloc[:, :6].to_string())
print((util * 100).round(0).astype(int).to_string())

Nominal capacity — machines × shift hours × working days — is never achieved: changeovers, breaks and small stops eat into it, so each work centre's efficiency scales it to the hours of useful output that can be expected. Public holidays remove days, which is why late December shows less capacity than other weeks. Planned maintenance removes a fixed number of machine hours in its week. Dividing required by available hours gives utilisation: below about 85% is comfortable, approaching 100% leaves no room for breakdowns, above 100% means the week's work cannot be done as planned.

Step 3 — Overloaded weeks, and whether levelling helps

over = util.stack()
over = over[over > 1]
print("overloaded:", [(wc, f"{w:%d %b}", f"{u:.0%}") for (wc, w), u in over.items()])

def level(req, avail):
    """Pull excess hours into earlier weeks with spare capacity; report what still cannot fit."""
    req, avail = req.copy(), avail.copy()
    planned = pd.Series(0.0, index=req.index)
    spare = (avail - req).clip(lower=0)
    for i, w in enumerate(req.index):
        excess = max(req[w] - avail[w], 0)
        for earlier in reversed(req.index[:i]):
            move = min(excess, spare[earlier])
            spare[earlier] -= move
            excess -= move
        planned[w] = excess
    return planned

late = pd.DataFrame({wc: level(required.loc[wc], available.loc[wc]) for wc in required.index}).T
print("hours that cannot be made on time even after pulling work forward:")
print(late.loc[:, late.sum() > 0].round(0).to_string())

The overload list is the headline: which work centre, which week, by how much. Welding is the only bottleneck: it runs just over capacity in early November, peaks at 189% in the week of 9 November, when the November surge of frame and housing orders coincides with forty hours of planned maintenance, and stays above 150% for the following two weeks. Laser and press never pass about 75%. The levelling function asks the obvious next question: if the excess in a week is made earlier, in weeks with spare hours, how much is still left over? It walks back from each overloaded week, using spare capacity in the nearest earlier weeks first, which is what a planner would do because it keeps stock built early to a minimum. Hours that still cannot be placed are a real shortage, to be met with overtime, an extra shift, subcontracting or a conversation about due dates. In this data, levelling absorbs the whole maintenance-week overload into the quieter October weeks, but that spare capacity is then used up: about 47 hours in the week of 16 November, 52 in the week of 23 November and 4 in the week of 30 November still cannot be made on time — roughly a hundred welding hours, or two to three weeks of a Saturday overtime shift.

Step 4 — The workbook with a heatmap

from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule

path = "capacity_plan_2026-q4.xlsx"
fmt = lambda df: df.set_axis(df.columns.strftime("%d %b"), axis=1)
with pd.ExcelWriter(path, engine="openpyxl") as xw:
    fmt(util).to_excel(xw, sheet_name="Utilisation")
    fmt(required.round(1)).to_excel(xw, sheet_name="Required hours")
    fmt(available.round(1)).to_excel(xw, sheet_name="Available hours")
    fmt(late.round(1)).to_excel(xw, sheet_name="Shortfall after levelling")
wb = load_workbook(path)
ws = wb["Utilisation"]
last = ws.cell(1, ws.max_column).column_letter
ws.conditional_formatting.add(f"B2:{last}{ws.max_row}", ColorScaleRule(start_type="num", start_value=0.6, start_color="63BE7B",
                              mid_type="num", mid_value=0.9, mid_color="FFEB84", end_type="num", end_value=1.1, end_color="F8696B"))
for row in ws.iter_rows(min_row=2, min_col=2):
    for cell in row:
        cell.number_format = "0%"
wb.save(path)
print(wb.sheetnames, "| peak utilisation:", f"{util.max().max():.0%}")

The Utilisation sheet is the one people look at: work centres down the side, weeks across, coloured from green below 60% through yellow at 90% to red above 110%, with fixed anchors so the colours mean the same every time the report is run. The required and available hours sheets show where the percentages come from, and the shortfall sheet shows what levelling could not solve. Colour scales with fixed thresholds are covered in Add Data Bars and Colour Scales with openpyxl.

Keeping the plan honest

A capacity plan is only as good as its two inputs, and both drift. Routing times are often set once and never revisited; compare them with actual booked hours from the shop floor every few months, because a routing that is 20% optimistic makes every week look 20% emptier than it is. Efficiency factors drift the same way. On the demand side, the order book covers firm orders only, so the later weeks look light simply because orders have not arrived yet — a forecast of likely demand, kept separate from firm orders, gives a fairer view of the weeks further out. Finally, run the plan weekly and keep each run: the history shows whether overloads are being resolved or just pushed forward a week at a time. Store each run with its date in the file name, and the trend of peak utilisation over successive runs becomes a useful early warning in its own right.

Common pitfalls

• Nominal hours as capacity. Scale by efficiency and remove holidays and maintenance.
• Load only on the final step. Use the routing so every work centre sees its load.
• One utilisation figure for the quarter. Weekly peaks are what cause late orders.
• Levelling by moving work later. Pull forward into spare earlier weeks instead.
• Stale routings. Compare standard hours with actual hours regularly.

Conclusion

To plan capacity, multiply open orders through a routing table into required hours per work centre and week, and compute available hours from machines, shift hours, working days, efficiency, holidays and maintenance. Divide one by the other for utilisation, list the overloaded weeks, test whether pulling excess work into earlier spare weeks clears them, and write a colour-coded utilisation sheet with the hours and the remaining shortfall behind it.

Frequently asked questions

How do I calculate required capacity from orders in pandas? Merge the order lines with a routing table of hours per unit by product and work centre, multiply quantity by hours, and pivot by work centre and week.

How do I calculate available capacity? Multiply machines by shift hours by working days, subtract planned maintenance, and multiply by an efficiency factor; remove public holidays from the working days.

What utilisation is too high? Above about 85 to 90 percent leaves little room for breakdowns and rush orders; above 100 percent the planned work cannot be done in that week.

How can I test whether moving work earlier solves an overload? Walk back from each overloaded week and move excess hours into earlier weeks with spare capacity; whatever cannot be placed is a real shortfall.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Staff Utilisation Report from Timesheets — utilisation of people.
• Flag Public Holidays in Excel Data with Python — the holiday calendar.
• Apply a Colour Scale per Row or per Column with openpyxl — heatmap options.