I was preparing for some face to face training for a client recently and I was struck by two things when looking through their spreadsheets:
They have clear separation between inputs, working and outputs.
They use different colours to identify inputs workings and outputs for both cells and worksheet tabs.
They have well written user notes.
A lot of care and attention to detail has been invested in building the models.
Long, complex, nested formula.
Nested IFS, with multiple VLOOKUPS and the INDIRECT function used throughout.
Working through the model, it became clear that the reason the team was using these complex formulae was because the data in their model was not in a format that could be easily referenced in the output reports.
It also became obvious that quite a lot of work went into getting that data into the model each month.
At Full Stack Modeller, we promote the well-established good practice modelling concept that formulae should be clear, simple and consistent. We believe that the way to achieve this is by putting the effort in at the design stage of the modelling process and then in the data stage of the build itself.
Investing time in the design stage ensures that you have a well thought out, well structured, user friendly and future proof model.
Investing time in the data stage of the build will enable you to create a single data set, in tabular structure that is the single source of truth in your model.
This single source can then be used in your analytical layer (Pivot Tables and Pivot Charts), and reporting layer (fixed format reports and charts). The functions used in these reports are often limited to SUMIFS and SUM. Not an INDIRECT, VLOOKUP or nested IF in sight!
Would you rather...
Save down file, open file, copy report, paste report into model, delete rows, columns, parse text, add mappings, combine text, update certain formulae, review. Start again because you realise you've made a mistake.
Or
Save down source file. Click a button. Review.
It is well understood that manual intervention in any process introduces risk. Add complex formulae, time pressure and multiple users into the mix and you have a recipe for disaster.
Models that have been well designed with a strong focus on data quality, with transformation and calculations delivered automatically by PowerQuery and a single source of truth for reporting, greatly reduce the risk of error.
Ever tried to make use of data that is spread throughout a model in various different formats? Almost impossible right.
Compare that to one single table of data that includes all of the data you need for your reporting.
You now not only have a more efficient, less risky model but also a valuable set of data that you can mine for useful business insights.
I still remember the first time I was sent a model from a colleague with the instruction to run it asap and send the results out to one of the directors. I opened the model and my heart sank. It was a maze of data and complex formulae with no clear structure or flow.
I had no idea where to start.
And when I finished I had no idea if the output was correct.
To quote Jeffrey Dean McClelland:
"Spreadsheets are like underwear, you like yours, but you don’t necessarily want to share anyone else’s"
Data Driven models are designed and built on good practice principles with the end user in mind. Collaboration is at the heart of the approach.
Great efficiency, lower risk, better insights and improved collaboration leads to a happier team who spend their time on the value adding job of finding and sharing insights instead of copying, pasting, deleting and editing.
Our comprehensive, year-long programme will teach you all of the skills you need to adopt a data-centric approach to your modelling. Find out more here.
If you would prefer the bootcamp style of training, then our EXCEL FUNDAMENTALS BOOTCAMP would be perfect for you. An intensive training course focused on all of the core skills:
All delivered through a blend of pre-recorded and in-person training, supported by worked examples and a comprehensive case study.