Requirement:
The client, a global pharmaceuticals company required a system to support a new process for managing the full lifecycle of a large portfolio of small IT projects.
Challenges:
There were 4 separately funded business areas in 3 countries, supported by around 40 Service Managers who were to be the primary users of the system. A central commercial team was also involved in invoice tracking, approval and payment. The projects themselves were delivered by two different outsourced, offshore service providers.
Solution:
The solution was a custom built VBA system based on Excel that had 5 modules, 1 user form and 74 procedures. There were four separate spreadsheets, one for each business area that were stored and manged using Microsoft SharePoint.
The solution included detailed technical support and process documentation, 12 short user quick reference guides. At the end of the project it was handed over to the offshore service providers to support.
Design Highlights:
The solution was designed around a bespoke input sheet that managed the 148 data fields. Features included a tab structure that followed the PPM process, browse and edit modes, error checking and messages for every field.
The solution also included a facility to generate emails, custom printing, hyperlinks to additional project documentation, bulk extract, edit and reload and an advanced search facility.
Security features had to be developed to allow different user groups to use the same spreadsheet. It also had KPI measurement and reporting, and a full audit log to track every change.
The data input sheet in edit mode showing field prompt and an option to send an email
The search screen enabling the user to quickly find the record to work on