How does the template calculate a job's end date?

Work hours are setup hours plus quantity times cycle minutes divided by 60. Dividing by the machine's usable hours per day gives working days needed, which are rounded up to whole days, and WORKDAY counts them from the start date, skipping weekends and holidays. For example, 600 stems at 3.2 minutes with 1.5 hours of setup is 33.5 hours; on a lathe with 6.48 usable hours a day that is 5.2 days, so 6 working days: a job starting Monday 2 November ends Monday 9 November.

What are usable hours per day?

Hours per day are the scheduled hours (shifts times paid hours minus breaks). Availability takes out breakdowns and unplanned stops, and efficiency takes out speed losses. A two-shift machining centre with 16 hours, 90 percent availability and 85 percent efficiency has 12.24 usable hours a day. Setups are not in these factors because each job carries its own setup hours.

Does the template sequence jobs automatically?

No. You type each job's start date, which keeps the plan in your hands and the formulas simple enough to work in every spreadsheet. The Check column flags a job whose dates overlap another job on the same machine, and the end date of the previous job tells you the next free day: start the next job on the working day after it.

How is machine load worked out?

Scheduled hours are the sum of the work hours of every job on the machine. Usable capacity is usable hours per day times the horizon in working days. Load is the ratio, with Near capacity and Over capacity thresholds you set on Setup. A lathe with 146.9 scheduled hours and 6.48 usable hours a day over 20 working days (129.6 hours) is at 113 percent: over capacity.

Why use a status date instead of today's date?

A TODAY formula changes the file every time it is opened, so last week's printout and this week's file disagree and nobody knows which statuses moved. You type the status date when you update percent complete; a job whose end date is before that date and is not complete shows Overdue.

Can I schedule more than 16 jobs?

Yes, the Schedule table grows as you type below it and every per-job formula follows. The Dashboard Gantt and the Timeline grid show the first 16 jobs; the Dashboard warns you when there are more, and the Method sheet explains how to extend the blocks. Many planners keep one workbook per area or per few weeks.

Production and Throughput

Production Schedule Gantt Excel Template

Schedule jobs on machines in working days and see each job's end date, slack to its due date, progress and the load on every machine on a live Gantt dashboard.

Overview

This production schedule is a working Excel Gantt for planners and supervisors in machine shops and other plants that run jobs through a few key machines. It ships with a fictional CNC shop's plan, sixteen jobs on four machines over twenty working days, so the Gantt chart, the timeline grid and the machine load chart are filled in the moment you open it; replace the example rows with your own jobs and everything recalculates.

Each job's work is its setup hours plus quantity times cycle minutes. Divided by the machine's usable hours per day (hours per day times availability times efficiency, the same usable-capacity arithmetic as the Finite Capacity Load calculator on this site), that gives the working days the job needs, and Excel's WORKDAY function counts them from the start date, skipping weekends and your holiday list. From the end date the workbook works out slack to the due date in working days, whether the job finishes on time, and its progress as of the status date you set. The Dashboard sums each machine's scheduled hours and compares them with its usable capacity over the horizon.

In practice, list jobs machine by machine in the order they will run and start each one on the working day after the previous one ends; the Check column tells you when two jobs overlap on one machine or a job starts on a holiday. In the example the one-shift lathe is loaded to 113 percent of its usable hours and its last job finishes three working days after its due date, which is exactly the conversation to have before the week starts: overtime, a second shift or a new promise date.

What this template includes

Schedule table, one row per job: customer, part, machine, quantity, setup hours, cycle minutes per piece, start and due dates, percent complete
Work hours, working days needed and an end date that skips weekends and your holiday list (Excel WORKDAY), per job
Slack to due date in working days, an On time Yes/No column and progress as of a status date you set (Complete, In progress, Not started, Overdue)
Check column that names planning mistakes in words: two jobs on one machine at the same time, a start on a weekend or holiday, a due date before the start, a duplicate job number
Machine list with hours per day, availability and efficiency giving usable hours per working day
Timeline sheet: the classic day-by-day Excel Gantt grid, done days darker, ready to print for the shop floor
Dashboard with a Gantt chart, scheduled hours against usable capacity per machine with capacity words, and tiles for late and behind-schedule jobs
Method sheet with every formula, and a Verify sheet that re-checks the date arithmetic and capacity against an independent reference (Self-check: PASS)

Suggested use case

Use it to plan the next two to six weeks of work on a handful of machines, to answer 'when will this job be done?' with a date that respects weekends and shutdowns, and to see before the week starts which machine is overloaded and which job will miss its due date.

How to use it

On Setup, set the plan start, the horizon in working days and the status date, then enter your machines with hours per day, availability and efficiency, and your holidays.
On Schedule, list the jobs machine by machine in run order: quantity, setup hours, cycle minutes per piece, start date, due date and percent complete.
Read each job's end date, slack and On time column, and fix anything the Check column names, such as an overlap with the job before it.
Update percent complete and the status date each day or week; the Progress column shows which jobs are behind.
Open the Dashboard to see the Gantt chart and the machine load chart, and print the Timeline sheet for the shop floor.

Frequently Asked Questions

How does the template calculate a job's end date?: Work hours are setup hours plus quantity times cycle minutes divided by 60. Dividing by the machine's usable hours per day gives working days needed, which are rounded up to whole days, and WORKDAY counts them from the start date, skipping weekends and holidays. For example, 600 stems at 3.2 minutes with 1.5 hours of setup is 33.5 hours; on a lathe with 6.48 usable hours a day that is 5.2 days, so 6 working days: a job starting Monday 2 November ends Monday 9 November.
What are usable hours per day?: Hours per day are the scheduled hours (shifts times paid hours minus breaks). Availability takes out breakdowns and unplanned stops, and efficiency takes out speed losses. A two-shift machining centre with 16 hours, 90 percent availability and 85 percent efficiency has 12.24 usable hours a day. Setups are not in these factors because each job carries its own setup hours.
Does the template sequence jobs automatically?: No. You type each job's start date, which keeps the plan in your hands and the formulas simple enough to work in every spreadsheet. The Check column flags a job whose dates overlap another job on the same machine, and the end date of the previous job tells you the next free day: start the next job on the working day after it.
How is machine load worked out?: Scheduled hours are the sum of the work hours of every job on the machine. Usable capacity is usable hours per day times the horizon in working days. Load is the ratio, with Near capacity and Over capacity thresholds you set on Setup. A lathe with 146.9 scheduled hours and 6.48 usable hours a day over 20 working days (129.6 hours) is at 113 percent: over capacity.
Why use a status date instead of today's date?: A TODAY formula changes the file every time it is opened, so last week's printout and this week's file disagree and nobody knows which statuses moved. You type the status date when you update percent complete; a job whose end date is before that date and is not complete shows Overdue.
Can I schedule more than 16 jobs?: Yes, the Schedule table grows as you type below it and every per-job formula follows. The Dashboard Gantt and the Timeline grid show the first 16 jobs; the Dashboard warns you when there are more, and the Method sheet explains how to extend the blocks. Many planners keep one workbook per area or per few weeks.