Making the business case for an enterprise data warehouse

The leader of 3M’s manufacturing enterprise data warehouse (EDW) project shows how to do a financial analysis of the supply chain management and manufacturing BI benefits.

An enterprise data warehouse (EDW) is the best solution for business intelligence. The EDW provides comprehensive...

and timely information meeting the requirements of all levels of executives, management, and all knowledge workers throughout the organization who use information to make decisions. The EDW has been proven to enable a new and better way of managing a manufacturing enterprise.

Two common methods for evaluating investments are ROI (return on investment) and NPV (net present value). Some companies use both. In either case, business improvement opportunities (returns) should include both cost reductions and incremental profit margin on increased revenue expectations. Because an EDW is strategic, use of a five-year scope for the investment calculations is recommended. A time-phased financial analysis, recognizing the implementation timing and benefits of each phase, should be used.

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.

The six business improvement opportunities described in this section primarily impact supply chain management.

Demand-Driven Supply Chain

Timely visibility of end customer demand drives manufacturer supply chain planning, enabling optimal customer service while minimizing inventory and manufacturing costs.


For companies that distribute through channel partners, timely visibility of end customer demand represents one of the best opportunities to improve supply chain planning and customer service, lower inventories, and improve manufacturing efficiency. Timely typically means daily, although even more frequent information is needed in some cases. The term POS (point of sale) is used here to mean end customer demand – either retail store POS data or end customer order data from any other channel partner. It may also include usage data for direct manufacturing customers, enabling automatic replenishment or VMI (vendor managed inventory).

EDI and XML standards and software are readily available for receiving POS data from channel partners and feeding it into the EDW.

Demand-driven supply chain implementation will conservatively reduce inventory by 10%. If inventory represents 10% of annual revenue and inventory carrying cost is 20% carrying cost, then profit is increased by .2% of revenue. Additional benefits may include better manufacturing efficiency, better service, and fewer lost customers.

Customer Service

Comprehensive, consistent, and accurate customer service metrics are derived from transactional data in the EDW. With immediate visibility of problems and access to actionable detail, customer service is improved.

By standardizing order, fulfillment, delivery, invoice, and payment data elements in the EDW, then calculating service metrics from transactional detail using standard algorithms, consistency is achieved and detailed analyses are feasible. The EDW then provides standardized service metric reporting and analysis for all plants, distribution centers, business units, and countries. Analysis capabilities include drill-down by service problem, shipping location, customer, product, carrier, etc.

In addition to savings from productivity and elimination of multiple data marts, improved customer service, customer satisfaction, and retention can add .5% to revenues at a 25% incremental profit margin, or .125% of revenue. Proving service performance to customers and holding carriers responsible for delivery failures can reduce customer deductions and complaint resolution costs by an additional .025%. Thus, total savings and revenue improvements are likely to increase profitability by .15% of revenue.
Order Enhancement

Sales are increased with comprehensive "one face" product information available to customer service representatives (CSRs), customers, and channel partners.

CSRs have access to comprehensive product information from the EDW. Customer sales history or authorized product lists are used to prompt ordering of additional products. Given incentives to sell, CSRs are converted from order takers to inside sales reps, increasing their value and morale.

For customers or channel partners ordering online, “one face” comprehensive multimedia product information is accessible. “Shopping” or “replenishment” lists specific to their channel, or based on their prior purchases, are used to increase sales and improve the customer experience.

Increasing sales by 5% on 8% of orders at 25% incremental profit margin increases profits by .1% of revenue.

Inventory Allocation

Optimal allocation of inventories improves customer service, reduces inventory investment, and improves profitability. The allocation process balances inventory among distribution centers, factories, and channel partners to achieve optimal service while minimizing inventory investment.

With integrated visibility via an EDW, unbalanced inventory versus demand situations can be monitored and corrected. Allocation algorithms can equalize service and inventory levels, create appropriate move orders or replenishment orders, and interface to transactional systems.

Enterprise-wide visibility of inventories, forecasts (demand plans), customer orders, internal replenishment orders, and supply plans in the EDW enables complex analysis of availability in all locations and offers the ability to allocate inventory to best meet requirements. Service or inventory levels can be equalized across all locations. The impact on your ability to fill current orders and forecasts in all locations, while responding to an exceptional situation, can be evaluated. Such optimal use of enterprise inventory has a substantial positive effect on sales and customer satisfaction.

With visibility of inventories, customer orders, replenishment orders, demand plans and supply plans for all distribution centers and their associated distribution regions, an allocation algorithm can actively equalize service in each region on a daily basis. Such an algorithm can be used to move existing inventories in response to changing demand or to proactively allocate prior to generating replenishment orders for the DC. Before generating a replenishment order for one DC, all DC available inventories and forecasts are analyzed and available inventory is allocated to equalize service throughout the distribution system.

With visibility of end customer orders from channel partners, it is possible to manage channel partner replenishment using the same principle – adjusting your replenishment shipments to their end customer demand. The best practice is to replenish channel partners automatically based on their customer demand (POS transactions).

If finished goods inventory is 10% of revenue, and its carrying cost is 20%, a conservative 10% reduction in inventory will result in a profit increase of .2% of revenue. Significantly greater inventory reductions have been achieved. Additional benefit will come from reducing stock-outs and improving customer service and satisfaction.

Procurement Optimization

Procurement activities are leveraged across business units and locations globally, assuring best terms, pricing, supply and delivery. All buyers have direct access to contract prices, best actual prices, and supplier information to facilitate day-to-day buying decisions. Internal and external information is leveraged with proactive buying campaigns, evaluating additional vendors, finding less expensive sources, and enticing new suppliers where competition is lacking. Unauthorized buying (“off-PO”) is monitored, managed, and minimized.

Major procurement savings opportunities come from integration of global vendor, contract, and purchase order data from all business units and sites into an EDW. Vendor names, addresses, and identification from source systems may need to be standardized and classified because vendors may operate under different names in different countries. Finding new suppliers, particularly in emerging source areas, is simplified with global business site and industry classification data from external information sources such as Dun & Bradstreet, Hoover’s, Thomson, One Source, etc.

Analysis of purchase prices for common materials and services identifies opportunities to lower prices by negotiation and to leverage larger volumes. In some cases, identical products are purchased in different packaging or shipping configurations (such as bulk versus drums for liquids).

Conservatively, if procurement accounts for 25% of revenue and 10% savings are achieved on 20% of those purchases, then profit is increased by .5% of revenue. (Actual best-practice procurement savings for a global manufacturer with an EDW exceeds 1% of revenue annually.)

Supply Chain Optimization

Integration of all supply chain activities and costs in the EDW enables analysis and simulation to optimize supply chain efficiency. Plants and distribution centers are optimally located. Products and geographic service areas are assigned to plants and distribution centers (DCs) to minimize global supply chain costs and meet service requirements. Day-to-day supply chain decisions are made with visibility of total cost and service impacts.

With an EDW, supply chain modeling and optimization becomes practical. With analysis of internal and external operations throughout the entire supply chain, substantial supply chain efficiencies are achieved. Vertical integration of operations and planning among trading partners becomes more feasible. CPFR (collaborative planning, forecasting and replenishment) and VMI (vendor managed inventory) can be implemented to improve supply chain management. DCs are consolidated when appropriate and located optimally, with service areas optimized. Products are manufactured in optimal factory locations for global supply chain efficiency.

Transportation costs can often be reduced by analyzing inbound and outbound shipments, and establishing backhauls to obtain substantial cost reductions.

Conservatively, if total supply chain transportation costs represent 2% of revenue and are reduced by 5%, then profitability is increased by .1% of revenue. In many cases, the opportunity is much larger.

Allen Messerli is president of Messerli Enterprise Systems LLC and has consulted on enterprise data warehouses for more than 400 companies. He conceived and directed implementation of the global enterprise data warehouse at 3M, the diversified manufacturer based in St. Paul, Minn. This article was excerpted from the 10-part BeyeNETWORK series, “Reinventing Business: Enterprise Data Warehouse Business Opportunities for Manufacturing.”


Dig Deeper on ERP infrastructure