The 6 Must-Have Skills for a Successful Financial Modelling

It’s frustrating to struggle at something only to discover that, after much hard work, there was an easier way. 

That’s why people seek out formal training courses in financial modelling. I frequently hear comments like: “I wish I’d done this course years ago” or “You do not know how much time and effort you will save me – and how much time I now know I have wasted”. 

Team managers and Learning & Development professionals both have a stake in making sure training courses are as effective as they need to be to meet the organisation’s objectives. Both are interested in skill development: for the team manager, it’s easy to judge whether a course participant has acquired a skill; for the L&D professional, it’s important to see that the training itself is focused on competence development. 

We think there are six skills every financial modeller should have.

Excel proficiency 

This skill is focused on you being able to use Excel like a professional. This means you are both productive and efficient in how you manipulate Excel. It is not the same as knowing Excel inside-out. Rather, it’s more to do with being very selective about what tools you should use and why. 

This includes setting up the Excel environment so that it works for you: being deliberate in deciding on a calculation mode (for example); deciding how the cell highlight box should move when you press Enter; editing in the formula bar (rather than in the cell itself). 

Taking care with the Excel environment is about a strong start. If you are deliberate about where you edit formulas then you are also likely to be deliberate about other, more complex tasks. 

It is easy to get excited about all the possibilities in Excel. A proficient Excel user will not only know what they are, but also which are the most valuable and why. Evaluating strengths and weakness of, for example, a specific function is a very important skill to have. Of 450+ functions in Excel, only 10 – 15 are needed for financial modelling. These are the work-horse functions: simple, transparent and easy to understand. 

While a financial modeller needs a working knowledge of VBA and macros, a good modeller will make deliberate choices as to how best to make use of it. They will often restrict its use to automating simple routines (designed to avoid repetitive manual tasks), judging that simplicity and transparency is preferable to macro routines making use of everything VBA has to offer.

Conceptual modelling 

Perhaps the most poorly understood skill, fconceptual modelling is nothing to do with Excel or spreadsheets. Conceptual modelling is about describing what is to be modelled (rather than how you propose to model it). 

A conceptual model of a production line, for example, will show how the component parts – the inputs, calculations and outputs – fit together so that someone other than you may put together a spreadsheet solution.

Conceptual models may be communicated through: 

• A conversation (the conceptual modeller briefing the spreadsheet engineer) 
• Written briefing (email, for example) 
• Wiring diagram (requiring pen and paper – or a computer-based equivalent)
• Computer software 

Good conceptual modellers will use their expertise gained from other disciplines to their advantage. For example, prior experience of accounting, treasury, banking, architecture or even philosophy will help in determining a logic flow (one of the best financial modelling students I ever met had a background in philosophy). Sector expertise is helpful in determining where conceptual models may be re-used: a conceptual model for one tranche of senior debt might be an excellent starting point for a second tranche of senior debt with similar characteristics.

Model design 

A good model designer can put together a detailed picture of the entire financial model before beginning work in Excel. They recognise that critical design decisions exist at all levels: 

• Who is the end user of the model? What expectations do they have about engaging with the model? 
• Is Excel the correct software platform for the model? 
• What best practices, financial modelling standards and user conventions do you plan to follow? 
• How many workbooks? Noting that more than one workbook is the exception rather than the rule.
• How many worksheets and what are their roles? What order? 
• Where will the inputs sit? And the presentation of outputs? 
• What about worksheets dedicated to quality control? 
• In a worksheet, what are the section headings required? 
• How are calculations to be presented? 
• How are individual line items to be presented? 
• How should the contents of individual cells be presented – including font colours and formatting? 

Financial model design carries with it a risk of duplicated effort / reinventing the wheel, and that’s why financial modelling standards are so important. A model designer who can remove 80 per cent of their design decisions through the simple act of adopting a standard and sticking to it will design better models. The only risk remaining is choosing the right standard to begin with.

Template models are also a useful starting point when making design decisions.

Model construction 

Every financial modelling course worthy of the name will teach a process based on repetitive actions. So someone modelling revenue, for example, will see a similar approach when the course moves on to modelling operating costs. 

And course participants will be able to spot the patterns. That’s an essential part of learning. Lessons to learn include: 

• What makes a good label / line item descriptor? 
• The importance of units (that should be included, separate from the label, for every single line item)
• The role of row totals for line items that are flows in or out of the business
• Consistency of formulas
• When to anchor cell references 
• How to build calculations that are easy on the eye 
• How to refer to existing calculations that are required for subsequent calculations 

At the most basic level, you should be able to touch-type if you are to master financial modelling skills. Follow that by minimising your use of the mouse, instead relying on keyboard shortcuts (that, ideally, should be committed to memory). If you break through these first two critical points of possible resistance then you will be in an excellent position to take a host of new skills on board.

Model review 

This skill is about picking up someone else’s model and getting to grips with it (regardless of how it’s been built). It’s a combination of the other competences.

It’s a little like being a supervisor. Most supervisors will have done the groundwork, but to be a good supervisor they need to deploy their knowledge in a different way. They need to be able to apply critical judgment to the work of others. 

Here are three ways to develop model review skills: 

• Learn to build models. If you can build models and have a strong opinion on how to build them, it’s simple to apply that opinion to other people’s models. Plus, many of the navigation skills / keyboard shortcuts that you learn to build financial models are just as important for review. And a proven method for undertaking a formal review of a financial model is to build a shadow model and compare the results. 
• Develop a professional scepticism akin to that of an auditor. Assume that mistakes are there to be found; assume that improvements are there to be made. This approach is helpful in forming a view of the modeller themselves. For example, if a model’s structure is not immediately obvious and minimal thought has been given to the model user’s experience, then the reviewer will begin to reach conclusions about the modeller (and the model they have built). Keep close to the question: why have they modelled it like this when better ways exist?
• Adopt a review framework. Make use of checklists and review software. A formal procedure combined with professional scepticism is a good foundation for a good model reviewer. The best frameworks are risk-based, focus on commercial outputs as much as technical calculations, and look for highest-impact results for least effort spent.

Presentation of results

If model review skills are a combination of the other skills listed, then being good at presenting results sits on its own. It calls upon a different set of competencies from all the others, focused not on transparency of workings / calculations but on making the numbers tell a story.

A financial modeller with good presentation skills will:

• Know their audience. In the context of financial modelling, this means presenting what the audience expects to see. We’ve rebuilt plenty of models where the presentation of outputs remains the same – even though there is now a new calculation engine sitting beneath.
• Have a keen eye for design, colour, white space and techniques to maximise visual impact. You’re likely to be an advocate for the principles of Edward Tufte and Stephen Few. For example: you will be rigorous in removing all non-essential information from the charts and graphs that you create.
• Gather the data required for the presentation of results and place it in a segregated part of the financial model (in a worksheet called “Graphs”, for example). The link between an Excel chart and its source data is not the easiest to manage.
• Make good use of a working knowledge of Excel functions for building an effective user interface (form controls being a good example), using them with care and with the needs of the end user in mind.