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!
But 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.
We have to admit, spreadsheets are a killer app. They allow a business to setup things fast and it’s extremely flexible. So if the alternative is the calculator, then the business benefits are enormous.
But as the figure below illustrates, at a point in time spreadsheets might mutate into unrelenting monsters that become uncontrolled, produce errors and eat a massive amount of working hours.
During Binocs (resource planning) implementations we typically discover as many planning Excel sheets as there are teams. Here’s what we see:
Excel skills rule
The sophistication level of the planning sheet is always related to the skills of the author. We’ve met basic versions, where you have to copy-paste information between sheets over and over to come to a result, and the most complex ones where our IQ is never high enough to understand the links and formulas.
In both cases there is a cost to set it up and a cost to keep it alive. And in both cases it will be in quite the same range. Let’s say on average:
- One-time: 40h (and I know I’m seriously under-estimating here)
- Keep it alive: 4h / month (includes structural modifications, new master data, additional chart, …, but not entering the data and evaluating the output).
Everybody on an island
In nearly all of the cases we observed that each team maintains their 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 datadumps 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.
Are my data correct?
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
Where’s the brain?
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 planning 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. So our initial one-time cost of 40h becomes a recurring cost.
Managers always ask the wrong questions
“I understand your way of thinking, but what if we outsource this part of the activity and …”.
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.
So what is the cost of this hidden factory?
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 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.
But what about the alternative?
In this blog, I only consider an enterprise ready application as an alternative to the Excel based Planning Sheets. Depending on the type of working environment different software solutions exist. I do the exercise based on BINOCS, our own solution for Workforce Planning.
Based on our project experience, implementing BINOCS in an organization with 5 teams of about 10 people costs 50K EUR (≈ $) for consultancy services. We can discuss for hours whether or not to consider this cost as “value adding” in the context of this exercise in order to make the correct comparison. For now I’ll 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%.
Nice…but not good enough?
Of course there must be more benefits to implementing a Workforce Planning 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 must be found in:
- Focus on measureable 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 capacity planning solutions are an improvement platform, an enabler for a next organizational maturity level.
In many organizations with expensive staffing and volatile demand, capacity planning tools have a potential of 5% to 10% reallocatable 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 a copy of the Excel that I used in the example, Just ask…!