Here’s a tool for assessing the complexity of your Excel spreadsheet model.
If your model is complex, as a spreadsheet design guideline we recommend separating these three key features of Excel:
- Data Storage
The more tightly coupled are the reporting, calculation and data storage, the more unwieldy and fragile your spreadsheet will be.
The criteria we use for measuring the complexity include:
- Number of Users: How many people need to collaborate using the spreadsheet?
- Process Complexity: How complex is the business process that is supported by the spreadsheet?
- Calculation Intensity: How much number crunching is performed by the model?
- Data Volume: How much data is contained in the spreadsheet?
- Data Change Frequency: How frequently does the data in the spreadsheet get added, deleted or modified?
- Data Model Complexity: How many different topics are included in the model and how complex are the relationships between the topics?
- Analytical Intensity: How much slicing and dicing of the data is required?
Take a look at this list before starting your spreadsheet model and as you expand it. A model typically increases in complexity over time, and if you want to avoid re-building a model because it has become too fragile, consider the basic spreadsheet design guidelines here.