So far, this series has defined the EDW and summarized its data contents (Part 1
) and described and quantified thirty major benefit opportunities (Part 2
, Part 3
, Part 4
, Part 5
, Part 6)
. This installment contains examples of business analysis applications related to selected business opportunities. Demonstration or prototype query screens are included. Just these ten business analytic capabilities would provide many manufacturers with substantial business benefits. This installment concludes with a summarization of the value of the business improvement opportunities as a percent of annual revenue for a typical manufacturer after the described functionality is implemented.
Business Analyses Examples
This section contains BI
analysis application examples for ten business improvement opportunities. Demonstration or prototype query screens are included. These can be used as examples for presenting functionality of an EDW or supporting discussion about opportunities in companies considering an EDW.1. Product Commercialization
Evaluation of orders and sales versus forecast nationally for first 15 days of a new product introduction. Results of both orders and sales are under forecast.
Drilling to regions below forecast for orders highlights that East is lagging in getting orders.
Drilling to regions lagging in sales shows that West has orders to exceed forecast but is below forecast for sales. This is found to be a West Coast plant supply problem.
Drilling on order information for territories in the East shows that the Mid-Atlantic is lagging most in getting orders.
Drilling to detail by customer for the Mid-Atlantic shows minimal activity for Wal*Mart and none for K-Mart or Target.
2. Customer Profitability
Ranking global customers in ascending order by margin highlights customers representing losses or low margins. Sharp is a large customer with negative margins.
Drilling on the Sharp business unit hierarchy indicates that the Business Products Group accounts for most of the problem.
Drilling to country and location for the Sharp Business Products Group shows the Korea Cash Register/POS plant as the single biggest problem area.
Drilling to transaction detail for the Korean Cash Register/POS plant shows a major rebate that requires investigation. 3. Customer Penetration
Here we compare Ford, the premier customer, with Fiat and Honda by product line based on percent of the customer's total corporate revenue. One area of opportunity for penetration is in the Exterior Trim line for Fiat, which now has about one third the sales penetration based on customer revenue versus Ford (.010 vs. .029).
Drilling on the Exterior Trim product line for Ford and Fiat shows that the Painting Materials, Decals, and Finishing Materials product families are all opportunities to target Fiat. 4. Global Pricing Rationalization
A list of SKUs with more than 20% pricing variance between country average and the global average in dollar equivalents shows several opportunities to investigate.
Drilling on Spain’s detail for the Dressing product shows a very low price to Euro Distribution. They may be selling to other countries, undercutting manufacturer pricing. This would enable your Spanish subsidiary to grow revenues at the expense of other countries – and margins.
Drilling to order detail shows very large orders from Euro Distribution – clear evidence of export activity. You have an obvious issue to discuss with your Managing Director in Spain. 5. Sales Profitability
Ranking sales territories versus percent of forecast, displaying margins, indicates that Mid-Atlantic is over forecast at the expense of meeting margin goals.
Drilling on Mid-Atlantic shows that Kroger is far over forecast and well below required margin.
Drilling to Kroger transaction detail shows both a recent invoice below required margin and a large rebate to make matters worse. 6. Business Unit Management
Comparison of major business unit P&Ls shows high factory cost for Business Unit B.
History of factory cost detail for Business Unit B shows a sharp increase in raw material cost this year. You could drill to specific material costs for further investigation. 7. Accounts Receivable
Analysis of customers with over 40 days outstanding highlights major customers with large amounts overdue.
Drilling to location for Bayer highlights Germany and the US as primary problem areas.
Drilling to transaction detail for Munich identifies specific invoices on which to take action. 8. Customer Service Metrics
Analysis of the major service metric (% delivered on time and complete) shows a problem at DC West.
Drilling to major areas served by DC West shows a problem in California.
Drilling to territories shows a problem in the Bay Area.
Drilling to shipment detail shows a specific carrier delivery problem for action. 9. Inventory Allocation
You have a new requirement of 8000 units in the next four weeks in the Central DC region (perhaps to meet a competitive promotion, or to replace a large return due to defective merchandise). This analysis projects the impact. Without cutting into safety stock, 1600 units (20% of new requirement) are available from the other DCs. Using up all safety stock at all three sites would enable you to meet 95% of the new requirement, but without any protection for other demands over forecast. Additional production of 3400 units would reduce the stock-out risk to 80%. Additional production of 6400 units is needed to keep you at normal levels of service.10. Procurement Optimization
This listing of your largest raw material purchases, in descending order of value, includes calculations of global average price per unit and countries with the highest and lowest prices per unit. The last column is a calculation of the total savings potential if all purchases could be made at the lowest price per unit. Additional freight cost is estimated using standard route freight rates. This data provides the basis for determining capacities of lower cost vendors and negotiating better supply contracts with new or existing vendors.
Business Improvement Opportunities Summary
This summary of the annual value and five-year net present value of business opportunities described in Part 2 of this series
is expressed as a percent of annual enterprise revenues. Not all the opportunities are necessarily applicable to any one company. Characteristics of the industry, current company costs, and current information system capabilities should be considered when estimating opportunity potential. A matching Excel spreadsheet is available upon request from the author to simplify calculations for a specific manufacturer.
Part 8 of this series will cover best practices.
SOURCE: Reinventing Business: Enterprise Data Warehouse Business Opportunities for Manufacturing, Part 7
Recent articles by Allen Messerli