Beginner

Free capacity-planning Excel template

A free, macro-free Excel template for planning jobs onto workstations, with a schedule grid that flags days where booked hours exceed capacity. Download it, and read where a spreadsheet stops being enough.

Published

Outcome

You have a working capacity plan for your shop in a spreadsheet: every workstation listed with its daily capacity, every job assigned to one, and a schedule grid that turns red on any day where the work booked exceeds what the workstation can absorb.

Download the template (.xlsx, 36 KB)

No macros, no sign-up, no email address. It opens in Excel, LibreOffice Calc, and Google Sheets. Free to use, copy and modify.

What is in it

Four sheets, meant to be filled in from left to right.

Workstations — one row per place where work happens, with its capacity in hours per day.

This is the sheet worth pausing on, because how you fill it in decides whether the rest works. List places, not assets. A machine is not its own row: it is what stands at a workstation, and you describe it in the “what stands there” column. So WS-01 · Mill 1 · Haas VF-2 is one row, not one row for the mill and another for the shop floor it occupies. Keeping one list rather than two is what stops the plan from disagreeing with itself later.

Capacity is hours per day. A single-operator machine running one shift is 8. A bench two people work at is 16. An assembly bay where three jobs can sit side by side is 24. Use whatever unit matches how you actually think about that place — just be consistent.

The four grey columns on this sheet fill themselves in: hours booked across the horizon, the peak day, how many days are overbooked, and utilisation. They read the Schedule with INDEX/MATCH on the workstation code, so they keep working if you reorder or add rows.

Jobs — one row per job: which workstation, start and end date, and how many hours per day it consumes there. The workstation column is a dropdown fed from the Workstations sheet, so a typo cannot silently drop a job out of the plan.

Its grey columns do the tedious part. VLOOKUP pulls the workstation’s name and capacity beside the job, NETWORKDAYS counts working days between the dates (weekends excluded), and total hours follow. A Check column then flags the three mistakes that are easy to make and hard to spot: an unknown workstation code, an end date before the start date, and a job asking for more hours per day than its workstation has — which no amount of scheduling can fix.

Schedule — entirely calculated, nothing to type. Rows are pulled from the Workstations sheet, so a workstation you add appears here on its own, and each cell is a SUMIFS over the Jobs sheet. It shows booked hours per workstation per day across eight weeks. A cell turns red when booked hours exceed that workstation’s capacity, and amber when it is exactly full — full being worth its own colour, because a workstation at exactly capacity has nothing left for the rush job.

Read me — the same instructions, plus the limits, so the file explains itself to whoever inherits it.

There are no macros anywhere, so the file is safe to email and opens without a security warning. The formulas stay on the conservative side — VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIF, NETWORKDAYS — deliberately avoiding XLOOKUP and dynamic arrays, which break in older Excel and in LibreOffice.

Using it

  1. Replace the six example workstations with your own. Keep the codes short — they are what the schedule matches on.
  2. Delete the example jobs and enter yours. Pick the workstation from the dropdown.
  3. Look at the Schedule sheet. Every red cell is a decision you have not made yet.

The example data ships with one deliberate collision: a rush repair (J-1044) lands on the mill that is already running a bracket order, and Wednesday and Thursday show 11 hours against a capacity of 8. That is what the template is for — it makes the overlap visible before somebody walks out to the machine and finds it.

There is room for 20 workstations and 200 jobs, with the formulas already in place — the sheets stay blank until you fill the rows in. To lengthen the horizon, copy the last date column of the Schedule to the right.

Where this template stops

It is worth being straight about this, because the limits are not bugs to be fixed in a later version. They are what a spreadsheet is.

  • One editor. Whoever has the file has the plan. Everyone else has a copy that is already out of date.
  • Hours only. It catches a workstation booked beyond capacity. It does not know the operator is on another job, that the fixture is in use, or that the second site has room.
  • Nobody has checked the formulas but us. When operational spreadsheets are audited properly, errors turn up in the overwhelming majority of them — and the people who built them almost always believe they are fine. That applies to this file and to whatever you extend it into; the research is worth reading.
  • No history. When a date moves, nothing records that it moved or why.
  • Manual everything. One rush job and the plan is rebuilt by hand, including everything it displaced.

If those are irritations, the template is doing its job and you should keep using it. If they are costing real time every week, that is the point where the file has become the problem rather than the tool — which is what Outgrowing the machine-shop Excel schedule is about, and what Orkyo’s scheduling is built to replace.

Bringing it into Orkyo

If you outgrow the file, you do not have to retype it. The Requests page has an Import from spreadsheet action that reads this exact workbook: the Workstations sheet becomes bookable workstations on a site you choose, and the Jobs sheet becomes scheduled work assigned to them, with codes matched so nothing is duplicated. See Import and export for what carries over and what doesn’t.