The Implications of not following a Modelling Standard

23 March 2023 | by ChatGPT and Lance Rubin?

Introduction to the co-author

“ChatGPT is a large language model developed by OpenAI that is trained to understand and generate human-like text. It is based on the GPT (Generative Pre-training Transformer) architecture, which uses deep learning techniques to generate text that is similar to what a human might write or say.”

“The model is trained on a large dataset of text, and can be fine-tuned for specific tasks such as language translation, question answering, and conversation.”?

“ChatGPT can be used to generate human-like responses in a chatbot, as well as to generate new text, such as articles, stories, and more.”?

“Overall, ChatGPT is a powerful tool for natural language processing and text generation, and can be used in a wide range of applications”, including co-authoring this article with a human.

All ChatGPT content is written in italics and exclamation marks for transparency in addition to some screenshots.

If you have not heard of ChatGPT you are probably living under a rock somewhere or maybe just not on LinkedIn, the business world is in a spin with how powerful it is.

With Co-Pilot here soon, it’s about to get even more interesting.?

It’s been equally exhilarating, frightening and mind-blowing at the same time. But it’s not perfect, it’s also early days and we will share some things from a financial modelling context that are insightful.

ChatGPT was asked: Why are financial modelling standards important??

“Financial modeling standards are important for several reasons.”

“First, they help to ensure consistency and accuracy in financial models, which can help to minimize errors and increase the overall quality of the model.”

“Second, they help to promote transparency and clear communication of the assumptions and inputs used in the model, which can help to increase the credibility and trust in the model.”?

“Third, they can help to facilitate the comparison and analysis of different financial models, which can be useful in a variety of contexts, such as investment decision-making, risk management, and regulatory compliance.”

“Overall, financial modeling standards can help to improve the reliability and usefulness of financial models, which can be beneficial for a wide range of users and stakeholders.”

Pretty hard to argue otherwise!?

Why bother, who cares?

With so much uncertainty in business trading and economic conditions, more financial modelling work is being requested. This was also true during the pandemic and hasn’t really changed with rising interest rates, supply chain challenges and many other commercial obstacles.?

There is still a big gap between the demand for good financial modellers and the supply, despite the arrival of ChatGPT!?

Excel skills are still very basic at best and this is getting worse with many younger people running off to learn hard core coding skills like Python and SQL rather than going deep on just Excel!?

Of course Excel is dead and they believe the hype!

But being good at Excel and building models or calculations are flawed without the application of a recognised best practice approach to building models which is supported by sound business concepts and logic.?

If you were going to build a financial model from scratch it becomes exceedingly clear to others who review your model when you have (or in most cases have not) followed any modelling standard.

Following one of the standards or code is more important than following none, so don't get hung up on the differences, pick one and follow it within the boundaries and trade-offs that always exist in applying a modelling standard and building the best and most appropriate solution to a complex financial decision.

There was already a pretty comprehensive article written on standards and resources you can read here.?

The intention of this article is not to create more controversy than what exists already, it appears this last one did enough of that.?

This article is more about the why and the implications of not having any process or following any standard or approach at all, along with the benefits of having some form of a standardised approach to building financial models.?

What’s the current state of play?

In the vast majority of cases I’ve seen, hardly any companies adopt any form of in-house approved modelling standard, let alone an externally recognised one.?

The FullStack Modeller survey , which targeted financial modellers (which are a very small % of all Excel users) provided some, albeit biased insight. The reality is that amongst accounting and finance professionals hardly any standard is followed at all!

I’ve run financial modelling training for 1,000’s of accountants and finance professionals across 5 countries (Australia, New Zealand, Sri Lanka, India and South Africa) for ACCA and CA(ANZ) and hardly anyone could name or recognise some of the basic principles of any of the current standards or knew they even existed.

But let's ask ChatGPT, maybe it has more insight.

Not even ChatGPT knows the answer when asked directly and it defaulted to accounting standards and types of models! No BPM, FAST, SMART, No Financial Modelling Code etc

In 2022 I wrote a large portion of the CA(ANZ) study guide on Financial modelling which is now part of an elective to the CA Program for all accountants (past and future).?

This study guide makes references to the use of modelling standards and is also further evidence of the accounting industry and bodies looking to assist in developing these skills more holistically in their programs for qualifying and qualified accountants.?

Luckily there is an increasing number of people when asked at many of the workshops we have run, who have become more aware of what a 3-way model is and recognise its value, but many have still never built one from scratch and the majority have still never heard of it.?

When I started financial modelling training more than 5 years ago this percentage was low single digits. More recently this is creeping towards 25%, which is a good sign, but still a long way to go.

When you start to build a fully integrated 3-statement model the need for applying elements that exist consistently across all the standards arises and their importance is critical when building integrated statements. The most important of these is the structure of the model.

By not following any standard you cannot easily benchmark yourself against this collective consistency and validate whether you are somewhat compliant or on the right track or “Dorothy we are no longer in Kansas!”

Hopefully the former rather than the latter.?

Sadly, I feel today we still have too many Dorothy models!?

Spreadsheets that are called models or forecasts are generally very badly built, error-prone calculators with more bugs than trash disposal and incredibly risky and frustrating to manage and gain any form of credibility for decision-making and insight.?

The moment you find and fix one bug, another one appears and so on and so forth.

Sound familiar??

Which standard is the best??

It doesn’t matter!??

I don’t care which one you choose, but just pick one, or chose the best bits of some of them and create your own given the high levels of consistency between all of them.

There has always been a battle fought between the creators of those standards (eg who was first?) for decades, but in reality, they all added significant value to the entire modelling community in one way or another.

There isn’t a professional modeller, including myself, that has not gained insight and value from those creators and we should thank all of them for making a contribution to the growth of the financial modelling sector. Nobody else stepped up!

Personally, there are two standards that resonate with me the most. These two are FAST, for its easy-to-understand acronym, and BPM for its detailed foundations and grounding I received early in my modelling career.?

It also helped me form the basis of developing one of the Financial Modelling Institute solutions to the AFM exam and building a financial modelling consultancy business and training my team and teaching many others around Asia Pacific, hopefully in other countries too soon.

Ideally, the International Accounting Standards Board (IASB) or the FASB (US equivalent) should have stepped up and written a standard.?

But as everyone knows most accounting standards cover backwards-looking historical reporting and are very limited to forecasting. Maybe IFRS9 and US ASU 2016-01 (US GAAP) are a little more forward-looking with respect to fair value, but that’s about it.?

Financial modelling is also not all technical and scientific, like accounting standards in general, but part art as well and this is why formulating a specific standard is somewhat subjective.?A well-designed, easy-to-use model is poetry in numbers.

You can get exactly the same answer by modelling differently and applying a different structure eg horizontal vs vertical modelling or table-based modelling with structured references and dynamic arrays.?

These approaches, despite having exactly the same answer, would not be compliant with all the standards at the same time, yet still, give you exactly the same answer to a set of assumptions.?

This is the case given the Financial Modeling Institute does not enforce or require you to apply any specific standard but allows students to choose their own provided they are conceptually and structurally sound.?

The ICAEW wrote the code and some principles (see links below) which are incredibly useful but don’t really go detailed enough to give the user more detailed steps to follow, especially when early learners need more detailed guidance at a formula level.

Hopefully, this article highlights clearly the pitfalls of not following a standard more deeply (distinct from which is best), whilst also highlighting some of the significant benefits of adopting even an in-house designed standard.

One of the most important takeaways is buying expensive planning and modelling software isn’t necessarily the answer either, out of the frying pan into the fire and you lose all that flexibility!

Excel is not dead and not going away and assuming you can apply any of the standards well, you will get incredible payback through developing your modelling capability, and not making other people rich!

What do we expose ourselves to when we don’t adhere to any standard or code of model building?

By not following any standard or principles we are selling ourselves short and succumbing to expensive software solutions that take away our freedom of decision-making and impose rigidity that can be easily solved by adopting a standard.?

Just like any form of the generally accepted way of doing something, rather than every person learning as they go and making mistakes (like errors we see so often mentioned in spreadsheets) a standard allows many people to leverage the mistakes of others and avoid making them earlier in the model building process.

Sadly very few spreadsheeters and corporates have any form of a standard approach to building or using spreadsheets across their finance teams.??

It’s just never been a priority! Perhaps now with the increases in model building it will be.?

As a result lots of errors and badly created workbooks exist and lots of money is spent (wastefully) trying to get rid of Excel (which magically creates the errors, not the human).?

As a result, many shy away from investing in using it better from a people skills development perspective but favour a magic wand technology investment that takes control away from accounting and finance users! Or worse creates a single super user key person dependency that becomes very expensive to replace or support going forward.??

Knowledge transfer is incredibly difficult between spreadsheeters and most importantly we cannot evolve what we do in a systemised and efficient manner.?

The mess we see in spreadsheets is a direct result of us not having the same (best practice) approach to solving this problem that is often met with a litany of naughty hardcoded formulas everywhere.

Let’s fix this problem once and for all!

We should be formulating, adopting, implementing and reviewing ongoing compliance with a standard (annually at least) given the significant dependency on, in many cases, pretty significant business decisions done on gut feel or rogue calculations in a spreadsheet.?

What are the benefits of adopting a standard or designing your own in-house designed approach to the model building??

Systemised spreadsheet management and development have huge benefits for corporate spreadsheet users like those in FP&A and across the financial modelling sector.

Many of the forecasting and modelling software technologies (outside Excel) exist purely on the fact that there is a lack of a systemised or structured process, which those packages impose and enforce on users!

You don’t need software to do this, you can simply adopt any of the existing standards or modify them to fit your needs.

The software can enforce rules of how data is managed and this is a good thing until it’s not.?

Until you are required to customise it or make changes for new business decisions or drivers. Let’s face it, that’s almost guaranteed to happen sometime or another over time.??

The flexibility of Excel is its most powerful attribute but also its weakest.?

If you apply a standard then we can keep its most powerful attribute of flexibility and remove the risk that is caused by rogue errors and unstructured formulas and logic creeping into models.?

The benefit of a recognised standard means that technology like modular spreadsheet development is possible. Modular spreadsheet development technology is certainly one of the most interesting advances in the financial modelling sector to date.

Modano was the world's first and most highly recognised and powerful example of applying modular spreadsheet development technology and leverages the BPM standard.??

Openbox is a similar technology that attempts to do a similar process with its use of reusable components of logic mainly focused on project finance and applies for the most part the FAST standard.?

Microsoft also recognises this need for the re-usability of logic through the creation of Lambda and Dynamic Arrays in recent years,?which greatly reduces the number of unique formulas in a model, which reduces formula risk given there are fewer formulas to get wrong.?

We recently applied dynamic arrays for time series extension and expense modelling. See the links below if you want access to the free recording and expense model built for Weel using EXL Cloud.

Also, stay tuned for a Lambda article coming out soon in 2023 for more on this.

The ability for team members to support each other and check each other's models is a huge benefit.

The chance to retain and grow all that Excel knowledge developed over years is a hidden benefit. It’s still the most used software by accountants and finance professionals who are serious about modelling.??

Modelling audit software is also incredibly useful in detecting potential errors and reducing risk.?

Financial modelling consultancies, like Model Citizn, can scale and grow more easily and it enables modellers to feel more supported.?

Too often highly sought-after modelling consultancies (remember demand-supply gap) can’t scale and are limited to 1 or maybe 2 modellers due to a lack of a standardised approach or scalable modelling technology inside Excel.

A systemised approach allows scalability, support and collaboration in model building.?

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

In school, you have rules like no swearing, no spitting, and no fighting and you are strongly advised to do your homework.

Like all rules, these are made to be broken...oops sorry I meant followed.

In all seriousness, sometimes rules can be a pain in the butt when (in very limited circumstances) they actually make things more complex and difficult.

But complete anarchy and lawlessness at school are probably not ideal learning environments either.

Well, picture for a moment there is an entire class of rogue students, that's exactly what is in the world of rogue spreadsheeters today, who were unfortunately never made aware of the standards or given the right training to do better.

Nobody has ever taught them what good modelling etiquette means!

There is simply no appreciation for the need for a standard way of designing and managing spreadsheets. It’s finger painting by numbers and often a complete mess!

It is simply not a priority, ahead of the next major technology project (ERP implementation) which aims to remove spreadsheets altogether!?

Kids should start by going deep in Excel and building financial models for their personal budgets, vacations or their parent's next house or major investment!?

Whilst doing so, please apply some rules, don't just go rogue finger-painting style!

What practical steps can accounting and finance people take now to learn more?

Here is a quick checklist of items you can benchmark yourself against.?

If you like you can even score yourself in the self-assessment below and see where you rank out of 200.

Do you have the following??

1. Separate assumptions, logic and output are closely visible with colours and locations transparently shown?
2. No hardcoding in formulas other than binary ie 1 or 0
3. Consistent formula logic ideally in blocks but definitely no changes to formulas midway across models?
4. Consistent and unique time series structure at the top of every relevant sheet that has a time series. Never mix time series across the same sheet
5. Break out the logic with flags and counters where required and limit the size of formulas?
6. Keep intersheet formula links to a minimum and link across only once to source and refer within the sheet to the master, not multiple servant references (daisy chaining). Here is a reference to this in an earlier article .??
7. Error and data validation checks summarised and easily identified preventing and highlighting errors.?
8. The cover sheet and model purpose are clearly defined.?
9. Navigation is structured either through sheet colours, a hyperlinked table of contents or a hamburger menu dropdown
10. No hidden or deep hidden sheets or logic embedded in VBA code or very deep nested if statements
11. A model containing a scenario manager, or it’s not a model by definition!?
12. Dashboards and visual outputs are essential to help users gain an understanding at a high level of both the assumptions and outcomes visually and to spot-check anomalies.
13. Summarised high-level executive KPIs or tables focused on the purpose of the model eg valuation, margin % or returns often showing IRR or XIRR.

If you want to assess where you are today then click this link for our free self-assessment which expands on the above to assess specific areas in more detail and where you may be leaking real value which can be extracted from your models.?

If you want to learn more, follow the links below.

Before you embark on any form of standardisation of models it’s worth reading an article written a few years ago (“Machines are not coming they are here ”)?on the challenges you might face when changing the way people use spreadsheets.??

Below are other useful links to resources that are mostly free!?

• Best Practice Spreadsheet Modelling ?
• CA ANZ Elective in Financial Modelling (includes the study guide)
• EXL Cloud
• FAST by F1F9
• Full Stack Modeller Introduction
• FullStack Modeller survey ?
• ICAEW Financial Modelling Code
• ICAEW - Introduction to Financial Modelling and 10 Principles
• Lambda
• Dynamic Arrays used in Weel expense modelling ?
• Modano
• Openbox
• PwC Global Financial Modeling Guidelines Finance and Accounting Modelling Assessment Tool
• SMART/Mazars
• Standards article by Kenny (Gridlines)

How important is this skill in the context of learning Financial Modelling?

It’s fundamental, period.

If you don't know of or have never applied in any way some of the concepts above, you simply cannot call yourself a financial modeller, but a spreadsheeter is probably the correct term.

If you start to apply these and follow the content in the rest of the series you are well on your way to being a financial modeller and soon will be highly sought after.?

Take it one step further and get qualified!

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

Whilst ChatGPT assisted in writing parts of this article, it clearly has no knowledge of financial modelling standards and wouldn’t be able to build a model that is compliant with best practice (for now!)

One of the reasons that this article is so controversial is it also starts to open the door and lay the platform for automated model-building technology on a new level, which already exists today but it’s not AI, just very smartly built code packaged into Addins.

Picture for a moment where you are no longer typing into Excel the workbook structure and formulas but rather dictating (maybe “Siri” or “OK ChatGPT” build me an integrated 3-way model with the following blocks of logic (volume x price) shortly after leaving a meeting.??

Then, when you get back to your desk there is a fully built 3-way model as per your request. This sort of evolution is absolutely game-changing and not far away at all.?

It won’t be a perfect model, but it will save you bucketloads of time!?

The future is very exciting and in many cases, we won’t be able to take full advantage of it for financial modelling without adopting some form of best practice standards for building models. It’s the critical first step!

Stay relevant, stay connected and don’t fall behind!

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.