Using Excel in itself does not mean losing control of data. The problem begins when, without manually combining information from ERP, spreadsheets and other systems, a company cannot clearly determine the current state of production, order fulfilment or inventory.
Warning signs include different figures describing the same situation, reports that depend on specific people, data available with a delay, and the inability to reconstruct how a result was calculated. Replacing the ERP system or abandoning Excel is usually not the solution. The first step is to determine which data is needed for decision-making, where it originates, who is responsible for it, and how it is currently processed.
Why does a company still use Excel even though it has an ERP system?
Using Excel alongside ERP is perfectly natural. New planning, reporting and analytical needs often emerge outside the scope supported by the ERP system.
A new report, an additional metric or a change in the planning method may be needed. Modifying the system takes time or requires the vendor’s involvement, so someone exports the data and prepares a solution in Excel.
At first, the spreadsheet may be entirely sufficient. Over time, more columns, formulas, data from other sources, imports or macros are added. The file begins to be used by several people, then by an entire department, and a solution created for a single situation becomes a permanent part of the process.
In a manufacturing company, ERP is usually not the only system. It may be accompanied by systems supporting production execution, warehouse management, quality control and maintenance, as well as solutions that collect data directly from machines.
The risk grows when Excel starts filling the gaps between these systems and becomes the basis for daily decisions.
When does Excel stop being a tool and become an operational risk?
Excel works well for ad hoc analyses, simulations, calculations and one-off reports. Risk appears when a spreadsheet starts acting as a process application, a database, an informal integration layer or the foundation of recurring reporting.
If a planner downloads data from the ERP system to test several schedule variants, Excel is serving its natural purpose.
However, if the current production plan exists only in that planner’s spreadsheet, and without the file no one can determine the order in which jobs should be completed, the spreadsheet has become part of the operational process rather than merely an analytical tool.
The same applies to reports. The mere fact that the final report ends up in Excel does not have to be a problem. The problem begins when preparing it requires downloading several files, copying data, handling exceptions manually, running custom formulas and finally checking whether the result looks credible.
One question provides a useful test:
What will happen to the process if the spreadsheet becomes unavailable or the person who knows how to use it is absent for two weeks?
If the report stops being produced, the plan is not updated, or no one can reconstruct how the result was calculated, the spreadsheet is a critical component of the process rather than merely a supporting tool.
How can you tell that a company is losing control of its data?
The problem should not be measured by the number of spreadsheets. What matters more is whether the information needed for management is unambiguous, current, reproducible and available without manually assembling it from multiple places.
Typical warning signs include:
- production, sales and logistics provide different values for the same job,
- preparing a report requires exporting data from several systems and combining it manually,
- information is copied manually between files or applications,
- only one person knows how to prepare a particular report,
- a meeting begins by determining “which number is correct”,
- a report shows the situation from several hours ago or from the previous day even though decisions must be made in real time,
- two people calculate the same metric and obtain different results,
- managers do not trust the data in the system and maintain their own reports,
- values are corrected manually, but it is later difficult to reconstruct the reason for the change,
- the same object — for example, a product, material, job, batch or machine — has different identifiers in different systems,
- no one can clearly identify the source of a number shown on the dashboard.
If several of these situations occur regularly, the problem no longer concerns a single report. It concerns the way the company creates, processes and uses data.
“Will we meet the plan?” — where does the answer really come from?
Consider one of the production director’s fundamental questions:
Will we complete the production plan by the end of the week and meet customer order deadlines?
The ERP system shows orders, jobs and due dates; the manufacturing execution system shows the actual completion of operations; and the warehouse management system shows material availability. Information about failures and planned downtime is kept in the maintenance system, while the quality system identifies batches that are blocked or awaiting a decision.
At the same time, the planner maintains a separate spreadsheet with the current production sequence, changed, for example, after a machine failure.
Each of these sources may contain correct data. Even so, the answer to the director’s question does not exist in any one of them.
It emerges only when someone gathers the information, combines it, accounts for exceptions and interprets the result.
Data freshness is a separate issue. Some information is refreshed almost in real time, while other information is updated only after the planner manually completes the spreadsheet. It is therefore necessary to know not only whether the data is correct, but also whether it describes the same point in time.
A company may have all the data it needs and still lack an answer to a basic operational question.
If such an analysis is a one-off exercise, manual work may be justified. However, if someone performs the same steps according to the same rules every day or every week, the problem is no longer a lack of data. It lies in the way the data flows and is used.
Why does ERP not necessarily provide a complete picture of production?
ERP supports specific processes and records the related events and transactions, but answering a management question often requires combining the plan with actual execution, material availability, quality status and the situation on the machines.
This does not mean that the ERP system is working incorrectly. The information needed to make a decision simply extends beyond the scope of one process and one system.
The problem is not data fragmentation itself, but the lack of unambiguous sources, combination rules and agreed requirements for data freshness.
A single source of truth does not mean a single system
Organizing data does not require moving all information into one application.
It is far more important to define clearly where each type of data comes from and which source should be treated as authoritative.
ERP may be responsible for order data, the production system for the actual completion of operations, the warehouse system for inventory levels, and the quality system for batch status.
The company therefore does not need one system containing all information. It should, however, have a consistent view of the situation based on clearly defined data sources.
Not every piece of business information has a single source. A metric for timeliness, efficiency or availability may be the result of calculations based on data from several systems.
For source data, an authoritative source must therefore be identified; for derived information, a common calculation rule must be defined.
Merely identifying sources and calculation rules is also not enough if individual systems use different identifiers for the same products, materials, jobs, batches, operations or machines. Common identifiers or unambiguous mapping rules are needed.
The level of detail at which information is to be compared must also be established. An analysis of an entire job differs from an analysis of a single operation, batch or machine cycle. Data may be correct in its source systems and still be unsuitable for direct comparison if it relates to different levels of detail.
Definitions are also important.
What exactly does “job completed” mean?
When is downtime counted?
What does “available material” mean — physical presence in the warehouse, or the quantity that can be used for a specific job?
Without such agreements, even a technically correct integration can provide different answers to the same question.
Manual reporting costs more than employees’ time
The greatest cost of manual reporting is not always the time spent preparing a report. As the problem grows, reconciling data before making a decision — rather than preparing the report itself — becomes the greater cost.
The first consequence is delayed information. If a report on the previous shift is ready several hours later, the decision may be based on a state that is no longer current.
The second is the risk of error. The more exports, copying, custom formulas and manual corrections are involved, the more the result depends on how the report was prepared.
The third is the inability to reconstruct the result. After several weeks, it may be difficult to determine which source a value came from, who changed it and why.
The most serious consequence, however, is often a loss of trust in the data.
A manager maintains a separate spreadsheet because they do not trust the central report. Sales prepares another report. Production prepares yet another. More versions of the information emerge and later have to be reconciled again.
The mechanism begins to feed itself:
lack of trust -> separate reports -> more versions of the data -> even less trust
As a result, managers spend part of their time establishing the facts rather than making decisions.
How to reduce manual reporting without replacing the ERP system?
Improving reporting should not begin with selecting a tool. The first step is to determine what information is genuinely needed for decision-making and how it is currently produced.
Start with business decisions
Instead of beginning with the question “what data do we have in ERP?”, it is better to identify the most important operational questions:
- Will we meet the plan?
- Which jobs are at risk?
- Do we have the materials needed to complete the upcoming jobs?
- Where does the most downtime occur, and why?
- Why is on-time delivery declining?
Only then should the company determine which data is needed to answer these questions reliably.
Define metrics and their definitions
If a decision is based on metrics, it is necessary to define not only how they are calculated, but also what the input data means.
Two departments may use correct data and still report different results if they understand “completion”, “downtime”, “shortage” or “timeliness” differently.
Standardizing definitions is often just as important as integrating the systems themselves.
Identify data sources, owners and required freshness
For each important piece of information, it is worth answering three questions:
Where does it come from?
Who is responsible for its definition, quality and maintenance rules?
How current must it be for a decision to be made on its basis?
A data owner is a person or business role responsible for the definition of the data, its quality requirements and the rules for maintaining it. They do not have to be the administrator of the system in which the data is stored.
Freshness is equally important. A report may use the correct source and still be useless if its data is updated once a day while decisions must be made every hour.
If data is incorrect, the correction should be made in the source system or through a controlled correction mechanism, not only in the final spreadsheet. Otherwise, the same problem will return with the next export.
Map the full decision-and-data flow
The analysis should begin with the decision and work backwards to the required metrics and data. The next step is to trace how the data moves from its sources, through successive transformations, to the report, alert or recipient.
The following map can be useful:
decision -> metric and its definition -> required data -> sources and owners -> identifiers and level of detail -> required freshness -> processing rules -> report or alert -> recipient
Such an analysis reveals the points where data is re-entered, duplicated, corrected manually or processed according to rules known to only one person.
It also helps distinguish a system problem from a problem with the process, the data definition or responsibility for maintaining it.
Only then choose the integration and reporting method
Once it is clear which sources are correct, how current the data should be and which rules should govern its interpretation, a technical solution can be selected.
The choice depends, among other things, on the required data freshness, the number and type of sources, the integration capabilities of existing systems, the need to retain historical data, the scale of the data and the criticality of the process.
The scope of the solution may include a simple data exchange and automatic report generation, a shared data layer with a BI tool, or merely the removal of several manual steps without rebuilding the entire architecture.
The target flow can be reduced to a simple model:
source systems -> controlled data flow -> agreed data and rules -> reporting and alerts -> decisions
Excel can remain an analytical tool used by the recipient of the data. It should not, however, serve as an uncontrolled integration layer on which the company’s day-to-day operations depend.
Maturity does not mean that a company does not use Excel. It means that critical processes and decisions do not depend on manually assembling data in it.
Why will implementing a new ERP or BI tool alone not solve the data problem?
When the number of manual reports grows, the natural response is to try to replace them with a new tool. Technology can be part of the solution, but it does not remove the source of the problem on its own.
A new ERP system may be justified if the current system genuinely limits the company’s growth. It will not, however, automatically standardize data definitions or responsibility for updating the data.
A dashboard or another BI tool can significantly improve access to information. However, if it is connected to inconsistent sources, it will primarily display a more readable version of the existing discrepancies.
A data warehouse may be an appropriate part of the architecture, but collecting data without clearly defined business needs can easily lead to a large technology project with unclear value.
Banning the use of Excel, meanwhile, removes the tool employees often used to fill a genuine gap. If a better way to perform the same task is not provided, the gap will remain.
The goal should therefore not be to eliminate a particular program. The goal is to remove the dependence of critical processes on the manual and uncontrolled flow of information.
Where to start organizing data in a manufacturing company?
There is no need to begin with a complete inventory of all systems, reports and spreadsheets.
A better starting point is to select 3–5 important operational decisions. Priority should be given to areas where data or reports:
- are needed regularly,
- require a great deal of manual work,
- lead to disputes about data correctness,
- depend on the knowledge of specific people,
- arrive too late to support the decision,
- have a significant impact on production or order fulfilment.
For each of these decisions, it is worth first working backwards to the required data and its sources, then tracing how the information is processed and delivered to the recipient.
Such an analysis shows where the real problem lies: an unclear data definition, several sources showing different versions of the same information, the lack of an owner, insufficient data freshness, manual processing or a lack of integration.
Only then can a meaningful decision be made about what actually needs to change.
The first question should therefore not be: “Which system should we implement?”
It is far better to begin with different questions:
Which data do we use to make our most important decisions? Where does that data come from, who is responsible for it, and how is it processed? Is it current enough at the moment the decision is made?
