Your Questions Answered #4 – Is Excel still the go to application for financial modelling?

‘Financial modelling and Scenario Planning: Mastering Effective Decision-Making’ webinar recording now available.

The question

Is Excel still your go to application for model building, especially for larger more complex modelling? If not, what are the best alternatives?

Answer

Yes, for financial and business modelling. And, for many reasons:

1. Excel is portable - pretty much everyone can access the model. This is important when considering how it will be used and validated. A model for one admittedly complex project involved a 120-year workbook that had to be used and/or audited by three of the big six (as it was then) accounting firms, four central government departments, several merchant banks and various specialist agencies. Excel is one of the few tools that all participants had access to and, crucially, had the necessary level of expertise to be able to operate and gain trust in the model.
2. Excel has an immense amount of power and capability within it, especially since Microsoft incorporated tools such as Data Streamer, Power Pivot and Analyze Data. There is also a large third-party eco-system which provides Excel add-ins to extend the native capabilities, such as Oracle Crystal Ball for risk management.
3. Excel is flexible - it is easy to extend and change. Using the best practices demonstrated by Ben in the webinar each set of calculations can be insulated from others so that a modular approach can be taken. This means that if you need to rip out your debt financing calculations and redo them it is possible without affecting the rest of the model. One additional tip in this area is to include at the bottom of each calculation section a repeat of the calculation results. You can then link the rest of the model to this section and any changes to the actual calculations will not require testing throughout the rest of the model.

Depending on the type of modelling and usage there may be more appropriate tools available. It is possible to create a manufacturing plant process flow model in Excel, but specialist tools exist (such as Lanner Witness) that can be a better fit.

The question

Is there a limit to the number of drivers you can have before overcomplicating a model? What would you do where there are too many? I'm thinking in terms of OPEX drivers.

Answer

It depends on the purpose of the model and hence the degree of accuracy needed. From a scenario planning perspective, you are looking to understand material movements in outcomes based on changes in key drivers.

One useful way of validating the accuracy of your model is to run it using historic actuals for drivers and determining how close the results are to reality. This can be used to assess the degree of accuracy required and achieved - being within 5-10% of net operating cash flow is a fairly typical benchmark.

The stress test models required by the Bank of England focus on eight key variables (Table A of link) such as GDP change, residential property prices and UK equity prices.  In this case most OPEX can be included at a very high level - a change in T&S is unlikely to be material in comparison to s reduction of residential property prices of 31% and consequent mortgage stress costs.

One practical way of determining which drivers are most important is to look at those which are actually being changed/considered by the users/recipients of the model. If you have modelled 15 rows of OPEX and only two are being used, then perhaps consider collapsing the other 13 into two or three summary cost headings.

Bear in mind though that as people's understanding of the scenarios increases, their questions may become increasingly fine grained and having some basic modelling for additional drivers may be useful, especially as timescales shorten and decision time approaches!

The question

Interesting that you mentioned using Monte Carlo to develop the input. What is the usual process to produce or validate the inputs for each scenario?

Answer

Work with the experts in your organisation. Getting their buy in to the inputs and calculations makes it far easier when presenting the model and its results to senior decision makers. Remember that what a model represents is potential change and that is not something people are typically comfortable with so finding ways to improve comfort is key.

Depending on the organisation and scenario being modelled this can be done one-to-one or in cross functional workshops. Often the model development, identifying relevant inputs and calculations, is done on an individual basis and then model use is done in workshops.

For example, manufacturing plant inputs and calculations would be defined with the operations team while sales parameters (prices, volumes, discounts) and calculations defined with the sales team. They would then be brought together in a scenario workshop where cross-function constraints and opportunities are identified.

A hypothetical workshop may start with the sales team assuming 1,200,000 units of product will be sold each year, flat line 100,000 per month. Manufacturing may then explain that there is a partial plant shutdown planned to upgrade production lines which means that in August there will be capacity to manufacture only 60,000 units and there is only warehousing space for 30,000 so there will be a shortfall of 10,000 units.

A logistics specialist says it would be possible to hire additional short term warehousing space at a cost of £75,000 for the month.

Sales confirm that expected margin on the 10,000-unit shortfall is likely to be £150,000 so it is worth taking forward the upgrade/extra warehouse scenario for review and decision.

The question

Where we have implemented a tool (Adaptive Planning) which gives the benefit of live inputs, no consolidation of spreadsheets by FP&A, and custom integration of real data and metadata - how can we leverage that tool and those benefits, whilst potentially using excel for scenario analysis?

Answer

It's great that you have such a solid base. These tools (others include Hyperion Financial Planning and SAP Business Planning and Consolidation) are normally internally and operationally focussed and contain significant amounts of detail which, while useful to the individual contributors, are not necessary for an outward looking strategic decision-making model.

Create a standard extract from the detailed tool that can be used to inform Excel scenarios and then use the Excel flexibility to understand the impact of strategic scenarios - what if interest rates rise by x%? Can the organisation still afford to fund the additional assets required to deliver its sales plan? Are there any tax changes which would affect investment plans? What is happening with forex rates? Are there any merger/demerger opportunities that would enable strategic objectives to be achieved? What if supply chains need to shift from one region to another?

The question

How would you go about reviewing and performing due diligence on such a model?

Answer

This is a complex subject and the ICAEW has published a number of very useful resources and training courses. 

From personal experience, a significant amount of comfort can be gained quickly by reviewing the structure of the model. If a model is well laid out with inputs, calculations and outputs separated; calculations broken down over several rows so they are easy to follow rather than being hidden in long complex formulae; and inspection of random calculations shows that formulae are consistent across a row and do not include hard coded numbers then it is more likely the model has been constructed in a considered professional manner.

As mentioned above, there are a number of add-ins available for Excel and when it comes to reviewing models this is no exception. Tools such as Arixcel, or Operis Analysis Kit (OAK) can be quite powerful to assist the checking and validation process, especially if that is a key part of your role (e.g., as an auditor either internal or external to the business). They provide functionality that enhances Excel’s built-in formula auditing tools (on the Formulas tab), such as spreadsheet comparison and checks for the vertical or horizontal consistency of formulas.