Welcome Guest. | Log In| Register | Membership Benefits

Intelligent Enterprise

Better Insight for Business Decisions

Home
Digital Library
Events
RSS | Newsletters
Webcasts

November 15, 2002

Designing the Financial Data Warehouse

Now that the finance function has knocked on your door, what's next?

by Dan Hughes
Edited by Ralph Kimball

Building a data warehouse to support financial decision-making can be one of the most challenging endeavors a data warehousing manager can undertake. Senior management visibility, political territory, domain expertise, complex transaction systems, and a need to understand the business at a cross-functional level are just a few of the obstacles. But working in this area can also be a boon to your career and very rewarding.

In this column, I will describe the typical transaction system landscape supporting the finance function and review some limitations of a typical financial analysis model. I will then present an approach that can overcome these limitations.

Currently, vendors are promoting the "data warehouse in a box" or "rapid implementation through zero programming" idea. This ease may be achievable for single-source, first-level data marts, but the golden nuggets of information — and thus the real challenges — exist when the corporate financial repository, the general ledger (GL), is joined with the transaction subsidiary ledgers (subledgers) to create a single repository of financial information. You have to approach the design of such a sophisticated data warehouse with great care. It's beneficial to step back and develop an approach that not only looks at the subledger, but also looks at how the data is integrated into the GL.

The Core Financial Applications

At the highest level of the organization, all financial activity is summarized into financial statements: the balance sheet and income statement. The balance sheet is an accounting of the assets, liabilities, and equity of the firm, while the income statement is an accounting of income and expenses. The GL is the source for financial statements.

The GL is a transaction system designed to record financial transactions in accordance with accounting standards defined by historical precedent and the various worldwide standards bodies. In addition, organizations use the GL to organize enterprise financial activity into a common structure known as the chart of accounts (COA). The COA is a data structure designed to numerically represent the physical organization of the business. COA segments, such as business units, cost centers, products, projects, and accounts, are common ways of classifying financial measures. All financial activity, regardless of the source, is consolidated into a format that will allow for common performance measurement of the enterprise. The GL and COA are the core financial applications.

Role of the SubLedgers

The detail transactions of the business, such as sales orders, corporate debt, accounts payable invoices, purchase orders, and customer receivables, are recorded in transaction systems categorized as subledgers. The purpose of each subledger is to track the transaction-level detail of financial activity, such as customer and vendor information, product attributes, quantities ordered, transaction dates, and amounts.

In addition, the subledger captures COA segment information, such as business unit, cost center, and account. The segments are used to aggregate the financial activity recorded in the subledger into journal entries for posting to the GL. These systems may or may not be part of an integrated ERP system.

The number and sophistication of the subledger systems will vary depending on the nature of the enterprise and its organization. Experience shows that integrated ERP applications generally account for the core business functions (such as inventory management, accounts payable, fixed assets, purchasing, accounts receivable, and cash management), while industry-specific functions, such as credit-card servicing, property management, or fixed income securities, are tracked through niche software products or spreadsheets. Most mid- to large-size organizations have a wide variety of disparate subledger applications.

Accounting Close Process

At the end of each period, usually a month, the accounting group begins a process called closing the books. Closing the books simply means compiling all the subledger transactions for a given period and posting them to the GL.

On the surface, this process seems to be straightforward, but the process of reconciling the subledger data to its source and preparing it for posting to the GL can be extremely arduous. Analysts spend many days each accounting period gathering and compiling the data to record the journal entries.

Once the data is recorded in the GL, the accounting period is closed, meaning no more entries are permitted in the GL for that period. Financial statements are then created from the GL and distributed to consumers of the information. The consumers include personnel from all levels of the organization as well as external entities, such as banks, investors, and regulatory bodies.

Current Financial Statement Analysis Model

The users of the financial statements have a summary view of the underlying transactions originated in the subledger systems.

The problem with this model is that analysis of the financial statements is limited to the intelligence captured in the COA. Any analysis requiring information captured in the transaction subledgers must be separately researched and retrieved.