Adding Value to the Organisation with Microsoft Excel – A Whitepaper

Introduction

Excel is not just a spreadsheet. Though it began as one way back in the 1980s.

In this article we look at Excel with fresh eyes. We examine how, by reimagining this ubiquitous software tool, we can make a significant difference to organisations, and those who work in them.

Who this is for

This whitepaper is for anyone who can gain from increasing productivity in the organisation. Principally there are three groups.

• Top Management
• Consultants advising Top Management
• Anyone working with spreadsheets (eg. in the accounting function)

Defining Value

Say you worked for a shipping company in the 1800s. You operated a route from the West Coast of the the New World to the East Coast, your ships would need to go all the way round South America. The ships would need to make several stops along the coast for provisions, and the voyage would typically take two weeks or more.

Now here’s a bright idea. If a canal was built in Central America between the Pacific Ocean and the Atlantic Ocean, the journey time would be reduced considerably. Thus saving, not just time, but cost – and also reducing risk, as many ships were lost sailing around Cape Horn. Many more types of cargo can now be shipped, such as perishable goods, as the journey time is now shorter.

Sailing through a canal (where Panama Canal was built in early 1900s) will have added value – not just for your shipping company but also for the entire shipping industry.

Transformations such as this add value to the organisation. In turn, this adds value to those that work in the organisation (relieving bottlenecks reduces stress, for example). And those that come up with such simple but effective ideas are rewarded both by achieving recognition, and acknowledged to be value-creators in the business. To most, this will result in higher pay and a permanent boost to the career.

The Problem

Spreadsheets are ubiquitous. Despite the media headlines, spreadsheets are not a problem.

When a new task, such as a budget, requires a spreadsheet to be created there is always someone in the office who has the competence to create one. Within minutes. It will do the job.

At least for now.

But, very soon events and requirement dictate the need for modifications and extensions. Our spreadsheet is adopted as a template for all the other departments, or product lines. Managers need to summarize the data, and so links are created between workbooks. In time, these become unmanageable. Errors appear when the fragile ‘pyramid’ of spreadsheets start to crumble; usually at the worst possible time, according to Sod’s Law.

What began as a Cute Little Puppy has now become a Monster.

We cope with it by various workarounds. We either resort to manual copying and pasting, or try to shoehorn new requirements into a spreadsheet design that was never properly thought through. We email spreadsheets to each other; thus spawning dozens more copies of them. We get accused of creating several versions of the truth. The list that defines the Monster goes on.

But, with all that hassle and stress, and risk, particularly at bottlenecks such as the Month End, we soldier on – it’s part of the joy of working with spreadsheets. It’s may be a Monster, but it’s a welcome one. It’s a Happy Monster.

We fight to retain our Happy Monster. What most people don’t realise is, and you’ll see below, the Monster is optional. It doesn’t have to be this way, as we can be (if we want to) much more capable with Excel just by slightly adjusting the way we think of spreadsheets.

As you’ll see. We get our Cute Little Puppy back!

The traditional ‘solutions’

To understand, and to quantify the real value of ‘thinking differently with Excel’, we need to take a quick look at the solutions that are currently promoted as ‘escape from Excel Hell’.

In the popular narrative, all of these are presented as ‘replacement to Excel spreadsheets’.

• ‘Your need has outgrown (mere) spreadsheet. You need to upgrade to Access/IT developer project’.
• ‘Your company needs to get rid of spreadsheets and replace everything with an ERP system’.
• ‘Excel can’t handle it – you need to replace it with our cloud solution’.

All of these propose replacing Excel. This is exactly where the proposition fails. (We like the Happy Monster, remember?)

Why the traditional solutions do not work

In the case of ‘upgrade to Access/IT-developed’ solution, very soon a small modification will invariably be needed. But the originally developer cannot be found, or the small modification is deemed not cost-effective. Then it’s abandoned and the process reverts back to spreadsheets. Replacement has failed.

In theory ERP is a great idea. The entire organisation, globally, working on a highly-customizable system. In reality though, in any organisation, there are several enterprise systems. They don’t talk to each other, they were implemented by different teams, at different times. When companies merge, or are taken over, the legacy IT systems come with them. All that adds to IT and data silos. The only reason businesses run at all (amidst this) is … spreadsheets! We bridge the gap with the Cute Little Puppies we create. So, rather than eliminate spreadsheets, the enterprise systems increase the demand for more spreadsheets, quite the opposite of the marketing message. Ironically.

The Cloud Alternatives to Excel is the same. You need Excel spreadsheets to process the inputs that enter the cloud systems, and also you need spreadsheets to process the output. And to bridge the gaps between the enterprise systems (in-house) and the external cloud solutions. There’s a long standing joke: ‘what’s the most commonly used function of an ERP/Cloud solution?’ Answer – ‘export to Excel’!

So, replacing Excel is not viable. Any suggestion that it is – is a myth. (See for example, ‘Nine Circles of Excel Hell’ a whitepaper by cloud alternative vendor Adaptive Insights, and our insights on the topic ‘War of the Alt-Excel’ TBA)

The practical solution that works

The practical solution proposed in this whitepaper, ironically, borrows from solutions that do not work (above).

However, it’s achieved with Excel, and by making a small adjustment to the spreadsheets you already have.

It is one simple idea. The next section introduces the many use cases where this one idea removes bottlenecks and increases productivity organisation-wide.

Here’s a short explanation.

On a spreadsheet we do four things. (1) We have a reporting layout, (2) we have the logic/formulas, (3) we have the User-Interface (that is, how we interact with the data/logic) and (4) we store the data.

This is fine. But the one thing that screws up everything is having the data stored in the workbook. Why? This will become clear when we look at some examples.

But for now, the missing piece of knowledge (evidently, for most people) is this.

How do we separate the data from the spreadsheet? How do we do that in a seamless way? Wouldn’t that be an extra layer of hassle/risk/cost?

No. The easiest way to answer that is with a small demonstration. (see The Fantasy Band Demo)

The Proof of Concept

The Fantasy Band Demonstration: In the live sessions, we demonstrate this with the participation of the those audience members who have laptops with them. You can see a video of the demonstration here (TBA).

There are three spreadsheets. The diminutive size of the spreadsheets is an indication that this idea is simple.

One spreadsheet has a list of Musicians with the Band they are in, what Country they’re from, and a numerical value for illustration (eg. Age or height!). The second has a summary by band, showing how many members are in each band. Third has an In-cell drop down, which shows a list of the bands. When you select a band and click a button ‘Get’, the sheet fills up with a list of members of the selected band.

Normally these spreadsheets will not have any influence over the others, as they are on separate laptops that are not aware of the presence of the others.

But the demonstration shows that updating the data in one updates the other spreadsheets. Right here and now!

This may not sound unusual, until you realise that there is no connection between the many laptops in the audience, and with the presenter’s.

Note that this is not the same as Google sheets, or Office live, where the same spreadsheet is shared by many people. This is, many different spreadsheet models sharing the same data.

Of course, this works because the data these spreadsheets are sharing is stored outside the spreadsheets, in some central location that all these spreadsheets can reach.

It may not be immediately obvious how this can make any difference to the Happy Monster. The series ‘Reimagine Excel: From WTF to OMG!‘ introduces the common issues that comprise the Happy Monster, such as the mess we create when we have external links, issues of sharing workbooks, and the hassle of rolling forward rolling forecasts. (see The Dirty Dozen) The solution in each of these rely on the simple idea of separating the data from the spreadsheets and making is centrally accessible.

The highly-visible difference this makes is (1) fewer user-interactions (2) elimination of unnecessary proliferation of spreadsheets (3) fewer moving parts thus increasing robustness (4) recognizing the true nature of collaborative processes and (5) future-proofing and scalability.

(Along with less-visible benefits, such as audit trail (who changed what, and when), customized security for sensitive data, and more. Benefits that are hitherto unheard of when we talk about spreadsheets)

In short, there is less labour-intensive work, and the process is more robust.

We have achieved a transformation, not by replacing Excel spreadsheets but by re-engineering our existing spreadsheets.

Value Revisited

The organisation benefits by achieving increased productivity across the organisation. This is the same promise made by the $millions IT systems, but we have achieved it with the technologies we already have. Thus saving $millions capital expenditure, and a lead time of at least two years involving many people both internal and external. And we have results within weeks, if not by Friday!

With this idea, you can experiment non-destructively. Your people can learn the principles as they apply the ideas to their own messy/troublesome spreadsheets.

Soon, the ideas spread across the organisation as more and more departments experience the ease with which they get through the daily work they do on their spreadsheets. Champions emerge. Culture changes.

As for individuals, those that lead gain recognition. Joel Garfinkel in his book ‘Getting Ahead’ encapsulates this in the acronym PVI – the individuals will now be perceived as value-creators, they will be more visible, and they will have greater influence. And their managers, likewise.

This will encourage buy-in from the whole department. Who doesn’t want to be in a winning team?

You have achieved, with Excel, improvements that both the in-house IT people and the external IT vendors consistently fail to achieve. And your intervention has a positive impact on the entire organisation.

More

• Some Examples of its Application – From WFT to OMG!
• A plan for ‘EUC remediation’