Financial model definitions can be tricky. Financial models are often dependent upon numerous functional areas and...
Financial Modelling and Automation
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
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
- Data Sources – What systems need to be queried to extract the relevant data for use in the model
- 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
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
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
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.