BI in FMCG/Retail Industry : An Overview

Introduction:

Every organization generates large amount of data from their day to day operation. From this pool of data, some of them able to harvest useful information; termed as business intelligence (BI). Organizations which are able to develop business intelligence as a competitive advantage do not focus only on descriptive analytics and reporting, rather on predictive analytics to take more effective steps towards delivering better performance and stronger bottom-line. Extended supply chains and globally dispersed customer base has made managers more keen to use decision support systems  based on sound  logical framework rather than going with their experience and gut-feelings

Major Changes in Retail/FMCG Industry

Retail and FMCG industry as whole evolved to a greater complexity due to certain changes in various fronts.  Customers, with larger disposable income now have greater appetite & demand for global products. Greater global exposure has resulted in requirements differentiated product & service without any compromise on quality. In India a middle class segment bulging can be observed which is full of vibrant young stars; fully conscious about the value proposition of the product. FMCG as an industry also experienced explosion of products; division of markets (traditional individual stores & organized retail); entry of new players, especially in organized sector; increasing competition as a result of this; innovation and process capability of global players. Above all, the industry is suffering from increasing supply chain bottlenecks due to internal and external issues. Changes in these fronts impacted strategy & operations of the industry. Organizations are trying to increase their flexibility by adopting contract manufacturing and designing demand driven supply chain; improving customer service by outlet segmentation, customized service, supply chain segmentation and outsourcing noncore activities to third party (e.g. 3PL and 4PL concepts). They are also seeking greater organizational alignment utilizing EPM, divisional scorecards and integrated sales & operations planning. Continued efforts to achieve greater customer focus can be watched as organizations realized importance of demand analysis and CRM.

Responding the Changes: How BI helps?

BI generally supports analytics function of an organization. Major areas under this function are listed below:

Financial	Marketing
Working Capital Optimization (C2C Cycle)  (Including Receivable Analytics, Payables Analytics,  Inventory management) and  Treasury Cash Analysis	Trade promotion effectiveness, WD profitability,  Outlet performance,  Channel utility, Integrated consumer demand & market performance visibility, Responsiveness
Supply Chain	Organization Strategy
Supply Chain Performance Management  Product cost & profitability analysis  Operational planning and execution  Inventory visibility & safety Route optimization  Supply network efficiency  Spend Analytics	Balanced Scorecards (Strategy Maps, Dashboards)  Budgeting, Planning & Consolidation systems  Operational performance management
	Operations
	Plant Performance Management  MES Analytics Bottleneck alerts

BI helps to raise the level of collaboration among supply chain partners. In a retail-FMCG set-up as retailer-supplier relationship matures, risk sharing occurs among supply chain partners. Partners can be convinced of sharing risk only when the proper information is available across the chain thorough BI so that they can take right decision at the right time. Most basic type of alliances is called a quick response program (QRP) where retailer provides point of sale (POS) information to supplier. Supplier may use this for scheduling production and delivery or determining inventory level. The retailer still submits individual orders and supplier maintain own forecasting system. In a continuous replenishment program (CR), supplier delivers at an interval mutually agreed by both the parties. The relationship gradually moves to an on-going relation from a transactional one. Vendor managed inventory (VMI) is a more sophisticated collaboration than QRP or CR and still evolving. Here, supplier takes over inventory functions that the customer would deal with in a traditional arrangement (e.g. decisions regarding storage and display of products, access to bins or storage facilities, replenishment, record keeping and managing delivery ). Collaborative planning, forecasting and replenishment (CPFR) is a set of best practices formalized in 1998 by the Voluntary Inter industry Commerce Standards Association (VICS).It provides a model for trading partners to jointly plan key activities from production to delivery of products and encompasses joint strategic planning, demand & supply management, execution and analysis based on sharing relevant information. To be successful, VMI and CPFR both needs intensive sharing of not mere data, but the predictive power of the data that resides in the systems of collaborators.

Key Performance Indicators in Retail Industry

The Supply Chain Council in their Supply Chain Operations Reference (SCOR®) framework has developed a system of metrics applicable to retailer-supplier context. The highest level is level 1 which is mentioned here along with respective performance attributes. Level 1 metrics can be drilled down to subsequent levels to get more specific answers. KPI’s can be combined into sets according to the need of different functions.

SCOR® Defined KPI’s		Other Popular KPI’s
Performance  Attributes	Level 1 Metric	1.       Sales compared to budget/target 2.       Sales compared to last year (or any other period) 3.       Sales per Square Foot 4.       Wage Cost Recovery 5.       Average Sale per Customer/Transaction 6.       Units per Customer/Transaction 7.       Conversion rate 8.       Sales per Hour (for store or associate) – selling hours only 9.       Sales per Hour (for store or associate) – total labor hours 10.   Time Spent in the Store
Reliability	Perfect order fulfillment
Responsiveness	Order fulfillment cycle time
Flexibility	Capacity  change (Upside and downside )
Costs	Cost of goods sold and  Supply chain management cost
Asset Management	Cash to cash cycle and ROA

BI System Framework in Retail

The BI System Framework involves establishment and integration of hardware and software components. Hardware’s facilitates data possession, storage, access and data management. Source system comprises of RFID and barcode reader, handheld scanner, sales force and CRM, visual audits, SAP ERP, WD System, PLM, human resource management system etc. which capture and store data from daily transactions. ETL layer is responsible for data extraction, cleansing, staging, transformation & loading. Data warehouse stores data to serve  different needs in terms of business plan, forecast, market research, outlet sales, Invoices, purchase orders, inventory, transport orders, stock orders, salary advice, re-distribution costs  , account receivable, account  payable, visual merch audit, production plans and jobs   etc. The BI layer does with number crunching applying business logic and generates metrics, regular and ad hock report. Output of this layer feeds performance management system of corporate office.

Data Modeling Steps: Retail Scenario

The substance of the data warehouse can be organized by applying dimensional design techniques. It arranges the data in a way that it can satisfy specific business query. Steps involved in dimensional design are:

■     Select the Business Process

♦      A major operational process that is supported by some kind of legacy system(s) from which data can be collected for the purpose of the storage and modeling

♦      Example: sales , orders, invoices, shipments, inventory

■     Decide Granularity

♦      The fundamental level of data represented in a fact table for the selected process. Every data mart / warehouse should be based on the most atomic)data that can possibly be collected and stored

♦      Example: individual transactions, individual daily snapshots

■     Choose the Dimensions

♦      Choose the dimensions that will apply to each fact table record

■     Identify the Facts

♦      Choose the measured facts that will populate each fact table record

■     Conforming the dimensions

♦      Common dimensions across the Facts/ data marts required  to be exactly same or subset of the main dimension table

■     Adding Attributes to Dimension Tables

♦      The depth of analysis can be augmented by increased number of attributes

Examples are customer age, address, occupation, product color, flavor, size, packaging type etc

■     Storing Pre-calculations in the Fact table

♦      Calculated based on one or more base measures

■     Choosing the Duration of the Database

♦      Need for analyzing the data over a period of time

■     Track Slowly Changing Dimensions

♦      Dimension attributes evolve over time. There is a need to identify “Changes” strategy for them.

♦      Type  1 : Overwrite the changed attributes

♦      Type  2 : Add a New Dimension Record\

♦      Type  3 : Add a “Prior” Attribute 

Various examples of sector wise data modeling is available on internet. Performing above mentioned steps along with proper selection of dimension and fact table would the basis for establishing BI framework.