By Hiran de Silva
This is a challenge.
It is a challenge to anyone who says, writes or promotes the idea that VBA is obsolete.
That VBA is legacy.
That VBA has been superseded by Office Scripts, Power Query, Python, Lambdas—or whatever technology happens to be fashionable today.
It is also a challenge to anyone who says that Excel cannot do bottom-up consolidation.
That Excel doesn’t scale.
That Excel is legacy.
Or that cloud-based planning and reporting platforms have somehow superseded what Excel can do.
So here is my challenge.
I’m going to show you a budgeting model created with Excel.
And then I want you to show me how you would deliver the same result using the technology stack of your choice.
Treat what you’re about to see as a mock-up.
Treat it as a sketch of the business requirement, if you wish.
Except it isn’t a mock-up.
This is a live, working model created with Excel.
And it is based on several real-life budgeting models that I have delivered for large organisations—models that have continued working for many years, through constant enhancements, modifications, organisational changes, workflow changes and changes of personnel.
So…
Here is the benchmark.
THE ORGANISATION
Imagine a large international organisation.
It has 400 bicycle shops.
Those shops are spread across 90 cities.
In 50 countries.
Across four regions of the world.
So we have several levels of consolidation and global reach.
And the organisation needs to compile its annual budget for the following year.
What you’re looking at here is the Excel budget template.
There is one master template, from which the budget holders receive their copies.
Let’s see how it works.
THE BUDGET HOLDER
The budget holder navigates through these cascading dropdowns to find the particular shop they’re responsible for.
They select it.
And they click:
GET.
The template is immediately populated with the current budget for that shop.
It may already be partially completed with numbers supplied by Group.
The budget holder then completes the budget in the normal way, by entering their numbers into Excel.
When they’re finished, they click:
PUT.
And the budget is saved.
If they’re responsible for another shop, they simply navigate to that shop, click GET, complete that budget and click PUT.
The same process works for all 400 shops.
And there is nothing particularly significant about the number 400.
It could be considerably more.
EVERYONE SEES THE SAME NUMBERS
Now suppose a budget holder needs to discuss their budget with their country manager, regional manager or somebody at Group.
They can do that in real time.
Everyone on the call clicks GET.
Everyone gets the latest numbers.
Everyone sees the same numbers.
There is no emailing spreadsheets backwards and forwards.
There is no wondering which version somebody is looking at.
There is no waiting for somebody to consolidate everything before management can see what has happened.
LIVE CONSOLIDATION
The country managers, regional managers and Group management will obviously want to see consolidations for their particular levels of responsibility.
That’s equally straightforward.
They use the same cascading dropdowns.
They select the level of the organisation they want to see.
They click GET.
And…
there is the consolidation.
Immediately.
Live.
There is no separate consolidation process between the updating of the individual budgets and management seeing the consolidated numbers.
It is already done.
And this isn’t rocket science.
The underlying methodology is remarkably simple.
GOVERNANCE AND ACCESS RIGHTS
But if hundreds of people are working with the same budgeting system, we obviously need governance.
We need authentication.
And we need access rights.
Anyone using this budgeting model enters their username and the access key or password they’ve been given.
Without the appropriate permissions, they cannot view or update the budgets.
And their permissions are restricted according to their area of responsibility.
A shop manager sees what they’re entitled to see.
A country manager sees their country.
A regional manager sees their region.
Group management can have access appropriate to Group.
So the system isn’t simply controlling whether someone can get in.
It controls what they can see and what they can change once they’re in.
MULTIPLE GROUP MAPS
Now let’s make the requirement more interesting.
Many companies I’ve worked with don’t have just one organisational hierarchy.
They have several.
So this model supports multiple group maps.
At the moment, we’re looking at the organisation geographically—by region.
I double-click this cell.
And now the hierarchy changes.
We’re looking at the organisation according to management responsibility.
Double-click again.
And here’s a third group map:
Legal Entity.
Different companies around the world may be incorporated as different local legal entities.
Now we can consolidate according to that structure instead.
I’ve included three group maps in this demonstration.
There is no reason why there couldn’t be more.
MANAGING ACCESS RIGHTS
Access rights themselves are managed by the controller.
The controller has a spreadsheet specifically for this purpose.
Click one button to refresh it and you see the current access rights.
Those permissions can then be edited directly in Excel.
Click PUT.
And immediately the access rights are updated.
If somebody leaves the organisation, remove their access.
They can no longer gain access to the budgeting process.
Simple.
DRILL DOWN
Now let’s return to our consolidation.
We’re in a Group meeting.
Management sees a number and asks the obvious question:
What is that number made up of?
Fine.
Select the number.
Here, for example, is the November figure for Asia.
Immediately we can see the countries in Asia that make up that number.
And we can see that they add up correctly.
But let’s go further.
Select India.
Then Chennai.
And now we’re down to the individual shops we were looking at earlier.
And those numbers include the budgets that have just been updated.
So we’ve moved from a Group consolidation…
to region…
to country…
to city…
to individual shop.
WHO CHANGED THAT NUMBER?
Now the CEO asks another question.
He’s looking at a number and says:
“I’m sure that number was different last week.”
He wants to know what happened.
No problem.
Double-click the number.
The system presents the history.
What changed.
When it changed.
What it changed to.
And who changed it.
But perhaps the individual number isn’t enough.
Maybe a cost increased because the corresponding revenue increased.
The CEO needs to understand that change in the context of the entire budget as it existed at the time.
So he clicks the appropriate historical version.
And Excel retrieves that complete version of the budget.
I’ve coloured historical versions pink deliberately, so nobody can confuse them with the live budget.
It is clearly identified as an archived version.
And, obviously, it cannot be updated.
NOTES AND EXPLANATIONS
Now suppose a budget holder enters an exceptional number.
Perhaps one month’s expenditure is unusually high.
Management is inevitably going to ask why.
So the budget holder can attach a note explaining it.
And perhaps they want to leave a note concerning the entire shop budget.
They can do that as well.
When they click PUT, those notes are saved with the budget.
When somebody subsequently retrieves that budget, the notes are retrieved too and displayed chronologically underneath it.
Anyone with the appropriate access can see them.
WHY THIS CAN KEEP EVOLVING
Most of these additional features didn’t originate with me sitting down and trying to dream up clever things Excel could do.
They came from clients.
Somebody suddenly has an idea.
“Could we do this?”
Yes.
Of course we can.
Because it is Excel.
It is agile.
We have control over how the model is designed.
We have control over how it is deployed.
And the business already has Excel.
Excel is provided to the business precisely to empower the business.
I can continue adding features to this model as new requirements emerge.
AND ONE MORE THING…
There’s another important requirement.
At the top of the budget is its completion status.
At the beginning of the budgeting process, the status is red:
NOT STARTED.
Once the budget holder begins working on it, they change it to yellow:
IN PROGRESS.
When the budget is finished, they change it to green:
COMPLETED.
Why?
Because the controller has another Excel sheet.
At the beginning of the process, all 400 budgets appear red.
As the budgeting process progresses, the controller clicks Refresh.
Some turn yellow.
Then they start turning green.
So the controller can see the progress of the entire annual budgeting exercise.
And if somebody is falling behind…
the controller knows exactly who needs a reminder.
NOW HERE IS THE CHALLENGE
That is the requirement.
That is the benchmark.
And I would argue that it is a perfectly reasonable representation of the requirements of a large global organisation conducting its annual budgeting process.
So if you say:
Excel cannot do budgeting…
If you say:
Excel cannot scale…
If you say:
Excel cannot do bottom-up consolidation…
If you say:
Excel is legacy…
Or if you say:
VBA is obsolete…
then here is my challenge to you.
Show me how you would deliver this.
Use any technology stack you like.
Power Platform.
Office Scripts.
Power Query.
Python.
Lambdas.
An enterprise planning platform.
A cloud application.
A custom web application.
Whatever you believe represents the modern alternative.
I’m not asking you to actually build it.
I’m asking for an outline.
Tell us how your chosen technology stack would deliver this set of requirements.
And please include some indication of:
- the technologies required;
- the licences required;
- approximate development time;
- approximate development cost;
- ongoing maintenance requirements;
- who would need to develop it;
- who would need to maintain it;
- how easily the business could modify it;
- how much could be changed in-house;
- and where the organisation would remain dependent upon external developers, consultants or vendors.
Ballpark numbers are perfectly acceptable.
BECAUSE HERE IS MY SUBMISSION
The model I’ve just demonstrated uses Microsoft Excel.
There are no external Excel add-ins required.
There is nothing proprietary added to Excel.
The user interface is Excel.
The small amount of programming involved is VBA.
And that is precisely why VBA is part of this challenge.
Because I repeatedly hear that VBA is legacy.
That VBA is obsolete.
That VBA has been replaced by Office Scripts.
Or Power Query.
Or Python.
Or Lambdas.
Or whatever comes next.
Fine.
Here is the benchmark.
Show me the replacement.
Not in terms of a list of features.
Not by telling me one technology is newer than another.
Show me how your preferred stack delivers this business requirement.
Once we have enough submissions, we can compare them.
Not simply on whether they can technically reproduce the result, but on:
Agility.
Scalability.
Maintainability.
Development time.
Development cost.
Licensing cost.
Ease of modification.
In-house capability.
User familiarity.
And the degree to which the organisation remains dependent upon external technology specialists.
Then we can publish the results.
And if somebody who has publicly argued that Excel or VBA is obsolete chooses not to respond, that’s fine too.
We’ll simply record that no submission was received.
And it would be useful if they explained why.
Again, nobody needs to actually develop the system.
We are only asking for an outline of how you would do it.
So there it is.
My annual budgeting challenge.
My standard response from now on to anyone who tells me that Excel doesn’t scale…
that Excel cannot handle enterprise budgeting…
or that VBA is obsolete.
I’m throwing down the gauntlet.
Here is the requirement.
Here is the Excel solution.
Here is the benchmark.
Now…
show me yours.
Add comment