Aggregate Planner An aggregate planning calculator and study tool

What aggregate planning is

Aggregate planning sets production for the next several months in broad strokes: not which product runs on which line, but how many people to employ, how much to make each month, and how to cover the gap between what the plant can make and what customers want. Demand rarely sits still, and every way of dealing with that costs something. Hiring and layoffs cost money, overtime pays a premium, stock built early has to be stored, and orders filled late cost goodwill.

Level vs. chase strategy, and the band between

A level plan keeps the same workforce all year, builds inventory in the slow months and draws it down in the busy ones. A chase plan changes the workforce to follow demand, so inventory stays low but hiring and layoffs pile up. A band lets the workforce move only within limits you set, which is how most plants actually work. The cheapest plan usually mixes all of these: a bit of overtime here, some early stock there, one hire before the peak.

Setting up a textbook case, and checking it in Excel Solver

Most aggregate planning cases give the same handful of facts, and each one has a field here. Demand is a monthly forecast. If it's given in thousands, set “Quantities in” to thousands and type the table as printed. Capacity comes from teams: workers per team, what a team makes in an hour, and the days and hours in a working month. A team that makes 1,000 units in an 8-hour shift makes 125 an hour. Labor is paid per worker-hour, with an overtime rate and a cap on overtime hours per worker. Hiring and layoff costs are per worker, and the planner multiplies them by the team size. Inventory and stockouts cost a set amount per unit per month. A stockout is an order filled from next month’s production, and cases often forbid any in the last month. End conditions usually ask the year to finish with the same workforce and inventory it started with, which is what “End where it starts” sets.

The case questions line up with the answers table. The level column is the current plan with a fixed workforce, the band column allows a range of teams, and the chase column lets the plant hire and fire as it likes. Each column gives the annual cost, the savings over the level plan, the maximum inventory and the production for every month. “Download spreadsheet” writes the three plans into an Excel workbook in the usual Solver layout: decision variables H, L, W, O, I, S and P by period, with period 0 holding the starting position, constraint columns that should read zero, and a cost table built from formulas. You can check any number there, or run Solver on it again.

A worked example

The page opens with a sample: a seasonal product whose demand climbs from 1,600 units in month 1 to about 4,100 in month 11. The plant starts with 12 teams of 5 workers, each team making 1.5 units an hour over 21 eight-hour days. Workers earn $25 an hour, or $37.50 on overtime, up to 12 overtime hours a month. Hiring costs $500 a worker and a layoff $800. A unit costs $12 a month to hold, $30 a month to leave unfilled, and $60 in material. The year has to end with 12 teams, at least 300 units in stock, and no orders still late.

The level plan pays for its steady workforce with inventory: it builds more than 3,200 units ahead of the peak and carries them for months. Letting the workforce move between 10 and 14 teams cuts peak inventory by more than 40% and saves 3.8%. Chasing demand, from 7 teams at the start of the year up to 17 at the peak, saves 7.3%, even after paying for ten hires and ten layoffs. The two flexible plans also run a little overtime where it's cheaper than another team, and the chase plan lets 16 units ship a month late rather than build stock early.

The model

For every month t, the decision variables are the teams hired (H_t), laid off (L_t) and employed (W_t), overtime team-hours (O_t), ending inventory (I_t), stockout (S_t) and production (P_t). D_t is demand. The optimizer finds the values that minimize the total cost, subject to:

Total cost

Σ_t hiring · H_t + layoff · L_t + regular pay · W_t + overtime pay · O_t + holding · I_t + stockout · S_t + material · P_t

Workforce balance

W_t = W_t−1 + H_t − L_t

Inventory balance

I_t−1 − S_t−1 + P_t = D_t + I_t − S_t

Capacity

P_t ≤ monthly output per team · W_t + output per team-hour · O_t

Overtime limit

O_t ≤ overtime hours per worker · W_t

Policy

level: W_t = W₀ · band: fewest ≤ W_t ≤ most · chase: no limit

End of the year

S_T = 0, W_T and I_T as required

How the solver finds the cheapest plan

The planner hands the model to HiGHS, an open-source solver first developed at the University of Edinburgh. If teams could be fractions, the model above would be a linear program. Every possible plan is a point inside a many-sided shape, and the cheapest one is always at a corner. The simplex method starts at a corner and keeps moving to a neighboring corner that costs less until none does. That’s the same engine as Excel Solver’s “Simplex LP” option.

Whole teams make it a mixed-integer program, which is harder, and the solver works on it in three steps. Here they are for the sample’s chase plan:

1. Solve the relaxation. First it ignores the whole-number rule. The fractional plan costs $4,867,016.41. No real plan can cost less, because a real plan has fewer options, so this is a floor. But it calls for things like 11.51 teams in month 8, which can’t be hired.
2. Branch and bound. Every real plan has either 11 or fewer teams in month 8, or 12 or more. So the solver splits the problem into those two cases and solves each one. The cases cost at least $4,869,224.41 and $4,868,504.41, and both still have fractions, so it keeps splitting. Any branch whose floor is already above the best real plan found so far gets dropped without being explored. That’s the “bound”, and it’s why the search never has to try every combination.
3. Close the gap. The solver keeps two numbers: the floor, the lowest cost still possible, and the best whole-team plan found so far. After a little over a dozen branches the two meet at $4,875,900. A gap of 0% is the proof that no cheaper plan exists.

The price of hiring whole teams is the difference between the floor and the final plan: $8,883.59 here, about 0.2%. That’s why a Solver run without integer constraints comes out slightly cheaper than this planner with “Whole teams only” on.

Shadow prices: what one more unit of demand costs

Once the plan is set, a useful question is how much the cheapest cost would go up if a customer ordered one more unit in a given month. That number is the month’s shadow price, and it shows what the plan is doing at the margin.

In the sample’s level plan, one more unit costs just $60, its material, in months 1 to 3, because there’s spare capacity. From month 4 on the price climbs by exactly $12 a month, the holding cost, because the extra unit has to be built in month 3 and stored until it’s needed. In the chase plan, prices run from $131 to $185, and $185 is exactly what a unit costs on overtime: $60 of material plus $187.50 ÷ 1.5 units per team-hour. Shadow prices are how a planner reads which months are bottlenecks and what extra capacity would be worth.

To see all of this for your own numbers, switch the results to Study. It walks through the inputs, the model, each month’s constraints, the solver’s search and the shadow prices, step by step.

Solved exactly, in your browser

The planner writes the model out and hands it to HiGHS, an open-source optimization solver compiled to WebAssembly. It searches until it can prove no cheaper plan exists, so the answer is the true optimum, not a spreadsheet heuristic or a close guess.

Everything runs on this page, and the numbers you enter stay in your browser. The share link carries them in the part of the address after the “#”, which browsers never send to a server. The source is on GitHub: model.js holds the whole model.

For optimization problems that aren't aggregate planning (staffing, transportation, product mix, scheduling, anything you'd set up in Excel Solver), Plain Solver builds the model from plain-English sentences and solves it with the same engine.