An old and indeed a close customer to me agreed to allow me to publish some data from a project we conducted last year. The agreement allows me to present some details but at the same time keep it in general terms and therefore no aspects on the effects on the organization etc. are discussed. I take this opportunity to thank them for their kindness.
The company is a subcontractor to a worldwide company and has around 120 employees. At the time for the project the company could be described as a core “Excel-company” since the staff used Excel extensively although the company had never actively made any strategically decision to use Excel as part of their business platform.
After we had implemented a minor MIS-solution (A frontloader in classic VB with Excel reports) we had a follow up meeting with the general manager and the IT-manager. During the meeting we were told that many co-workers had raised demands for a longer time on more powerful pc, more RAM and wider screens and even to have two screens on their desks. The main reason for these requests was that they experienced poor performance and not seeing enough on the screen when working with their spreadsheets. What it finally turned out to be about was large workbooks and after additional meetings it was decided that the project “Large workbooks” should be started.
The focus with the project was:
Explore and document the present standard for storage and handle information in Excel, including the data
Analyze the present internally information workflow and to identify the real information needs
Offer a solution that would solve the information needs and the storage and the value added processes to generate reports with the data from the central sources
The following picture shows how the company viewed their IT-situation at the time of the project:
The following picture shows how it really was:
(The pictures are simplified and also adjusted in order to keep it in more general terms.)
The Large Workbooks
Data from the Business system was exported to Excel’s workbooks and then manually transferred to the user’s workbooks. The MPS system generates textfiles that was manually imported and then manually handled by the users. Data from the SQL Server was retrieved via DTS and then manually imported to the user’s workbooks.
We randomly picked 42 workbooks to analyze and interviewed 18 co-workers from different departments about their way of working with the information.
The average file size was around 8,7 MB (37 workbooks was included as the remaining files had file sizes greater then 20 MB)
100 % of the workbooks contained outdated data (The question “Why” was commonly answered with “just in case” and “No time to clean up”)
About 88 % of the workbooks contained some kind of calculation errors
About 76 % of the workbooks were poorly designed and was difficult to interpretate without asking the users about the contents and the purposes of the files
About 95 % where “designed” individually and had names that poorly referred to their contents
The Suggested Solution
The following picture (again simplified) shows the suggested solution which was also implemented by the company:
The fixed reports, which are accessible through the Intranet, are all created with Crystal Report Enterprise. The fixed Excel reports are DTS-generated and then completed with a COM add-in tool. The ad hoc reports are generated with a special COM add-in tool (directly connected to the SQL Server) that is available only to a limit group of users.
In addition some standalone desktop’s databases (aka “Access databases”) was developed and implemented for a smaller group of users and only connected to a group of special Excel reports. All generated Excel files are named like the following Reportname & date & Time and these files are automatically deleted after 2-4 weeks. All files are stored in network folders. Different groups of users have different access rights.
The most important conclusion is that the company with the solution now takes full responsibility of the internally information workflow. The users were previous on their own to a) decide which information was relevant for their work b) and where to store and how to add value to the data
A higher information and storage security has been achieved and the company is now less key person depended
The numbers of distributed reports have decreased from over 100 to only 35 and all the standalone reports are now unified (design and names), including a full documentation
The average quality of the workbooks has heavily increased and the file sizes are on the average less then 1 MB
Less time consuming processes to generate the fix reports due to nearly full automation
No upgrades of any hardware was necessary to accomplish
Did we manage to fix all the issues to 100%? Of course not, my estimation is that we managed to take care of about 70 75 % of the problems and that it’s quite normal for projects like this one, at least in my experience.
In the early 90’s Robert S Kaplan and Thomas Johnson wrote the book “Relevance Lost – The Raise and Fall of Management Accounting” which became the starting point for the global era for Activity Based Costing (ABC). The only relation between their excellent work and this post is that I have “borrowed” the title from them and the fact that I like case studies.