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 is listed with its daily capacity. Every job is assigned to one. A schedule grid turns red on any day where the booked work exceeds what the workstation can absorb.

Download the template (.xlsx, 36 KB)

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

What is in it

There are four sheets. Fill them in from left to right.

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

Pause on this sheet. 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. Do not make one row for the mill and another for the shop floor it occupies. One list, not two, is what stops the plan from disagreeing with itself later.

Capacity is hours per day. A single-operator machine on one shift is 8. A bench that two people work at is 16. An assembly bay where three jobs sit side by side is 24. Use the unit that matches how you think about that place, and stay consistent.

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

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

Its grey columns do the tedious part. VLOOKUP pulls the name and capacity of the workstation beside the job. NETWORKDAYS counts the working days between the dates and excludes weekends. The total hours follow.

A Check column then flags the three mistakes that are easy to make and hard to see: an unknown workstation code, an end date before the start date, and a job that asks for more hours per day than its workstation has. No amount of scheduling can fix the third one.

Schedule — completely calculated, with nothing to type. The rows come from the Workstations sheet, so a workstation you add appears here on its own. Each cell is a SUMIFS over the Jobs sheet.

The sheet shows booked hours per workstation per day across eight weeks. A cell turns red when the booked hours exceed the capacity of that workstation. It turns amber when the workstation is exactly full. Full has its own color, because a workstation at exactly capacity has nothing left for the rush job.

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

There are no macros anywhere, so the file is safe to email and opens with no security warning. The formulas stay conservative: VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIF, and NETWORKDAYS. They deliberately avoid XLOOKUP and dynamic arrays, which break in older Excel and in LibreOffice.

Use it

  1. Replace the six example workstations with your own. Keep the codes short. The schedule matches on them.
  2. Delete the example jobs and enter yours. Select the workstation from the dropdown.
  3. Look at the Schedule sheet. Every red cell is a decision you did not make yet.

The example data ships with one deliberate collision. A rush repair (J-1044) lands on the mill that already runs a bracket order. Wednesday and Thursday then 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, and the formulas are already in place. The sheets stay blank until you fill the rows in. To make the horizon longer, copy the last date column of the Schedule to the right.

Where this template stops

Be straight about this. The limits are not bugs for 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 its capacity. It does not know that the operator is on another job, that the fixture is in use, or that the second site has room.
  • Nobody checked the formulas but us. A proper audit of operational spreadsheets finds errors in the large 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 you rebuild the plan by hand, including everything it displaced.

If those limits are irritations, the template does its job. Keep using it. If they cost you real time every week, the file itself became the problem. Outgrowing the machine-shop Excel schedule is about that moment, and Orkyo’s scheduling is built to replace the file.

Bring 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. The Jobs sheet becomes scheduled work assigned to them. Codes are matched, so nothing is duplicated. See Import and export for what carries over and what does not.