Rummaging through some old hard drives, I found this I had written on 25/02/2013. I don’t know if was meant as a script for a video, or what. But it can now be!

ExcelOMG! – Taming the runaway spreadsheet model

We all know it.

It started with one person quickly creating a new worksheet to get his work done. Soon it grew into a megalithic monster, spanning several departments, dozens of users, thousands of external links to summarise data, if not millions, copies of the spreadsheet emailed to everyone and his dog. A nightmare to ‘roll forward to next month’ and as many versions of the truth as there are proliferated-offshoots of the original spreadsheet as can fill the network drive.

There, I said it in one paragraph. Usually the bods that specialise in errors in spreadsheets devote an entire conference to it.

The result?

If IBM or SAP had their way Excel models are out, replaced by their megalithic enterprise solutions that may be up and semi-opersational, hopefully the year after next.

The business user does not want IBM or SAP obstructing their work. So what can he do?

Conventional wisdom says the reason for all the mess is the huge amount of manual work that goes on, in and around the spreadsheet models. Conventional wisdom says ‘automation’ is the answer.

But what? What are we automating?

Let’s cut to Henry Ford for a moment.

Henry Ford is reputed to have said “If I listened to my customers I would have built faster horses”! That may sound somewhat arrogant, given that an entreprenuer who has not listened to his customers, and listened well, is an entreprenuer who will fail. Right?

But he has a point. At least when it comes to the runaway spreadsheet model, he has a very good point (though I’m sure he wasn’t referring to spreadsheets when he said it).

Let me explain.

Most efforts at automating manual work go something like this. Turn on the macro recorder while you do the repetitive manual work. Stop recording when it’s finished. Tweek the code that Excel wrote. Hit the run button. There you have it. Automation.

Wow! Now we have horses that run 1,000 times faster! We got faster horses, Mr Ford. What a breakthrough!

Ok, perhaps I’m being a tad unfair. I’m being cynical about something that happens a lot, routinely, in the Excel world. And I don’t want to sound as if cutting down manual work is a bad thing. It is a good thing. Definitely.

But I’m also trying to make – better still, demonstrate – a very important point here.

And the point is, we don’t have to do it that way anymore! We built the model because we understand Excel to be a spreadsheet.

But, the spreadsheet is no longer just a spreadsheet. It became what it is more than 10 years ago. We don’t need the horses anymore. The horses were replaced by the invention of the motor car. It is a new paradigm.

The New Paradigm

This is easier to show than it is to describe. However, let me try.

Let’s take 2 common examples.

Example 1 – A monthly forecast/Rolling column headers model

A monthly spreadsheet that has new items added, that has columns that have the next six months as headers. An example is a forecast. The totals appear at the bottom and the SUM formulas show that. The manager has a summary of the dozen or so such spreadsheets that are maintained by a dozen pleople. It all works fine.

It works fine except at month end. At month end someone has to, very carefully, ‘roll forward’ the model to the next month. One would think, usually this task is delegated to the person that most people do not like – because it is not a task that one would choose to undertake, if one had a choice! The task must be carried out without a single mistake. All the external links must continue to work, and the first sign of #Ref or #NA should indicate something really horrible coming up now or later in the model.

If that’s not enough to put anyone off, before long there will be a new requirement. Why? Because business, like many things, does not stand still. As business changes so does business requirements, and then so must the spreadsheet model. What does this mean? It means, new columns need to be inserted perhaps, new product codes need to be accomodated, and did anyone tell you there’s a new division just opened and it also needs a new spreadsheet. The links must add all this new things into what they are already adding up. More work for the guy most unpopular in the department. He gets fed up and leaves. And now a new person has to be shown what to do.

Let’s summarise.

The model has dozens or hundreds of sheets. Is maintained by dozens of people. They are all linked up by links. Monthly roll forward is a major pain. Inserting new products (columns) or departments (sheets) is a major pain.

And now they are talking about automating all this mess!! Aaaaaaaah!

What you get from an automated mess, is an automated mess that’s even more complex than the old manual mess. Ask anyone who has been there done that.

Instead how about this.

The model is just one sheet. Yes, that’s right. Just one sheet. So only one sheet to manage, for example if a new column is needed. And nothing to do if a new department is needed. Bear with me…

The manager clicks a button, and within seconds the entire model is ‘rolled forward’, totally accurately, to the next monthn. You see nothing visible when this happens. The one sheet remains exactly as it was, yet it is now the next month, headed up with the next six months in column headers.

Total number of external links, that was needed to summarise the hundreds of departments – is nil. That’s NIL as in ZERO (zilch, nada, none). There are no links to break. The managers summary sheet is exactly as he had it before, only difference is, there are no links. And, he’s happy the figures are all correct.

How does he know that?

The flag next to the total, or the cell itself that’s the total, has turned green to indicate that the total he is looking at has been independently double checked by the model.

Example 2 – a budgeting model/summarised into sub-groups/business units

This is the familiar spreadsheet which starts out as a template issued to 100s of department heads for completion. An example is an annual budgeting exercise. The model is built under extreme time pressure, is out there for completion for a few weeks, and then a mad panic to get the results reported by a deadline. The issuing deadline is as critical to meet as the reporting deadline.

Typically we have a worksheet template. It is populated with actual data from the enterprise system. It is populated by data from HR, where people’s time is billed this data may go into quite considerable detail, perhaps by individual/grade/charge-out-rate.

The templates are linked into Business Unit subgroups, regional subgroups, all rolling up to the top level.

A nightmare? Yes.

Why?

For a start, when this model should start being built none of the information that’s needed (for example, the eventual group map for next year) exists. The actuals from the Departmental P&L, the staff data, won’t be available right now. All this data needs to populate the templates. That takes time. A lot of time. If the group map is different to what we started with (ie. Different to last year) the links will need to be changed accordingly. Sod’s Law says, if this didn’t happen during the model-building phase then it will surely happen during the reporting phase!

What all this means is, some poor bugger has to survive several nervous breakdowns during this process. The model has to be built without a single mistake. Just one mistake and the whole purpose of the model is lost – the figures are wrong. Someone is in for the high jump. With several million formulas, and data, and links, what are the chances ….?

So, ok we can automate some of that, that is, make the making happen faster. But we will still be doing the same thing, only faster. The result is still the same mess, but now, built faster.

In other words, once again, we have achieved faster horses!

Instead, consider this.

Rather than this, let’s say we do none of that. Instead, we describe everything by data.

Yes, data.

We develop a template, but we put nothing on the templates.

We collect all the HR data, and get HR to check that it is correct. We hold that data somewhere approapriate, like an Access database, or SQL Server.

We do the same with the actuals from the departmental P&L. Similarly, anything else we need, we simply hold that data in our database, for example data from the asset register, overhead absorption rates etc.

The group map is also described, and this typically is a heirarchicla map. Again, this can go in a flat table.

Then, and here’s the trick, on the day the model is issued, we hit one button and Excel creates the budget sheets, and populates them with the data already verified. If passwords are needed these are also put in. If the sheets need to be saves in specific location the Excel process does this also.

But there are no links to summarise. Not one.

Instead, the users click a button to indicate that they have finished. If they need to modify their figures they can click finish again, until the deadline.

Summaries, top level? Sub groups? Business Units?

The manager(s) have a separate template, identical to the ones distributed. There they have a set of nested dropdowns, from which they can select any level in the group, including at the lowest departmental level – the level they are entering budget data.

They click GET or REFRESH and the summarise figures come down into the summary spreadsheet. Magic.

No links at all.

Nothing to break.

And, they have a conditional formated cell that confirms that the figures have been cross checked.

So where have the horses gone?

As you may have gathered, to achieve this we did not just ‘make the manual process run faster’.

At the heart of it, there is a radical paradigm shift. But it’s only a shift. Just another approach – not more complexity, but less. Not more risk, but less. More managable. More extendable, way beyond what I described above. Do far more than we would have ever attempted on the manual model, or an automated version of the manual model.

And it is this. I have already touched on it.

The data is saved in a database, such as Access or (better) a SQL Server.

Why woul dit be less complex?

The simple answer is, when we create these models in Excel, sure we are using the enormous power of Excel for what it’s good for (good) – but we’re also trying to do with Excel things that Excel, and the spreadsheet concept, is pretty hopeless at (bad).

The ‘data’ that comprises both the budget model, and the monthly forecast model describes above, is best kept ‘structured’ and in a place where the enormous power of data processing can take place – and that’s in a database.

A relational database does VLOOKUPS natively – they are called relations. It can modify millions of rows of data by some criteria in seconds. It can summarise by any level with just one simple command – the same, if we rely on Excel, will need milllions of external links; that can and do break eventually, at some point (usually it’s just before a board meeting, Sod’s Law).

We are using data to drive the model, not hard-coded links. We are using data to help Excel programmatically populate the templates. We are getting Excel to keep us from doing any of those things that (invariably) make us make mistakes.

But hey. Like I said, words words words!

The meaning, the sheer force, of what I have described cannot be grasped when described in just words. It has to be seen, to be experienced.

Therefore, head over to the two demos I have setup. They corresond to the two examples above.

You may be near one of these ….

• An Online Video Demo
• Live Demo

Closing

A spreasheet model starts timid, but soon turns into a ferocious animal, often due to its very own success. The complexity of manually maintaining it is only eclipsed by the even greater complexity of automating it, usually by external contractors. The latter scares more people, particularly managers who eventually find themselved left holding the baby when an initiative to automate has gone tits up, the contractor who built it has long gone, and a new one has to start all over again.

The answer lies in a paradigm shift.

That means, what we need is the motor car, driven by an engine, not faster horses. Henry Ford said it first.

The paradigm shift described in this article comes from combining Excel and a backend database seamlessly. With that we have centralised data. One version of the truth.

The skill level to achieve this is well within that of the average Excel intermediate/advanced/power-user – every department has one, at least. The methodologies are self documenting. The results reported by the models are independently verifyiable, thus giving confidence to those presenting those reports to top level, board.

(11:47 read very fast)