All the best practice publications say spreadsheets must flow from left to right, must be well documented, input areas and output areas clearly identified, some of the older standards even specifying what colors must be used.
None of them mention Future-Proofing and Scalability as a desirable design goal.
So, spreadsheets become a mess when the needs of the future require modification to the spreadsheets. Either the users cope with it by manual adjustment (insert columns, overwrite formulas etc) or introduce complexity by technically risky workarounds (external links, pyramids to consolidate etc).
We know this mess is real – a section of the IT industry has grown into a $50 billion market value on the back of this. (see War of the Alt-Excel)
Why we should care
Shouldn’t future-proofing and scalability be at the forefront of ‘best practice’?
(Note that the ICAEW Twenty Principles of Good Spreadsheet Practice has Principle No 8. But it deals with data in cells needing to be updated in the future, rather than the model itself needing to change in the future)
THOUGHTS FOR A FURTHER BLOG …
Another design principle should be to take into account that the spreadsheet is part of a larger information process in the organisation, rather than ‘a single person’s workload that impacts on no one else’ (Peter Bartholomew pointed this out c. 2/12/2019 LinkedIn)
Greater productivity is achieved by recognizing workflow as workflow. This is helped by a centralized data model.
Now that the concept of a Data Model is not alien to spreadsheet work (since Power Pivot was introduced), why is a local DM preferred to a central DM that we could already do?
OBVIOUSLY, TO BE CONTINUED …