Many Additional Benefits

This is an excellent example of how careful process design leveraged efforts towards a stated objective while, at a tactical level, simultaneously achieving other useful results.

Crisis Situation:
On closing the books each quarter, a specialized computer company analyzed its performance in each of targeted key markets for presentation to the financial community and the general public at a meeting held three weeks later. When the employee responsible for these reports for many years resigned, the employee taking over this responsibility failed to get the reports one within the three weeks assigned for 2 successive quarters. Four weeks into the third quarter, management asked Roy Sequeira to ensure the reports were available on time.

Background:
Three sales divisions had their own finance and accounting staff. Appropriate interdivisional transfers accounted for individual sales transactions split between divisions. Corporate Finance and Accounting coordinated the three divisions and produced all corporate reports. The IS staff was busy with a major rollout and had no “spare cycles” to allocate to any new project

The prior revenue analysis process went as follows:

Each week the employee assigned to revenue analysis received a listing of all sales transactions by customer and market segment from the divisional accounting reps and manually entered the data into a current quarter database as well as into a spreadsheet-based corporate “Installed Base” database by customer.

Inter-divisional transfers did not track the market segment of the original transaction. While such transfers were subtracted from the original market segment at the division booking the sale, the receiving division entered the value in a Miscellaneous account. This allowed total revenues to "balance" but threw further analysis off as the individual "buckets" contained inaccurate data.

Another major problem was that the Installed Base database was text based. (In effect, the "database" was a computer equivalent of an extremely wide, long, and extensible piece of paper.) Sub-totals and totals were manually calculated and inserted below each sub-account and account.

Reconciling all these transactions was a cumbersome and lengthy process that consumed a bit over 30 hours each week.

First Steps:
Given the need for quick action, Roy took the following steps:

1. Parsed the monthly combined sales transaction listing to create a corporate spreadsheet, which was then split into divisional spreadsheets. Each division used these spreadsheets for monthly accounting reconciliations.
2. Created a series of macros to automate and facilitate accounting tasks like adjustment control and inter-divisional transfers. These macros ensured interdivisional transfers affected the same market segment at sending and receiving divisions.
3. Created a summary sheet that automatically showed performance by branch and sales rep. This cut hours from the reconciliation process by highlighting discrepant items and totals. Enabling real-time reconciliation made the high-pressure and time-constrained quarterly reconciliation much easier and faster.
4. Created all macros needed to re-combine divisional data into a corporate database and produce all the tables and graphs needed for publication.

These steps enabled the company to satisfy the analysts’ needs that quarter and could be used until IS created the necessary programs and processes.

Further Actions:
Roy had also noted a number of problems that, while not directly connected to his initial assignment, had seriously hampered the company.

When Roy ran earlier quarter data through the new process, the company was chagrined to find earlier quarterly reports were highly approximate in that the Miscellaneous sector had been grossly overstated at the expense of other sectors. This resulted in focusing management attention to inappropriate areas of the business. For example, Service was the second largest source of revenues - a fact that had been completely unsuspected, and consequently neglected in Marketing programs. The analysis also showed that some sectors were actually performing much better than realized and others needed more attention than they had been getting.

The lack of genuine Installed Base data also hampered Service in managing its programs and Marketing in quantifying possible upgrade programs. Roy transformed the text-based Installed Base database into a genuine spreadsheet as a prelude to creating a relational database, and automated feeding new data from the revenue analysis process.

He also found considerable bickering about how to allocate a given sale. Since sales and marketing compensation depended on sales performance by sector, they tended to force the allocation to their sector rather than the customer’s sector. Roy created a number of additional fields to enable proper multi-dimensional sales sector mapping.

In the interim, while IS found the needed resources to take the entire process under its wing, Roy created all the feeds into other processes like Service planning and Marketing programs.

Summary:

Immediate Benefits:

1. Made revenue analysis reports available in three days from closing the books instead of fourteen, - the original objective.
2. Cut HQ labour from 30 hours a week to one hour a month
3. Cut divisional labour by eight person-days per quarter.
4. Provided highly accurate results
   
   Long-term benefits:
5. Current and highly accurate information enabled precise channeling of marketing efforts and resources
6. New sources of information enabled creating targeted programs to encourage existing customers to upgrade to newer equipment
7. Eliminated sources of friction between various functions and groups within the company.

Benefits 2 through 7 were not included in the original objective. By making better use of existing resources instead of using additional resources, Roy found ways of achieving the additional benefits without compromising his original objective.