Reinventing Business: Enterprise Data Warehouse Business Opportunities for Manufacturing, Part 5

Originally published July 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 “typical” manufacturer. These should be modified based on current information system capabilities, current costs, and opportunities appropriate to the specific business and industry.
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.

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’s money, using an appropriate annual cost of money to reduce future cash flow values to today’s 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.

Part 2 of this series described business improvement opportunities in the enterprise, Part 3 described opportunities in marketing and sales, and Part 4 continued with a description of financial business improvement opportunities. This installment covers opportunities in the supply chain.

Supply Chain

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

1. Demand-Driven Supply Chain

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

Background:
Lacking visibility of end customer demand or point-of-sale (POS) information, manufacturers try to forecast demand and plan supply chain operations based on the erratic “lumpy” replenishment orders from channel partners (wholesalers, distributors, dealers, and retailers). Because channel partner replenishment orders are large and unpredictable, large inventories are required and stock-outs are common. Companies often invest heavily to improve forecasting and planning systems, but get minimal results because of the inherent unpredictability of the channel partner replenishment orders they receive. Their investment would be better spent getting POS information, but many haven’t tried to get it because they don’t have the experience, data capacity, or processing power to use it.

New Process:
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. The EDW can handle large POS data volumes. The EDW becomes the source of demand information feeding supply chain planning systems. Also, the EDW can provide significant benefits to distribution channel partners, providing the incentive for them to share POS information with the manufacturer.

The desire for POS detail is often driven by marketing considerations, but it also satisfies supply chain planning requirements. If detail is not required, a daily summary of sales by store (or channel partner site) by SKU is typically adequate for supply chain planning. For non-retail sales, it is best to get the demand information when the end customer order is received, rather than after shipping and invoicing, to assure timeliness and visibility of demand if the channel partner is out of stock (when demand information is particularly critical).

[DDSN (demand-driven supply network) and POS-based planning are other terms used to describe demand-driven supply chain.]

Leadership:
Channel partner relationships are built around sharing information to optimize value for both partners. Relationship managers have responsibility to obtain the POS information to optimize channel performance.

Results:
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.

2. Customer Service

Objective:
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.

Background:
In large corporations, it is typically difficult or impossible to get comprehensive, consistent, and accurate customer service metrics for all plants and distribution centers (DCs). In many cases, facility managers “report on themselves,” with results biased accordingly. Lacking accurate and consistent data, the manufacturer can’t respond to customer complaints or general customer perceptions of service problems.

New Process:
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.

All relevant order-related transaction data (including order date, order entry date, customer need-by dates, promised dates, credit holds and releases, backorder and release quantities and dates, released-for-fill date, filled date, ship date and quantity, delivered date, invoiced date, payment-received date) need to be included in the EDW. Visibility is provided by the original customer purchase order, for a customer perspective, even when the manufacturer splits orders. Service metrics include:
  • Total cycle time from order date to delivered date
  • Order department time, credit hold time, plant or warehouse time, transit time
  • Order cycle times versus stated lead times
  • Time from shipment to delivery, by shipment mode, by area
  • % shipped on-time and complete
  • % delivered by need-by date
  • % custom orders shipped by promise date
  • % backorder rate, time on backorder
  • Partial (short) shipments

These metrics combined with customer, product and geographic hierarchies enable detailed analysis of order cycles, with drill down to individual orders and delay causes. The same applies to vendors and purchase orders.

In addition to historical service metrics, customers can be given direct access to their current order status. EDW order access can be combined with integrated visibility of in-transit order status using carrier web sites, thus providing visibility of current order status at all times. (Order status information should ideally be updated continuously or “real-time” in the EDW.) Open order visibility is a valuable customer service, which is often difficult to provide because it is not practical to give customers access to operational fulfillment systems.

Leadership:
Fulfillment performance problems are resolved promptly with actionable, current, consistent service metrics. Customer service is improved and customer satisfaction improved, resulting in fewer lost customers. Customer service and call center costs are decreased by providing customers with direct access to order status information. Redundant plant or warehouse systems are eliminated.

Results:
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.
 
3. Order Enhancement

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

Background:
CSR knowledge and information is often limited to one product line or one business unit. Customers may have to deal with multiple CSRs, who are often really just order takers. Answering questions about products or having the opportunity to act proactively is limited to CSR personal experience and knowledge. Online ordering systems, if they exist, do not have integrated comprehensive product catalog information.

New Process:
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.

Leadership:
Management instills the concept that each customer contact is a sales opportunity. The EDW is used to provide the requisite comprehensive product information. Order entry systems provide the appropriate shopping or customer replenishment functionality.

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

4. Inventory Allocation

Objective:
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.

Background:
Unexpected demand can cause an imbalance of inventory among multiple distribution centers or plants. This happens for a number of reasons, including:
  • An unexpected large order from a customer or channel partner.
  • A large return due to defective merchandise, requiring prompt replacement.
  • A competitive promotion resulting in one’s own urgent, unplanned promotion.
  • Orders transferred in because an emergency renders another plant unable to produce.
  • A capacity or production problem creates shortages. Channel partners or customers order large amounts attempting to corner supplies thus exacerbating the situation.
Manufacturers are at the mercy of large, unpredictable channel replenishment orders. Automated order processing systems normally release large orders to be filled in chronological sequence, creating an imbalance between channel partners’ inventories (first large order gets it all) with a very negative effect on end customers. This situation can be unintentional or intentional, as when one distributor or wholesaler tries to corner the market.

In large global companies, plants and distribution centers are often using different operational systems, so inventory allocation processes are not feasible.

New Process:
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).

Related analyses:
  • Report impact of major stock-outs on sales (based on backorder value).
  • Identify lost orders due to lack of inventory (cancelled backorders).
  • Exception reporting of excess inventory over maximum and X week supply.
  • Exception reporting of replenishment, production, or purchase orders that will result in inventory over maximum level.
  • Identify most serious product and raw material shortages (negative available balances).
  • For items out of stock, identify replenishment or production orders overdue.
  • Report value of open, overdue orders for custom-made products.
  • Rank products and materials with highest and lowest inventory turns.
  • Measure inventory investment optimization (higher inventory level for fast moving, less expensive items) to
  • optimize service to investment ratio.
  • Exception reporting of promotional items needing replenishment expedited to meet planned promotional demand.
  • Identify products not meeting target service levels (too many out-of-stocks).
Leadership:
Supply chain management uses EDW integration of inventory, order, and supply chain planning information globally to allocate and optimize inventory and production. They also use the extensive analytic capability of the EDW to monitor supply chain performance and the impact on the enterprise.

Results:
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.

5. Procurement Optimization

Objective:
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.

Background:
Large manufacturers with multiple factories, often in multiple countries, typically do not have integrated, detailed purchasing information. Thus, buyers do not have access to the best prices globally for specific raw material or indirect supply requirements. They are left to do their best with local information or corporate information lacking adequate detail. Many indirect materials and services are purchased with informal “off-PO” processes and are not well monitored or controlled.

New Process:
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.

Integrated procurement order and invoice data includes vendor contracts, purchase orders, delivery data, quality data, invoice quantities, prices, credits, debits, rebates, allowances, returns, and payment information for real net price visibility. Invoiced transportation costs should be included and matched to POs for freight allocation. Standard freight costs associated with specific source-to-destination route combinations allow analysis of delivered cost options.

Material item and services transactional data from source systems needs to be supplemented with standard material and services identification and classification to enable price comparisons of like materials and to identify possible substitutions. External data services are available to help automate identification and classification. Use of the UNSPSC (United Nations Standard Product and Services Code managed by the GS1 Global Standards organization) is recommended.

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). It is important to see the combined purchases of all configurations, as well as each configuration, because they may or may not be substitutable. Assure use of common measuring units for analysis because different manufacturing sites may be ordering in different units.

Provide department summary and exception reporting for “off-PO” purchasing by department or area of responsibility to instill discipline and control these expenses.

Provide standard analyses such as:
  • Buyer query to find lowest cost source
  • Year-over-year unit cost comparisons for materials and services
  • Global comparisons of material and service costs
  • Calculate savings opportunity if everyone buys at lowest price
  • Value/cost comparisons using quality information
Other analyses such as:
  • Spending trend by business unit, cost center, and buyer
  • Vendors not meeting on-time delivery and quality requirements
  • Exception report of significant price change versus previous PO
  • Exception report of purchases differing from contract prices
  • Compare vendor price, service, and quality for materials purchased from multiple vendors.

Leadership:
Corporate executives establish global procurement policies and monitor results. They focus on spending, with executive goals, report cards, and incentives. Metrics and goals are established for every manager of people who buy. They assure that all buyers have, and use, query capability to find the best sources and price.

Results:
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.)

6. Supply Chain Optimization

Objective:
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.

Background:
Because strategic supply chain optimization involves complex analysis using sophisticated modeling tools and lots of data, it is typically difficult to accomplish because the required data is not available. Decisions are often made intuitively like “we need DCs close to customers to meet service requirements” when, in reality, too many DCs results in worse service because full product lines are not being stocked at small DCs with inadequate demand. When modeling experts are employed, they expend lots of time and money getting data and often must make do without complete information.
 
New Process:
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.

Leadership:
A corporate supply chain executive is responsible for enterprise supply chain optimization. Modeling tools and experts are used to identify opportunities to improve factory and DC locations, sourcing changes, product lines and distribution areas served by each DC, and channel partner changes to improve end-to-end supply chain efficiency. Transportation costs are minimized and customer service is optimized.

Results:
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.

Part 6 of this series will explore EDW-enabled business improvement opportunities in information technology.

  • Allen MesserliAllen 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.

Recent articles by Allen Messerli



 

Comments

Want to post a comment? Login or become a member today!

Be the first to comment!