Plant heads and operational finance leaders in Indian manufacturing hubs (Chakan, Peenya, Hazira, Sanand) often struggle with fragmented daily production reports spread across shift logs and ERP exports. The five-KPI dashboard below is the one we ship most often. It is built in Excel 365 with Power Query and Power Pivot - no new platform, no licence, no migration. The same pattern works for a 50-person shop floor and a 5,000-person plant.
The five KPIs that actually matter
The dashboard we ship on every manufacturing engagement has five tiles. The temptation in Indian plants is to track twenty KPIs and present a wall of charts. The reality is that the plant head and the finance team both want the same five numbers, refreshed daily. Anything beyond those five is a report, not a dashboard.
01
Daily Production Yield
Actual output versus planned capacity, broken down by shift and line. Calculated as SUMIFS(ActualQty) / SUMIFS(PlannedQty) for the day. The target is 95% for most discrete manufacturing; below 90% indicates a line problem that needs investigation within the same shift.
=SUMIFS(Output, Line, B5, Shift, B6) / SUMIFS(Planned, Line, B5, Shift, B6)
02
Scrap and Rejection Rate
Percentage of raw material or finished units rejected, with the breakdown by cause code (machine defect, operator error, material defect, rework, customer return). The cause code breakdown is what makes the KPI useful - the percentage alone is decoration.
=SUMIFS(Scrap, Line, B5) / SUMIFS(Input, Line, B5)
03
OTIF (On-Time In-Full)
Customer orders delivered on the agreed date and in the agreed quantity. Calculated as orders meeting both criteria divided by total orders. Anything below 90% is a customer-experience problem; anything below 80% is a commercial problem that needs escalation.
=COUNTIFS(DateDelivered, "<="&PromisedDate, QtyDelivered, ">="&QtyOrdered) / COUNTA(OrderRef)
04
Machine Downtime
Total breakdown hours vs planned maintenance hours, per machine center. The ratio of unplanned to total is the leading indicator. A plant with 80% planned / 20% unplanned is healthy; a plant with 30% planned is firefighting.
=SUMIFS(Downtime, Machine, B5, Type, "Unplanned") / SUMIFS(Downtime, Machine, B5)
05
Cost-per-Unit
Fully-loaded cost including raw material, conversion, scrap, downtime allocation, and overhead. Calculated monthly, not daily, because raw material prices fluctuate. The trend is what matters - a flat cost-per-unit is the goal, an upward trend is the warning.
=SUMIFS(Cost, Product, B5, Month, B6) / SUMIFS(QtyProduced, Product, B5, Month, B6)
Where the data comes from
Every Indian plant we work with has the same four data sources, and the four Power Query connections that read them. The pattern is identical for a Chakan auto ancillary and a Surat textile unit. The only thing that changes is the column names and the rate of arrival.
MES export
Most modern Indian plants run a Manufacturing Execution System - a software that records every production event, downtime event, and quality check. The MES exports a CSV or pushes to a database. Power Query reads the CSV from a shared folder or connects to the database directly. The standard refresh is hourly.
Shift log
Many Indian plants still record the shift on paper or in a Google Sheet. The Sheet is dropped into a shared folder at the end of every shift. Power Query From Folder reads all the shift logs and appends them. The format standardisation is the only manual work - and once that is in place, it does not need to be redone.
ERP (SAP / Tally / ERPNext)
The ERP holds the despatch register, the sales register, and the inventory. The OTIF and the cost-per-unit come from the ERP. Power Query connects to the ERP database directly, or reads a CSV export dropped into the shared folder by the ERP scheduler.
Quality register
Rejection and rework data often lives in a separate system or a paper register. Once the quality team agrees on a column structure, the register becomes a Google Form that drops straight into the shared folder. The Forms → Sheets → Power Query chain is the most underrated pattern in Indian manufacturing analytics.
Designing for the plant head AND the finance team
A manufacturing dashboard has two audiences. The plant head wants the operations view: yield, scrap, downtime, OTIF. The finance team wants the financial view: cost-per-unit, contribution margin, working capital impact. A good dashboard gives both audiences their view from a single source of truth. The pattern is a top strip with the five KPIs, a middle band with the daily trend, and a bottom scroll with the line-level detail.
- Top strip - five KPI tiles. Each tile shows the current value, the delta from yesterday, and a sparkline of the last 30 days.
- Middle band - daily trend. A single line chart for each KPI, with the target line and the warning zone. Red where the line crosses the warning zone.
- Bottom band - the line-level table. The raw data, filtered by line and shift. Plant supervisors use this to find the row that triggered the red.
How the refresh runs
The Power Query connections are configured to refresh on file open. The team opens the dashboard, clicks Refresh All once, and the day is in front of them. For plants that want a TV display, the file is published to OneDrive, the refresh schedule is set to hourly, and the TV opens the file in Chrome kiosk mode.
The standard pattern for a five-KPI plant dashboard ships in two to three weeks. Add a week for OTIF from the despatch register and another for cost-per-unit. The full engagement is fixed scope, fixed price, named owner - ₹40K to ₹1.5L from an senior remote consultant. The handover is a recorded walkthrough plus 30 days of support so the plant team can run it themselves.
What to add next
Once the five-KPI dashboard is in production, the natural next step is a quality tile (first-pass yield vs final yield) and a maintenance tile (MTBF, MTTR). Both come from the same shift log and add one Power Query connection each. The five-tile-to-seven-tile upgrade is a one-week engagement and the most common follow-on we ship.
