Requirement

The client, a global IT outsourcing company needed a resource tracking and forecasting tool to get better control of its resource utilisation. Over-simplification of how resource utilisation should be managed and forecast coupled with a lack of a clear contractual understanding had led to an overspend that had to be clawed back in the last 6 months of the year. The forecasting tool was used improve the management disciplines needed to achieve this.

Challenges

Forecast dashboard showing breakdown and graphs

Ten teams were required to forecast around 90 resources working onshore and offshore to three different sets Ts & Cs.  Resources could book time to four different work types, all of which required forecasting.  Public holidays are different in different countries and had to be handled automatically by the model.

The final tool – a single spreadsheet – had to be used by all ten team managers, so the risk of updating the wrong team’s forecast or introducing functional errors had to be minimised.  Budgets were also subject to contractual change, and it was necessary to track and reflect these as they happened.

Solution

The solution was a custom built VBA system based on Excel that had 6 procedures used to control the user input forms so they displayed just one team’s data.  

The main data entry sheet required the manager to enter monthly forecasts for headcount, non-chargeable days an overtime hours.  This gave a total number of chargeable hours that the manager had to split between three work types for which they had agreed target budgets.  Worksheets were protected apart from required input cells.   

Design Highlights

This example uses an innovative approach to filtering to allow a single form to be used by 10 different teams without any risk that users will accidentally change another team’s data.

It shows how complicated requirements and processes can be boiled down into a tool that is relatively easy to use.  It is also an example of where the tool itself was a catalyst for improvements in the way things were understood and managed.