Manufacturing Analytics for Small Plants: Excel, Power BI or Custom Software?

For most small plants, the best manufacturing analytics software to start with is Excel with Power Query, fed by your ERP exports. Move to a BI tool such as Power BI when many people need the same numbers or the weekly Excel update keeps breaking. Add machine monitoring when you need live machine states, and build custom software only when your process, data or users fit none of these tools.

The table compares five routes to manufacturing analytics for a plant with 20 to 500 people. Effort levels are rough guides, not quotes.

ERP reports and Excel are the default start. A BI tool fixes sharing, machine monitoring fixes blind spots on the machines, and custom software fixes fit. Move to the next option only when you can name the problem it would solve, such as why the packaging line keeps stopping.

When Excel and Power Query are enough

Most lists of manufacturing analytics tools compare enterprise platforms built for a data team, not for small manufacturers. A small plant more often has ERP exports, a scrap log in a spreadsheet and one person who builds the reports. In the EU in 2025, own staff did data analytics in only about 28% of small enterprises (10 to 49 people), against about 79% of large ones.¹

Power Query is available in Excel for Windows and Mac, and Power BI runs the same engine. It records your cleaning steps as a query that you refresh to get new data, without changing the source.² You remove blank rows, fix dates and join the scrap log to work orders once. Next week, clicking Refresh runs the same steps on the new export.

This is enough while one to three people own the report and a weekly refresh answers their questions. Three habits keep it working. Save every export under one name pattern in one folder. Fix errors in the ERP or the log, not in the sheet. Keep one tab listing each KPI’s formula and owner.

Move on when the report has to be shared. Versions multiply in email, and two managers quote two scrap rates for the same SMT line. A third data source is another signal, and so are managers who want to click from plant to line to order.

When a BI tool earns its license

The choice between Excel and Power BI turns on sharing, not on calculation. A BI tool gives everyone one data model, one refresh time and one scrap rate. On Microsoft’s pricing page, checked in 2026, Power BI Pro costs $14.00 per user per month and Premium Per User $24.00, both paid yearly. Power BI Desktop, where you build reports, is a free download.³

The bill grows with the audience. Unless reports sit in Premium or Fabric F64 capacity or larger, people who view shared Pro reports need a Pro license too.⁴ Budget one license per viewer, plus time for a model owner who keeps the formulas and the refresh in order.

Pro allows up to 8 scheduled refreshes a day, and Premium Per User up to 48.⁵ That suits shift and daily reports, not live machine states. An old ERP need not rule a BI tool out. If its database runs on your own network, Power BI’s on-premises data gateway can connect the Power BI service to it.⁶

The real limit is coverage: a BI tool only shows what your sources hold.

When machine monitoring is worth adding

ERP data tells you what was made and booked. It doesn’t tell you that a press sat idle in many short gaps that nobody logged. Machine-monitoring software reads machine signals and shows each machine’s state live: running, idle or down. Operators then pick a reason for each stop from a short, fixed list on a tablet or terminal, because free-text reasons such as “issue” can’t be counted.

Signals come from the controller (many newer CNC controls support MTConnect or OPC UA), from PLC tags, or from add-on sensors on older machines. This data quickly outgrows a spreadsheet. A machine logged once per second creates 86,400 rows a day. An Excel sheet holds 1,048,576 rows, so one machine fills a sheet in about 12 days.⁷

Monitoring is worth adding when a bottleneck loses hours nobody can explain. Pilot it on one to three such machines: a packaging line that limits shipping, not a saw that runs a few hours a week. Expand only if the data changes what supervisors do. Until its data is joined to the ERP, monitoring sees machines, not orders or costs.

When custom software makes sense

Custom software is the last step for most small plants, not the first. It makes sense when your process doesn’t fit a standard tool, such as tracing each board by serial number across SMT, AOI and test. It also fits when results must flow back, such as booking scrap in the ERP or sending a stop alert.

Two more cases can justify it: a large audience that needs only simple screens, where per-viewer BI licenses add up, and data in odd places such as test-station log files.

Custom work brings custom upkeep. Ask for the code repository, documentation and monitoring at handover, and agree who fixes the app when a data source changes. Without that, the app becomes the next spreadsheet that only one person understands.

What to measure first

Whichever option you choose, pick three to five KPIs and write down each formula before you build anything. A production data analytics dashboard usually shows them by line and shift.

ISO 22400-2 defines a selected set of KPIs for manufacturing operations management and presents each one with its formula.⁸ Its list includes an OEE index, availability and a quality ratio.⁹ The formulas below are the common shop-floor forms, not quotations from the standard, and not every KPI here appears in it.

OEE                = Availability × Performance × Quality
Availability       = Run time / Planned production time
                     (run time = planned production time − stop time)
Performance        = (Ideal cycle time × Total count) / Run time
Quality            = Good count / Total count
First pass yield   = Units passed first time (no rework, no scrap) / Units started
Scrap rate         = Scrapped units / Total units produced
Downtime by reason = Stop minutes in planned time, per reason code, largest first
On-time completion = Orders finished by due date / Orders due in the period

OEE is the share of planned time spent making good parts at full speed. On an SMT line, first pass yield counts boards that pass AOI and test with no rework. Track scrap cost next to scrap rate, since parts differ in value.

OEE also needs an ideal cycle time, which is often missing. Use the machine’s rated speed or the best rate seen on steady runs, because ERP routing times may include allowances. The check below uses assumed numbers for one SMT shift, not data from a real line.

Example (illustrative, assumed numbers): one SMT line, one 8-hour shift
Planned production time = 480 min − 30 min break       = 450 min
Stop time (feeder jams, one changeover)                =  45 min
Run time                                               = 405 min
Availability = 405 / 450                               = 90.0%
Performance  = (0.5 min × 700 boards) / 405 min        = 86.4%
Quality      = 680 good / 700 boards                   = 97.1%
OEE          = 0.9000 × 0.8642 × 0.9714                = 75.6%
Check:         680 good × 0.5 min / 450 min            = 75.6%

The last line is a quick test for any OEE report: good count times ideal cycle time, divided by planned production time, must give the same figure.

Why analytics projects stall

In May 2026, L2L, a software vendor, released research based on a survey of more than 600 US manufacturing leaders. Its release reports that “74% remain trapped by reporting delays that slow production”, with “only 9% of respondents stating they can find the root cause of a shop-floor issue immediately”.¹⁰ Inside one plant, the causes and the fixes are usually simple.

1. No owner. Name one owner per KPI who keeps the formula and runs the weekly review.
2. Numbers nobody trusts. Match dashboard totals to ERP totals for a few weeks, and show the data date on every page.
3. Too many KPIs. A wall of charts leads to no decision. Start with three to five on one page.
4. Manual steps that break. Copy-paste chains fail when someone renames a column or goes on vacation. Move each step into Power Query or a scheduled import.
5. Dashboards nobody opens. Tie each view to a routine, such as the daily shift meeting, and put the line view on a screen where the team stands.