Case Study 2: From Spreadsheets to Strategy: Building Visibility to Enable Smarter Resource Planning/Forecasting

Problem Statement

Large, complex organisations lack a reliable, centralised view of their resources, making effective planning, allocation, and cost management nearly impossible. The root cause is that resource and cost data exists in two problematic states: scattered across disconnected Excel spreadsheets owned at the team or project level and held too broadly in HR databases structured for payroll and compliance rather than operational planning.

This leaves organisations with:

• No single view of resource allocation across projects and regions

• No ability to forecast needs as programmes wind down

• Inconsistent cost tracking across onshore and offshore resources

• No framework for comparing spend across skill levels and geographies

Without bridging these two data worlds, leadership lacks the visibility needed to make confident, timely decisions about resource deployment, gaps, and future demand.

The Approach

1. Audited all existing resource tracking across projects and teams to understand the full landscape of fragmented data, identifying inconsistencies in how roles, skill sets, utilization, and costs were being captured

2. Established a golden standard, defining role-based utilization benchmarks that could be applied consistently across the global workforce, accounting for differences in seniority, skill set, and regional cost structures

3. Designed and built a centralised resource MI database, consolidating all fragmented spreadsheet data into a single source of truth, structured around standardised parameters including role, skill set, project assignment, region, onshore or offshore status, and cost

4. Built cost modelling capability into the database, enabling leadership to model future resource spend scenarios, compare onshore versus offshore cost structures, and plan for resource reallocation as programmes approached completion

5. Established near real-time reporting and visibility, enabling programme and portfolio leaders to monitor utilization, track budget variance, and identify underspend or overspend as it emerged rather than retrospectively

   
   

The Outcome

✅ A single source of truth for resource data Consolidating fragmented Excel spreadsheets into one centralised database eliminated duplication and inconsistency. For the first time, leadership had a complete, accurate view of who was working on what, where, at what cost, and at what utilization level, across the entire portfolio.

✅ Standardised utilization benchmarks across a global workforce Establishing universal role-based utilization standards meant resources could be compared and analysed on a like-for-like basis regardless of region, seniority, or programme. This gave leadership a consistent framework for assessing whether resources were being used effectively and where capacity existed.

✅ Proactive resource reallocation and forward planning With visibility into project/programme timelines and resource assignments, leadership could identify well in advance when projects were approaching completion and begin planning reallocation. This shifted resource management from a reactive scramble to a proactive, structured process, reducing gaps between assignments and improving continuity.

✅ Real-time budget variance management Near real-time MI enabled immediate visibility into underspend and overspend across programmes. Rather than waiting for quarterly reviews to identify budget issues, decisions could be made quickly to rebalance resources, zero out overspend, and redirect underspend where it was needed most. This significantly improved cost control and financial discipline across the portfolio.

✅ Faster, more confident decision-making With clean, centralised, and timely data replacing fragmented spreadsheets, the time spent collecting and reconciling information dropped considerably. Leadership could make resourcing and cost decisions with greater speed and confidence, backed by data they could trust.

Next Steps > Automating and Scaling the Model

With centralised resource MI in place, the next phase is automating data capture and deepening analytical capability to reduce manual effort and unlock strategic insights:

• Automate data collection and eliminate spreadsheets, leveraging AI-powered tools like Microsoft Copilot to streamline data entry, validation, and reconciliation across programmes, moving from manual spreadsheet updates to automated data flows that keep the centralised database current without placing burden on individual programme teams

• Build advanced analytics with Power BI, creating dynamic dashboards and predictive models that enable leadership to visualize resource trends, forecast future demand by skill set and region, and model scenarios in real time rather than relying on static reports

• Redesign resource planning processes with AI integration, adjusting workflows and decision cadences to leverage AI-driven insights and automated recommendations, enabling faster, more agile resource reallocation and forecasting based on real-time data