A Case Study on Data Migration Strategy and Template Design for Mergers and Acquisitions in the Oil and Gas Sector

Executive Summary

Companies often grow through mergers and acquisitions, and as they expand inorganically, the issues and challenges faced by them are numerous. The major issues are synergy evaluation, technology integration, selecting the management team, resolving cultural issues, and internal and external communications.

While there is plenty of non-information technology (IT) issues involved in any merger and acquisition (M&A;), technology issues do play a significant part, and the data aspects of this are the most significant of all. Under technology, the biggest issue involved is smooth transition of master data from the acquired company to acquiring company systems. Each acquired company will already have its own business processes, procedures, and business applications in which customer and vendor records are maintained. So, the transformation of data from their systems is a very challenging and time-consuming task.
Various organizations constantly strive to enhance their operational efficiency through automation. In a majority of cases, this involves introducing new systems or products through which they can improve their processes. Implementing these require master data conversion or migration from the existing system to the target system.

According to several research studies, one of the main reasons for unsuccessful transition of master data at the time of acquisition is lack of tools to support data migration (DM). The traditional tools available are very generic in nature and hence time-consuming, which leads to cost overrun and delays in the project. So keeping this in mind, we should have a proper standardized tool specifically designed to support DM. The “Acquisition Data Migration Template” is one of the possible ways that will help in improving the process of transforming legacy data, extracting the transformed data, and then loading of same in the targeted SAP systems per required format. The Microsoft Excel- based tool helps in reducing time of loading data by splitting it in two parts, the main data and the derived data, in which the field value gets derived automatically using main data, with the help of business translational logic. The template is also standardized in nature, which helps the organization to use the same product for any acquisition in the future. At the time of acquisition, depending on the acquired company, data availability differs. Using the standardization principle, many fields have been kept as standard in this template, which will remain the same for any acquisition.
The Acquisition Migration Data Template helps in addressing these challenges by creating and enabling processes for aggregating, extracting, and loading data with no error and a significant reduction in time. It ensures the smooth transition of data of legacy systems into the SAP system. This product is targeted to reduce the overall human effort and time consumed in loading the master data of the acquired company to the systems. Currently, the master data has been loaded by the users manually, and the probability of making errors is higher. So this work is focusing on making the process error-free and as automatic as possible.

The effects and benefits of this product are many, which are described in detail later in this paper, but the main objective,  which was “To reduce the time consumed in data migration”’ has been successfully achieved.


Oil and Gas Sector Overview

The oil and gas sector is one of the core industries worldwide and has very significant forward linkages with the entire global economy. The nature of this industry is very dynamic, and changes at an unprecedented pace. This particular sector has to respond very quickly to emerging trends, anticipate risk effectively, improve performance, and operate efficiently.

In this paper, we will discuss in detail more about the propane gas industry, one of the important contributors to the United States’ (U.S.) economy. The nation’s propane gas industry includes production, transportation, storage, wholesaling, and retail sectors, all of which contribute to U.S. gross domestic product. As players in this industry are more involved in distribution, the investment required for business is less, and results in many small players in the sector. This makes the sector highly competitive and full of challenges with many M&A; opportunities. As companies in this industry tend to grow inorganically, we can see a large number of M&A; in this sector in the past few years and increased stress on IT landscape, which needs to devise different business process integration strategies to realize the intended M&A; return on investment (ROI).

We analyzed the DM process and different problems faced by an organization during M&A; by studying “Company ABC” processes. So, for our further discussion, we will focus on one of the biggest U.S. propane distribution companies, Company ABC.

About Company ABC

Company ABC is the largest distributor of propane, propane equipment, and related services in the U.S, and has 5,900 employees in 600 distribution centers. The company has more than 1.3 million customers, in all 50 states, composed of both residential and commercial customers. They also sell, install, and service propane appliances. Additionally, the company also installs and services propane fuel systems for motor vehicles.

Company ABC Acquisition Program in Propane Industry

As M&A; are one of the common processes in propane industry, organizations run a separate program which can make M&A; successful for them. Like this, Company ABC is running a program named the “Acquisition Program,” which is looking to acquire propane companies. One of the key requirements of this program is smooth transition, which also includes transition of data from other companies systems to Company ABC systems.

Master Data and Its Importance

Master data is centralized data that is shared or exchanged across business applications, business processes, and different business lines. Master data information is vital to the operation of the business. It includes data about customers and prospective clients, employees, inventory, suppliers, financial data, analytics, and more. Master data that is typically shared by multiple users and groups across an organization and stored in different data systems may or may not be referenced centrally; therefore, the possibility exists for duplicate and/or inaccurate master data.

It is central-level data in SAP which stores the long-term information, which can be accessed to perform multiple transactions in SAP. Thus, master data is that persistent, nontransactional data that defines a business entity, for which there is an agreed- upon view across the organization.

Integration of Master Data in Mergers and Acquisitions

With the rising pace of takeover activity, every acquiring company technology department has to come up with many integration strategies. Data integration is one key element in any M&A; without which no deal can be successful or can give intended ROI. During any M&A;, there are basically three approaches to the integration of master data:

  • No integration:  Sometimes an acquiring company allows the acquired company to operate with the same systems and method it has, without integrating the business processes and master data. In this case, organizations will not gain any operational efficiency, with no consolidated reports, and function as independent environments.

  • Consolidation of data:  In this case, acquiring companies consolidate the data of acquired companies to their own system, using DM techniques or tools. This approach requires major effort, but in the long run, has proven to be the best.
  • Synchronize the two sets of data: Here organizations neither migrate data, nor let it alone; they synchronize the two sets of data using some software like master data management.

In the case of consolidation, data needs to be smoothly migrated from the source system to the target system. This needs proper processes in place to facilitate the smooth transition of data.

Data Migration

According to Wikipedia, DM is the transferring of data between storage types, formats, or computer systems. It is required when organizations or individuals change systems or upgrade to new systems, or one company acquires another and there is a need for translating data from one format to another. It is a one-time process and once the DM process is completed for a particular acquisition, it stops. This is in contrast to many data integration tasks that continue in an ongoing process. DM does not mean just copying the data (data movement), but actually is the process that also includes the validation and cleansing of data to ensure that the data being migrated is correct and in format for use by application.

DM involves the restructuring of data in some way; this may mean fields being merged, or formats being changed, or the data being transformed in various other ways. It is the process by which company transforms the legacy data into its standards, using standardization and business translational logic. The DM market is large and growing. The total budget for DM projects in 2007 is set to break the $5 billion mark, rising to $8 billion by 2012. According to a report, banking, diversified financials, and oil and gas operations are the vertical sectors predicted to drive most spend on a global basis.

So, as we know the kind of problems lots of companies are facing in the smooth transition of data, and as it will also attract a very big market in the near future, effective practices need to be established to ensure the integration of the master data, by which time and budget overrun can be reduced by a significant percentage. 

Problem and Need of this Project

As demonstrated by much research, more than 50% of M&A; fail to reach value. A significant part of the reason why M&A; activity fails to create value is precisely because they are dependent on the ability to integrate two companies from an IT perspective. Most of the time, the reason is negligence in master data transition. Bloor Research presents some daunting figures: More than 80% of DM projects fail or overrun, 64% are delivered late, and 37% run over budget. According to the National Computing Centre, more than 50% of companies were not able to provide any integration within three months of acquisition. Also, more than a third of companies did not expect to complete integration for more than two years, or could not say when they would complete such integration.

With these statistics, one cannot deny the importance of the smooth transition of data during any M&A;, and it would not be inappropriate to say that when any DM project fails, the initiative to consolidate systems or integrate business processes fails. The promised business results of increased operational efficiency or greater revenue fail to materialize in time to meet executive and shareholder expectations.

The challenges during any M&A; in the propane industry are not just limited to consolidation of data, but also the need of completely understand the data to be migrated. The data sources of acquired companies are neither fully known (too many spreadsheets and Microsoft Access databases) nor in the proper format. So, along with the need to migrate data from many systems to one system, data transformation is also the biggest task at the time of acquisitions or mergers. The need of the right data at the right place at the right time is imperative for any business deal to be successful. So, this paper deals with one possible solution, with which organizations can overcome this problem, improve their process by shifting from a manual process to automation with minimal effort required, and eventually save significantly in cost.

Scope of the Study

The scope of this project is to suggest the possible solution that can overcome the problem of DM issues from the acquisition point and can help organizations in successful transition of data from source systems to target systems.

Also, it has been found that the lack of a proper tool is one of the biggest problems in the integration of master data. So our scope is to develop a tool or a product for oil and gas sector organizations, in which M&A; is a regular phenomenon, to help them transfer the master data of acquired companies to their own systems.

Acquisition Migration Data Template

According to Philip Howard (Bloor Research), in any SAP migration project, the most useful tool that helps in a smooth transition is a template of the target company business application. With the relevant data model built into it, the template would significantly improve migration timescales. To continue on this principle, the Acquisition Migration Data Template is built on the Excel platform, which will help smooth transition of data of an acquired company to the acquiring company system. This template methodology is based on deriving the dependent fields on the basis of business translational logic and determining the value of numerous fields with the help of business standards and default values.

Template Design

This template consists of 15 master data key objects that are relevant for any acquisition and more than 450 fields. The template is divided in two sections: one section in which the user needs to enter the field values, known as key fields, and one section in which all the derived and default field values will be generated automatically.

Key Field: These are the main data fields in which the user needs to provide values, e.g., customer address and storage objects material. Also in key field sections, drop-down lists are provided wherever possible to ensure correct entries and to save time in making entries. For example, whether the customer is commercial or residential are two options in a drop-down and the user can easily select from them.

Derived and Default Fields: Using business translational logic, various fields have been derived using key field values. For example, using the customer group key field value template will determine the reconciliation account value—if the customer group is residential, then reconciliation account value will be different, and if the customer group is commercial, then it will also be different, and will be automatically derived by the template. The rest of many fields will be standard ones per the business and will not change for any acquisition (e.g., currency used will always be U.S. dollars in North America, units of measurement will always be gallons, and many more). Initially, in the absence of this template during any acquisition, all the fields were key fields and the user needed to manually enter all, which resulted in many time-consuming tasks.

Acquisition Data Migration Template Analysis


The detailed analysis of the identified objects is as follows:


Process Steps in Template

The process steps in the template are:


  1. Selecting the source: Selecting the source of data is a very important task, as most data is in different databases or spreadsheets. So in this step, the user needs to identify all the data sources that need to enter into the target system. In this, the user has to discuss the needs and requirements of the business, and accordingly has to decide what data needs to migrate.

  2. Mapping of data fields: Data in the source and target systems might have different headings or nomenclature. Here, the user needs to study the data and will have to map the source field and target data manually.

  3. Transformation of data: In this step, the user will manually enter all the field values in the template. The template contains different worksheets in which the user needs to enter the data of the acquired company. All the derived and default fields will be generated by themselves. It is simply a manual exercise that needs to be accomplished once the user has a full understanding of both the source and target systems.

  4. Validations and mandatory field check: This step works in parallel to the transformation of data step. Here, the template will ensure, with predefined validations, the user has not mistakenly left some mandatory field blank and graying out a field which is not required for some specific selection already made by user. This step helps reduce errors, improves quality of data, and saves the user time in entering the records into the system.

  5. Generation of extract files: Once all the data is entered in the template worksheets, extract files of all the master data objects will be generated automatically.

  6. Creation of load file: Using macro-enabled extract files, with just a single click, the system will generate the load file in text format. This step ensures that data present in the text file is in exactly the same format as we require in the SAP system.

  7. Loading of data in the SAP system: The final step is to load the data we generated in the last step using SAP workbench. Here, using legacy system workbench transactions or customized programs, we can load the data.

Benefits and Strategic Objectives of Acquisition Data Migration Template

The primary goal of this product is to improve Client DM capabilities and efficiencies, and eventually reduce cost. This is being achieved by making the process automatic and with many validations, which will help the user load the data into the SAP system. The four main strategic objectives on which this template is designed are discussed below:

  1. Increase Efficiency: As mentioned, with the fields being derived using business translational logics, and kept default considering standards, the time consumed in making all the field entries is significantly low in comparison to manually entering all the field values. Initial estimates show that it will save more than 50% of the time taken by a user to enter one customer record. So, this template decreases the time of delivery by improving and automating DM. Also, usage of this template is not limited for any particular acquisition, but using a systematic process, is designed in such a way that it can be used for any company’s DM.

  2. Reduce Errors: Using different validations and checks, this template has been designed to avoid human errors. In this template wherever possible, a drop-down list is provided, so that the user will only be able to select data from that list. Moreover, the drop-down in most of the cases will be dynamic, and will get changed on the selection of some prior field value. This will ensure the right data at the right place. Also, to avoid any confusion and chance for error, fields not required for particular records on the basis of some selection made by the user will automatically be grayed out by the system. This will enable the user to identify the fields in which he or she should not make any entry. Moreover, there is an auto check that will make sure all the mandatory fields have been entered by the user; otherwise, the system will throw an error.

  3. Smooth Transition of Data: With the improvement in process and the formats of fields being defined clearly, data can be easily transferred from source systems to target systems. Also, with user guides at necessary places and drop-downs present, the user can easily process the migration of data.

  4.  Reduction Cost and Time: With the improvement in process and the significant reduction in time consumed in entering data, the cost incurred in migrating data is reduced. With this template, there will be no need of any third-party tool or support, which will save money for any organization. Moreover, with errorless data, there will be multiplying effect on profitability and cost-reduction elements.             

Limitations and Future Scope

As this framework is executed on Excel, it can handle only limited data. Formulas, formatting, and macros make the template heavy and after a threshold, it becomes unsuitable. We set this threshold to 3,000 entries. Beyond this threshold, the user may encounter the following problems:

  • Huge size of Excel file: The formatting, formulas, and macros lead to increase in file size, even when there is no data on the template. And beyond this limit, size makes the template difficult to use.

  • Long-running macros: The macros will take more time to perform various automations. Under the current limit, no macro runs for more than one minute.

Though our template solves the problem of DM for high volumes of data, there still are future enhancements that can be made. We recommend an alternate implementation that will help in scaling up. The implementation can be enhanced by the use of a back-end database. As many people are comfortable with using Access along with Excel, we recommend the same. Under this implementation, the front end will be an Excel form and rows of records will be inserted into the database upon submitting the form. The load files can be viewed on Excel or can be directly exported in a text file format suitable for importing and can directly load into the system.


Data migration is one process many organizations overlook and then face undesirable consequences. In this paper, we tried to throw some light on the importance of DM and how crucial it can be during any merger or acquisition. For seamless and efficient operation of any organization during any acquisition, this is one process which creates the difference between a successful and unsuccessful M&A; deal. The tool mentioned in this paper is very effective, as it not only takes care of data loading in less time but it also gives error-free quality data for loading purposes. With the help of standardization, automation, and derivation of derived fields, this helps improve the process of data migration.



submit to reddit