Introduction

Where's the Analysis?

Supplier Familying: Behind the Hype

Perfect Cubes and Golden Chalices

Suite Silliness

Data Warehouses Disappoint

ERP Is Not Spend Analysis

Practical Solutions for Multiple
   Accounting Systems

On Beyond A/P

It's All About Visibility

One Spend Cube Is Never Enough

What Spend Analysis Can't Do

Mapping Spend: Three Easy Steps

Building Datasets: Experts
   Need Not Apply

Why Spend Analysis Frustrates

... and, in case it applies:

I Have No Resources To Do This

Practical Solutions for Multiple Accounting Systems

It is very common to encounter multiple accounting systems within a company umbrella, because of acquisitions or other historical reasons. Performing spend analysis for such a business requires that the spending from each of the accounting systems be brought together in a useful way.

The methodology for bringing together feeds from disparate systems is straightforward, and with BIQ, simple. It consists of three steps:

1) Normalize transactions to a standard format.
2) Combine dimensions together.
3) Derive useful new dimensions.

Step 1: Normalize transactions

BIQ provides easy-to-use translation tools to normalize the format of transactions from two or more different accounting systems. These tools can add columns of data as functions of other columns; permute the order of columns; and create empty columns as "fillers" when one system can't provide data, but another can. We also use these tools to add a new column to the transaction, namely the SystemID (for example, "SystemA" and "SystemB").

The first step is to define a common transaction format by inspecting sample feeds from the candidate systems. This format should provide for maximum detail where it exists, but it must also allow for the inclusion of non-specific data from less capable systems.

A/P transactions from all systems generally include a notion of Vendor, GL, Cost Center, and date; they may also include Geography, Business Unit, and other ancillary information. Typically the basic transactions contain codes for Vendor and GL, with separate indexes of Vendor names or GL names. When there are separate such indexes, those too must be normalized into a common format. For example, if SystemA has the Vendor name built into the transaction, but SystemB provides it as a separate index, BIQ's translation tools can be used to derive a compatible index out of the transaction file from SystemA, so that both system feeds are normalized.

Step 2: Combine dimensions together

There are three fundamental dimensions to any spending cube: Vendor (aka "Supplier"), GL (General Ledger code/description), and Cost Center. From these, one can build a Commodity dimension using rules, and from there one can derive Contract and other interesting meta-dimensions with more rules. So the first task in the multiple accounting system scenario is to create uniform Vendor, GL, and Cost Center dimensions.

Vendor

Let's start with Vendor, which is easiest. Note that there isn't any fundamental difference between Vendors in SystemA versus Vendors in SystemB. If there is an entry for IBM in SystemA, and there is an entry for IBM in SystemB, it's likely that the only difference between the entries in the two systems is the way we spelled "IBM." So, we combine both Vendor lists into one list (BIQ will combine them for us), and family like Vendors together, just as we would do with a single accounting system (see Supplier Familying: Behind the Hype).

General Ledger (GL)

Next, let's consider GL. The first problem with consolidating two GL systems together is that a GL code may appear both in SystemA and in SystemB, but that code may mean very different things in the two systems. So, the first objective is to make sure that all GL codes are unique. We do that by prepending the GL codes with the system ID, for example:

SystemA-code23
SystemB-code23

As we mentioned above, BIQ provides translation tools to make such operations easy.

Once the GL codes are loaded into BIQ, they can be familied together using the same tools that we used to family Vendors. The idea is to family GLs into an "super-GL" structure, such that the new structure can accomodate data from both accounting systems. When detail is missing from one GL structure, but present in another, the hierarchy is built to accommodate this. For example, given these codes:

SystemA-code37,"Commercial Print"
SystemB-code123,"Lettershop"
SystemB-code456,"Envelopes"
SystemB-code789,"Print"

a useful hierarchy might be:

Print
General
SystemA-code37, "Commercial Print"
SystemB-code789,"Print"
Lettershop
SystemB-code123,"Lettershop"
Envelopes
SystemB-code456,"Envelopes"

Note that the extra detail from SystemB is represented, but the non-specific SystemA Print spend (and non-specific SystemB Print spend) is captured in a "General" category.

If a hierarchy is already present in one or more of the GL files, we use BIQ's tools to normalize the hierarchy formats and import them. The BIQ Dimension Editor can then be used to modify them as necessary with simple drag-drop operations.

Cost Center

The treatment of Cost Center varies, because often the Cost Center is held outside any accounting system (since it must span all of them). The key is to ensure that the "super Cost Center" dimension is built similarly to the GL dimension above — i.e., there is a place in the hierarchy for all of the elements of each system.

Step 3: Derive useful new dimensions

Finally, by deriving a dimension on the SystemID (added to the transaction files in Step 1, above), one can:

• See a combined view of the systems
• See a view containing only one system
• See a view containing some of the systems, and not others (if there are more than two)

The completed system provides the best of both worlds — an integrated view across all or any subset of the systems, as well as an individual system-by-system view, all accessible by filtering on the SystemID dimension.