Data, calculations and reports all mixed together on the same worksheet. This is the single biggest source of frustration and inflexibility for business users of Excel.
The typical scenario goes as follows: A worksheet contains some data; the user needs to see calculations based on the data, adds new columns or rows and then creates a formula. This mix of data and calculations is then used as a report to manage the values and share with other people.
Below is a typical example of a workbook that is used to plan production capacity. It carries a mix of calculations and reporting, keeping the data captive and static.
The problem is that this sheet is fixed to only perform the original calculations. If a user wishes to view the production schedule to the day or to the month, they have to create another sheet.
Once this sheet is sent to another user, and that user makes changes to data, then they now have two versions, which is the correct one? To distribute reports, they distribute copies of the data which can be changed again. Very soon, no-one has a correct copy and uncertainty reigns.
A better way is to separate data, calculations and reporting. The data is pulled from a single source: a database, or more simply a text file import. Each user pulls from the same source of data. This is the “single source of the truth”, cherished by all operations and IT professionals.
The calculations are performed on dedicated worksheets and workbooks. The reports are generated from data tables that enable different slice-and-dice, charting or presentation formats.
These design principles provide a strong foundation to the system. All users are working from the same set of data, the same set of facts. A controlled group of planners and specialists perform calculations and then distribute the data using reports without exposing the calculations to unauthorised change.
The robust and reliable performance of good software is not limited to expensive software development. Anyone can follow the Fast Excel Development Method to develop systems that have these same qualities.
Here is a short video tutorial that shows how the workbook shown above is improved using these techniques.
Read on to see how these design principles support the fast implementation of planning logic by enabling users to define a calculation once and instantly apply it to thousands of rows of data.
Next: Fix the calculation logic in a single place and apply it to thousands of rows.