We use cookies and other similar technologies (Cookies) to enhance your experience and to provide you with relevant content and ads. By using our website, you are agreeing to the use of Cookies. You can change your settings at any time. Cookie Policy.

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

Originally published June 3, 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 enterprise data warehouse.
  • New Process describes recommended processes and enterprise data warehouse 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 enterprise data warehouse 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 enterprise data warehouse 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 and Part 3 described opportunities in marketing and sales. This installment will continue with a description of 4 financial business improvement opportunities.


The four business opportunities described in this section primarily impact financial functions and business unit performance metrics.

1. Finance Cost Reduction

Finance function costs are minimized and effectiveness improved with all financial information maintained in one database. The financial reporting close process is simplified and shortened. Finance personnel are financial analysts focused on improving business performance, not number crunchers using desktop databases to roll-up, consolidate, and report financial results.

Many large manufacturers with multiple financial systems in business units or subsidiaries do not have consistent financial data. Financial results are typically summarized only at a high level. Some companies invest heavily to implement standard financial ERP (enterprise resource planning) systems. There are difficulties scaling these systems to large enterprises, and business intelligence functionality consists primarily of structured reporting, with limited ability to drill down to actionable detail for decision making.

New Process:
All transactions and allocations with financial impact are integrated and standardized in the EDW at the detail level, although they may be sourced from diverse transactional systems. Using the standardized base of detail in the EDW, one centralized financial ERP system and process summarizes financial results by allocating and rolling up revenue, cost, asset, and liability information, with results returned to the EDW. The centralized financial ERP system is the “processing engine” for financial data, but the EDW stores the inputs and the outputs. (In this case, storing summarized information is usually required for computing performance reasons because allocation and summarization processes involve intensive computing by a financial ERP system. Also, summarized results will be accessed frequently, so it is more efficient to store them.)

This recommended financial architecture does not require extensive reengineering of source transaction systems, yet allows implementation of one standard centralized financial process. With detailed and summary financial information stored in the EDW, top-to-bottom visibility of financial information is provided from one central source. This approach costs less than traditional ERP reengineering and can be implemented faster. Note that most “financial” transactions (invoices, receipts, material and labor usage, production output, etc.) are not used only for financial purposes. The data from these transactions is used by all functional areas and stored only once in the EDW. Thus, benefits of this integrated enterprise approach are greater than with a separate financial database.

With this EDW-centric financial architecture, it is practical to analyze revenue, cost, and margin by customer, market, brand, geography, and SKU. Exception reporting and actionable detail for all functional areas enables business unit people to take responsibility for financial results.

The monthly and quarterly financial close process is substantially simplified. In the best-practice example, operating expenses, allocations, balance sheets, consolidations, and preliminary P&L statements are run daily the last five working days of each month. With all detail and summary data in one database, most reconciliation processes are eliminated. (Routine internal audits validate the daily or real-time ETL processes feeding data from source systems to the EDW.) Questionable results can usually be identified and resolved before closing. Top-to-bottom financial results drill-down visibility can be available daily. Only subjective decisions, such as write-offs, repatriation of profits, etc. need to be made prior to closing.

Finance is treated as an integral component of the enterprise, sharing information with other functional areas (although there are tighter constraints on access to financial information, enabled by appropriate database-level security). Charts of accounts are maintained in the EDW, like other hierarchies. All financial reporting and analyses use the EDW. Finance organization staffing is greatly reduced and remaining people become analysts and consultants, rather than clerical people generating financial reports from their desktops with a myriad of spreadsheets, databases, and inconsistent results.

A finance organization with costs equal to 1% of revenue can achieve a 10% cost reduction, improving profitability by .1% of revenue.

2. Reporting Compliance

Cost of compliance with financial reporting regulatory requirements, particularly Sarbanes-Oxley, is minimized or eliminated.

Currently, large companies registered for trading on U.S. exchanges are spending tens of millions of dollars per year to meet Sarbanes-Oxley (SOX) financial reporting requirements. Much of the spending relates to the challenges of auditing distributed and inconsistent roll-up processes. It is often difficult or impossible to trace summary financial results back to transactional detail. Because of the expense of meeting SOX requirements, U.S. firms are going private, moving their headquarters to other countries, moving their stock listings to exchanges in other countries, and pushing for relaxed regulations. Many new businesses are not going public because of the high cost of compliance. Non-U.S. firms do not want to register for trading on U.S. exchanges. Public auditors and accounting firms are being enriched at high cost to their clients, who blame the government for onerous regulations.

New Process:
The real fault lies not with governmental regulations, but with deficient management and systems. Unfortunately, executives have proven that the regulations are required. Now, systems people need to prove they can step up to the challenge and meet the requirements. The architecture described in the Finance Cost Reduction section, provides top-to-bottom financial information in one single place and enables companies to meet regulatory requirements with no incremental spending.

The EDW support organization is held responsible for assuring that transactions from all source systems are correctly extracted, transformed, and loaded into the EDW. Source systems, processes, and people are held responsible for the accuracy of all transactions. Corrections go through normal system processes to the EDW. Internal Auditing is held responsible for validating operational systems and processes. Public auditors verify reporting processes in the EDW. This job is quite easy because they can drill down to detailed transactions with top-to-bottom visibility in one place. Auditing costs go down – not up.

SOX compliance is conservatively costing most publicly held manufacturers an incremental .1% of revenues. Best practice proves that the incremental cost of SOX (external spending) can be eliminated, increasing profits by .1% of revenue.

3. Business Unit Management

Business units within the enterprise are managed based on fully allocated costs and consistent metrics such as productivity, sales volume change, operating income, economic profit, and return on capital employed. Organizational transformation is simplified and business unit flexibility is supported. Centralized information enables decentralized management where appropriate.

Manufacturing company business units often have different metrics or different systems that calculate inconsistent results, thus contributing to inefficient management processes and decisions. Alternatively, forced system standardization often diminishes availability of information required to efficiently manage different business units.

New Process:
Standardized and comparable information for all business units and subsidiaries enables reliable comparisons of results with drill-down to actionable information using a corporate “chart of accounts” (organizational structure and relationships). Daily, monthly, and quarterly results are available within 24 hours. This enables faster reaction to problem areas and timely financial reporting, thus improving stockholder credibility and market value of the company. With standardized financial data and analyses available from the EDW, business unit personnel can practically take far more responsibility for results.

Business units can define their own views (hierarchies) of products, customers, markets, manufacturing, and procurement, enabling flexibility to manage their business as needed. In many cases, the corporate chart of accounts does not provide enough detail to meet business unit requirements. The EDW can provide all levels of detail required by the business units.

Briefly, the five suggested common business unit metrics are:
  • Productivity at the business unit level is typically measured by revenue in common currency per employee.
  • Sales volume change is typically stated as a percent change of this-year-to-date versus last-year-to-date net sales in one common currency, but may also be stated in standard product units where applicable to differentiate unit growth versus pricing changes.
  • Operating income excludes taxes but requires allocation of asset depreciation to business units.
  • Economic profit is net profit calculated after allocating and subtracting a corporate cost of money for all assets employed by the business unit.
  • Return on capital employed is similar to economic profit, but stated as a percentage return on capital investment (assets) allocated to the business unit.
All of these metrics are enabled by the EDW, and executives at the enterprise, business unit, and subsidiary levels should have direct access to them. (The analyses are complex and typically need to be pre-calculated and stored on an executive BI server, rather than calculated on demand.) Where business units share manufacturing or distribution facilities, activity based costing is important to provide accurate allocation of costs, at least to the business unit level. Also, fixed assets (property, plant, and equipment), inventory, and accounts receivable data must be correctly identified and allocated to business units to calculate operating income, economic profit, and return on capital employed.

Other related business analyses include:
  • What are our most/least profitable factories or source locations?
  • What is the effect of currency fluctuation on performance metrics?
  • What are major variances from our financial plan or budget?
  • What is our overhead cost versus variable production and distribution costs?
  • What is our spending trend by cost category?
Corporate executives measure business unit performance with standard performance metrics, similar to the way stockholders evaluate companies. Thus, business unit executives are held accountable for overall results without micromanagement by their bosses, enabling decentralized business unit management where appropriate. Within the business unit, flexibility exists to establish other metrics appropriate to the individual business. Business units may be managed globally or locally. If managed globally, they have access to global information. With detailed business unit performance visibility, executives spend a great deal less of their valuable time (and expensive corporate aircraft time) traveling around the world attempting to micromanage. These EDW capabilities may enable elimination of a whole layer of management in large corporations. Subsidiaries may be consolidated by region; business units and staff functions may be consolidated; and business units may be given global responsibility.

In Part 2, we accounted for general knowledge management productivity improvement. But, additional benefits will be realized due to organizational simplification with reductions in corporate executive and staff organizations. If corporate executive and staff costs are 1% of revenues, and that cost is reduced by 5%, benefits will be .05% of revenue. Additional benefits will likely be realized due to improved management at the business unit level.

4. Asset and Liability Management

Current assets (cash, accounts receivable, inventories, etc.) and fixed assets (property, plant, equipment, etc.) are actively managed to assure optimal return on investment at corporate and business unit levels. Non-performing assets are minimized or sold. Liabilities (debt, accounts payable, accrued payroll, taxes, etc.) are monitored and managed to control risk and leverage debt effectively.

Manufacturers’ focus, particularly at the business unit level, is often on costs, prices, and margins (the numerator of ROI), rather than assets or investments (the denominator of ROI). In many cases, business units or functional areas are not assigned responsibility for assets.

New Process:
To improve ROI, reducing investment is often just as easy and effective as reducing costs or increasing prices. Of course, inventory represents a substantial asset for manufacturers and is usually visible and managed. Integrated planning, demand driven supply chain and inventory allocation collectively identify opportunities to reduce inventories. Cash management involves investment strategy and is outside the scope of this article.

Fixed assets are typically the largest asset and most important for a manufacturer to manage. Plant and equipment may be managed at the corporate level under a manufacturing or supply chain executive, or at a business unit level where a specific business unit is given responsibility to run each plant (even though plants and equipment often serve multiple business units). The EDW should provide complete visibility of asset book values (cost minus depreciation to-date) and current market value of fixed assets. Return on fixed plant and major equipment assets (investment) should be calculated based on product profitability for products produced by each facility.

Global visibility of plant and equipment assets, utilization, and cost for the enterprise will help identify opportunities to improve utilization. Equipment can be moved to other regions where it may be needed. Production can be re-allocated to plants where capacity is available. New investment is avoided when current assets can be better utilized.

Accounts receivable management is improved with the EDW providing complete visibility of accounts receivable (measured by total value and “days outstanding”) by customer account, customer business unit, and the global customer corporation. The customer’s payment strategy is often mandated at their corporate level. Suppliers can best identify problems and influence correction with global corporate customer visibility. The manufacturer needs the EDW with global internal visibility across business units and subsidiaries, particularly when business units with different billing systems share common customers.

Credit risk is inherent in accounts receivable. Manufacturers use external credit ratings (like Dun & Bradstreet) to judge acceptable levels of credit (receivables) for customers, and may integrate credit rating information into the EDW along with customer hierarchies and industry classifications. Write-offs occur when customers go bankrupt or don’t pay – and these write-offs come directly from the bottom line. Accounts receivable are usually treated as a corporate asset, and can best be managed at a corporate level when business units share common customers. There is an inherent conflict when business unit credit and sales strategies lead to excessive credit risk. These conflicts and risks are best managed with visibility in the EDW. Best practice is using the EDW for all accounts receivable analyses and actions, particularly for companies with multiple billing systems.

Targets for current asset and liability levels should be established in the EDW, with monitoring and exception reporting. When customer payments take longer than allowed by invoice terms, action messages should go to appropriate management levels, with escalation as appropriate.

Analyses related to accounts receivable and payable include:
  • What are receivables and payables trends over last two years?
  • What is the age (days outstanding) of payables/receivables?
  • What is total value of receivables by customer or business unit?
  • What customer receivables exceed levels appropriate to their credit rating?
  • Are we paying faster than required?
  • What customers frequently violate terms and what is the cost to us?
  • How much do excessively favorable terms cost us?
Responsibilities for asset and liability management are clearly defined. Executives are assigned global responsibility for receivables by customer, payables by vendor, assets, and liabilities when appropriate. Fixed asset utilization is actively managed across business units globally. All significant assets and liabilities are allocated to business units for business unit metrics.

A two day reduction in receivables with a 10% cost of money (.10 x 2/365) adds .05% of revenue to profitability. A 5% reduction of receivable write-offs, running at .4% of revenues, adds another .02%. A 1% improvement in fixed asset utilization, with a 10% cost of money, with fixed assets valued at 30% of revenues, adds .03% to profitability. Combined, these benefits increase profitability by .1% of revenue.

Part 5 of this series will cover EDW-enabled business improvement opportunities in the supply chain.

  • 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



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

Be the first to comment!