Why a hot tub experience which went wrong…

A bit of a longer blog this week, but I want to communicate a story metaphor which I trust will help you to 'have your cake and eat it' when it comes to using Microsoft Excel as a flexible system to support your reporting and forecasting in a changing, pandemic affected 2021.

Last year like many other people, my holiday plans were cancelled and I had to stay at home. My wife suggested that we spend some of our holiday budget on an inflatable hot tub for home. I was a little sceptical, but given that 2020 was a 'milestone' wedding anniversary for us, that's not the kind of request that a wise husband turns down.

How wrong I was to think that this would be a waste of money!

We spent many a happy evening in the late summer sat in steaming bliss on our patio, sipping drinks and engaging in conversation without an electronic device in sight. The temporary hot tub was packed away when the good weather broke.

We realised that the chill of the air temperature didn’t matter, so we resolved to get it out again with the whole family in the Christmas holidays. But, being sensible people who know what the British weather can do, we had the foresight to buy a collapsible gazebo to go over the hot tub, just in case of rain or snow. Based upon Amazon reviews of 'cheap' Gazebos, we spent a bit more money to try and get one with solid supports and advertised as waterproof.

Although our Christmas plans to gather the whole family were not possible due to COVID rules, we still got the hot tub out. On New Year's Eve we sat in the hot tub, sipping champagne and protected from the drizzle from our newly erected gazebo while the clock ticked past midnight. A truly memorable experience!

Looking at the early January weather forecast, we noted that it was going to turn colder but we were not going to have much wind. So I left the hot tub and gazebo out for further use.

Now this is where the story takes an unfortunate turn.

Whilst I had complied with hygiene protocols and emptied out the hot tub water, we were back to work and the busyness that entails, so I left the gazebo up. I had firmly secured it to the ground in case of wind and it seemed like a faff.

I didn't however, check the precipitation forecast properly. A week into the new year we had a little rain. And then it snowed. Not a big fall, but around 2cm lying on the garden.

It turns out that a "little rain" creates a pool or water in the sagging roof of the gazebo. It also turns out that 2cm of snow falling on a sagging gazebo roof forms a large ball of icy slush that weighs several kilos.

And several kilos of icy slush is more weight than an mid-range gazebo frame can bear…

When I surveyed the glorious sight of my garden winter wonderland I also had the tragic sight of my broken gazebo. Panicked, I rushed outside to see what I could do. After manically removing the gazebo's roof and sides to get rid of the ice and snow, all I achieved was a pair of frozen hands.

The gazebo frame was broken beyond repair.

Of course with hindsight, what I should have done is taken the gazebo down at the first sign of anything more than a drip from the sky. Or thought more strategically and constructed a more permanent roof over my patio.

But having done neither, the solution that worked for a magical temporary experience proved inadequate for the rigours of long-term use in a changing world.

As I returned to InfoSuperstar work mode last week, this struck me as a useful metaphor for how many people use Microsoft Excel. And by that I mean me. And probably you!

We have a brilliant idea. Or we need to forecast and model something quickly. So we create an Excel spreadsheet to report data or create a budget/forecast.

It works and we get the job done. But, just like my hot tub experience, we want to do it again. Like a gazebo, we extend our worksheets and cell formulae, to cover us for the changing circumstances. It might still work for a while.

But then more changes are required because the in a global pandemic the world is changing and our businesses are changing.

Management reporting requires flexible analysis and we have up update our forecasts almost constantly. Annual budgets and the cell formula-based worksheets that support them come under strain.

And it doesn't take much to break both our spreadsheets and the finance teams that are responsible for using them.

Historically when this has happened to me, I've felt I had two choices. I could invest a large amount of time in re-writing and maintaining an increasingly complex set of worksheets. Or I could invest a large amount of time and money to develop or purchase a more permanent 'database' application to support reporting and forecasting.

If times were good, I might get approval for the big database project.

But often, money was tight and I had to just continue to suffer 'death by spreadsheet'. Sometimes, as an confessed spreadsheet geek, this was perverse mix of pain and pleasure!

Does that sound like your world too?

Surely there's a better way?

It's possible to continue to use Excel and the flexibility it offers. But also to harness the power and robust data processing of a database application to avoid the inherent resilience issues with cell formulae.

There's no magic bullet, but in recent years Microsoft have developed Excel so that it’s now a long way from the row and column worksheet spreadsheet that was its original scope. Sometimes you should implement a major database/software project. However that's not the case in an increasing number of situations because Excel, and it's sibling product Power BI, now include advanced data handling and reporting functionality. Ten years ago you had to spend a lot of money on servers and enterprise/business intelligence software to get those features. Right now, every current Excel user has a lot of those features on their desktop.

But most people are not using those features. I suspect it’s because they don’t even know they are there, or don’t understand the power at their fingertips. They continue to invest huge amounts of time reporting and forecasting based upon the fair-weather support of cell formulae. Is that you?

I've spent the first couple of weeks of 2021 working face-to-face online with clients in my "InfoSuperstar Accelerator" programme, to help them unlock Excel and Power BI to save time and increase their organisational flexibility and data robustness.

You don't have to sign up for my courses or my accelerator programme. But I believe you’d rapidly transform your ability to produce meaningful and flexible reports and forecasts if you did. Feel free to message me, or even book a chat using this link if my hot tub gazebo metaphor rings a bell with you.

In the meantime, if you're not yet ready to chat with me, how about I help you 'one small step' a time...

My past few videos/blogs talked about using table formatsand data validation. Some of my subscribers clicked through and watched these. If that was you - thanks! Why not let me know what you thought?

If it wasn't, then why not click on the links above while you take a quick coffee/ tea break? I hope you'll find each post as 5 minutes well-spent.

I'm suggesting you take a look at these because, building upon them, my next blog post is going to show you the Excel gateway to a whole new world of robust spreadsheet data transformation…

Are you ready to head to a whole new world? Look out for the next blog!

Adios Amigos!
Andy Biggs