The Challenge

With a mainframe accounting system (OpenAccounts) producing some of the month end data which then required manipulation and collating with standalone Excel workbooks, it was asked if we could design and build a user friendly low maintenance Excel based system that could use the output from the main frame finance system along with collating standalone data and produce reporting packs that could be sent out to the Managing Director, Finance Director, Commercial Directors, Area Managers and Contract Managers.

Three reports came out of the mainframe system in an Excel format split by contract and\or director : Report 1 containing 70 sheets, Report 2 containing 12 sheets and Report three containing 10 sheets.

There were 7 standalone Excel workbooks containing a variety of worksheets in different styles and formats. Data was held in a variety of ways including Month, Year, Cumulative, Forecast, Actual and percentages.

With limited technical and programming knowledge held by current staff it was a requirement to produce a virtually maintenance free system which would last for several years.

Scope, Budget and timeframe agreed.

---

The Solution

Using Microsoft Excel and Outlook and a combination of VB language and in built functions, we built a system to update and collate data which finished its life cycle in the form of Excel workbooks which were identified as “Reporting Packs”.

Once the mainframe system has been “Hard Closed” for the month, authorised user can open the control panel and run the month end procedure.

Password protected control panel including period Selection constructed.
Copy programs written to enable the accurate and fast transfer of selected data between workbooks.
A new report was written to export transactions out of the mainframe system along with a process to enable creation of individual sheets by contract, sorted and sub-totalled.
We took existing Excel workbook’s containing Aged Debt and Revenue information and put in a process to enable creation of individual sheets by contract for both tasks.

Set up easy to maintain worksheets which contained the path/file name/tab name needed by the program written to collate the required worksheets to assemble a reporting pack.
Program compiled to add a “Hyperlinked Index” page to reporting packs.
Program compiled to add a Password to all reporting packs.
Program compiled to allow Emailing of all packs at once or by selected packs.
Set up new folders for processing and data storage including easy retrieval of previous month’s reporting packs.

Produced User/Technical manual in electronic and hard copy format
Full end user training was given.

---

The Outcome

Previous process used to take one person with some additional help one whole day to compile and then courier delivery of hard copy reporting packs would take an additional one / two days.

New process now takes 90 minutes and reporting packs are in the recipients email inbox

A fully operational low maintenance reporting system was delivered in-line with budget and on time which not only met but exceeded the Clients expectations.

Screenshot of Control Panel :