Common Mistakes in Business Process Automation. When the methodology is flawed but the client believes otherwise

Situation

A company with established business processes and stable operations of the financial department gathers budgets, forecasts, and management reporting in Excel. There is a working package of forms for creating plans and forecasts as well as for entering historical data from the ERP system.

The company's CFO decided to automate the budgeting and reporting processes using a CPM system. When the contractor suggested including a phase for "analysis and refinement of the forms and reporting methodology" in the project, the CFO responded that this was unnecessary: "the methodology has been tested over time and is ready for automation."

The project starts, but the contractor's specialists begin to regularly encounter "blind spots" in the methodology, lack of clear connections between different levels of reporting, and problems with versions and relevance of the master data.

The project begins to fall behind schedule, leading to additional costs for data and formula validation as well as refining the methodology to a state suitable for automation. Meanwhile, no contingency budget has been allocated for these "extra tasks" within the project.

Core Issue

This is a very common case when the Client does not want to incur additional costs for auditing and refining the current methodology and package of forms, justifying this by saying that the methodology is completely ready, "we are already using these files for budgeting and reporting." However, "ready in Excel" and "ready for automation" are not the same thing:

1. Common calculation situations:

• Indicators calculated in external workbooks, visible through formula references. These calculations in external workbooks are the result of "heavy" calculations by other departments not included in the project scope.

='C:\Documents\[Workings.xlsx]Sheet1'!$O$39

• Calculated indicators with formulas containing certain constants whose values change in a non-obvious way between periods or other data slices.

=J33*0.1+1.4%

=IF(T8<60%;92%;94%)

• Indicators with values clearly copied from external sources, but the source is unknown.

58.4184107122037%

• "Broken formulas" in the workbooks due to careless changes in source templates.

=#REF!A5

• The methodology specifies a formula for a certain indicator, but subsequent analysis reveals that this formula doesn't apply uniformly across all data slices. For example, the same indicator might be calculated differently for various product groups. However, by the time the methodology is handed over to the vendor, it has not been formalized for all product groups.

2. Common situations with master data:

• The methodology provided includes an outdated set of master data and outdated content. In the future system, the reference data must undergo significant updates due to business changes.
• The content of identical master data differs between different forms. Aggregated reference elements lack explicit aggregation rules or clearly defined mappings.
• The master data in accounting systems, from which actual data is supposed to be exported to the planning system, does not correspond to the reference data in the forms workbook. Current mapping is absent.

3. Common situations with the methodology as a whole:

• Certain blocks of the methodology remain underdeveloped and have always been calculated "with certain assumptions." However, due to the large amount of information, this detail was overlooked when handing over the materials to the contractor.
• Recently, a new business line, cost center, calculation objects have appeared in the company for which a methodology has not yet been developed, though it falls within the project scope.
• Indicators in final forms are not linked to functional budgets. In reality, final reports are compiled in a semi-manual mode.

Consequences

1. Increase in timeline and budget: The initial assumption was that "everything was ready for implementation," but an additional stage — from several weeks to several months — is required to clarify the methodology and transform it into a structured format suitable for automation.
2. Heightened conflicts between teams. The contractor's project team is forced to spend time resolving disputes, redesigning models due to outdated reference data or inaccuracies in the methodology, and justifying increased budgets. Meanwhile, the CFO may not have additional funds for change management, putting them in a difficult position before project sponsors or the board of directors.

Case Study

Situation 1

A large manufacturing holding begins a project to create consolidated financial statements. The client assures the contractor that the methodology is fully prepared, undergoes annual financial audits from external specialized companies, and requires no refinement. The contractor receives a large package of Excel forms with consolidated reporting for the previous period, consisting of dozens of files and hundreds of worksheets in total.

During the automation of one of the important consolidation adjustments – Unrealized Profit – it becomes apparent that no formalized methodology exists. Calculations for past periods consist of tables with data from unknown sources, almost without formulas or comments, making them extremely difficult to understand. The situation is complicated by the fact that this adjustment was previously calculated by an accountant who recently resigned. Neither the chief accountant nor other financial department employees possessed knowledge of the calculation mechanics and details. The project timeline shifts significantly to accommodate the time required for revising and restoring the calculation methodology.

Situation 2

A group of companies launches a project to automate planning and forecasting. More than 10 companies are in scope, each with its specific budgeting process. The contractor requests a package of forms to estimate the project budget. By direction of the group's CFO, the project manager requests financial reporting packages from all companies. Due to various reasons and company specificities, not all companies can collect and submit complete form sets by the required deadline. However, a complete reporting package is prepared for 4 main companies, and the CFO confidently states that the remaining companies have typical business processes that fully align with the already provided methodology for data collection and calculations.

The contractor estimates the work effort required, and the project commences. During execution, it emerges that the business processes of other companies differ from the four main ones. Differences exist in organizational structure (and consequently in functional budgets), production processes, and master data. Although the differences are not radical individually, their cumulative effect significantly increases the project's workload. The group's CFO, focused on strategic tasks, lacks detailed information on all methodological nuances in each company of the group. The situation is complicated further because complete methodological documentation don't exist for all companies, necessitating ongoing refinement throughout the project.

Problem Indicators

1. Quick methodology analysis highlights problem markers: "broken" formulas, external file references, constants in formulas, inconsistent entity naming conventions, and absence of common mapping.
2. Pre-project inability to collect complete methodology for all work specified in the contract: For organizational, time-related, or other reasons, teams lack portions of the methodology at project start — whether functional blocks or form packages for individual companies within the implementation scope.
3. Project scope includes new entities: Recently acquired companies, newly launched business lines, or recently formed departments. Such scenarios typically require extended time to organize processes and align current methodology with corporate standards, or to develop methodology from scratch for new structures.

How to Avoid the Mistake

1. Audit and formalize the methodology before the project start. Plan for such a stage before beginning the project. Sometimes a rapid audit suffices for all parties to understand the "scale of the problem." The work of organizing the methodology and preparing it for automation can proceed in parallel with the automation project tasks.
2. Unify and update master data. Preparing and validating working reference data for planning and forecasting systems typically requires the largest share of effort and project time from the client's team. Begin these activities well in advance.
3. Implement in phases. When possible, postpone automation of a new company, business area, or department until unified processes are established and methodology is developed and validated.
4. Plan a reserve budget for potential changes. Large IT projects rarely proceed without changing management procedures. During project execution, existing requirements evolve, new ones emerge, task priorities shift, and methodological or technical challenges arise that are difficult to predict. To avoid making difficult compromise decisions mid-project, allocate reserves for possible changes and project expansion.

Join us on April 17 for the webinar "How to avoid mistakes in automation projects" — we’ll cover common mistakes companies make at various stages of automation. The webinar starts at 10:30 AM (UAE time). Full agenda and registration — via the link.