New Technologies for Activity-Based Costing and Customer/Product-Line Profitability Analysis
By
Roger
K. Harvey, D.B.A
I.
Introduction
Activity-Based Costing (ABC) has made “do-able” customer profitability analysis (CPA), product-line profitability analysis (PPA), and order costing for wholesale distributors. These decision-making tools are possible because of new software and hardware technologies. The new technologies have been categorized as “business intelligence solutions” (BIS) and “decision support systems” (DDS). But these so-called information technology (I/T) “solutions” cause their own set of problems—trying to determine what business intelligence solution or combination of solutions to use to economically and effectively implement ABC/CPA/PPA. A wholesale distributor wishing to implement ABC/CPA/PPA is faced with a confusing array of hardware and software combinations; he or she is looking into an I/T black box.
This article is written to shed light on the black box by presenting alternative software solutions for implementing ABC/CPA/PPA, and discussing the computer hardware needed to adopt the software. Software should always precede hardware in an I/T decision so software solutions will be explored in the context of hardware. The reader is assumed to be familiar with activity-based costing and customer profitability methodology; if this is not the case, the author has written several Working Papers on these topics, which are cited at the end of this article.
The following ABC/CPA/PPA technologies will be discussed:
1. Legacy Financial Accounting System on a Mainframe Computer
2. Spreadsheet Solution on Single and Networked PCs
3. Dedicated ABC Software Solution on Single and Networked PCs
4. Data Warehouse on a Client-Server Network
5. ABC Software combined with a Data Warehouse on a Client-Server Network
II.
Dimensions
of the I/T Decision
The answers to the I/T black box questions begin by understanding the dimensions of the technology decision. The dimensions to deciding what technology to use for ABC/CPA/PPA, as shown in Exhibit I, are model complexity, organizational influence, and systems integration. A major consideration, not shown as a dimension, is model- building development time, talent and resources.
EXHIBIT I 1
The complexity of an ABC/CPA/PPA model depends on the number of activities in the ABC component of the model. The more activities in an ABC model, the more complex the model. On the one hand, too many activities require unreasonable and costly amounts of data. On the other hand, too few activities may under-define or miss-define cost drivers in a business. The complexity dimension hinges on the balance between accuracy and data cost. Pricing and bidding models require a high degree of accuracy and, therefore, require more complex models. Customer profitability models, especially where the models are not being used to fire customers, require less accuracy and, therefore, require simpler models.
When beginning an ABC/CPA/PPA project, the KISS (“Keep It Simple Stupid”) principal applies. Start with a model focused on one purpose (e.g., order costing or product costing or customer profitability), use pilot models, validate and revalidate the results of the model, and build toward more complex models. This approach might be characterized as a building block approach where you start with aggregated activities, next subdivide activities, and only over time, build toward more complex models.
The second dimension is organizational influence, that is, the extent to which ABC/CPA/PPA is to be disseminated among members of an organization. For example, if ABC/CPA/PPA is going to be developed in the Finance Department and used only by finance people, then a spreadsheet solution on the Controller’s PC may be all the I/T that is needed. This solution is usually appropriate for one-shot, pilot projects. If, on the other hand, information from the ABC/CPA/PPA system is to be used by executive managers, sales people, and operations people for on-going decision making purposes, then the I/T solution may be networked PCs or a model residing on a central mainframe computer.
The organization influence decision is difficult to make as a building block decision. The modeling time and cost may be unreasonable if a spreadsheet model is initially built on a single PC, then moved to an ABC software solution on a single PC, and finally, moved to networked PCs. If, from the outset of the project, the ABC/CPA/PPA application is to be used throughout the organization, it would be much more time and cost effective to develop the models on a client server network rather than on a single PC. In most cases, resources available to an organization (time, talent and dollars) will determine the organization influence decision.
The third dimension of making the ABC/CPA/PPA technology decision—systems integration—is closely related to the organizational influence decision. Will the ABC/CPA/PPA model be on a single PC? Will it be on the company’s mainframe? Will it be on its own client-server network? The more people using your models, the more compelling the case for integration.
The systems integration decision depends not only on organizational influence but also on centralizing data collection, data control, and processing. ABC/CPA/PPA models have extensive data requirements, much of which should reside on a single computer under the control of a single person or organizational unit (e.g., I/T department). If data sources are spread throughout an organization and maintained as islands of data by non-I/T professionals, then data accuracy is certain to be an issue. This will quickly undermine the validity of the models and question their usefulness for making decisions.
The final consideration in making the ABC/CPA/PPA technology decision is development time, talent and resources. Developing your ABC/CPA/PPA models on a PC spreadsheet requires substantial development time and a spreadsheet “programmer,” but the dollar cost of software and hardware is minimal. An ABC/CPA/PPA application software system carries with it a costly out-of-pocket expense upfront, but substantially reduces development time and in-house talent. Development time, talent and resources will be discussed as we evaluate each technology alternative for ABC/CPA/PPA.
As we address the question of software/hardware combinations for implementing ABC/CPA/PPA, keep in mind the dimensions shown in Exhibit I. In terms of the Exhibit, the question is “should you start and remain at the origin of the graph (simple model, one user, spreadsheet model on a single PC) or should you move to the right and upward (complex model, multiple users, integrated system)?” As you consider the technologies discussed in this article, you should also assess development time, talent and resources available in your organization. Your answer to these questions will determine the technology appropriate for your organization.
III.
Legacy
Accounting System
Your first idea for technologies for ABC/CPA/PPA modeling might be your organization’s current financial accounting system. “It already has all your numbers and currently generates customer, product, and order reports.” The reason this is an unlikely technology for ABC/CPA/PPA modeling is that most accounting systems were initially designed to do financial accounting and not managerial accounting. Financial accounting generates financial reports for top management, stockholders, creditors, and government agencies. Managerial accounting generates information for managers. ABC is managerial accounting—the methodology for mapping resources into activity pools and then assigning costs to cost objects via cost drivers has not been programmed into most financial accounting systems. This is the reason dedicated ABC software solutions were born.
As you pursue the list of reports generated by your financial accounting system, you may see names like “customer profitability report” or “product-line profitability report.” Don’t be misled: these reports typically stop at the gross margin level. Today, ABC/CPA/PPA models generate bottom-line profit for each of your customers or groups of customers (i.e., market segments) and for each of your product-lines. When financial accounting systems do allocate operating expenses to customers or products, they usually do it based on percent-of-sales or percent-of-gross margin. This crude and misleading methodology is used because very few financial accounting systems have the internal logic to track resources to activities and activities to customers, products or orders.
If ever there is customer/product report that leads to incorrect decisions, it is one based on the percentage allocations of expenses. Customers do not cause costs in your business based on the volume of business they do with you; they cause costs based on their number of orders and lines, the picks you perform for them, their order processing time, the number and distance of deliveries, the amount of duration of their accounts receivable, etc. Activities you perform for your customers drive costs; they are not caused by sales or gross margin volume.
There are financial accounting vendors who are partnering with ABC software vendors. These partnerships typically result in more efficient mapping (importing) of your general ledger data into the ABC software solution. The ABC vendor provides a data link to your financial accounting system, making it easier for you and all others using the accounting software to import some or all the data needed to run ABC/CPA/PPA models.
An ABC software solution will never replace your financial accounting system; a legacy financial accounting system will seldom provide an ABC solution. The internal structure of legacy accounting systems was not designed to meet the logic requirements for ABC/CPA/PPA decision models. This is not to say that future financial accounting systems won’t be designed for ABC/CPA/PPA modeling, but at this time, very few software vendors offer fully integrated financial accounting and ABC solutions.
The remainder of this Article will address ABC/CPA/PPA software solutions that are available outside of your financial accounting system.
IV. Filling the Black Box
EXHIBIT II
We suggest filling the black box with one or a combination of the following technologies:
1. Spreadsheet Solutions—Single PC Implementation
2. Dedicated ABC Software Solutions—Single and Networked PCs
3. Data Warehouses—Client-Server Networks
4. ABC Software combined with Data Warehouses—Client-Server Networks
The software/hardware combination you use to fill the box will depend on the dimensions we discussed in the beginning of the article: model complexity, organization influence, and systems integration. Your available resources—time, talent, and money—will also be a major factor in deciding how to fill the black box: a low budget will limit your decision to a spreadsheet/PC alternative while a bigger budget will move you toward ABC software on a client-server network. Again, budget considerations will dictate how far along the model complexity, organization influence, and systems integration dimension axis you will be able to move.
Regardless of how you fill the black box, the data inputs (as shown on the left of Exhibit II) will be the same. You will map “$” data from your general ledger into activity cost pools—the data source being your financial accounting system. Resource data such as number of employees, square feet of floor space, number of equipment hours, etc. will come from other sources: written documents, spreadsheets, operations databases, and estimates. Cost driver data such as number of invoices, invoice lines, picks, deliveries, etc. will come from either your financial accounting system or other sources. Unfortunately, as you move away from your financial accounting system for data, the cost of data collection increases and the degree of accuracy decreases.
The accuracy of resource and cost driver data can be a major implementation issue in building an ABC/CPA/PPA system. Ideally, all data would be available from your financial accounting system. This helps insure the validity of the data because the same accuracy standards usually apply to dollar and non-dollar data on a financial accounting system. Practically speaking, only a limited number of resource and cost driver data items will be available from your financial accounting system. This means you will have to impose strict measurement standards on non-financial accounting system data in order to insure the validity of the data. Poor controls and low measurement standards can have a significant negative impact on the validity.
The output of the black box , again regardless of how you fill the box, is information presented in a form to be used for strategic and tactical decision making. The reporting system (as shown on the right of Exhibit II) should follow the format of any good decision support system: numerical and graphical screens and reports, summary reporting with drill down capability, slice and dice capability, and ad hoc query and report writing capability.
Each of the technologies for filling the black box discussed below has the input data and output reporting capabilities required for an ABC/CPA/PPA model. The differences among the technologies are the ease with which these capabilities can be implemented; that is, ease of model building, ease of procuring data, ease of processing data, and ease of generating reports. The other major difference is cost. Both these considerations will be addressed for each alternative technology.
A.
PC Spreadsheet
The
black box may be filled with a PC
running spreadsheet software products such as Excelä or Lotus
When using spreadsheet software for ABC/CPA/PPA applications, you first define a workbook with multiple spreadsheets. Each spreadsheet defines a process in a business (selling, order processing, delivery, etc.). Resources are often defined in other spreadsheets with their costs imported from the general ledger. Each process spreadsheet has its own Resources-Activities Matrix containing resource costs (rows) allocated to the activities (columns) in the process. Total costs for each activity (i.e., column) are calculated and divided by total driver units to arrive at a cost per unit for the activity. The Resources-Activities Matrices are linked to the resource spreadsheets for resource costs, and report spreadsheets are linked to the Resources-Activities Matrices to extract per unit activity costs for ABC/CPA/PPA reports.
There is an extensive amount of “programming” associated with using spreadsheets for ABC/CPA/PPA applications: mapping and importing input data, defining formulas, linking spreadsheets, and formatting reports and graphs. Some spreadsheet cells will be filled with imported data from mainframe files (which will be coordinated with your I/T person, vendor, or consultant), while other cells will be manually filled with data from sources other than a financial accounting system. Filling the black box with this technology requires voluminous hours and a talented spreadsheet person.
Although spreadsheet software on single or networked PCs offers the modeling capability, computing power, and storage requirements for even large ABC/CPA/PPA applications, it may not be a cost and time efficient tool for building an ABC/CPA/PPA decision support systems. In a figurative sense, you start with a blank piece of paper and build your models from scratch. This is costly in terms of time even though the hardware and software are inexpensive. As you evaluate this alternative, be sure to consider the total cost of implementation—the cost of the hardware/software plus the time and cost of model development.
Placing the PC spreadsheet technology alternative along the technology dimensions of model complexity, organizational influence and systems integration, this alternative falls short on two of the three dimensions. Building complex models on today’s spreadsheets is do-able, although time and talent consuming. It is possible to move along the organizational influence axis by moving your spreadsheet model from a single PC to networked PCs. As discussed below under the topic of Client-Server Networks, the spreadsheet can reside on a server where multiple people and departments throughout an organization can access it.
It is difficult, however, to move far along the systems integration axis with a spreadsheet on a single PC or even on networked PCs. A spreadsheet on a networked PC is a separate entity from your financial accounting system. You may import files from your financial accounting system into your spreadsheets; however, this is simply a file link and not systems integration. True systems integration gives you the advantage of continuous and seamless data input to an ABC/CPA/PPA application and output data, reports, and queries generated from within a main information system or decision support system. If you want systems integration for an ABC/CPA/PPA application, single or networked PCs is not usually a viable technology.
B.
ABC Software Solution
This technology is filling the black box with software specially developed for activity-based costing. These software solutions have built-in capabilities to:
· Extract and import general ledger data from a financial accounting system directly into an ABC model
· Specify a variety of mapping schemes to assign dollars from resources to activity pools
· Trace costs from activity cost pools to cost objects, and from one set of cost objects to other costs objects (e.g., products to orders to customers)
· Translate formulas which are frequently used in lieu clocking time for calculating processing times
· Slice, dice, and query profit and cost data (e.g., profit per customer, contribution per territory, cost per product category, etc.)
· Generate pre-defined and ad hoc reports
· Perform “what-if” scenarios to determine the cost and profit impact of changes in cost drivers and per unit costs
The key word for all these capabilities is “built-in.” ABC software solutions have built-in capabilities for importing data from a general ledger. After mapping relationships have been defined between the general ledger and the software, updating and calculating the ABC model becomes a matter of pressing a button. Importing non-dollar data from non-financial accounting system databases (e.g., number of deliveries, number of sales calls, number of technical support calls, etc.) can be made routine because of the data capturing capabilities of the software. When the ABC vendor has a partnership agreement with a financial accounting software vendor, the data exchange between the financial accounting software and ABC software becomes even more seamless. The two applications “talk” to each other, which not only reduces data transfer glitches but also reduces development time.
ABC software has built into it all the logic for linking resource costs and resource drivers to activity cost pools, for calculating per unit activity costs, and for tracing activity costs to cost objects. It is part of the software’s “built-in” logic. Depending on whether accounting costs or economic costs are used in the model, total costs may also be reconciled with general ledger expenses. This feature is always attractive to accountants.
ABC software also has report generating capabilities built into the software, although these capabilities seem to vary widely from vendor to vendor. This is one feature of ABC Software Solutions that should be carefully evaluated before adopting a vendor’s software. Frequently, limited report generating capabilities are built into the base software package, but adopters are given “add-on” options for query, report, and graphical capabilities. For example, an ABC software vendor might offer a seamless link with more powerful business intelligence tools such as Cognos’ PowerPlayÔ (www.cognos.com) or Brio Technology’s BrioQueryÔ (www.brio.com). (These business intelligence tools allow slicing and dicing of ABC databases; generating reports by customer, product-line, region, salesperson, etc.; building analytical and presentation graphics; drag and drop report building capabilities; and ad hoc on-line query of ABC databases.)
It is important to define your report and query requirements early in your ABC/CPA/PPA software decision process, and then evaluate those requirements against the internal report generating capabilities and other querying options offered by the vendor. If your primary application is Customer Profitability Analysis (CPA), you might also consider a dedicated customer profitability analysis business intelligence tool such as that offered by Value Associates’ CPA for WindowsÔ. Unlike “general” business intelligence tools, CPA for Windows has built-in customer reports, graphs, customer scoring algorithms, and customer performance measures for analyzing the profitability of individual customers and customer segments.
Placing
the dedicated ABC Software Solutions technology alternative along the
technology dimensions of model complexity, organizational influence
and systems integration, this alternative extends far along all three
dimensions. Complex models in terms of
number of processes, activities, and resources are possible and very easily
defined. In a model building sense, the
models can be unlimited in size, but more importantly, they are easy to
define. Acorn Systems’ QuickABC
software has built in wizards: one
wizard to help you assess your need and capability to do ABC modeling; and
another wizard to walk you through the model building process in a step-by-step
procedure. ABC Technologies, Inc., one
of the largest vendors of ABC software products, offers model-building
workshops throughout the year and throughout the
ABC Software Solutions, almost without exception, offers their products on client-server networks and/or mainframe computers such as the IBM AS/400. This means ABC models can be used throughout the organization, whether that means departments in one location or departments and groups throughout the world. ABC Software Solutions are typically built on hardware platforms that support local area networks (LANs), wide area networks, and the Internet.
ABC Software Solutions are separate applications so they may or may not be fully integrated into an organization’s financial accounting system or information system. As noted earlier, they are not typically modules in a financial accounting system because these systems have not been designed for activity based costing. There may be seamless integration between data files when financial accounting system vendors and ABC software vendors form a partnership. An ABC Software Solution may be fully integrated into an organization’s information system, especially if the organization has separate hardware/software systems under the umbrella of a “decision support system.” As discussed below, this hardware/software system today is known as a “data warehouse.”
It appears that ABC Software Solutions offer the ideal technology: capability of building complex models, high organizational influence, fair-to-good system integration, and quick develop time. These benefits don’t come without cost. The ABC software itself with data capturing and report generating modules may cost from $50,000 to $100,000. If consultants from the vendor are used (which may not always be necessary), another $50,000 to $100,000 may be added to the software cost. Workshops may cost from $1,500 to $2,000 per participant.
These costs must be weighed against the cost reductions associated with shorter development time and reduced demand for internal organizational resources. ABC Software Solutions reduce the demands on in-house time, talent, and resources even when vendor consultants are not used. This avoids “dragging-out” the project and loss of organizational interest. Most importantly, profit/cost benefits are realized more quickly. Bottom line: higher out-of-pocket software costs must be weighed against lower internal model development costs and immediate profit/cost benefits.
Two ABC Software Solutions vendors have targeted the wholesale distribution industry with their product and marketing efforts: Acorn Systems, Inc. (www.acornsys.com) and ICMS, Inc. (www.icms.net). Both these vendors have clients in other industries, but seem to have built expertise in various lines of distribution. ABC Technologies Inc. (www.abctech.com) has clients in manufacturing, service, and retailing/distribution industries. All three vendors offer consulting services to help clients build and implement their models.
C.
Data Warehouse
Larger distributors may fill the ABC/CPA/PPA technology black box with a hardware/software technology known as “data warehouses.” Data warehouse software evolved from the utility industry where I/T people sought to analyze large volumes of customer data for marketing purposes. In I/T terminology, data warehouses are very sophisticated database management systems. Data warehouse software is capable of absorbing large volumes of dollar and non-dollar data from a wide variety of sources, indexing the data, and quickly retrieving the data for analysis or report generation. Data sources include an organization’s financial accounting system, internal operations databases, external databases, and the Internet. The advantage of a data warehouse is that the data is all in one place, uniformly coded, static (i.e., does not change constantly as is true with a transaction processing system), and is easily accessible throughout the organization.
Client-server data warehouses typically are separate I/T systems from an organization’s transactions processing system. Data warehouse software will reside on its own client-server hardware (e.g., an IBM Netfinity or AS/400 server) and link to its own set of client PCs or terminals. A data warehouse will be updated, usually nightly, with transactions data from the organization’s transactions processing systems as well as data from other internal and external databases. Data warehouse users then have easy access to current data for all types of decision support purposes, including ABC/CPA/PPA applications.
ABC/CPA/PPA applications may be developed on data warehouses using the data manipulation tools built into the software. These tools are general database management capabilities that apply to data in general and not specially to ABC/CPA/PPA applications. Approaching ABC/CPA/PPA development with data warehouse software is similar to starting with a PC spreadsheet’s “blank sheet of paper,” albeit with much more powerful data base management tools. Relationships and calculations have to be defined, data has to be imported (although this is extremely easy with most data warehouse importing capabilities), and reports, ratios, and graphs must be defined. Data warehouse data manipulation capabilities are extremely powerful and user friendly, but nevertheless, they have to be tailored to the ABC/CPA/PPA application.
It is possible to move far along all three axes on the technology dimensions of model complexity, organizational influence, and systems integration using data warehouses for ABC/CPA/PPA applications. Complex models are possible and easy to build with data warehouse tools and programming languages. Because data warehouses run on client-server networks, ABC/CPA/PPA models can be accessed throughout the organization. Data warehouses run as systems linked to the mainframe transactions system so they offer a high degree of systems integration with data outside the data warehouse as well as within the data warehouse.
The hardware cost associated with data warehouses is the cost of a client-server network. PC-type Servers running a Windows NT operating system cost around $10,000 while mainframe servers such as an AS 400 may cost $20,000 and up depending on options. The cost of clients is the cost of today’s PCs plus network card/cable costs. The cost of data warehouse software may range from $50,000 to $100,000, depending on the nature and number of modules ordered with the software. Silvon Software, Inc. (www.silvon.com) markets its data warehouse DataTrackerÔ and DecisionMarkÔ to companies in the wholesale distribution industry. With Silvon’s data warehouse software, it is possible to develop enterprise-wide ABC/CPA/PPA applications.
As part of larger ABC/CPA/PPA applications, data warehouses may have ABC software integrated with the data warehouse software as an ABC analytical engine. This configuration is discussed in the next section.
D.
Data
Warehouse With ABC Software Solution
EXHIBIT III
Exhibit III shows the relationship between a data warehouse and ABC software solution. The ABC software may either extract its data (e.g., resource, activity cost pool, and cost driver data) from a data warehouse or it may export its data (e.g., unit activity costs) to the data warehouse. These are design considerations that are beyond the scope of this article. To the right, Exhibit III also shows the possibility of adding an “On-line Analytical Processor” (OLAP). Cognos’ PowerPlayÔ, discussed earlier, is an example of an OLAP tool.
This technology alternative—Data Warehouse, ABC Software Solutions, and OLAP tool—are the components for a powerful and comprehensive ABC/CPA/PPA solution. This alternative is capable of moving an organization far along the technology dimensions of model complexity, organization influence, and systems integration. The hardware cost may range from $10,000 to $20,000; the software cost may reach $100,000. Obviously, this is not a technology alternative for a small company or of a large company with a small I/T budget. It will, however, enhance the capabilities of building complex and company-wide models, of making the models available throughout the organization, and of integrating the system with the organization’s overall information system.
E.
Summary
This article discussed the wide spectrum of alternative technologies for implementing activity-based costing, customer and product-line profitability analysis, and order costing in a wholesale distribution organization. Along with an organization’s model development budget, talent, and resources, the three dimensions on which the technology decision should be based are model complexity, organizational influence, and systems integration. This article described six alternative technologies: Legacy financial accounting systems, PC spreadsheets, ABC software solutions, data warehouses, and combined data warehouses and ABC software.
The technology decision is a black box for many distributors. Hopefully, this article has shed some light on a difficult and confusing technology decision. The reader is urged to seek additional information using the references and websites listed at the end of the article.
ABC/CPA/PPA
RESOURCES
Harvey, Roger K. and Peter L.
Mullins, ABC White Paper Series. Available
from Value Associates, Ltd., (970) 963-1444, at no charge. E-mail: rharvey_2@yahoo.com.
Mullins, Peter M., Measuring Customer & Product Line
Profitability: Beyond “Turn & Earn”, 1984. Originally published by the Distribution
Research & Education Foundation of NAW but currently out-of-print and being
revised. Available from the author
through Value Associates, Ltd. (970) 963-1444 for $39.
ABC/CPA/PPA
SOFTWARE PRODUCTS
SSCI Cost Modeling Software: Steel
Service Center Institute, Voice: 216-694-3630, Fax: 216-694-3940, Web
Site: www.ssci.org
CPA Software: CPA
for Windows published by Value Associates, Ltd., Voice: 970-963-1444, Fax:
970-704-9740, Email: www.valueassociates.org.
ABC Software: QuickABC
published by Acorn Systems, Inc.,
Voice: 713-963-9000, Fax: 713-963-9010, Web Site: www.acornsys.com
ABC Software: EasyABC
Plus published by ABC Technologies, Inc., Voice: 503-617-7100, Fax:
503-617-7200, Web Site: www.abctech.com
ABC Software: CMS-PC
4.0 published by ICMS, Inc., Voice: 817-633-2873, Fax: 817-633-3781, Web Site: www.icms.net.