The first few weeks of the project, I continued to work with Digneer while everyone else suddenly was immersed in the race. Monica Logan headed up the team organization, and Chris Stallman took on the project approach. In perhaps a week the team expanded to 20 people.
The first order of business was to deploy people to 12 different countries or processing regions that represented something like 80% of the global PwC revenues. These included the U.S., Australia, Belgium, Canada, Switzerland, Germany, Spain, UK, Japan, Netherlands, and groups of countries in both East Asia and South America. Rick outlined the instructions to the head executives in those countries, and used global executive support whenever resistance was encountered to following them. In three weeks, each finance department was instructed to provide the following data files from their systems for the current and two prior years:
- General ledger balances or the journal entries, specifically identifying any manual adjustments
- All the client billing detailed records from the billing systems
- All the timesheet entries for billable staff.
Others began to work with the Global PwC Finance team. They learned that every month each country submitted a spreadsheet, called the MOR for Monthly Operating Report, of the financial results. These were consolidated into the global financial statements using a tool called TM1. Business team members obtained copies of these.
Note the pattern here: Find known numbers. Find the event file from the existing ledger. Establish the ability to recreate the numbers from the journals, and then drive to lower levels of detail in the transaction system files.
Build reference data was next. The reference data was listings of client code, with industry codes on them, billing rates, etc. They found, with time, that they needed to make these date effective, to account for changes in the reference data over time. So a small program was written to compare changed reference data. The team refined the reference data over time by producing many reports.
The firm had an existing data warehouse system that in summary showed the revenues by industry, be it consumer products, financial services, transportation and utilities, high tech, etc. However, there was a critical flaw in the data. The numbers were accumulated from the core financial system, the industry field representing the industry the consultant was assigned to, not the industry of the client. Each staff was assigned to an industry, say financial services, but if that staff worked on a consumer products client, the revenue showed up as financial services. There was tremendous sharing of resources, and so the numbers weren’t accurate at all.
Rick knew that the financial numbers were the accumulation of the business events. In a consulting company the business events are timesheet entries of staff working on client engagements. Timesheets accumulate costs to projects. Costs are marked up, and turned into billing records to clients. So on the time and billing records the client would be identified in every case, and that client could be associated with an industry.
The files began to show up. The Global Financial Data Warehouse or GFDW architecture was very simple. The team guessed at a simple data model the records from all 12 countries would fit into. Because every country had different systems, SAFR views were created to transform in an ETL like manner the country source system specific formats into the standard layout. There were really three standard layouts, one for each of the file types.
The data volumes were not huge; there were a few hundred thousand employees worldwide, and they submitted timesheets weekly or so. There were fewer clients. However, the PwC mainframe was one of the smallest made. If we had to purchase and install hardware we never would have been able to meet the deadlines. As it was, because of the efficiency of SAFR we could make the system work within existing hardware.
Sequential generation data group or GDGs were used to partition the data by year and month for the larger countries. Using sequential files added efficiency by eliminating CPU cycles putting data into the database and pulling it out of the database. We also did not have to perform extensive data cleansing to prepare the data to load the database. If an extracted field was not used contained garbage, it was ignored through our entire process but not dropped. At times, as we did more data discovery, we found instances where fields we hadn’t understood became useful. There was no additional work to use them since they were retained in the repository.
From Rick’s phone call on February 20th, people were deployed to many of the countries starting February 25th. By the middle of March, data files from many of them were received, and the ETL views constructed to put them into the standard format. The MOR reports had been gathered, and control numbers identified. A simple set of balancing views against the standard files had been constructed.
I began to help out the second week of April or so as the team began to work to reconcile the data. By the 26th of April – 2 months and 6 days after that phone call – the following reconciliation summary was produced.
Figure 141. Revenue Balancing Spreadsheet
The team had recreated from the detailed billing files (column D) and adjustments (column E) nearly 99% of the revenue numbers for three years of history to the globally reported financial statements (column C).1 The team had also reconciled hours billed by staff to within 2.5% for the same 3 years.
Many of the regional finance people told Rick they had never reconciled the detail to their ledger numbers and they weren’t sure it could be done. Rick asked if they were suggesting they wouldn’t balance which would be a real audit issue. Immediately they all would say that it could be balanced, they just had never done it. When the auditors did arrive to test the S-1 schedule they signed off very quickly with no issues. They could easily test where the business events had come from, and recreate the summary numbers we had accumulated.
The first S-1 industry segmentation report was produced on April 17th, I believe. The schedule was a summary of the billing detail file joined to the client reference data, summarized by industry codes and countries. The outputs all fit in Excel, and Rick learned how to use pivot tables to change the dimensions easily. We would run the views to produce the S-1 data at the same time we balanced the files.
Rick had Mukesh Patel start a one man project completed in a few weeks. He used SAFR to allocate employee cost for the year to the projects the employee had worked on by hours worked. Mukesh did this with two or three passes of SAFR, and a handful of views. The outputs from the process then could be accumulated by project, to show the cost of the project, the revenue, and the gross profit.
Other team members enhanced the reference data, by creating mapping from local company IDs to global companies. In this way, the global project for General Motors or Nestle could be shown. Rick found that people inside the firm had never known the things he was discovering in the data. Because the event level data was gathered and accessible, it was an incredibly flexible reporting system. The following is the June 27th report ahead of the updated S-1 filing date. It is still incredible to me what the team could do, when using the right approach, with a system architecture that focused on capturing and reporting from business events.
Figure 142. Industry Segmentation Report
I had a family vacation the middle of July and was pretty much completely cut off from the news media and the project. When I returned to work the following Monday, Lynn Groves Zuccala, the project manager, asked what I thought of the announcement. I asked what announcement. She couldn’t believe I had not heard; IBM had announced they would buy the consulting division for $4 billion. What a radical change in plans.
Beginning in May and June the team turned the system into an operational system. Each country on a monthly basis would extract that month’s data and send it over to the team to add to the warehouse. IBM used the results of the system to understand the business. Portions of it were used to convert the legacy data over to IBM’s systems when the merger took effect on October 1st, 2002 and systems were converted in January.
About the middle of August I called Doug one night to talk about prospects and all the changes. In the course of the conversation he confided that he was being forced to retire prior to the merger with IBM. He asked me to keep the knowledge to myself. It concerned me. Over the next few days I vacillated between being worried and then at times I would feel that things would work out. I surely didn’t know how, though.
About two weeks later we were together in the PwC office in Rosemont, Illinois. I suspect he could tell I was having one of the more worrisome days, and I think he changed his flight plans to spend a bit more time with me. It was a very kind thing of him to worry about and serve me at such a time, but very consistent with his nature.
He pulled me into an office and told me that we were still very good friends, and that would not change. He said he was very confident we would continue to have contact with each other. I knew that he was only a few months from when he would qualify for retirement, but he said he wasn’t bitter, or really upset. He said that his ego was a bit bruised because he wasn’t chosen to go forward into the new organization, but he wasn’t really upset. He expressed faith that the right things would happen in the end.
I expressed affection for him, and I said that I wasn’t really worried. Rather I felt comfortable with where things were going, but also felt bruised when he felt bruised. I did feel lightened after the discussion. We met for a few minutes multiple times throughout the day to coordinate how to handle his departure. He gave me approval to start telling selected individuals.
He left for the airport to fly home late in the afternoon. I walked to a conference room where Randall, Monica, Chris and Jim were talking. I said I had some bad news, that Doug was being forced to retire. I then said, “But there isn’t any other group of people I would want to face this challenge with than this team.”
Parent Topic: Part 5. The Programmer