Financial Modelling and Automation

Financial Modelling and Automation

By Mohammed Hussain, Senior FP&A Analyst at BCA Logistics Limited

Mohammed is an advocate for clean financial modelling and process automation. His key function is "modern" problem solving by blending traditional Excel techniques with BI as a visualisation tool. He strives to produce models that are transparent and easily understood by the end user. This is achieved through efficient Workbook and Worksheet design, and VBA.

His transcendent goal is life is to help others recognise where they can introduce efficiencies in their work in the hope that this extends to other areas of their lives.

LinkedIn account: https://www.linkedin.com/in/mohammedafhussain/

Disclaimer: Financial Modelling has no strict “right” or “wrong” method of application. It does, however, have forms of best practice and this what this article attempts to highlight

Driver-based modelling has been around for a long time, yet very few know the details behind it and how to implement it. Why? Perhaps because if I ask you the question “what do you think about when you need to build a model?” – the answer is verbose and lengthy detailing the technicalities of the build. As FP&A professionals, we need to transcend the low level of detail and understand the purpose of what we are doing; in this case – what drives the business? 

FP&A fits into a very niche gap of empowering people to understand the “bigger picture” whilst simultaneously preparing the low-level modelling needed to see the picture through to completion. Somewhere in between these 2 roles exist a problem: Wastage
The “wastage” may come in the form of:

  • Old processes which like old habits, cause more damage than good
  • Management who prefer to use a plaster instead of treating the wound
  • Repetitive processes taking days could be automated to hours or minutes

All of the above (and more) go into the making of the beautiful…Business Financial Model

1. Goal of a Model

Financial Models exist to allow people to make informed decisions. It is our jobs as modellers to ensure they have the easiest time in making those decisions

This begins from the end…how the model is presented

To draw from the FAST Standard:

“The high-level layout of a model must reflect the requirements of two fundamentally different groups of interested parties”

The two parties being the Modeller and the Intended User

As modellers, we may often lose sight of the goal and try to implement things into the model that may not be necessary. Therefore, it is vital that the vision of the intended user is always kept at the front of the modellers mind when designing and building the model

2. Brief Outline of an effective Financial Model


 

Output

Without an Output that mirrors the vision of the Intended User, the rest of the model is of little use

Depending on the purpose of the model, consider the following in model Output:

  • 3 financial statements (COS and OH line items to be kept relevant to industry)
  • Variance analysis. Less ALWAYS means more if the Intended User is a leader or business owner/manager. Detailed analysis can be done separately for those that require it
  • A storyboard. Yes…tell a story! Remember the last time you watched a thriller movie and it kept you alert and focussed? The same approach needs to be taken when communicating financial messages to (potentially) non-financial stakeholders. Give them a reason to spend more than 10 seconds before their phone or colleague distracts them
    • A storyboard explains the movement in 2 numbers in a way that is meaningful. A variance analysis only states the movement. It doesn’t necessarily offer actionable insight.
    • The storyboard “Chunks” the many drivers of a business into to easily understood sections
  • Example: An Emotional Intelligence training academy. This academy offers course to business leaders. The purpose of this academy is to empower leaders to step OUT of cruise control, and think creatively by solving a rubix cube (without algorithms). The key is they are forced to use their innate problem solving skills to solve the cube instead of following a defined set of instructions. The academy is new in the market and to prove itself, only requires payment once a leader solves the rubix cube. The drivers therefore are:
    • Number of leaders taking the course each month
    • Time taken to complete the cube (the less time the quicker the revenue is earned)

For illustration purposes, a simplified variance analysis may show the following: 
 

This is useful – it informs the business that month 2 provided £19,250 more receivables for the business as a result of 50% more leaders taking the course.

The storyboard may extend on this as follows:

  • Month 2 saw a 50% increase in leaders taking the course resulting in c70% more receivables
    • This is as a result of previous leaders referring the course on to others
  • The time taken to complete the cube has increased in month 2. This may be an indication that the training content is not helping the leaders solve the cube quickly enough
  • As the key driver of the business is time taken to complete the cube, this could result in the future dip in leaders taking the course
  • The action is to revise the training the leaders receive before being asked to solve the cube

The above is explained in words, but may often be communicated via a dashboard

Inputs

  1. Data Sources – What systems need to be queried to extract the relevant data for use in the model
  2. Variable/fixed inputs – splitting out what the business CAN and CANNOT (to some degree) control is vital. What are the KEY DRIVERS of the business? Establishing these key drivers is perhaps requires most thought from both parties

Workings

Transform those Inputs, and make them more meaningful before being sorted into the correct Output sheets

Workings can be the backend layer that makes up the financial statements for example, or as simple as detailed analysis of a specific P&L item

Example: Sales on a P&L may have workings that detail all of the services the company provides that generate the sales and the drivers of each service type. Or, a CAPEX schedule that feeds the Fixed Asset Register to populate the Balance Sheet

Automation Layer

Please use VBA Automation with great care. Getting carried away with coding and automating everything you see is an easy trap to fall into, one I have fallen into many times!

In order to continue being effective in driver-based planning, we need to constantly be challenging the driver inputs and the outputs they produce. It is therefore suggested to leave a manual element when it comes to this specific area of Financial Modelling 

If this model is to be used for weekly forecasting, automation may be focussed around ETL processes from a data source

If this model is to be used for month-end reporting, automation may be used to roll over previous accounts, and query data from accounting systems

Summary

So, somewhere in the middle of empowerment of purpose and fulfillment of detailed models, remember to ask the question:

“does the output of this model serve the overall objective it was intended for?”

I am excited for how Artificial Intelligence will empower FP&A professionals to adapt and change. A small insight into what could be different with the intent of AI is the analytical tasks could be automated away from us (such as statement creation and variance analysis). This means FP&A professionals can focus more on leading businesses in a positive and transformative direction

Whilst AI can analyse the past to the nth level of detail and generate a promising prediction of the future, the question is – can it be developed to have the objectivity of a human and interpret its own prediction to the Intended Users?

AI and Humans CAN and WILL coexist. Fear of AI taking over the world is simply the same as when an animal is threatened by a predator. This “Fear” is irrational and is representative of the reptilian brain that ensures our survival.