Free Factory Production Planning Excel Template
A weekly factory plan in one workbook: what each product builds each week, whether the lines have the hours, whether the material will be there, how many labour hours it takes, and how the plan compared with what was actually built.
What you get
A free .xlsx factory production planning template with no email and no macros: a 13-week plan by product, line load against capacity, a material availability check, labour hours against the roster, and planned vs actual attainment, filled with a two-line worked example.
factory-production-planning-excel-template.xlsx · 8 sheets · No email required · No macros
Sheets: Instructions, Products, Lines, Plan, Capacity, Material, Labour, Planned vs Actual
Why manufacturers still use Excel for this
A factory production plan answers five questions every week: what are we building, on which line, do we have the capacity, do we have the material, and how did last week go against plan. Most small and mid-sized factories answer those questions in a spreadsheet, and there is nothing wrong with that as long as the spreadsheet actually connects the five answers. The usual failure is five separate tabs maintained by five people: sales keeps a demand sheet, the planner keeps a line schedule, purchasing keeps a shortage list, HR keeps a headcount sheet, and the operations manager builds a planned vs actual chart on Friday afternoon from whatever numbers people remember. This template puts all five on linked sheets, so a change to one planned quantity changes the capacity check, the material check and the labour hours on the same recalculation.
The formulas are deliberately plain. Line load in a week is the planned quantity of each product times its standard hours per unit on that line, added up: on the Capacity sheet, Line A load in week 36 is =SUMPRODUCT(Plan!B2:B3,Products!C2:C3). Line capacity = shifts per day x hours per shift x working days that week x efficiency. The flag is =IF(load>capacity,"OVER",IF(load>0.9*capacity,"TIGHT","OK")). The material check compares the components the plan needs with stock carried from the previous week plus scheduled receipts: =IF(required>available,"SHORT","OK"). Labour hours needed = line hours x crew per shift, compared with rostered hours for the week. Planned vs actual is attainment = actual / planned.
A worked example: a factory with two lines. Line A (assembly) runs 2 shifts x 8 hours x 5 days at 85% efficiency = 68 hours per week; Line B (packing) runs 1 shift, 34 hours. Week 36 demand: product P100, 400 units at 0.10 h each on Line A (40 h); P200, 250 units at 0.12 h on Line A (30 h); both then pack on Line B at 0.04 h each (26 h). Line A load is 70 h against 68 h capacity, so the flag reads OVER by 2 hours; Line B is OK at 76%. The material check shows P200 needs 250 housings, stock is 180 and the delivery of 200 arrives in week 37, so P200 is SHORT by 70 in week 36. The plan that comes out of the sheet is obvious once the flags are side by side: build 180 of P200 in week 36 (21.6 h, bringing Line A to 61.6 h and clearing the OVER flag) and carry 70 into week 37 when the housings land. Without the linked sheets, the planner sees the capacity problem and purchasing sees the material problem, and neither sees that one fix solves both.
Excel carries this model comfortably for a factory with a handful of lines and a few dozen products planned in weekly buckets. It stops being trustworthy when the buckets need to be days or shifts, when products share lines and the sequence matters (changeovers, allergen or colour sequences, tooling), or when a late material delivery has to push a job and every downstream job with it. That is finite-capacity scheduling rather than production planning, and the handoff is described at the end of this page.
What's inside the template
Products sheet
One row per product: standard hours per unit on the assembly line and on the packing line, and how many of the constrained component (a housing in the example) each unit uses. Standard hours are the single most important input; if they are guesses, every flag downstream is a guess too.
Lines sheet
Shifts per day, hours per shift, efficiency and crew per shift for each line, plus a 13-week grid of working days so shutdowns, bank holidays and short weeks reduce capacity in the right week.
Plan grid (product by week)
Planned quantity for each product in each of 13 weeks. Line load, material required and labour hours are all derived from this grid, so the planner only ever types quantities.
Capacity check
Line load in hours against line capacity per week, with load % and a three-state flag (OK, TIGHT above 90%, OVER above 100%) in colour.
Material check
For the constrained component: required quantity from the plan, scheduled receipts, available stock carried forward, projected balance and a SHORT flag. Not a full MRP run; it covers the parts that actually stop a line.
Labour sheet
Operator hours needed (line hours x crew per shift) against rostered hours per week. Negative spare hours show where the plan needs overtime before the week starts.
Planned vs Actual tracker
Frozen planned quantity, actual quantity, attainment % and variance by week and product. It only works if the planned column is frozen on Monday rather than edited to match on Friday.
How to use this template
A practical walkthrough, from the sample data to a plan built on your own numbers.
- 1
Load the products with real standard hours
Enter every product you expect to plan in the next quarter with its assembly and packing hours per unit and its usage of the constrained component. Take standard hours from routings if you have them, otherwise from timed observation of a normal run. This step is where the plan is won or lost.
- 2
Set line capacity and the 13-week calendar
On the Lines sheet, enter the shift pattern for each line and a realistic efficiency (80 to 85% for most assembly and packing lines). Then set working days per week: 0 for shutdown weeks, 4 for weeks with a bank holiday or public holiday.
- 3
Type quantities into the Plan grid
Enter the quantity of each product you intend to build in each week, using firm orders plus forecast. Start from customer due dates and place quantity in the week before the ship week.
- 4
Clear the capacity flags
Open the Capacity sheet. Any OVER cell means that line cannot build what is planned in that week. Move quantity earlier (build ahead if material and storage allow) or later (if the customer date allows). Aim for TIGHT at most in peak weeks; a plan that is OVER in three weeks out of thirteen is not a plan, it is a wish.
- 5
Run the material check and reconcile with purchasing
Enter stock at the start of the first week and scheduled receipts. Any SHORT flag is a week where the plan consumes material that will not have arrived. Pull the delivery in, push the build out, or split the build across two weeks as in the worked example, and share the SHORT list with purchasing the same day.
- 6
Freeze the plan, then record actuals at week end
Once the flags are clear, copy the coming week's planned quantities into Planned vs Actual as values so they cannot drift. On Friday, enter actual quantities built. Attainment below 90% for two weeks running on the same line usually means the standard hours or efficiency are wrong, not that the operators are slow; fix the input rather than the plan.
Worked example: weeks 36 to 39 as they ship in the file
The file holds the original plan, so you can see both flags fire before you fix them. Line A capacity is 68 hours a week, Line B 34. Housings start at 180 in stock, with deliveries of 200 in weeks 37 and 38 and 250 in week 39.
| Week | Line A load (h) | Line A capacity (h) | Line A flag | Line B load (h) | Housings needed | Housings available | Material flag |
|---|---|---|---|---|---|---|---|
| 36 | 70.0 | 68.0 | OVER | 26.0 | 250 | 180 | SHORT |
| 37 | 45.6 | 68.0 | OK | 17.2 | 130 | 200 | OK |
| 38 | 53.0 | 68.0 | OK | 20.0 | 150 | 270 | OK |
| 39 | 66.0 | 68.0 | TIGHT | 24.0 | 300 | 370 | OK |
Week 36 has both problems at once: Line A is 2 hours over and P200 is 70 housings short. Build 180 of P200 in week 36 instead of 250: Line A drops to 61.6 hours (91%, TIGHT but no longer OVER) and the 180 housings in stock cover it exactly.
Move the other 70 into week 37 next to the 130 already planned. Week 37 then needs exactly the 200 housings that arrive that week, and Line A rises to 54 hours (79%). One change clears both flags, which is the point of keeping capacity and material on linked sheets.
When you outgrow this template
Excel is the right answer for early-stage planning, until it isn't. These are the warning signs that you need a real production scheduling tool.
If three or more of these apply, you have outgrown spreadsheet planning. EDGEBIC, the current generation of RMDB, is User Solutions' finite capacity scheduling and production planning software. It imports the same orders, routings, work centers and stock from Excel, CSV or your ERP, schedules them against real machine, labor and material limits, and shows the result on a drag-and-drop Gantt chart. It is a one-time license: EDGEBIC APS is $25,000, and EDGEBIC Complete, which adds MRP, inventory and purchasing, is $35,000.
See EDGEBICFrequently asked questions
Is this factory production planning Excel template free?+
Yes. Download the .xlsx directly from this page: no email, no sign-up and no macros. It works in Excel 2016 or later, including Microsoft 365.
What is the difference between a factory production plan and a production schedule?+
A production plan sets quantities by product, line and week and checks them against capacity and material; it answers "what and how much". A production schedule sequences individual jobs on individual machines with start and finish times; it answers "in what order and when". This template is a production plan. When you need the sequence, use the production schedule template or a finite-capacity scheduler.
How does the demand vs capacity check work?+
Line load is SUMPRODUCT of the week's planned quantities and each product's standard hours per unit on that line. Line capacity is shifts per day x hours per shift x working days x efficiency. The flag is =IF(load>capacity,"OVER",IF(load>0.9*capacity,"TIGHT","OK")). In the worked example, Line A shows 70 hours of load against 68 hours of capacity in week 36, so it is flagged OVER by 2 hours.
Is the material availability check a full MRP?+
No. It is a single-level check on the component you choose to track: required quantity from the plan against stock carried from the previous week plus scheduled receipts. It deliberately covers the parts that stop lines rather than the full bill of materials. For a full time-phased MRP use the MRP Excel template, and for MRP combined with finite scheduling use RMDB, now EDGEBIC Complete.
How are labour hours calculated?+
Labour hours needed = line hours x crew per shift, because every hour a line runs needs its whole crew. In the example, Line A runs 70 hours in week 36 with 3 operators per shift, so it needs 210 operator hours against 240 rostered. Negative spare hours show where you need overtime or agency labour.
Can I plan in days instead of weeks?+
The grid can be relabelled to days, but daily buckets across 13 weeks means 65 columns and the checks become hard to read. More importantly, at day level the order jobs run in starts to matter, and a bucket total cannot represent sequence. If you need daily plans, that is the signal to move to finite-capacity scheduling.
Start with the spreadsheet. Move to EDGEBIC when it breaks.
The template handles the math for a small plan. EDGEBIC is what manufacturers move to when orders, routings and capacity change faster than a spreadsheet can follow: published one-time pricing, and implementation measured in days.