Production planning Excel template, and when to move off it
Almost every plant starts planning in Excel, and for good reason: it is flexible, everyone has it, and it costs nothing to start. This page gives you the structure of a solid production planning template, then shows honestly where a spreadsheet stops working and what replaces it.
The columns of a production plan template in Excel
A production schedule in Excel works best as three sheets that reference each other, not one big grid.
Sheet 1: Orders
| Column | What goes in it |
|---|---|
| Order number | One row per customer or stock order |
| Product | The item code, matching the routing sheet |
| Quantity | Units to make |
| Due date | The date promised to the customer |
| Priority | Rush, normal, or a customer ranking |
| Status | Open, in progress, done |
Sheet 2: Routings
| Column | What goes in it |
|---|---|
| Product | The item code |
| Operation number | 10, 20, 30, in the order the part moves |
| Work center | The machine or cell that does the operation |
| Setup minutes | Time to change the work center over to this product |
| Minutes per unit | Run time for one unit |
Sheet 3: Capacity
| Column | What goes in it |
|---|---|
| Work center | Each machine or cell |
| Hours per shift | Productive hours, after breaks |
| Shifts per day | One, two or three |
| Working days | Days per week, minus holidays and maintenance |
| Available hours per week | Hours per shift times shifts times days |
Copy this template
Paste each header row into cell A1 of a new sheet, then split it with Data, Text to Columns, comma as the separator. Name the three sheets Orders, Routings and Capacity.
| Sheet | Header row to copy |
|---|---|
| Orders | Order number,Product,Quantity,Due date,Priority,Status |
| Routings | Product,Operation number,Work center,Setup minutes,Minutes per unit |
| Capacity | Work center,Hours per shift,Shifts per day,Working days,Available hours per week |
In the Capacity sheet, fill the last column with =B2*C2*D2 and copy it down, so available hours update when you change a shift or a holiday.
With these three sheets, a planner can calculate the hours each order needs on each work center and compare the weekly total against available hours. That is a rough cut capacity check, and it is already better than planning from memory.
Where production scheduling in Excel breaks
The template above tells you whether a week fits in total. It does not tell you when each job runs, and that is the question the floor and the customer ask.
- No sequence: the sheet knows Mill 2 needs 70 hours this week, not which job goes first
- No finite capacity: nothing stops two jobs from sitting on the same machine at the same hour
- No knock on effects: when one operation slips, the operations after it do not move
- Setups ignored: changeovers depend on the sequence, which the sheet does not have
- Rebuilt by hand after every breakdown and every rush order
- One file, one owner: the plan lives on one laptop and changes without anyone else knowing
Formulas and macros can push this a long way, and some planners build impressive manufacturing schedules in Excel. The cost is the time to maintain them and the risk when their author is away.
An Excel capacity planner, compared with a finite capacity schedule
| Question | Excel capacity planner | ProductionPlanning.ai |
|---|---|---|
| Does this week fit in total? | Yes, with formulas | Yes |
| Which job runs first on each machine? | No | Yes, earliest due date first, or by the sequencing rule you choose (Plant plan and above) |
| Which orders will be late, by how many days? | Only by hand | Yes, automatically |
| What happens after a breakdown? | Rebuild the sheet | Reschedule in one click |
| Can the floor see today's queue? | Print it out | Yes, per work center (Plant plan and above) |
| Who can edit it safely? | One person | Everyone, with change history (Plant plan and above) |
Move your production planning Excel file in an afternoon
You do not have to start over. If your spreadsheet has orders, routings and work centers, it already has what ProductionPlanning.ai needs.
- Import the orders sheet and the routings sheet
- Map the columns once, and the mapping is saved for next time
- Add shifts per work center
- Run the schedule and compare it with your spreadsheet's plan
Spreadsheet import is included on the Plant plan. Keep the spreadsheet open for the first week, side by side, until you trust the new schedule. Read how production planning and scheduling works, or see why the production bottleneck is the first thing a spreadsheet misses.
Keep the template. Lose the rebuilding.
Bring your production planning Excel file across and see your first finite capacity schedule today.
Your data stays yours. Export or delete it any time. No card to try the demo.