Microsoft Excel can be a powerful business tool...if used correctly! Every day in businesses around the world, analysts plug away at numbers, be it Financial modelling, data analysis, scenario modelling, or similar. However it’s surprising how often the basics of Excel modelling are ignored, often leading to wasted time, or worse, mistakes being made. Here are 8 simple tips to help you nail the basics and improve your modelling capabilities
Check data inputs BEFORE building your model
There’s no such thing as perfect data. But the data you use forms the spine of your model, meaning making changes to it after your model has been built can often lead to unnecessary rework. Taking the time up front to check your data thoroughly not only prevents this, it also helps you to discover and account for anomalies before anyone reviews an output, and helps you to understand the context with which you are working. Examples of questions you should be asking yourself include:
Does the total volume/value/order number/profit/time in the data add up to what I expect?
Are all customers/suppliers/manufacturers/users/products/categories included in the data?
Do these numbers reconcile to what the business expects? If not, why?
Do the KPI’s that are most likely to draw attention making sense?
I’ve attached a few examples of formulas that can help to check and cleanse data [here]
Set up an assumptions log
As we haven’t yet perfected time travel (I'm working on it...slowly...), we often use assumptions to build a picture of what the future may look like. These could be as simple as assuming revenue grows at the same rate as the previous 5 years, or could be more complex, such as cross price elasticity assumptions. Regardless of what they are, where they came from is just as important as the assumptions themselves. Keep a log of what the assumption is, how it was derived, and who by to enhance traceability.
Separate inputs, assumptions, and outputs
Be clear on what the inputs, assumptions and outputs are by mapping them
Unless your model is incredibly simple, there is no reason why inputs should be kept in the same tab as outputs. Confusing the two will lead to all manners of problems in repeatability, traceability, and user friendliness. Think of your workbook as a novel. Inputs are the beginning, where you introduce characters and build a picture. Assumptions and interventions are the middle, where you shape the story. Outputs are the dramatic ending, where you dazzle audiences with your brilliance. Avoid confusing your audience by using separate tables to be super clear where data input is required, where they can input “human intelligence” to shape the outcome, and where the final output is.
Never over complicate calculations
I love it when I present my Excel models and people say “it all feels much more simple now”. That was, after all, the aim! Before you write 15 “IF” statements in a row, stop and think about the simplest possible way to perform the task. Ask yourself:
Are my calculations traceable by someone who has never seen this model before?
Is the formula/the data structure/query I’m using going to be a drain on my computing power (hint: array formulas are terrible for this!)
How do I minimise the steps users need to take to get to the output in future?
Make sure you can change your assumptions quickly
If you’re reviewing your analysis with senior people within the business, they’re highly likely to ask what your numbers would look like if you changed variable X or Y. Prepare your inputs so that making a modification to the assumption is lightning fast. This will not only impress your stakeholders, it will also save you time reworking numbers offline later on
Always perform row and column checks
If your inputs or outputs contain large numbers of rows or columns, have a think about what checks you need to make in order to be sure they have calculated correctly. For example, does your ledger balance, does your input volume equal your output volume, are there any blank rows/columns that should be filled with data. I tend to use the below structure in many of my Excel models, which is particularly effective when performing financial modelling. Simple but effective!
Be clear where data ends and calculations begin
Spend time on visualising your output
Pivot charts are a quick way to interrogate outputs
Going back to the analogy of thinking of your model as a novel, the output Is your chance to tell your story and make it stick in people’s minds. Hence why you should spend time to really showcase your work by making compelling outputs. Go back to the initial reason for building the model and think about what your most important stakeholders would want to know. Once you have a clear idea, think about what form of graph, table, or visualisation would be best to communicate this to them. As a rule of thumb, Finance Directors will want to know what the impact on their P&L will be, so show them a P&L and at least one comparator. Other stakeholders may prefer more of a “dashboard layout” that allows them to explore high level KPI’s in a visually appealing way.
Always review your analysis with business experts
Some of the consultants, finance analysts, and Strategy bods I’ve worked with in the past have preferred to gather information, perform their analysis in isolation, then come out with a “big bang” answer that shocks and surprises everyone. 80% of the time I’ve witnessed this, it hasn’t been effective and here’s why. Regardless of how capable you are, there will always be someone in the business with more and/or different knowledge to you. Only involving them at the end of the process can leave them feeling like their opinion isn’t valued, or worse, that they need to defend their opinion against the new information you’re presenting. Take the time to identify these people early on, and review your analysis with them. In doing so you may have prevented unnecessary mistakes, leveraged subject expertise, and created an advocate for your work in the process!