Strategic merger – Corality and Navigator form the Corality Financial Group

Financial model developers and relevant stakeholders spend a lot of time building a financial model. Unfortunately many financial models never fully yield enough value or relevance to a company. Even when immense time and resources are employed in the model development stage. Here are some of the reasons why your financial model has been consigned to the scrap heap.

Audit Risk

Model stakeholders, medical especially executive management, don’t have adequate confidence or understanding of the financial model. The major reason is the esoteric and complex nature of the model, which has been over engineered without the use of a logical process flow across worksheets. Often the developers have tried to calculate numerous elements in one column, instead of breaking it down and calculating each element one at a time.

A prime example would be a model that uses nested IF statements to calculate outputs.

Unknown External Referencing

A common culprit with audit risk is the incorrect use of random cell referencing to external sources, such as a file from an email, a file saved to a personal C drive or desktop, or a third-party’s hard drive network. To mitigate against this problem, always save all files to your Company’s network and ring fence all external data referencing to designated Model Import worksheets in your financial model.

Cell Value Errors

This is related often to models referencing external files. The more non user-friendly a model is the more prevalent and harder it is to find the exact location of cell value errors. As mentioned, if cells reference only external values via model import sheets, and data entry is restricted to the Assumptions area of a model, it will go a long way to avoiding this issue.

A comprehensive rollout of Basic Error and Alert Checks across the entire model would more effectively identify such errors. Remember to cross check not just headline numbers from source financial statements on the Model Import sheets, but also calculated numbers such as operating cash flow from a) a direct cash flow and b) an indirect cash flow perspective.

No User Guide

Like anything in life, we humans sometimes underestimate how challenging (on the surface) our work is for others to understand. As much as a user guide is a drag and a bore, it is imperative to clearly articulate in writing with screen shots, the essence and mechanics of the financial model. The user guide must be comprehensive, straight to the point and written clearly.

One Gatekeeper

The model developer is the sole user, operator and custodian of the financial model. No one else in the company ever gets around or has the opportunity to manage the model, let alone understands how to maintain the model going forward. Hence the sole gatekeeper of the model leaves the company, and no one else can take over the reins.

It is paramount with all financials in your company, where executive management rely on it to make financial decisions such as a strategic plan, that there is a knowledge transfer to at least one or two other people in the organisation.

Poor layout

The commercial world is an ever-changing organism. You must remember this when building the financial model. No one can predict the future. Who knows, half the company might be hived off into a joint venture entity, numerous acquisitions may occur or there could be an internal corporate reorganisation. There must be adequate provision, flexibility and room allocated in the financial model to cater for material corporate changes. Do not be afraid to include an additional 24 (or another applicable number) business units, projects or assets into the Assumptions area of the financial model.

Hence significant corporate changes must result in a relatively seamless update to the model. Such significant changes must not jeopardise or compromise the model.

Use of static functionality

Although Pivot Tables are a powerful, value-adding and time-saving feature, by default they do not automatically update to reflect changes in source data. Herein lies the problem, assuming the model has not been assigned VBA code to ensure automatic updating, this is a major risk with models that rely upon Pivot Tables. This is something I discussed in an earlier article, The Dangers of PivotTables, array formulae and other non-seamless Excel features, which sparked debate among peer financial modelling professionals.

Similarly Ctrl+Shift+Enter (CSE formulae) or Array formulae suffer from a similar issue and pose a risk to a model. A CSE formula can indeed perform the calculation of numerous formulae, but whenever the source cells change, the CSE formula will need to be updated manually or via customised VBA code.

Scrap conclusions

There are many reasons why your financial model can be at risk of the scrap heap. Unfortunately some reasons are often outside your control or as a result of company politics. However there are measures to consider when building a model, which may help to extend the longevity of your financial model going forward.

Keep the model simple, clear and concise, in order to ensure it is audit friendly. Restrict all external referencing to data that has been placed into the Model Import worksheets.  Safeguard against all cell value errors in the model, through the use of Error and Alert Checks. Take the time to write up a user guide with the thorough use of screen shots to better explain the model. Avoid only one person being the builder, user and manager of the model.

Make certain of an aesthetic layout with provision to add new business units or other entities in the future. Avoid the use of static functionality such as Pivot Tables (if so, make sure to attach VBA code) and CSE formulae (ditto, please use VBA code).

Such measures will help you to realise model user flexibility, user friendliness and user transparency as per the auspices of SMART best practice modelling methodology, which states the importance of minimising model risk, enhanced model stakeholder confidence, heightened modelling productivity, and the ability to undertake a model audit in an efficient manner.
Financial model developers and relevant stakeholders spend a lot of time building a financial model. Unfortunately many financial models never fully yield enough value or relevance to a company. Even when immense time and resources are employed in the model development stage. Here are some of the reasons why your financial model has been consigned to the scrap heap.

Audit Risk

Model stakeholders, sickness
especially executive management, don’t have adequate confidence or understanding of the financial model. The major reason is the esoteric and complex nature of the model, which has been over engineered without the use of a logical process flow across worksheets. Often the developers have tried to calculate numerous elements in one column, instead of breaking it down and calculating each element one at a time.

A prime example would be a model that uses nested IF statements to calculate outputs.

Unknown External Referencing

A common culprit with audit risk is the incorrect use of random cell referencing to external sources, such as a file from an email, a file saved to a personal C drive or desktop, or a third-party’s hard drive network. To mitigate against this problem, always save all files to your Company’s network and ring fence all external data referencing to designated Model Import worksheets in your financial model.

Cell Value Errors

This is related often to models referencing external files. The more non user-friendly a model is the more prevalent and harder it is to find the exact location of cell value errors. As mentioned, if cells reference only external values via model import sheets, and data entry is restricted to the Assumptions area of a model, it will go a long way to avoiding this issue.

A comprehensive rollout of Basic Error and Alert Checks across the entire model would more effectively identify such errors. Remember to cross check not just headline numbers from source financial statements on the Model Import sheets, but also calculated numbers such as operating cash flow from a) a direct cash flow and b) an indirect cash flow perspective.

No User Guide

Like anything in life, we humans sometimes underestimate how challenging (on the surface) our work is for others to understand. As much as a user guide is a drag and a bore, it is imperative to clearly articulate in writing with screen shots, the essence and mechanics of the financial model. The user guide must be comprehensive, straight to the point and written clearly.

One Gatekeeper

The model developer is the sole user, operator and custodian of the financial model. No one else in the company ever gets around or has the opportunity to manage the model, let alone understands how to maintain the model going forward. Hence the sole gatekeeper of the model leaves the company, and no one else can take over the reins.

It is paramount with all financials in your company, where executive management rely on it to make financial decisions such as a strategic plan, that there is a knowledge transfer to at least one or two other people in the organisation.

Poor layout

The commercial world is an ever-changing organism. You must remember this when building the financial model. No one can predict the future. Who knows, half the company might be hived off into a joint venture entity, numerous acquisitions may occur or there could be an internal corporate reorganisation. There must be adequate provision, flexibility and room allocated in the financial model to cater for material corporate changes. Do not be afraid to include an additional 24 (or another applicable number) business units, projects or assets into the Assumptions area of the financial model.

Hence significant corporate changes must result in a relatively seamless update to the model. Such significant changes must not jeopardise or compromise the model.

Use of static functionality

Although Pivot Tables are a powerful, value-adding and time-saving feature, by default they do not automatically update to reflect changes in source data. Herein lies the problem, assuming the model has not been assigned VBA code to ensure automatic updating, this is a major risk with models that rely upon Pivot Tables. This is something I discussed in an earlier article, The Dangers of PivotTables, array formulae and other non-seamless Excel features, which sparked debate among peer financial modelling professionals.

Similarly Ctrl+Shift+Enter (CSE formulae) or Array formulae suffer from a similar issue and pose a risk to a model. A CSE formula can indeed perform the calculation of numerous formulae, but whenever the source cells change, the CSE formula will need to be updated manually or via customised VBA code.

Scrap conclusions

There are many reasons why your financial model can be at risk of the scrap heap. Unfortunately some reasons are often outside your control or as a result of company politics. However there are measures to consider when building a model, which may help to extend the longevity of your financial model going forward.

Keep the model simple, clear and concise, in order to ensure it is audit friendly. Restrict all external referencing to data that has been placed into the Model Import worksheets.  Safeguard against all cell value errors in the model, through the use of Error and Alert Checks. Take the time to write up a user guide with the thorough use of screen shots to better explain the model. Avoid only one person being the builder, user and manager of the model.

Make certain of an aesthetic layout with provision to add new business units or other entities in the future. Avoid the use of static functionality such as Pivot Tables (if so, make sure to attach VBA code) and CSE formulae (ditto, please use VBA code).

Such measures will help you to realise model user flexibility, user friendliness and user transparency as per the auspices of SMART best practice modelling methodology, which states the importance of minimising model risk, enhanced model stakeholder confidence, heightened modelling productivity, and the ability to undertake a model audit in an efficient manner.
You have received a call from a client. The client wants a pricing tool or some other financial model built. The model must add value to the client; it merely can’t rehash and beautify the client’s legacy spreadsheets. The notion of value-adding is open to discussion; for example it could be forecasting and valuing the client’s company, link
producing scenario analysis, nurse or it may be simply undertaking sensitivity analysis. This article will focus on the value-adding power of a sensitivity analysis in a pricing model.

Unlike a more macro-approach of a client’s financial model, a pricing tool will compute expected financial returns of a company delivering a service; based on price, units of sale, and direct and indirect costs. Allocating indirect costs incrementally based on activity-based costing is often overlooked in price decision-making, when a service company is aiming for a specific return on sales.

The value of sensitivity analysis

The value-added benefit of a sensitivity analysis for a service company is the ability to compare a preliminary price, compared to hypothetical changes in certain key drivers; in order to compare expected financial returns. One example depicted in the screen following is the ability for the client to increase gross income from $131,212 to around $200,000, based on expanding course enrolments from 22 students up to 34 students using Solver.

Dashboard to compare sensitivity outputs against base case

Your financial model can go one step further. It can leverage its existing dashboard functionality and deliver further value to your client. A great example is a dashboard that can compare the sensitivity outputs against base case, which in this case is a service company’s base service price, sales volumes etc.  The following Waterfall Chart can demonstrate this. This model can generate such a chart in terms of Gross Profit, EBIT, Net Profit After Tax (NPAT) or Operating Cash Flow; in this instance the Gross Profit option has been selected to reveal the additional 12 students increases Gross Profit from $88,234.8 to $148,667.2.

The essence of sensitivity analysis is such that users may only want to get an understanding of how revenue or cost drivers influence returns in a financial model. The sensitivity analysis of a model should facilitate this user-friendly flexibility. The following enables users to understand how a mere 10% decrease in the Course Enrolment Fee, a 10% increase in the Course Curriculum Fee, and a 10% decrease in Students Enrolled; will generate small decreases in NPAT (from  $49,834.8 to $49,704.0) and Operating Cash Flow ($50,798.4 to $50,667.6).

Illustrate financial impact with Waterfall Charts

Again, the model can go one step further and illustrate its financial impact in a Waterfall Chart. The above example of Operating Cash Flow falling slightly from $50,798.4 to $50,667.6 due to changes in Course fees and Students Enrolled is depicted in the following.

How about if there is a 25% increase in Course Curriculum Fee per Student, assuming the other two changes remain constant; as you can see Operating Cash Flow moves materially from $50,798.4 up to $61,722.6.

The two different approaches, solver and arbitrary changes to specific drivers, highlight the benefits of a sensitivity analysis feature in a financial model. It provides an important tool for executives when they are faced with making financial decisions; either from the more micro aspect of pricing a specific service (as depicted), or more macro decisions such a divestment or a debt-raising.

Remember corporate executives are time poor and aren’t interested in trawling through pages and pages of worksheets. Sure they still need to understand the fundamentals via a high-level dashboard, but often they want to refer to a pictorial format such as a waterfall chart.

Concluding the insensitivity benefits of your financial model

The application of a sensitivity analysis is an integral feature of any type of financial model. It is a great way to turbo-charge the added-value that you can deliver to a client or inside your company. No financial model can merely regurgitate financial statements via an accounting system. It needs to deliver intrinsic value and push the envelope, in terms of offering management flexibility to operate the model and get a better sense of the revenue or cost drivers influencing financial returns to their company.
Corality yesterday announced the strategic merger of Corality and Navigator Project Finance into the Corality Financial Group, for sale
a decisive move to accelerate company growth by offering the industry’s most complete set of analytical consulting, training and advisory services. The merger will leverage the strengths of both companies to increase market share through a greater service offering and a larger consulting team including experts in project finance, banking, strategy, mathematics, engineering, accounting, actuarial and physics. The merger also focuses heavily on international expansion through both companies existing offices in London and Singapore.

Nick Crawley of Navigator Project Finance has been appointed Managing Director for the Corality Financial Group and Rickard Wärnelid from Corality Global Head of Business Development.

Greater market share and improved efficiency

In addition to the clear strategic benefits of combining two highly complementary organisations and service offerings, the merger means greater market share and improved efficiencies. The Corality Financial Group is set out to service a number of industry sectors including investments, corporate finance, project finance / PPP, private equity, risk, strategy, operations, commercial, accounting and training.

Read the full press release:  Strategic merger – Corality and Navigator forms Corality Financial Group.

Recent posts by Rickard Wärnelid

Comments for “Strategic merger – Corality and Navigator form the Corality Financial Group”

  1. Stefan says:

    Now the brains of modeling have joined forces. Congrats and best of luck

  2. Charlie Roberts says:

    This makes good sense to me as your financial modelling tutorials are very useful!

Comment on this Article