Financial Model Development

16 January 2020 | 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. TGH does this by applying best practices in systems, tools & process in strategic planning, corporate development including mergers and acquisition (M&A), FP&A, budgets & forecasts, valuations and financial modelling.

He has led Strategy, M&A, Finance, Valuations and Modelling teams 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?

Financial Models should be built using at the very least one of the recognised standards or methodologies (e.g. Best Practice Modelling (BPM) Standards). The Development stage is a crucial part of building a best practice financial model (the “Model”).

I spend a lot of time designing unique front ends (“Inputs” or “Assumptions”) and back ends (“Outputs”) for users ensuring the Model delivers exactly what they expect to make the relevant decisions.

The key is then to understand the data & information sources, size, scope, ranges, assumptions etc that feed into the Model and how to manipulate (i.e. model) these in a robust way by using for example the BPM Standards into the Outputs & deliverables expected by the users.

Users should spend most of their time interpreting and analysing Assumptions and Outputs, not worrying too much about the calculations or logic contained within the Model. This is the role for the Model developer.

Therefore, the Model needs to be dynamic and include a pertinent number of “smarts” (i.e. scenario management or simulation, dashboards and charts etc) necessary to achieve the desired result and communicate succinctly the intended decision to be made using the Model.

If the users of the Model don’t have confidence in the integrity of the logic by seeing results that are inconsistent with their “gut feel” or intuition, then they won’t have confidence in the Outputs in order to make the decisions that the Model is intended to support.

Topic and context

The Model should be built optimising the trade-offs and balance of Robustness, Flexibility and User-friendliness.

Users of the Model may try to use it to answer questions that it cannot handle because it contains simplified assumptions. If the matter being evaluated is one where unsubstantiated assumptions (guesses or fingers in the air) have been made, then the forecast results may not be reflected in actual outcomes.

So, a detailed scope for the Model should be developed to identify the range of reasonable situations to be dealt with by the Model.

Tip: Documentation should specify any limitations of the Model.

The Model should also be developed and constructed with various end-users in mind. Using one of the standards and methodology will enable easy development of the Model, as well as ease of use by others once the Model has been constructed. In developing the Model, quality and accuracy (including error checks and alerts) should be built in from the start.

More on the financial modelling standards later in the series of articles.

If you had to teach this topic in a class to school kids what key tips would you give them to focus on?

Think about the process of building a Model like building an interactive electric train track on a table.

The process is the same, however some of the steps may be relatively small or straight forward in comparison to others. The other steps may become unique or more complex to building relative to the number of pieces available, size of the table, number of corners vs straight tracks etc.

I would encourage any Model developer/builder (think train track builder) to spend the requisite amount of time understanding the requirements of the user(s) (think your parents and friends) and documenting and agreeing the Model Specification (think direction of the trains) BEFORE setting out to develop or build anything (i.e. don’t touch the keyboard or trains).

It continues to surprise us how often the user(s) actually want something different to what they had envisaged BEFORE we sit down and specify precisely what is to be included and excluded from the Model.

Will there be a bridge and water to cross? Do we have one engine on the tracks or two? Will your parents and friends be impressed if they could control the train’s directions?

What practical steps can people take now to learn more?

As others have said in previous articles, the key in gaining experience, developing skills or becoming an “expert”, is to obtain as much knowledge and practice as you can. Read widely and spend time practicing. I’ve lost count of the number of times people have asked me how I know the things I do and what courses they can take to gain the same skills and knowledge, without ever having done the research or simply used the Help facilities in Excel to understand how to do something. It’s called initiative and Trial and Error people … oh … and lots of grey hairs or none at all (as the case may be ??)!!!

There are so many good trainers and training courses (from basic to advanced) out there, but I strongly encourage people to take the initiative, do the research, buy a book and learn as much as they can before asking someone else to pay for them to go on a course. That way you can also be focused on the areas you not aware of compared to starting from scratch.

Good employers will fall over themselves to hire, help and even promote people that show some initiative.

These are the same people employers will want to keep!

For more information on building Models also think about software development and whilst you won’t be coding in a language like C#, Golang (Go) or JAVA you will building it in the most widely known business language on the planet.

Spreadsheet formulas (namely Excel but there are others) are known pretty much by everyone no matter their language or age in the business world. It’s a very handy skill to have and is not going anywhere just yet.

There are a number of steps involved in actually building a Model depending on the level of granularity, but are basically as follows:

1. Initiation (overarching needs identified).
2. Concept Development (feasibility, cost-benefit).
3. Plan (project plan, schedule, budget).
4. Requirements Analysis (understanding the problem, goals, needs more specifically including logic).
5. Design (detailed design & how to deliver the Model to fit the problem).
6. Development (converting the design, requirements and logic through coding in formulas).
7. Testing (code quality checking, reasonableness sniff test, stress and performance testing).
8. Deployment / Implementation (launch and training users).
9. Maintenance (monitor, evaluate, manage, fix bugs)

Relative times for each stage might be proportioned as follows (depending on scale & scope of Model):

The table is only a guide as there will naturally be movements between stages and its difficult to accurately measure depending on the complexity, but it’s a useful outline nonetheless which many fail to follow.

Where are good places (links) to find out more on the topic?

Here is a couple of links relating to Systems Development Life Cycle that people might find useful in providing some background to Model development and building.

•  https://raygun.com/blog/software-development-life-cycle/#the-seven-phases
• https://en.wikipedia.org/wiki/Systems_development_life_cycle

How important is this skill in the context of learning FM?

A financial model developer/builder needs to listen to the user’s requirements and be able to guide them to developing an agreed specification. Remember you have two ears and one mouth, so use them in that proportion at the very least.

Another very important skill is NOT falling into the trap of owning data and assumptions in the Model, but ensuring there are documented sources and ownership of that information.

However, a modeller should always be able to play the devil’s advocate role and challenge what looks like potential outliers or errors in data and logic proposed that seem unrealistic.

As touched on by Lisa Barnham in article 1 in the series “Mindset of Embracing the Grey”, there is only one guarantee that I give recipients of Financial Model Outputs … and that is … that the actuals will not match the forecast. That is, there are a lot of grey areas in modelling and no-one can model the future with absolute accuracy and certainty.

As Lisa said, “the realisation that even with accurate data no model is 100% accurate and the value is in the relative outcome compared to the absolute. It’s the value on the what-ifs and various scenarios that we can find some tangible benefits.”

So, the goal with any financial model is to provide users with a robust, flexible and user-friendly tool that enables them to forecast the future with as much precision and knowledge as they are comfortable with and believe appropriate to the circumstances to enable them to make informed decisions.

How does all this disruption, AI and automation talk impact this topic?

There are several Models or Model packages in the market that might automate the process and make life “simpler” for users at times, especially when their requirements/specifications are very limited in scope and vanilla in nature.

However, these do not enhance anyone’s financial modelling skills if they don’t exist from the start and at times can cause more damage if the core concepts of financial modelling are not well understood.

Further, most of these packages have limitations (as does anything purchased “off the shelf”), that does not allow for the flexibility required by some users. However, they have their uses and place in the right circumstances and if used correctly as designed. 

Imagine if the only choice was to use Xero for accounting and bookkeeping purposes. Whilst it’s beautifully designed for business it does not suit every business all the time. These times start based on how the business evolves and grows, e.g. multiple foreign entities, and require a full ERP implementation and consolidation of foreign entities.

Just like accounting systems have limitations, off the shelf automated modelling packages can leave you stuck with little flexibility to adjust for business trading conditions and logic.

Finally, there are many add-ons available to Excel to enhance model development and visualisation capabilities today as well as a range of Excel auditing packages.

Spreadsheet to Financial Modelling Article Series

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.