May 14, 2021

Common issues when using Excel for resource management

I’ve been using spreadsheet apps during my entire career (almost 30 years now). It started with Lotus Symphony on an IBM PC AT, then Lotus 123 and finally Excel. And I’m still a big fan!

I’ve been transforming spreadsheets to enterprise applications during my entire career as well. Implementing ERP systems that replace holy Excels are often perceived as a business replica of an inquisition.

I must admit, spreadsheets are a killer app. They allow a business to setup things fast and it’s extremely flexible. But at some point, spreadsheets might mutate into unrelenting monsters that become uncontrolled, produce errors, eat a massive amount of working hours and result into hidden costs for all the following reasons.

No single source of truth

In nearly all of the cases analyzed, each team maintains its own version of the input data. In workforce capacity management there are often common sources of demand. Ideally, you want to capture them once and you’ll feed all the delivery teams.

The reality is often different. Teams consult the same data sources separately. They have their own meetings with project managers, supply chain planners, etc. Then they enter their part of the data in their own planning Excel. They create their own Excel data dumps from the BI solution (business intelligence) or databases.

So let’s assume we have 5 teams. Each team spends 8h/month to capture the same data.

Uncertainty regarding data accuracy

This will sound familiar… In interviews where I try to understand how a team lead works with his/her planning Excel, I almost always hear: “… but I should check if my sheet is up to date, and the result is correct because…”.

How confident are teams in the input, calculations, and outputs of their sheets? Basic, simple sheets won’t have that problem. But then the added value might be lower as well. So each time a team consults the output of the planning Excel we spend time checking if the result is correct. Let’s not exaggerate: cost = 4h/month

Requirements and planners change

Excel planning sheets never come with a manual. They are the result of a quest for quick wins. Team leads have a lot of bullets in their job description, but making Excels is not one them. And organizations change and so move the brains behind the Excel.

After one or two years the resource management sheet is not matching the current requirements anymore and the brains that could adapt it are out of sight. So let’s start again. Let’s assume that every 3 years we have to redo the Excel planning sheet. Unlike our Excel alternative. So our initial one-time cost of 40h becomes a recurring cost.

Inaccurate capacity planning

“I understand your way of thinking, but what if we outsource this part of the activity? Or what if we train our staff? Should we hire more people?”. And then the lights go out because our planning sheet was not built to respond to that kind of questions.

That’s the point where team leads make one-time clones of their “operational sheet” and start juggling with data. By the time they have to present and they get challenged by their manager, they forgot how they came to the result. It all had to be done fast, isn’t it.

So for the case: 3 such questions a year, costing 8h/case.

 

This hidden factory is expensive

In lean six sigma the hidden factory is defined as “the extra useful, positive output that would theoretically be possible if the energy directed at creating waste were released”.

We assume that the initial creation of the resource planning sheet is useful. Entering data and evaluating output to prepare planning decisions is considered as useful as well, but not if the same is replicated across all teams. Then on 5 teams, 4 of them end up as waste. The table below shows how the hidden factory cost builds up to 45K/year for maintaining Excel Planning sheets for 5 teams of 10 people.
The hidden costs of using Excel for resource management

 

Looking for an Excel alternative for resource management?

If you are looking for an enterprise-ready application as an alternative to the Excel based planning sheets, you should consider BINOCS.

Our resource demand and capacity planning + scheduling software is specifically tailored for QC labs, R&D labs, Cell & Gene Therapies, Regulatory Affairs and Resource Management Offices.

Cost benefit

Based on our project experience, implementing BINOCS in an organization with 5 teams of about 10 people costs 50K EUR (≈ $) for consultancy services. We could argue for hours whether to consider this cost as “value adding” in the context of this exercise in order to make the correct comparison.

But for now, let’s include the full implementation. The annual license for BINOCS for 50p will be around 12 K EUR. So, on a lifecycle of 5 years, the average cost that compares to our 45K in the table above is 22K (50K + 5 x 12K across 5 years). So in the BINOCS example we reduce the hidden factory with 50%.

Wonder how much savings your lab could make by improving planning and resource utilization? Simply fill out 5 easy questions in our ROI calculator here.

The value of using BINOCS

Of course, there are much more benefits to implementing a resource management solution. The example above is more an awareness exercise to feel the marginal business case. The true ROI for implementing an enterprise-ready tools for capacity planning such as BINOCS is found in:

  • Focus on measurable standard workload and lead times for team deliverables
  • Reduce staffing variability through workload leveling and correct prioritization
  • Collaboration, team flexing
  • On-time and in-full delivery
  • End-to-end business process integration
  • Enterprise-ready workforce scheduling planning solution are an improvement platform, an enabler for the next organizational maturity level.

In many organizations with expensive staffing and volatile resource demand planning, capacity planning tools have a potential of 5% to 10% re-allocatable FTE. This annual value of 200 to 500K goes far beyond the marginal 22K saving we calculated in the Hidden Factory example.

But it draws the scale between the pragmatics and the believers. Where are you on that scale? If you want an alternative to the Excel that I used in the example, Just ask!

Author

Geert Vanhove

Founder and head of strategic markets

No Comments

Post A Comment