Use Case - Reporting Excel Replacement

The Situation

The company XXX*) creates monthly management reports, which include printed PDF-formatted reports, Excel-sheets and a structured Powerpoint presentation with summaries and charts. The reports are created from Excel-sheets and database downloads and sent via Email according to a predefined mailing list.

Data Acquisition

Two persons are charged with the creation of the reports.  They extract data from three databases and receive data from four sources as Excel sheets via Email. The data from the databases are also saved in Excel sheets. The delivery of the Excel sheets is not 100% reliable. Sometimes the structure and content of the data deliveries (Excel sheets and database extracts) change without advanced notice. The structure, keys and content of the Excel-sheets are not standardized. Therefor it is necessary to perform a QA and reformatting process.

Creation of Reports

The resulting Excel-sheets are consolidated into one large master Excel-map which is then used to create the requested reports and charts. Some charts and tables are then pasted into a standardized Powerpoint-file which is presented at the monthly management meeting. Several sub-Excel-Sheets are setup and data are extracted from the master sheet. They are used for analysis, simulation and planning.

The entire process is handled by two persons, which are responsible to chase the latecomers, collect and clean the data and create the reports. From time to time they get requests for additional details and reports. They work out how to find the data and how to feed these data into the reporting process. The total cost of this "system" amounts to about 20.000 a month.

EXL-Reporting - Open Issues

There are increasingly "glitches" where the data are just not right. An investigation found that in one case the data from a previous month has been mixed with current data. Changes in the data structure lead to errors in the calculations and delays in the delivery of the result. A few managers started to maintain their private Excel-sheet from other sources. This lead always to confusions and discussing in the management meeting and the confidence in the reports became shaky. The person responsible for the reports finally gave up and gave notice to leave the company in two month.

Proposals

Consequently the CEO asked for immediate action and the preparation of proposals how to overcome these problems. There were some arguments between the CIO and the Chief Controller how to proceed. Finally two alternatives were presented.

Alternative Nr 1 – BI-System

A BI-System should replace the current reporting system. The development should concentrate in a first step on the replacement of this system alone to meet tight time and budget constraints. An integrated system was proposed which accesses the databases directly and replaces also the delivered Excel-Sheets. The proposed budget was 1.6Million, it was estimated that development would take about 16months.

Alternative Nr 2 – Metasafe Proposal

In a first phase the existing interfaces would be maintained as they are: The incoming data should be captured, validated and stored in the Metasafe DB, using the Excel-Import-facility and an import program for additional validation. The reports will be created using the BIRT (Business Intelligence and Reporting Tool) in the same format as before.  Budget and project plan: The total budget was 120.000, development time 5 months, with a proof of concept after 2 month.

An extension was proposed for the next step:  some reports should be saved on on a server for direct / secure web-access instead of sending them per mail. A direct access to the data via a query should provide an access for power-users.

Discussion and Decision

The decision body was confronted with some difficult questions:
(1) Do we really believe that a BI-system can be introduced under these conditions (time and budget)
(2) Can we stay with the existing system for 16 more months?
(3) Should we go for an integrated system to clean up the entire situation or should be take a step by step approach?
(4) Two of the suppliers of the Excel-Sheet declared strongly that they are not in a position to provide a direct access or to change the reporting short term.
The final decision was to pursue both proposals until more facts are on the table.  It was further decided to provide a budget of 20.000 for a proof of concept to be delivered within 4 weeks.