Reinventing Business: Enterprise Data Warehouse Business Opportunities for Manufacturing, Part 2
Originally published April 15, 2010
Business Improvement Opportunities
While Part 1 of this series provided the definition and scope of the enterprise data warehouse (EDW), Parts 2 � 6 address how the EDW is used to reinvent business. Thirty major business improvement opportunities are described. Collectively, they comprise revolutionary change for most manufacturers. Each opportunity is described as follows:- Objective describes the goal state.
- Background describes a typical current state without an EDW.
- New Process describes recommended processes and EDW functionality.
- Leadership summarizes management focus required to achieve the results.
- Results are business improvement opportunity financial benefit potentials as a percent of revenue for a 뱓ypical� manufacturer. These should be modified based on current information system capabilities, current costs, and opportunities appropriate to the specific business and industry.
Because the cost of implementing an EDW depends on company specifics, notably the number of source systems, this series does not include ROI estimates. But, best practice EDWs have been proven to provide exceptional ROIs.
EDW implementation cost estimates should be based on net added cost beyond spending for the current business intelligence (BI) environment. BI environments with many data marts and operational data stores are expensive. During EDW implementation, spending on data marts and operational data stores should be minimized so the net added cost is less than the total EDW cost. An EDW does not require re-engineering of operational processes, so it is substantially easier, faster, and less expensive than enterprise resource planning (ERP) projects.
Net present value calculations state future costs and benefits in terms of today뭩 money, using an appropriate annual cost of money to reduce future cash flow values to today뭩 value. This enables direct comparison of ongoing benefits with one-time investments, such as an EDW implementation. To simplify the concept, NPV for each opportunity is included in Part 8. The stated five-year NPV is four times the annual benefit, using a simple and conservative valuation. This represents roughly an 8% annual cost of money for the next five years. Using a lower value of money or longer time horizon will increase NPV.
This installment will continue with a description of 7 business improvement opportunities in the enterprise.
Enterprise
The seven business improvement opportunities described in this article impact all functional areas of the enterprise.1. Productivity Improvement
Objective:
Productivity for knowledge workers is improved with easier, faster access to business intelligence and analyses. Marketing, sales, customer service, and technical service organizations become more efficient with access to comprehensive market-focused multimedia product information. The EDW is used to monitor and measure productivity for both operational and knowledge workers.
Background:
Without an EDW, most knowledge workers waste a great deal of time finding and retrieving information they need � if they can get it at all. Comprehensive, yet flexible, productivity measurement capabilities are rare. Knowledge workers are defined as everyone whose performance depends on information, including executives and management in all functional areas and most personnel in sales, marketing, customer service, and finance positions.
New Process:
EDW business intelligence applications are designed to provide easy flexible access to information, with the goal of answering 밶ny question, any time,� down to and including actionable detail. Applications enable users to analyze many common metrics (sales, margins, service, demand, inventory, productivity, purchases, etc.) from the perspective of their own responsibilities. With the ability to enter personalized product, market, customer, and geographic hierarchies or 뱕iews� online into the EDW, managers can define their own responsibilities. Adding the ability to maintain their own responsibility goals or KPIs (key performance indicators) in the EDW makes it a comprehensive performance management tool.
밨ole-based, event-driven� exception reporting can be used to proactively inform managers when they need to take action because performance is not on target or there is a specific operational problem. The result is additional productivity improvement for everyone who uses information because they don뭪 need to analyze reports to determine when action is required.
Market-focused product information includes descriptions, characteristics, packaging hierarchies and dimensions, standard marketplace identification, images, documents, multimedia training, ordering quantities, pricing, and much more. Providing easy access and sharing of product information throughout the enterprise leads to dramatic improvements in marketing communications, customer service, sales, and channel partner productivity � particularly in global companies.
Management should have timely visibility of actual productivity versus goals (such as: customer service orders entered per hour, factory output per shift, warehouse shipments per day, call center calls per hour, sales contacts per week, etc.). With supporting drill-down capability, the performance issues can be analyzed and resolved.
Measuring productivity requires organizational and staffing information for all functional areas, combined with appropriate metrics derived from transactional and other activity detail relevant to each area.
Leadership:
Executives take responsibility for productivity, setting improvement goals, and realizing results. With actionable information at everyone뭩 fingertips, it is often feasible to eliminate whole levels of management. At a minimum, substantially larger executive and management spans of control should be expected and implemented. And, non-management knowledge workers should substantially improve productivity.
Results:
Conservatively assuming a productivity improvement of 2.5% (equivalent to one hour per week per person) for management, marketing, sales, customer service, and technical service personnel costing 10% of revenue, savings are .25% of revenue.
2. Quality and Warranty
Objective:
Quality is improved and the total cost of quality, including field repair, replacement, warranty claims, recalls, government compliance, lost customers, and liability lawsuits, is reduced. Management at all levels in all functional areas has integrated and comprehensive information about the total cost of quality, customer perceptions about quality, the analytic capability to trace quality problems to sources, early detection, and recall capability.
Background:
Comprehensive information and analyses concerning quality are a significant challenge for many manufacturers because of diverse information sources, lack of systems capabilities and capacity to integrate the data, and the prevalence of unstructured data. Many operational systems are not readily adapted to collect the traceability information needed for analysis. Few materials or products have been labeled with the requisite bar codes or radio frequency tags (RFID) to automate capturing of lots or serial numbers. Traceability can be expensive and is not common in many manufacturing industries (including food and many other low cost, high volume consumer products). Customer complaints, warranty work, and field or dealer technical service activities are typically not integrated into analytic systems because the key information is represented in unstructured text format.
New Process:
Traceability information is often required to solve quality problems. Traceability involves tracking lot or serial number of raw materials and components through manufacturing processes, and tracking product distribution by lot or serial number to end users. Tracking enables early detection of manufacturing processes, products and customers who may be impacted by a component or process problem. Early detection allows impacts to be minimized by stopping manufacturing processes or distribution before the product gets to the customer.
밫rack and trace� systems and processes are becoming more common because of the high cost of quality problems. Bar codes and RFIDs are becoming more prevalent to enable track and trace, and their cost is coming down. The very large data volumes generated by track and trace systems typically require EDW technology to process the volume and integrate the diverse data sources. Quality testing processes at each step of the supply chain, from material receipts through all manufacturing processes, enable prevention. With quality information captured, along with track and trace, integrated analytics can help link vendor or process quality variables to product performance. These 밹radle to grave� complex analytics often require the power and data integration of an EDW platform.
Unfortunately, the best quality systems and processes are not perfect. Recalls, field technical service fixes, warranty claims, and lawsuits may occur. All of these can be minimized with EDW-based track and trace capability, enabling early identification and recall of impacted product from wherever it is in the supply chain or customer hands.
It is important to capture customer complaint, service, and warranty information promptly, and integrate it in the EDW. These information sources may be the first indicators of a problem. Today뭩 EDW technology supports integration and 밿ntelligent analysis� of text information, helping to automate early detection and analysis processes.
Related analyses are almost endless, but some common examples include:
- To whom did we ship potentially defective product?
- Is there a linkage of returns, complaints, or service issues with a specific product or component lot or serial number?
- Is there a linkage of product problems to specific components, vendors, lot or serial number? Can we hold vendors responsible?
- What is our total cost of quality? By product?
A Six Sigma or similar rigorous approach to improving quality and minimizing the total cost of quality is instilled in the organization, with the EDW supporting requisite metrics and analytics.
Results:
If cost of quality is 2% of revenue and it is reduced 10%, profitability improves by .2% of revenues. If improved market perception of quality increases sales by .4% with an incremental profit margin of 25%, profitability improves by a further .1%. The total profit improvement is .3% of annual revenue.
3. Product Commercialization
Objective:
Time-to-market is substantially reduced by integrating internal and market-focused product information, and providing access to all functional areas, subsidiaries, channel partners (wholesalers, distributors, dealers, and retailers), and end customers or prospects. Also, management has timely information about orders and sales versus plan for new products, and responds promptly with fact-based decisions.
Background:
Product commercialization processes in manufacturing are generally not well integrated. Global rollouts are typically slowed due to the complexity of sharing and distributing product information. Much of the marketing content required, including outsourced advertising and graphic arts content, is reinvented country-by-country � a slow and expensive process. And, monitoring of new product sales results versus plan is often an inconsistent process.
New Process:
Local and global product development and commercialization processes feed all product information into the EDW, where it is integrated at least daily. Integrated, consistent, and current multimedia product information is made available for internal sales and marketing, channel partners, and customers. Product information is translated and/or localized for each region, subsidiary, or country. The EDW is the repository for sharing global and local product information.
Channel partner product information maintenance processes are automated via EDI or XML standards for sharing new product information � via direct transmission or industry gateways such as UCCnet. Similarly, the EDW pushes product data to internal fulfillment systems.
Time-to-market for a new product is monitored and further improved with timely visibility of roll-out results. Including visibility of the impact on products subject to cannibalization provides a full picture of revenue and margin impacts. Daily or weekly exception reporting of orders versus forecast by region, area, or territory enables prompt action by sales management to assure effective introduction processes are in place.
Order (demand) visibility is critical because there may be out-of-stock (backorder) situations or production backlogs. Order, invoice and backlog data are available daily in both units and value at the order item-line level for drill-down analysis.
Leadership:
Accessibility and distribution of multimedia product information is considered part of business intelligence (like analytics), and is a function of the EDW. Executives assure that information is not a barrier to time-to-market optimization.
Management reacts appropriately when sales are not on target. Failure to generate orders is a marketing or sales issue, whereas failure to fill orders and generate sales may be a supply chain issue. Cross-functional product introduction planning improves due to better understanding of results.
Results:
Faster product commercialization processes translate to significant increases in market share. One major company reduced average global time-to-market by several months. Reducing average time-to-market for new products by one month, with 10% of revenue from products new within the past 12 months, yields a revenue gain of .8% (10% of annual revenue times 1/12 of a year). With an incremental profit margin of 25%, profit is increased by .2% of revenue. This is a conservative calculation because more rapid introduction will also achieve greater market share.
4. Integrated Planning
Objective:
Planning activities across the enterprise are coordinated and integrated with top-to-bottom visibility of sales, financial, operational, and supply chain impacts of the common plan. As a result, all functional areas are 뱋n the same page� and measured against common objectives.
Background:
Integrated planning (also called sales and operational planning or S&OP) is a common goal of manufacturers. But, typically, each functional area creates forecasts or plans based on their own data sources, from their own perspective. Financial forecasts, sales forecasts, marketing plans, demand forecasts, supply plans, and factory plans are often inconsistent. Product, market, and customer segmentation may differ, currency value and unit relationships (pricing) assumptions may differ, time frames differ (months versus weeks), level of detail differs (product family, product, SKU, etc.). Sales territories may not align with distribution center or plant distribution regions.
A great deal of time is typically spent in cross-functional meetings attempting to resolve these differences but, lacking a common data source with the detail data and hierarchies necessary to summarize in different ways, accurate reconciliation is impossible.
New Process:
The EDW enables integration of sales forecasts, marketing plans, financial forecasts and budgets, demand forecasts, supply plans, operational and factory plans. And, the EDW provides a single source of historical information on which to base those forecasts and plans so that everyone starts with the same history. Forecasting, planning, and budgeting systems access the EDW for historical information, then return their output � forecasts, plans, and budgets � back to the EDW.
Flexible hierarchies enable different views and levels of summary appropriate to each planning process. Even if the plans are created at different levels of summary, history detail enables allocation and reconciliation processes. After reconciling planning differences and agreeing on an enterprise or business unit plan, the integrated plan is stored in the EDW and can be viewed from the perspectives of each functional area. A single integrated report or chart can display historical demand, sales, production, inventory, and service levels, along with planned demand, sales, production, inventory, and service for the next 12-18 months (literally 뱋n the same page�). This plan is credible, because drill-down capabilities allow it to be viewed in product, channel, or geographic detail as required. Calendar-based algorithms do the week-month transformations to align time periods correctly.
Leadership:
Executives require agreement across functional areas on the integrated business plan. Of course, the plan may include high and low assumptions, but everyone is synchronized on the plan.
Results:
There are many improvements with this approach, including productivity, marketing effectiveness, business unit management, and manufacturing efficiency. Best-practice experience has shown that one of the significant measurable impacts is on inventory. Conservatively, if inventory represents 10% of revenue and is reduced 10%, with an inventory carrying cost of 20%, profits are increased by .2% of revenue.
5. Activity Based Costing
Objective:
Activity based costing (ABC) provides accurate allocation of all significant costs, enabling enterprise performance management based on accurate unit costs. Six Sigma and lean manufacturing processes are based on current accurate cost information. Distribution, pricing, sales strategies, product line rationalization, and customer profitability analyses are all based on accurately allocated costs.
Background:
Most manufacturers desire ABC, but do not have the systems or data management capacities to implement a comprehensive ABC system.
Because manufacturing costs often account for a major portion of total enterprise cost, most manufacturing companies do a reasonably good job of managing total manufacturing cost. But, they typically allocate manufacturing inputs (raw materials, labor, factory overhead and equipment) to general products or categories, based on total production inputs and outputs. This cost of goods sold (COGS) is a basic financial metric for managing manufacturing.
However, COGS is often averaged over quarterly or annual time periods (called standard COGS) rather than being calculated for specific production output (orders, batches, or individual items). Also, standard COGS is typically averaged across all the SKUs within a product category, so SKU-level costing is not accurate. [SKU, or stock-keeping unit, means a unique product item or configuration. For commodities, an SKU may represent a specific grade or other classification. An SKU may also represent a specific service.]
Due to system data capacity and processing limitations, distribution, sales, and customer support costs are typically allocated based on revenues, resulting in invalid cost data and bad decisions.
New Process:
An EDW provides the data capacity and processing capability to do ABC at least monthly. With ABC functionality in manufacturing through all production stages (including packaging and shipping), accurate and current SKU-level COGS can be achieved. Calculating SKU-level COGS requires comprehensive manufacturing detail, including all material, labor, and capital equipment inputs and their costs, allocated to specific SKU output. An EDW offers the potential to get ABC accuracy down to the specific production order, lot, or serial number level. (Serial number represents a specific single unit. This level of detail is important for major capital goods like planes, trains, automobiles, and other large equipment with varying configurations.)
Beyond the factory, ABC includes allocation of costs for order processing and billing, distribution center space, order filling and shipping, transportation, direct delivery, call centers, marketing, sales, technical support, and any other major cost activity. Freight invoices should be allocated to each purchase order, intra-company move order, and shipment to customers. Distribution center labor and equipment costs should be allocated to specific shipments of specific products to specific customers. Storage should be allocated to SKUs based on actual product cube and inventory levels. Allocating activity costs to order line items, where possible, enables roll-up of revenues, costs, and profitability by order item line, order, product, customer location or store, total customer, brand, product segment, market or channel, etc.
One simple example will help you understand the opportunity: Suppose that customers A and B each buy $100,000 per month of the same product. Customer A places daily orders and requires daily shipments, while Customer B orders weekly and requires weekly shipments. With pricing based on total contract purchase quantities, both get the same price. With costs allocated based on revenues, we will conclude that the two customers are equally profitable, whereas reality may be that we are losing money on Customer A and Customer B is profitable.
Examples of some major ABC allocation opportunities with an EDW include:
- All production and packaging costs to SKUs based on production reports
- Warehouse storage to SKUs, based on storage space used
- Warehouse labor cost to order line items, using actual transaction detail available from automated warehouse systems or applying labor factors for shipping units and line items
- Transportation invoices paid by manufacturer matched to shipment line item detail
- DSD (direct store or customer delivery) route and delivery cost to SKUs and store
- Call center time to customer and SKUs based on customer calls
- Technical service activities to customer and SKUs
- Sales calls to customer and SKUs
- Deductions, rebates, trade funds, and allowances to customer and SKUs
Which SKUs are not yielding margin goals?
Are product quantity price breaks properly aligned with costs for various physical packaging hierarchies (pallets, cases, cartons, consumer units)?
- Which brands or categories are not yielding margin goals?
- Which customers and customer locations are not yielding margin goals?
- Which orders are not yielding margin goals?
- Which sales territories are not yielding margin goals?
- Which contracts or promotions are not yielding margin goals?
- What pricing policy changes are required to meet margin goals?
Leadership:
Executives banish the process of allocating costs based on revenues. Direct cost impacts of manufacturing, distribution, selling, pricing, and customer service strategies are fully understood and provide the foundation for substantially improved processes and decisions. Operational management people in all functional areas are measured based on specific unit cost goals and performance. ABC provides the foundation for better product rationalization and customer profitability analyses.
Results:
A profit margin improvement of .25% of revenue from improved operating cost management is achievable, in addition to improving product line rationalization and customer profitability.
6. Product Line Rationalization
Objective:
Product lines are continuously rationalized based on SKU profitability, while considering customer impacts. Unprofitable items, or those not meeting profit goals, are dropped, re-priced or re-launched to assure optimal total product line or category margins. Impact on the current product line is considered before introducing new items.
Background:
Due to system constraints in many companies, profitability is only available summarized by product line by country, not at the SKU detail level. Hence companies may try to rationalize the product line by ranking SKU order frequency, demand, or sales. But, low volume or slow-moving SKUs may be very profitable, so bad decisions are common.
New Process:
Profit line rationalization is based on visibility of margin or profitability for each specific product SKU and customer. Margin or profitability is dynamically calculated based on revenues minus costs in the EDW. Revenue should be net revenue: invoice amount minus credits, deductions, rebates, allowances, etc. Rebates and allowances often apply to groups of SKUs, but should be allocated appropriately to SKUs. Cost can be standard manufacturing cost (cost of goods sold), but will preferably be delivered unit cost based on ABC of logistics processes including transportation costs allocated to shipment and orders. Ideally, marketing, sales, and customer service costs are also allocated specifically to SKUs based on ABC techniques.
Related business questions to consider include:
- What percent of gross margin is generated from SKUs introduced in the past 3 years?
- What is the margin trend for the category? Most positive and negative trends by SKU?
- What is the ranking of SKUs in a product category by margin? By customer site?
- What is the cannibalization impact of a new product (effect on other product margins)?
- For a product category, which SKUs should be dropped based on low margin, low preference by top customers, redundancy, and low affinity to higher margin items? (Affinity requires POS data by customer transaction.)
Leadership:
Product line rationalization becomes an established cross-functional enterprise or business unit process with clearly defined goals and responsibilities. Product line decisions are based on total profitability and impact on manufacturing and the supply chain, as well as customer and market considerations. Product value to the customer is understood and decisions include both product offerings and pricing strategy.
Results:
Assume a typical company not doing effective product rationalization at the SKU level. If product line rationalization reduces losses or improves profits by 5% on 2% of the product line, then savings are .1% of revenue.
7.젨� Acquisitions and Mergers
Objective:
Acquisitions or mergers are concluded with minimal effort, without disrupting operations. An integrated view of customers, financials, supply chain, and vendors for acquired or merged businesses is available within a few months to achieve potential synergy benefits quickly.
Background:
If companies being integrated or merged are using different operational (밇RP�) systems, it is a common misperception that they must be converted to a standard system to gain operational synergies. Forcing immediate compliance and conversion to the acquiring enterprise뭩 operational systems is expensive, time consuming, and has a significant negative impact on short-term results. This is a major contributing factor in acquisitions that fail or don뭪 achieve expected results.
New Process:
If the acquiring company (or a merging company) has an EDW in place, it is relatively fast and inexpensive to integrate the acquired or merged company information into the EDW. This is not a disruptive process because operational processes do not need to change. Extract and transformation processes should only affect the IT department, not business operations. Within a few months, information should be integrated in the EDW to gain a common view of customers, vendors, and supply chain information.
EDW integration typically enables many of the synergies expected from acquisitions, including cross-selling to common customers, buying from common vendors, integrating management and staff functions to eliminate redundancy, and utilizing available manufacturing capacities. For complete fulfillment integration (one integrated ordering system), it may be necessary to convert to one common operational system. But, this step is often not urgent and can take place gradually if, or when, it makes sense.
Leadership:
The EDW team should be an early and integral part of the acquisition or merger team. They should be expected to go into the company being acquired or merged to map their major source systems data to the EDW standard input formats and implement the ETL processes for data integration in the EDW within a few months. Existing common BI applications and tools can then immediately be used to identify and implement synergistic opportunities.
Results:
If acquisitions represent 5% of annual enterprise revenues, and faster synergies improve combined performance by 1% of acquisition revenue, then total enterprise profitability improves by .05% of revenue.
Part 3 of this series will continue with EDW-enabled business improvement opportunities in marketing and sales.
Allen Messerli
Allen Messerli, President of Messerli Enterprise Systems LLC, specializes in enterprise data warehouse consulting, and has provided vision, direction and leadership for 400 major enterprises globally. Previously he had more than thirty years experience in a wide variety of positions at 3M, with an extensive record of successfully managing large-scale, innovative information technology solutions across supply chain, manufacturing, sales and marketing functions. 3M is a diverse global manufacturing company, with 40 business units operating in all countries and selling 500,000 products through most market channels. Al conceived, justified, architected, and directed implementation of 3M’s Global Enterprise Data Warehouse, which contributed more than $1 billion net business benefits with a very large ROI, and is now a global best practice enterprise data warehouse. He has extensive leadership experience in industry, national, and international logistics and electronic commerce organizations, and was a pioneer in electronic business and data warehousing, often speaking on these subjects around the world.
