Manufacturing Production Planning System in Excel: Machine Hours, Planned Finish Date, Progress, Rejects and Machine Load

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: manufacturing-production-planning-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Orders, Production log, Report, Job order, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Salman Engineering Works, Sambhaji Nagar, as of 17 October 2026 (made-up data). 8-hour shift at 85% efficiency: two CNC lathes give 13.6 hours a day, the power press 6.8. Sunday is the weekly off.

6 of 8 orders are still open; 2 of them are late on plan.

2. Build it sheet by sheet

Step 1. Setup: machines and products

1. B4 shift hours (8), B5 efficiency (85%), B6 steel rate per kg, B7 reject alert (3%).
2. Machines in A10:B11; C10 hours per day =B10*$B$4*$B$5: 2 lathes × 8 × 0.85 = 13.6.
3. Products in A14:D18: Product, Machine, Cycle time (min), Steel per unit (kg).

Step 2. Orders: hours, days and finish date

1. A5 ="SO-"&(100+ROW()-4). Type customer, product, qty, due date and planned start.
2. H5 hours =ROUND(D5*XLOOKUP(C5,Setup!$A$14:$A$18,Setup!$C$14:$C$18,0)/60,1) and I5 days =ROUNDUP(H5/XLOOKUP(G5,Setup!$A$10:$A$11,Setup!$C$10:$C$11,1),0).
3. J5 planned finish =WORKDAY.INTL(F5,I5-1,11). Weekend code 11 means Sunday only.
4. K5 =IF(J5<=E5,"On time","Late"), red when Late (conditional formatting).

Step 3. Production log and progress

1. Production log: Date, Order no, Good, Rejects each shift.
2. Orders N5 good made and O5 rejects with SUMIFS, P5 balance, Q5 progress =MIN(1,N5/D5) with a data bar.
3. R5 status: Completed, In progress or Not started.

Step 4. Report: load and quality

1. B5 open hours =SUMIFS(Orders!$H$5:$H$500,Orders!$G$5:$G$500,A5,Orders!$R$5:$R$500,"<>Completed"), C5 hours in 20 days, D5 load %.
2. Rows 10 to 14: good, rejects, reject % =IFERROR(C10/(B10+C10),0) and an alert. Compare with the manufacturing rejects project.

Step 5. Job order and Dashboard

1. Job order: type an order no in B4; rows 6 to 14 give the shop floor the product, qty, machine, hours, dates and steel to issue. Set the print area.
2. KPI cards: open orders, late on plan, CNC and press load, reject % and steel for open orders. Charts: open hours by machine and reject % by product.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Ignoring efficiency. 8 hours on the clock is not 8 hours of cutting. Setups and breaks are why efficiency is on Setup.
2. WORKDAY when Saturday is a working day. Plain WORKDAY skips Saturday and Sunday. WORKDAY.INTL with code 11 skips only Sunday.
3. Counting from start + days. A 5-day job starting Monday ends Friday: finish = WORKDAY.INTL(start, days − 1, 11).
4. Rejects not logged. Without rejects, quality problems show up only as short orders.
5. Planning each order alone. Orders on the same machine queue up. Check machine load before promising a date.

5. More real examples

Example 1: Garment unit. Machines become sewing lines; cycle time is minutes per garment from the line's standard.

Example 2: Food processing. Lots per shift instead of pieces, with yield % instead of rejects.

Example 3: Printing and packaging. Machines are presses and cutters; link quotes to the printing press system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate production hours in Excel?

Qty × cycle time in minutes ÷ 60, as in Orders H5.

How do I find a finish date skipping Sundays?

=WORKDAY.INTL(start, days-1, 11). Code 11 treats only Sunday as the weekend; add a holiday range as the fourth argument.

How do I work out machine load?

Open hours on the machine ÷ hours per day × working days ahead.

How do I calculate reject %?

Rejects ÷ (good + rejects), as in Report D10.

How do I plan raw material?

Qty × steel per unit gives kg; add the orders not completed for the steel to buy.

7. Quick quiz

Ten questions on this page. Sign in to save your score.