User Friendliness in Financial Modelling
Written by David Thompson and Lance Rubin
Introduction to the co-author
David established TGH in 2009 to work with businesses of all sizes to help them improve Shareholder Value. We do this by applying best practices in systems, tools & process in Strategic Planning, Corporate Development / M&A, FP&A, Budgets & Forecasts, Valuations and Financial Modelling.
He has led Strategy, M&A, Finance, Valuations and Modelling for large infrastructure and energy businesses and investment managers. He has worked in or consulted to the Infrastructure, Electricity, Oil and Gas, Water, Resources, Mining, Transport, Manufacturing, Telecommunications, Information Technology, Engineering, Construction, Financial Services, Funds Management, Agriculture, Education and Health sectors, Regulators and Revenue Authorities.
David has valued hundreds of companies, businesses and major projects, developed financial models, been a financial advisor on reforms in the energy, water and port industries, and lead teams on M&A projects totalling more than $130 billion.
Why did David select the topic and why is he passionate about it?
I think this is one of the most important aspects of building financial models and missed by many model developers/ builders. I spend a lot of time designing unique front ends (“Inputs”) and back ends (“Outputs”) for users ensuring the financial model delivers exactly what they expect in a way that abides by standards used in financial modelling and contains drivers, assumptions and logic that are flexible. Then and only then can it truly be called a (“Model”) otherwise it’s just another spreadsheet.
But the Model also needs to be developed and presented in such a way that users know what and where all the Inputs, drivers and assumptions are located and what and where all the Outputs (reports, tables, charts etc) are located as well as understanding what story they are “telling” the user. For example, if the user “pulls one lever” (assumption), they should have an idea roughly what the resulting impact / outcome (Output) should be.
Applying a few simple standard concepts helps make a Model more user friendly as well, such as:
A welcome and/or cover page (perhaps include a summary Scope, Objectives, Purpose & Requirements)
A Model map (my preference) or Table of Content hyperlinks to each Worksheet (or major section)
Separate the key assumptions (Inputs) from the calculations (“Logic”) and the Outputs
Make Assumption worksheet(s) look different to Logic and Output worksheets (e.g. colours schemes)
Standard set of headers at the top of each worksheet
All dates starting in the same column on each time series worksheet
Try to ensure all Items start on same row (where practical), especially if multiple divisions, BU, segments, companies, departments etc where you are consolidating)
Consistent Logic applied over the time series
NO hard codes within any formulae in the Model (i.e. Use Range Names in lieu of days or months in year or thousands, millions etc). Range Names avoid any possible ambiquity created by say 12 products or 12 divisions that might exist as well as 12 months.
Use of white space (not to be underestimated)
Dynamic charting (if appropriate/ desired)
Conditional formatting so that changes in Inputs show up easily
Exception Inputs on Scenario Managers (in lieu of populating every Cell in every Scenario with the same data along with the data that has changed making it difficult to spot any differences), and
Various form controls e.g. Options, Drop Down menus, Check boxes etc
I have tested/audited hundreds of Models and a lot of them were poorly structured, with Inputs spread throughout the Model (usually because it is convenient or close to where the user wants to see the result) and whilst they are not hard to find (with the right auditing tools), they are certainly not easily found by (other) users of the Model.
Topic and Context
Model developers can often fall into the trap of developing a model based on a set of expected outcomes, resulting is a lack of dynamic capability, meaning that the model may not be able to be used to easily assess other required scenarios, thus making it unfriendly to the user. With this in mind, a detailed scope for the model should be done to identify the range of reasonable scenarios to be analysed using the model.
The model should also be constructed with various end-users in mind. Using a financial modelling standard will enable easy development of financial models, as well as ease of use by others once the model has been constructed. In developing a financial model, quality and accuracy should be built in from the start.
Financial Models should be built optimising the trade-offs and balance of Robustness, Flexibility and User-friendliness.
More on the standards will be covered later in the article series.
If you had to teach this topic in a class to school kids what key tips would you give them to focus on?
Always begin with the end user in mind whether they are using the entire Model or just some of the key Outputs.
If you were going to build the next biggest and coolest mobile app (“App”) for all your friends, families and others to use you must keep this “end” outcome in mind.
To understand what the App will contain, you really need to understand the problem clearly.
Ask lots of questions so that you can uncover the underlying problem that your App is planning to solve. If you don’t understand the problem deeply then your App will not be user Friendly or simply not be used at all.
Once you have understood the problem that your Model (think App) is going to solve, make sure you spend time challenging and ensuring the validity and accuracy of the Inputs (think features and characters in the App).
You will also need to make sure that your Model Outputs (think App graphics and display) are easy to interpret and analyse, not having users focussed on the Logic (think the code used in the App) in the Model.
Users should have as much confidence in the Model (your App) as they would other Main Frame computing and accounting system such as SAP, TM1, MYOB, Xero etc (think other banking Apps).
To make the experience of using your Model (App) as friendly as possible it really has to consider all the design aspects of the user interface (UI - buttons, icons etc) and the user experience (UX- how it makes them feel when they use it – happy, sad, angry).
Friendliness of Models and Apps is often subjective so being sure you cater to what other people expect then you should be fine.
What practical steps can people take now to learn more?
Friendliness of a Model can be measured by means of assessing the various components contained within it.
These components can be measured against some of the published standards in Financial Modelling and will be covered in detail in a later article in this series. However, here is a brief summary of some of those components just to set the context for measuring Friendliness. The below has been extracted from the Best Practice Spreadsheet Modelling Standards from the Spreadsheet Standards Review Board (SSRB).
The Institute of Chartered Accountants in Engand and Wales (ICAEW) also published twenty Principles for good spreadsheet practice as per below and can also be used as a basis for measuring Friendliness:
Be sure to look out for the article on Financial Modelling Standards later in the series which will cover the above in more detail.
Where are good places (links) to find out more on the topic?
Here is a list of some useful links to the main standards websites:
How important is this skill in the context of learning FM?
According to the Corporate Finance Institute, the 10 most important financial modelling skills are:
1. A solid understanding of Accounting
2. Strong Excel skills
3. Knowing how to link the 3 Financial Statements
4. Understanding how to build a forecast
5. A logical framework for problem-solving
6. Attention to detail
7. Ability to distil large amounts of data into a simple format
8. An eye for design and aesthetics
9. Clear presentation skills
10. The ability to easily zoom in on details, and zoom out to high-level strategy
However, making Models user-friendly (which is embedded in several of the above skills) should be one of the key focuses for Model developers / builders.
As mentioned, above, it is important to understand and focus your time optimising the trade-offs and balance of Robustness, Flexibility and User-friendliness so that users get the most out of the Models they possible can and therefore make better decisions that solve their problems.
Some Examples of making a model more user-friendly include:
Conditional formatting
Below are two sets of inputs; one without conditional formatting (“CF”) and one with. Can you easily spot the change in data in the first set of inputs?
Figure 1: Set of Inputs without CF
Figure 2: Set of Inputs with CF
Scenario Manager Tables/Inputs
Below are two scenario tables; one with all inputs repeated including the new inputs (we see this so often) and one with only changes in inputs. Can you easily spot the change in inputs in the first scenario table?
Figure 3: Scenarios fully populated
Figure 4: Scenarios populated by exception
You should also highlight the scenario being used as above (or with some sort of indicator in the column).
Structuring
If modifying a previously designed and built model, it’s important to step back and consider if the structure of the original model could be improved to deliver the same or better outcome. We did this just recently reducing the client’s model from 35MB down to 8MB. The model owner now has a more efficient model that enables them to focus on interpreting and analysis the results and deliver reports very quickly / instantly to senior management.
How does all this disruption, AI and automation talk impact this topic?
The increase in popularity of programs and collaboration in the industry using PowerBI, Tableau and websites such as Eloquens, suggests there is a strong focus on financial modelling and data analytics skills along with the presentation of Model results that are much more user-friendly. This focus is quite exciting for those passionate about the topics.
Therefore, Model developers/ builders need to understand how to present their Models by making them even more user-friendly and enable those Models to maintain currency and ride the wave of interest in this space.
If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App.
If you want to find more information on financial modelling and content visit the Model Citizn website.