Acknowledgement
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
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.
The Project
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
quality aspect
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.
Some Conclusions
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.
Kind regards,
Dennis
Ps:
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.
Dennis, thank you for sharing that with us, I for one found it very, very intresting.
may i ask for the time span and size of work force for the whole piece of work?
from a techincal POV how where workbooks limted to 2-3 weeks? surely users could copy them to there local drives – or copy the data anyway,
thanks
Ross
Hi Ross,
Thanks for kind words.
I tried to cut down the post so it wouldn’t be too long to read (after all Daily Dose of Excel is a blog ;) ) but Your questions indicate that I missed some parts.
The project run during a period of approximated 7 months.
4 external consultants were engaged:
1 VB/Excel – Me
1 SQL Server specialist
1 Crystal Report/information specialist
1 Technical coder (MPS) who also wrote a special software (C/C++) that handle the reports-life cycles.
(It’s a privilegie to work with other specialist and I learned a lot from them.)
Per se we can’t fully prevent anyone from copying & saving files so instead the company set up a stronger IT-policy (including IT-security, defined different groups of users, access rights and system owners etc) that serves as a guideline and to support the entire staff.
One thing I would like to point out is the fact that the general manager was strong committed to the project and propably made it possible (both in terms of decision power and as a driver).
Unfortunately I can’t give more detailed information then above.
Kind regards,
Dennis
Thanks for the swift reply Dennis, – that info is just fine, nice of the copmany to allow you to share it with the excel community.
I guess it really does come down to IT polices, when you get to the reporting level, and i think user knowledge as well.
Thanks Again Dennis
Thanks, Dennis. This is exactly what some of us need to see in action, especially those of us inside the company, who seldom if ever get an overview of the entire project. Kudos to the company for allowing you to post. And well-done to you on condensing a 7 month project to an excellent top-level, coherent presentation.
At the end I wanted to jump on a flight to Sweden, and grab you and shout, “Dennis, next time take me with you!”
Congrats on a job well-done.
Rich,
Thanks for Your kind comments and I will forward the appreciation to the customer.
Sometimes I wished that we could share all the knowledge and experience we have but business is business and NDAs are present.
Kind regards,
Dennis
Heya Dennis
You know it’s weird… this could almost describe my job over the last two years… your initial diagram was especially a case of deja vu!!
We now only have 2 users who can create so called “ad-hoc” reports from our SQL databases… the rest, like your project are served eiher via Crystal Reports server (some of these have parameter abilities) and the rest are delivered via secured workbooks (by secured, I mean that the 99% of Excel users view them as unbreakable – most readers of this blog would not think so though ;) )
We are now currently moving towards phase two, with a new SQL based banking system & separate Accounting & CRM systems – the intention is to use web-based reporting via a sharepoint scenario… but we are as yet at the design stage so can’t say much more “in public” (as my boss says, people have ears…)
Thanks for the article Dennis, makes me feel that I got some things almost correct since we “met”
Will
Will,
You’re most welcome and based on some feedback I got it seems that many companies face the same situation nowadays.
From what I’ve seen and taken part of The Sharepoint Services / Portal Services can create a solid platform to co-ordinate and control the information flow within companies in an excellent way. But it also raise high demands in many ways.
Speaking about the C/R Server, the company will next year implement it.
Thanks – Now I know why we have so much in common ;)
Kind regards,
Dennis
Dennis
Great post, thanks for sharing.
I found the metrics especially interesting (v. similar to what I have seen).
cheers
Simon
Simon,
Thank you for Your kind feedback :)
I’ve forwarded all feedback to the company and my contactperson told me last week amogn other things that it’s good to know that they are *not* unique.
Kind regards,
Dennis
Being the only spreadsheet tool in all computers and the basic trained computer skill in my company,it is no doubt that Excel is the most common program used when dealing with data and reports.
So the extensive used of Excel from simple word processing task,report writing and complex analysis tasks are very common.
The advantage of Excel is the ease of transferring files from one user to another – sometime it coluld be outside of the company especially when dealing with other affiliates within a global company, beside Excel there could be very limited common language.
For example, we are now wotking on Fixed Asset Management, Company A & B used SAP, but there are big differences in the system structures. whereas Company C is using a Japanese data system, hence when we want to do any comparision, there are massive transfer of data, sorting, twisting etc… to make the close match.
So… will there be a solution? Unless Excel can do more than it can now… SQL servers, Crystal Report etc.. could only add complexity to the whole problem …
Casey,
The *key message* in the above case is that the company take full responsibility. What tools that are in use is a secondary question and subject to several aspects.
The key questions in Your case should be:
#1 Does Your company take full responsibility for the whole information process or do they expect that all the end users fix the issues down to the road?
#2 Who control all the workbooks in use?
# What are the quality of the workbooks in use?
As for the tools/platforms to be used the wide general point of view is that different issues usually demands different tools to be used.
Kind regards,
Dennis
Thanks, Dennis i would like to ask u some Q what the relation betwwen this case and relevance lost ?is this case has relevant to manageral accounting or no ?