Requirement:
The client, a large multi-national bank needed a single resource tracking tool to consolidate the information held by various different departments. There was also a desire to be able to create organisation charts quickly and simply.
Main tab of the staff tracking spreadsheet
Challenges:
Lists of staff were maintained by various different teams and each held slightly different data as they were used for different purposes. There were no processes in place to ensure all lists were in step. The manual creation of organisation charts was very time consuming and typically involved multiple review cycles before they could be published.
Solution:
Viso Organisation Chart created from data imported from the staff tracking spreadsheet
The solution was an Excel spreadsheet with 2 simple VBA procedures to create organisation chart data. Data from the various sources was drawn together into the one spreadsheet with 23 separate data fields. Instead of just tracking people, the solution was based on roles within the organisation. This made it possible to track vacancies alongside staff, and enabled better reporting. Organisation chart data was imported manually into Visio, and although some minor reformatting was required the time to publish a new organisation chart was cut from days to minutes.
Design Highlights:
The Excel functions used in the solution were relatively straightforward: vlookup, countif, conditional formatting and concatenation. Even so, consolidating the data into one place resulted in significant time saving across a number of teams. The ability to use up to date information to create organisation charts was also a significant benefit.
This example serves to illustrate that time savings and improvements in data quality can be made with quite simple Excel projects.