I know I come up with some weird illustrations. But often ‘outside-the-box thinking’ can’t be properly communicated without some weirdness. Even though the thinking itself is perfectly non-weird and ordinary.

The topic buzzing inside my head right now is the contrast between ‘modelling’ and ‘doing manual stuff’.

So, this is a rant about the tendency to call something ‘modelling’ when actually its mostly ‘manual manipulation’. How is ‘manual manipulation’ modelling?

Let me try this illustration.

Let’s say you have a convertible. For those who’re not into cars, a convertible is a car in which the roof can be opened. Like in the picture above. (that’s me with my kids in …. yes, well it’s obvious!)

Usually, when you need to take down the roof the only manual intervention involved is that you have to unlock some lock above you and then the electrics of the car smartly open the roof, and it gets securely fastened at the back.

Imagine a car in which, to open the roof you have to use a metal cutting tool to separate it from the car. You leave the roof in your garage and drive off. To refasten it, you take out your welding apparatus. Even if these tools came with the car – this is not a convertible!

Group Map

When we consolidate spreadsheets with links (aka. a pyramid of spreadsheets) we have hard coded the links to individual cells on separate sheets/workbooks, according to a group structure.

In reality, group structures change. And the model has to change to reflect the change.

(they change when part of the structure has to leave the hierarchy when that part of the group leaves the group, or when a new company joins the groups with it’s own group, when parts of the structure move to another part etc. Another change is when several new group reporting structures need to be reported on, as well as the original main one)

When these changes happen there is no choice but to un-link the links (ouch!) and re-link the links (ouch ouch!).

This is exactly the same (figuratively speaking) as cutting off the roof of the car with a metal cutting tool! It is as messy. And risky, And your neighbors will think you’re nuts.

Is this modelling? Or manual annihilation of a model?

Instead, to be a model, the model should be capable of these adjustments simply by describing the structure (by data), and changes effected simply by modifying the data to reflect the required change.

This video illustrates this @ 8:08 Group Consolidation.

Related Articles

ICAEW ‘Twenty Principles of Good Spreadsheet Practice’ deals with this in Principle No. 8 ‘Design for Longevity’ (see below) But it’s confined to changing values on spreadsheets (obvious?). I don’t understand what is meant by ‘simplest thing that could possibly work’. I hope they don’t mean ‘removing the roof with metal cutting tool’ as the simplest thing. (I shall follow this up with the ICAEW and report back).

What does ‘Design for Longevity’ actually mean if we ignore the main reasons for the lack of longevity?

Rant over.

Hiran de Silva

View all posts

1 comment

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

  • Maybe the ICAEW ‘Twenty Principles of Good Spreadsheet Practice could make an insightful video teaching series