Developing a financial modelling strategy that supports business goals

Transcript of a presentation provided by Adrian Miric to the Leaderex conference

Before we go into developing a financial modelling strategy that supports business goals I want to address some Financial Modelling misconceptions.

Financial Modelling

IS NOT detailed debt, NPV and IRR calculations
IS NOT Monte Carlo Analysis
IS NOT The next step after Advanced Excel
IS NOT Only for the top of organisations

Financial Modelling

IS Any calculation that is forward looking (hence our course is called Financial Modelling, Forecasting and Budgeting)
IS Important for everyone in the organisation down to almost the lowest level
Should rather be called Decision Modelling as it is not always financial
Can be used for any Business Goal

As a result I will refer to the strategy as rather applying to Decision Modelling but it is interchangeable with financial modelling.

We will look at the following areas

Why a financial/ decision modelling strategy matters
Who should the strategy incorporate
What are some of the areas it should cover

Why a Financial/ Decision modelling strategy matters

Below some research results showing the prevalence or errors in spreadsheets, which are the main tool used in the modelling process.

Although some of these are a bit old now, various groups looked at a selection of spreadsheets and tried to identify errors, sometimes based on their particular focus (tax errors, >5% errors etc).

You will note that the percentage with errors is never zero percent. Our experience with Financial Model reviews bares this out. We have never performed a review where we found no errors. In fact if we don’t have at least 50 queries, we tend to go and find what we have missed.

In the ideal world, a spreadsheet model is carefully planned, built with lots of time and there are no last minute changes. In reality, there are time pressures, last minute changes and new requirements that appear and these pressures cause the errors.

But so what, no one has lost money due to these errors! See below

Financial Model errors cost real money

EUSPRIG maintains a list of known errors in financial models and spreadsheets where the impact is shown. Some examples include

US$ 26 million hedging error- budgeted for more than needed
South African example- Eskom and the Optimum fine- spreadsheet error reduced fine by R1.5 bn
M&S financial reporting error
Rounding of numbers on budget- underestimate of $36 million
Inserting/ deleting rows or columns- underbid of project amounting to $250 000
Incorrect sign on accounts- $2.3 billion information distortion
Your own examples here

Note that these don’t all appear to be the ‘typical’ financial models. Some seem to be budgets, others are valuation models, some are simple management reports with some forward looking numbers.

So the above explains why you MUST implement a financial/ decision modelling strategy. Errors occur all the time and they do cost real money.

Who should the Financial/ Decision Modelling strategy address

Upfront I will tell you I am biased. We believe the whole organisation should be considered in any strategy.

Instead of just listing every department in a business, below some case studies of recent consulting engagements to understand who in an organisation uses financial/ decision model thinking.

Typical Financial Models and Financial Model Reviews

In the past year or so we have been involved in the following financial model builds and reviews. Note that in all these cases, the client knew they wanted a financial model and asked for it.

Fish farm
Greenfield property development (South Africa, Rwanda and Baghdad)
Mine feasibility models
Rental property investments

Note all these assignments were characterized by the typical financial modelling requirements i.e.

10 plus years of forecasts
Detailed operational modelling- taking into account life cycles of fish, sales profiles and repayment methods of various countries property developments and key mining metrics)
Detailed debt, NPV and IRR Calculations
Sensitivity analysis
Requested by the FD/ CFO/ New Business department- generally the top levels within an organisation.

If this was the only thing to cover in the financial modelling strategy, then it is very simple.

There are only a few people involved so just focus on them.

But we discovered that the techniques used to solve these problems, were also being used on assignments that would never have initially been called financial modelling. When the client contacted us they didn’t know they were asking for a financial model, but that was the reality. Some examples below

Not so typical Financial Models

Actual, Budget and Forecast Reporting Tools

Management reports that can (quickly) update for the latest actual results, and at the same time compare to budget/ forecast, but more importantly look at the remaining period and indicate what the company is likely to end up on.

Quoting Tools in manufacturing with direct and indirect costs

A quoting tool for large scale manufacturing that takes direct costs into account (simple financial model stuff) but also takes into account the Indirect costs.

Contract Management Reports in Biotech

In the one case the legal department needed to track performance against a contract. The historic information needed to be included, but more important was an updating forecast to assess what needs to be done over the remaining period to meet the contract obligations and how feasible it is to do this.

Machine Maintenance forecasting- when will a part fail

In these cases it is NOT a financial model. All the clients wanted to know was, given the machine parts historical lives, when will each part likely fail given some forecast production. No mention was made of financial modelling, but the techniques used in financial modelling was the only way to provide the solution. Note that there was also no financial impact included.

HR BEE Scorecard

HR needed to keep track of their head count statistics, but also assess the possible impact on these statistics taking into account future retirements and other movements in order to meet BEE targets.

Note that the above assignments are all characterized by

Some historic information and then forward looking
Short term- next job, next year, next few months
No debt, NPV or IRR aspects
Sometimes does not even cover financial information
Requested by FD’s, Sales team, Legal Departments, Engineers and HR

IFRS and the Non Typical Financial Models

Recently we have also assisted in a number of IFRS calculations. In all cases IFRS calculations are financial models. In the one case the financial model was very small (20 rows) but needed to be repeated 400 000 times for the underlying assets.

The clients did not ask for an IFRS financial model. They asked for help with the calculations and as it turned out, these were just financial models repeated again and again.

These assignments were characterised by:

Forecasting forward and then bringing back to today’s numbers
Significant Time Value of Money Calculations
Requested by FD but for the accountants.

So who should be considered as part of any strategy to address financial and decision modelling?

We believe that

Most people in any organisation should be aware of it
But the level of knowledge will differ

NPV, IRR, Monte Carlo simulation are just one minor part of decision modelling. Very few people need to know this level of detail. But there are tons more decision models that are being built that would benefit from understanding financial/ decision modelling.

What should be covered in the Strategy

Difficult to pin down as the strategy needs to potentially cover so many areas
It is a people issue, there is no magic bullet piece of software
All people within the organisation should have access to
- Best practice guidelines – freely available
  - They are common sense and not complex
  - Our online financial modelling course has a free section which covers the best practice techniques
Incorporate the thought process into training, typically in Excel training but a standalone half day course on the topic is also useful.
The strategy needs to create consistency across the organization
Emphasis on getting the ‘engine’ right- NPV, IRR’s are easy to calculate when the operational numbers work. Too much emphasis is placed on this financing jargon and not enough on the engine of any model which are the operations.
Importance of decision being made must affect approach
Consider minimum expected outputs
Share concepts not prescriptive on method
Flexible to address the different users of decision models

Some items to include in the Strategy

Importance Questionnaire
- SOX in the USA has helped with some of these
Insist that all stakeholders are considered (or at least listed)
Must contain all financial statements for a financial model
- NO Balancing number on the BS. It MUST self calculate.
Some form of check for other models (mass balance for engineers?)
Insist on Testing- lack of testing of decision models is the biggest issue

If you want to be a bit more prescriptive consider including some of these:

Time periods should be monthly or less, never annual. We can show you an Excel trick that converts monthly into annual in 5 minutes, but the other way is much harder.
Must have error check sheet built in

Whatever you end up including, keep in mind that you are trying to create a common approach to forward looking calculations (not just financial models).

It is also important to note that Decision Modelling is NOT an advanced skill. Any Intermediate Excel user can (and does) do decision modelling. How well they do it depends on the knowledge you provide.

Financial Modelling Strategy Conclusion

Financial Modelling is about more than the ‘typical’ financial models (should be called Decision Modelling)
It is a people issue, best addressed by training and knowledge sharing
Your strategy MUST include every department in the organization as they are all making decisions continuously

20% Coupon for online Financial Modelling Course

If you attended the live presentation you will have the coupon to receive the 20% discount on the online financial modelling, budgeting and forecasting course

Contact Details

www.AuditExcel.co.za
info@AuditExcel.co.za

Reception- +27 (0) 66 492 8062