Has it happened to you before, that every time somebody in your office says the word “Excel” – you shiver at the thought of this huge grid of numbers where the unknown happens.
Well, I’m here to dispel this myth and fear because Excel really is simple, as long as you know how to approach it. 🙂
Having spent the last 10 years of my international career using Excel on a daily basis, here are a couple of key principles and tips to keep in mind – i’ve had to learn them the difficult way… so as long as you use them, you’ll never have to be fearful of Excel ever again!
1. PLAN your work
More often than not, people just dive head first into their huge excel grid, not really knowing what outputs or objective of the analysis they want to achieve. This wastes time and is not very effective for the person trying to build it. Hence before doing so, it is always good to spend some time planning. Remember, it is always 80% planning 20% execution! Some tips are:
- Firstly, understand the objective of building your financial model – will this need to be updated constantly, or is this just a one-off analysis? This will help you determine how “robust & flexible” (in the next blog post) you need to design your model.
- Draw up what you want to get out of the analyse – a specific result / output that you are looking for. This ideally should be a table or a graph!
- Once you are happy with the result/output that you want to generate, make sure that you have the data required:
- Example, if you want to analyse how much customers have bought from you, you’ll definitely at least need the customer name and $ spend in your data!
All this should be done via pen and paper or on those huge whiteboards where you can scribble away your thoughts!
2. STRUCTURE your document
All documents usually have more than one sheet and I’m sure many times, when you open someone else’s document, you see many sheets, all randomly titled Sheet1, Sheet2, etc. This doesn’t really help the user understand how each sheet is linked to the other. A simple tip to always adhere to are:
- Always have a sheet for data INPUTS – try not to mix them up as the idea is to be able to update these data quickly & hassle free.
- Always have a sheet for ASSUMPTIONS – this is so you have a known location where you are putting in all your own data.
- Always have a sheet for CALCULATIONS – this is the location where you draw in your inputs & assumptions, to do all the funky formulas so that you can process your data in the best way possible.
- Always have a sheet for OUTPUTS – this is where the results of your calculations get summarised in a nice neat table, which you have pre-determined beforehand.
Based on the structure above, I always advice users to give their sheets a simple name with the I-A-C-O tag in front, as it allows you to quickly maneuver through the document (I-Customer Data, A-Assumption, C-Customer analysis, O-summary table).
3. FORMAT your tables
Sounds simple, however the visual appearance of a neatly formatted excel table can have a huge effect on how efficient you are in digesting the information that you see whilst scrolling in your document and reduces excel fatigue- all these convert to productivity in the workplace, especially if you spend a few hours a day on financial modelling. Some simple tips to always adhere to are:
- Create title & headers for your table so you know what data you are looking at
- Use borders to segregate sections of a table
- Lightly colour cells – especially useful for Totals, Subtotals or important cells
- Align words in the cell, either center or left
- Space the columns properly, so that the table looks neat
With that, you can all now start planning, structuring and formatting your excel documents, saving yourself time so that you can spend that extra time in your office pantry catching up with your colleagues and getting to know more people in the office!
In part 2, I’ll be sharing more about what you need to know about building a robust & flexible financial model, plus shortcuts & formulas that helps you save time.