When building complex models, the question of repeatability often crops up: if I handed this over to someone else tomorrow, could they use it effectively? After all, there's not much point in you pouring countless hours to create a technical work of art only for it to be replaced by something more manageable in a few months time.
This is where model schematics come in handy
What is a model schematic?
Put simply it's a diagram that shows the most significant components of the model, and how information flows from each constituent part. A user of the model should be able to use this schematic to understand how the model has been put together, and how they need to navigate through it.
Example model schematic
The above example shows one (of many!) ways this can be done. In this case, each tab in the workbook is colour coded to show clear differentiation between inputs, processes, and outputs.
What are the advantages?
Your organisation won't be reliant on you to run the model
One unhealthy habit I've seen analysts repeat time and time again is being "the only guy/girl" who can run a particular model/process. This becomes risky for the business (what if they're ill?) as well as risky for the analyst (it consumes their time). A model schematic should help anyone else in the organisation to quickly understand the inner workings of what you have built, which helps them get the information they require, and helps you to look like a hero
Designing the schematic up front forces you to think about structure
Before doing anything in Excel, I often sit down with a pencil and paper, and plan out how I'm going to calculate things. Visualising these calculations as part of a model schematic will force you to identify where errors in calculations may appear, where the data flow you have defined doesn't make sense, and help you to anticipate additional data/input you may need.
Making corrections to your thinking this early on will save LOADS of time that you may have otherwise wasted in redesigning tabs and overwriting formulas
Simple model navigation
In examples like the above, I often hyperlink my models, so it becomes a one stop shop for new users to easily navigate around the model. This becomes very important in analyses such as scenario analysis, where there may be dozens of tabs and hoards of assumptions. Think about what you would want to know if you were handed a complex model? How to navigate it to find the right information may well be high on your wish list.
What should I watch out for?
Keep it simple!
Schematics should rarely show every calculation and data flow you've put into your model. It should simply demonstrate to users the key things they need to know in order to understand your model construct.
Pick a schematic to suit your audience
Think about the most likely users of your model - are they marketing gurus who want to identify customer KPI's quickly? Are they Finance Analysts who want to decompose a P&L down to the minutiae? Design your schematic to suit your end user
Cant visualise it? Think again
If you're struggling to draw your model schematic out in a legible form, it may be because the flow of data through your model isn't logical. Consider restructuring things to improve traceability of numbers (and sometimes calculation speed!)