Just HOW poor are spreadsheets for business challenges and planning?

I have often hinted – or more than ‘hinted’ – that spreadsheets are not fit-for-purpose for time-based planning, challenges or initiatives. 

… so I tried a small exercise to see just how poor they actually are. (Catch a video explaining the issues here)

I have previously talked about a model built with a client about the launch of a consumer tech-product. Well I built a spreadsheet that exactly matches that model – at least, a part of it.

What I found truly shocked me. I am so used to working with digital-twin models, I had forgotten just how poor spreadsheets actually are for time-based modeling (likely the most common use for business spreadsheets?) 

Here’s what the model needs to cover …

Figure showing the issues a model needs to cover. The spreadsheet deal with consumers, repeat-purchase, pricing, marketing spend, sales, revenue, costs and profit. It does not deal with affordability, value-for-money, customer losses, learning-curve cost reductions, retail price mark-up - or any of the corresponding issues for the competing product

… and here’s a screen-shot of the spreadsheet, showing 2 scenarios covering the green items above – a further handful of columns are to the right. (Yes, it could be slicker, but with little added value).

screenshot of the spreadsheet model, showing 2 scenarios and covering only the first set of issues in the previous figure - plus 2 time-charts showing the growth of sales and profits

I knew that even this task would be tedious. But the spreadsheet model is hopelessly poor:

  • The spreadsheet model is impenetrable – you can’t see what causes what. (Sure, you can use that clunky ‘trace precedents’ tool, but you have to choose which items to trace and any more than a handful becomes a mess.) You can’t see how changes happening to 2, 3, 4 or more items flow through the chain of causality to explain outcomes (Yes there’s that sparklines thing, but that’s clunky too and they don’t clarify causality
  • The spreadsheet is error-prone. Accidentally off-set some cell reference and the result may look OK while actually being plain wrong, Endless research finds that fundamental errors in important corporate spreadsheets are rife – prevalence is anything from 85% up.  
  • The spreadsheet can’t capture the full scope of this case, nor of any significant plan, challenge or initiative. Well, the technology could do so in principle, but the human brain simply couldn’t construct a valid causal structure when all it has to work with is rows and columns of numbers. The spreadsheet I built does, for example, handle how word-of-mouth might drive faster purchase of the product, but linking that to value-for-money, changing affordability, experience-curve cost-reductions – and all the same issues for the competitor – is mind-bendingly complicated.
  • It’s really hard work. You are having to work with column/row reference codes and constantly check that those codes refer to the right item. Yes you can freeze the column titles, but if a formula links to things off-screen or on another sheet, it is still a struggle,
  • … so there’s no way you could ever build a substantial model with the leaders or teams who need them in real time. You have to do workshops or 1-to-1 meetings with key people, then go away and work up the spreadsheet, then share it back with them and hope they trust you got it right.

Of course, the opposite of all this is true for a digital-twin dynamic business model:

  • it’s transparent, so you can always see what causes what and why;
  • it protects against errors by using physical causal links and using natural-language items and formulae;
  • all of which makes it easier and faster to build;
  • it can be easily extended to a wide scope, encompassing multiple business functions
  • … and it’s easier, faster and transparent, you can build models with leaders and teams in real time.

Here is a picture of the model, covering all the issues in green above.  The full model, covering all the yellow issues above as well, is about 2x the size. 

screen-shot of the digital-twin model, with time-charts on all key items. Arrows show all causal links between items. This shows clearly: 
- how marketing and word-of-mouth drive the capture of potential consumers 
- how these drive 1st-time sales and repeat-purchase 
- how total sales and changing price drive growing revenue
- how falling unit-costs change gross margin and drive growth of gross profit
- how changing revenue, gross profit and costs drive growing operating profit

Adding competitive interactions would be about 2x larger again … although we don’t actually have to do that. We can simply “call” a second instance of the same model and flex it for the few competitor-specific factors, like marketing spend and price.

In fact, we can model interactions with multiple competitors the same way. That way, we only have to build a single model, which is easily validated as we go. 

(Here’s that video again, explaining the issues)

Leave a Reply

Your email address will not be published. Required fields are marked *