Creating a Single Version of Truth to Uncover Product Profitability

Background and Problem

Our client wanted to build an enterprise data program with the objective of getting greater insight into their product profitability at product (SKU), region, and customer level. At the time, their systems were unable to effectively allocate input costs to specific products, which made it difficult for them to assess product costing and margins. The client had recently undergone organizational changes and they wanted to get a holistic, real-time, financial picture of the company.

Prior to the project, there was limited data exchange between the various lines of businesses, and many of the processes, including data governance, were being done in silos with limited collaboration and synergies between divisions and regions. While they did have specific databases to support their business units, most financial reporting was being done manually using Excel. The client had limited knowledge about the potential of an Enterprise Data Architecture but were open to adopting best practices to bring efficiency and transparency into their data preparation and reporting processes and synergies between different lines of businesses.

Why Zelusit?

Zelusit had previously worked with the client on a few projects, and our expertise and past successes had been instrumental in building our relationship with their team. When the client narrowed down on their requirements and the scope, Zelusit was invited to participate in the implementation of the enterprise data project.

Zelusit’s Solution

Our team started with an initial 8-week discovery session, where we evaluated their processes and capabilities to identify gaps and weaknesses. We then grouped them into 5 capabilities that needed to be strengthened in order to fulfil their goal of getting a unified version of Gross Margin at the product and customer level. We showcased the business value we could deliver through our solutions under the 5 key areas below:

Enterprise Data Warehouse

Building product profitability into their Enterprise Data Warehouse would change how they currently report products and view financial information. Our solution enabled them to consolidate their product profitability data across various subsidiaries and business units and create a strategic repository of information.

Zelusit’s team built and deployed an Enterprise Data Warehouse solution architecture and data models using Microsoft Azure. The solution comprised of an on-premise environment where all the data sources are brought together before being moved to the Azure Data Lake on the cloud. The data sources included different versions of SAP used by various business units, SQL server tables, MS Access database, Excel files, and some external systems. Ingestion into the Data Lake was done through Azure Data Factory. The Staging layer comprised of Azure SQL Data Warehouse, and the architecture also included authorization and access control through Azure Active Directory and Azure Security Center. The only non-Microsoft part of the architecture was Qlik, the BI reporting and Analytics tool, which was a part of their earlier enterprise reporting system.

Data Governance

Although the client was implementing some data governance, there was no formal enterprise structure or framework. As a result, there were inefficiencies arising due to poor data quality, limited documentation, and inadequate data validation. Our role was to ensure that the client had the right competencies and framework to curate and manage data on an ongoing basis. Zelusit’s team reviewed the existing data governance processes, conducted workshops with the client’s team and, with their inputs, developed a framework and implementation roadmap for enterprise-wide data governance.

Our team helped the client set up a Data Governance Centre of Excellence (CoE) with proper support at the executive level and assigned key individuals for the implementation of the framework. The CoE will be responsible for formalizing roles and responsibilities, assignment of data domains, allocating owners and data stewards for governance. With the aim of aligning or standardizing calculations and better allocating costs to products, we also created KPI definitions for the Gross Margin and instituted a proper Data Provisioning process.

Master Data Management/Reference Data Management

With the objective of creating golden records of the client’s data, Zelusit developed a high-level solution architecture for Master Data Management and Reference Data Management using Ataccama. Since this was not an immediate priority for the client, we created an implementation roadmap and a delivery plan with resource estimates that could be used when the client started considering MDM/RDM.

Data Quality/Metadata Repository

There were a lot of duplicate records in their product data, which impacted the quality of the results derived through analytics and data science. It also diluted the reliability of their reports from a business decision-making perspective. Zelusit did a Proof of Concept (PoC) of data quality using Ataccama technology and gave recommendations based on our review. We also delivered a concise metadata representation to help the client improve completeness and accuracy of data.

Data Science

While the client already had sales data available, they could not arrive at Gross Margin figures without calculating Cost of Goods Sold (COGS) and there were a lot of costs that could not easily be allocated to a particular product. Zelusit’s Data Science team used Databricks and using a graph-based methodology to map the raw ingredients to the final product and then allocated costs based on cost of all the inputs. Using this methodology, the client could now allocate costs down to the SKU/product level and calculate gross margin for each product line. This was a key capability from the perspective of real-time and predictive reporting of product profitability and helped the client get an accurate representation of the organization’s financial picture, something that they had been unable to do before.

Zelusit created two reporting views – one for their financial statements, and the other for commercial or business decision-making. We also built a dashboard which showcased key metrics like COGS, Gross Margin, Net Sales, and Operating Margin at SKU level and provided a comparative depiction of actual versus budgeted numbers for various parameters.

Impact

• Cost/profitability allocation at SKU level
• Enterprise-level financial view
• Improved data quality
• Time and resources savings
• Increased operational efficiency

Zelusit solution allowed the client to look at their data with a different lens and provided a holistic view of their previously siloed data.

From a financial perspective, the client could now allocate costs to different products more effectively, and consequently, could make business decisions based on product profitability, rather than sales. The Data Warehouse provided a clearer, real-time, financial view of the company, and they were able to account for the value of their data assets, which will essentially increase the company’s worth. The automation of reporting will also reduce the client’s reliance on manual processes, result in time and resource savings, and lower the likelihood of errors in financial documentation.

The Data Governance CoE is bringing consistency and transparency into the way different lines of businesses report their data,and opening opportunities for streamlining processes and getting rid of shadow IT, or IT systems deployed by other departments to work around the shortcomings of the central information systems. The remediation of data quality issues and appropriate documentation of processes will increase operational efficiencies. The CoE is also erasing the earlier silos, so that governance can be implemented at an enterprise level and data is available to users in a consistent, validated, trusted format, which can be leveraged for insights and decision-making.

Interested in learning how Zelusit can help your business build a modern enterprise architecture and add advanced analytics capabilities? Schedule a free consultation with our experts today.