Analysis & Modelling – Best Practices

How to

• (1) get the most out of your analysis,
• (2) allow you to move and make decisions quickly,

instead of redoing the same analysis over and over again.

Here are a couple of analysis ‘key rules and recommendations’ I have learned in my experiences in both big corporates (Amex, PwC, KPMG) and Startups (MadPaws and Expert360).

1) Get the data right, get the right data.

Firstly, make sure you have a strong foundation and understand your data, where it comes from, its format (this is crucial) and what it represents. Test its every variation and understand all its anomalies. There is nothing worse than starting an analysis with bad data. The cleanups are going to take you forever and, god forbid, if you need to rerun the analysis it will be a huge pain to recreate the same cleaned data again. Key Learning - try to keep your data clean. If it is already bad data, clean it in the system first (do not do it offline, to avoid having to redo it).

Also, when implementing data pushes from other systems, try to include as much meaningful information as possible about the source of the data. This will help you to understand trends (such as the main source of your leads), as well as help you debug your data and trace back the root cause of bad data. This will save you countless hours and allow you to quickly troubleshoot.

2) Tell me a story!

Once you have the fundamentals ready, let us tackle the core – the analysis. Structure your model the way you would write an essay / tell a story.

A)    Table of contents

Recommended: "Intro" 1st Tab, Black or Dark Red

The first tab should be a summary of where to find which piece of the analysis. What do the blue tabs indicate? Where can I input data? Etc. Give the reader an understanding of how to navigate your analysis or use the model (ie how to update the data). This sets the scene, gives the reader a feeling of control and of a well thought through analysis. You’re scoring brownie points and impressing the audience.

B)   Introduction

Recommended: "Input", Light Blue (possibly 3rd as well, if a lot of analysis)

This is where you start telling the story. Introduce the various characters into your adventure, outline all the different input variables there are in all their shapes and forms. Most importantly provide commentary and sources (best with a ‘commentary’ column on the side), provide some insights into where the input variables are coming from, why they change and what your assumptions are.

Often I find myself in such a rush to meet a deadline, that I skip this. Once you get the same question three times as to where a certain number came from or why it changed and you’ll have to search for the supporting analysis for 20 minutes each time – you will understand. NEVER skip this step – for the reader and more so, for yourself!

C)   Main Body

Recommended: "Analysis", Tabs at the end, Grey

This is where the story really gets cracking. The characters meet each other, go through adventures, jump hurdles together and make more friends with other protagonists. This is the place for the bulk of the analysis – all your background calculations. Try to split those across tabs, if necessary, either chronologically or by topic (ie for a P&L - revenue vs. OpEx), ideally both, so the reader doesn’t get lost on their trip through your numbers.

If your analysis is more than a once-off analysis and you know that you will want to / need to update it again – DO NOT use Pivot Tables. In fact, even if you think it is a once-off, don’t do it. In 99% of the cases (that’s probably me exaggerating, but it feels that way) it will not be a static analysis. Pivot tables are great for quick overviews, but a pain to maintain / update and simply don’t live in serious models, which need to be flexible.

Similarly, NEVER hardcode anything, but inputs on your inputs tab, which you have a commentary against as to where they came from and what they are. Hardcoding something midway through an analysis can be the easy fix to your immediate problem, but you’re definitely not making friends with your future self. That future self will hate your guts, when he’s trying to figure out where those numbers came from and why the hell you ever thought that it was a good idea to hardcode.

D)   Climax and Conclusion

Recommended: "Summary/Findings", Purple (can be various shades of purple, if there is a high level summary vs more detailed insights)

This is what the reader has been waiting for – the big finale. It is also what the audience is going to spend most time on. Therefore, make this part especially intuitive (spell out definitions and abbreviations) and aesthetically pleasing. One tip here is to use light colours (easier on the eye) and remove gridlines (Pro Tip - this instantly will make your insights look a million times better). If you’re going to apply your visual designer skills to any tabs, it should be these ones.

As you can see, it’s not only about the final output of your analysis. If you want to make a professional impression, it’s just as much about the structure and presentation of your analysis. This can and will make you stand out amongst analysts, as well as save you time and enable you to work faster.

Hopefully this guide has provided you with a couple of ideas for how to structure your next analysis.

If you have read this until the very end – please comment below with

(1) your favourite quote from the article OR

(2) let me know if there’s anything you disagree with, OR - even better –

(3) if you have any other recommendations.

Hope you enjoyed the read!

Michael Batko