Production KPIs: 10 Metrics with Formulas and a Free Excel

Production KPIs are the numbers that tell you whether a line did what it was supposed to do: how much it produced against plan, how long it was stopped, how fast it ran, how much came out good and what it cost in labor hours and energy. Ten are enough to run a plant; the rest is usually noise.

Below are the ten with their formulas, the mistake that most distorts weekly OEE, and a free spreadsheet that calculates them from what gets logged at the end of each shift.

What Are the Production KPIs? #

The first four are logged or calculated per shift. OEE is availability times performance times quality, and its value is that it forces you to separate the three losses: a line at 70% OEE can have a downtime problem or a speed problem, and each one has a different fix. The guide to OEE in Excel covers it in detail.

How Do You Calculate Weekly OEE Without Inflating or Penalizing It? #

Add first, divide last. It's the most common mistake in production spreadsheets: calculating OEE for each shift and then averaging the percentages.

An example with a line whose ideal cycle time is 1.2 seconds per unit:

The average of the two percentages is 52%. The real OEE for those ten hours is 69%: 600 planned minutes, 492 run minutes, 419 minutes of production at ideal speed and 98.9% quality. The average gave the half shift the same weight as the full shift.

The rule applies to any KPI that is a ratio (attainment, quality, productivity): add up the numerators and denominators for the period and divide at the end. The spreadsheet does it that way in every dashboard formula.

What Happens When the Recorded Number Doesn't Match What the Line Produced? #

It's the KPI almost no spreadsheet includes and the one that heads off the most arguments. The line has a counter or a PLC that counts; the shift fills out a report; the report ends up on a shipping document or in the ERP. If those two numbers differ by more than 2%, your OEE, plan attainment and cost per unit are calculated on a number that isn't real.

In the plants we work with, it is by far the first problem that shows up, before any discussion about efficiency. The spreadsheet has a column for each number and flags in red the shifts where they diverge. If you only have one of the two, leave the other blank: the rest of the KPIs work the same.

How Many Production KPIs Should You Watch? #

It depends on who's watching and how often:

• On the line, every hour: units produced against that hour's plan. Nothing else.
• At the end of the shift: availability, performance, quality and the minutes of downtime with their cause.
• In management, every week: OEE, plan attainment, labor productivity and the gap between recorded and counted.

A monthly KPI explains what already happened. A per-shift KPI still lets you change something tomorrow.

What Is a Good OEE? #

The 85% that gets repeated as "world class" comes from Seiichi Nakajima, the creator of TPM, and describes stable process lines with few changeovers. A food plant with many formats and scheduled cleanings can be doing fine well below that. The useful target is your own line last month; that's why the targets in the spreadsheet are editable cells.

The Spreadsheet: Production KPIs in Excel #

It works without macros in Excel 2019 or later, Google Sheets and LibreOffice. You enter one row per line and shift (plan, counted, recorded, rejects, planned minutes and downtime minutes, headcount) and the dashboard calculates the ten KPIs for whatever period and line you choose, with a traffic light against your targets, a table per line and the trend over the last twelve weeks. It comes with twelve weeks of sample data so you can see the dashboard working before you clear it and start.

The Limit of Measuring with a Spreadsheet #

A spreadsheet only knows what someone wrote down at the end of the shift. Stops of one or two minutes almost never get logged, so the availability it shows is usually better than reality, and the loss shows up as low performance without anyone knowing why. Micro-stops are the most common explanation.

When logging by hand stops being enough, iGromi reads counts and stops directly from the PLC and the cameras the plant already has, and calculates the same KPIs without anyone writing them down: production monitoring.

Frequently Asked Questions #

What's the difference between production KPIs and productivity KPIs?

Production KPIs measure what came off the line against what was expected: quantity, time, speed and quality. Productivity KPIs relate what was produced to the resources used to produce it, such as labor hours or energy. In practice they're reviewed together, and the spreadsheet includes both.

Do I need an MES or software to measure production KPIs?

Not to get started. With a well-built spreadsheet and discipline at shift close, you get all ten. Software is justified when manual logging stops being reliable or when you need to see the line while it's producing instead of the next day.

What is the ideal cycle time and where do I get it?

It's the minimum time to produce one unit with the machine running well. Take it from the spec sheet or from the best sustained rate the line has achieved. If performance comes out above 100%, the ideal cycle time is almost always set wrong.