A robust and professionally built financial model can provide valuable insight and commercial analysis to support management's strategic decisions - whether a business is expanding operations planning a transaction or just needs to budget for the upcoming year.
Introduction
- Most accounting systems can collate and present historical data, but forecasts are still mostly undertaken with Excel models.
- Excel is still the most popular modelling tool due to the flexibility and transparency it provides, but these models are often out of date and not fit-for-purpose.
- The real drivers of business performance are not always incorporated into the forecast, leading to overly simplistic models which nevertheless over time become complex because of an accumulation of spreadsheet changes as the underlying business develops.
- This may give rise to the notion that “our business is unforecastable,” yet a methodology exists to develop a robust and easy-to-use financial model that can deliver useful and accurate forecasts from a given data set. The key to this is the concept of a drivers-based model.
What is a Drivers-based Model?
A drivers-based model or “bottom-up” forecast seeks to recreate business operations not on a “top-down” basis (taking the prior year’s number and extrapolating it for future period), but by looking at the business’s drivers. As an example, sales could be forecast by looking at the various constituents thereof such as volumes and price by key product. Multisite businesses can have costs built up on a location-by-location basis.
Using business drivers for forecasts enhances the potential accuracy of a forecast and thus credibility with key stakeholders. The exercise of creating a bottom-up model also forces the developer to consider the business in a fresh light – considering how a dynamic operation can be recreated into a series of two-dimensional spreadsheets (the “model universe”) requires additional thinking.
Core Elements of a Robust Modelling Process
A powerful financial model, taking into account the real drivers of the business performance, should be developed using a methodical and step-by-step approach. This approach requires significant planning and communication with stakeholders.
1: Understand business requirements
- Decide the aim of the financial model.
- Understand the company's business objectives.
- Identify what data you will have available to populate the model, what systems you will need to extract data from and what periods the data is available for.
2: Scope and design
- Document a financial model blueprint and agree to model specification. This sets out expectations on what the model will achieve.
- Think ahead to ensure model design is flexible for possible future changes. If not, there is a risk that it would be more time and cost inefficient to replace or update the model.
- Revisit scope during the process if required, e.g. new business requirements. It is important to be flexible and adapt as new information becomes available.
- Consider if system data needs to be manipulated before incorporating in the model. This may require tweaks to existing data reports or specific database manipulation.
- Devise the requisite outputs.
3: Develop the drivers-based model
- Deploy best practice modelling to develop a flexible, robust and transparent financial model. Modelling standards, such as the FAST standard, the ICEAW modelling code or Teneo’s in-house approach, are a series of modelling principles which help developers build models in a consistent way.
- If required, build in a scenario manager to ease comparison of different assumptions. This helps users identify the marginal profit / costs arising from different decisions.
- Create a dashboard with key information, graphical or numerical, with a few sensitivity / scenario toggles.
- Outputs can be prepared for further processing in visualisation software, e.g. PowerBI.
4: Communicate regularly and effectively with stakeholders
- Involve all stakeholders to achieve a common goal. A model is only as good as its inputs, and stakeholders are best placed to provide the most accurate information.
- Clear, concise and transparent communication is key. Time pressures mean that stakeholder time must be used wisely for a more effective working environment.
5: Test, test, test!
- Models ought to be tested rigorously both internally and through draft deployment. This reduces the risk of modelling error.
- A detailed and documented testing plan should be in place so that stakeholders get comfortable on what is being tested.
- A methodical model review should also be performed by a modeller outside the model build process. This helps reduce modelling errors and commercial inconsistencies.
- User feedback should be considered throughout the development process. This ensures user buy-in and a more fit-for-purpose financial tool.
6: Delivery
- The model is deployed, and further feedback is revised into the model.
- Outputs are assessed and assumptions may be revisited.
- User training is provided ensuring optimal usability and functionality.
Conclusion
A robust and well-designed financial model provides useful information to support management and investors’ strategic decisions. This can help identify opportunities and unlock value from more informed and transparent financial information.
Teneo’s modelling team can help develop simple, yet robust and fit-for-purpose financial models cost effectively – and in a time-efficient manner.
Case Studies
International Commodities Business
Situation
- A major commodities group (£7bn revenue) required multiple investment-grade forecast models built to support a potential sale of business
- As the business had a variety of different operations, each model needed to take into account the nuances in that business
- Commodity volatility meant a perceived lack of forecasting ability of the business
- Forecasting derivatives trading was inherently problematic
Teneo's Approach
- Team worked hand-in-hand with finance leadership in each company to understand key drivers of the business and the impact of market volatility on trading
- Teneo’s data analytics team supported the process by extracting the relevant transaction data to support the models
- Consolidated model for the entire group was also created to incorporate the results of the multiple models
- Models tested strenuously to ensure forecast outputs were in line with expectations.
Client Impact
- Robust models produced which allowed multiple forecast scenarios to be run
- Models have withstood advisor scrutiny and have supported the sale of the business
UK Chemicals Manufacturing PLC
Situation
- Chemicals business required a tactical tool that could quantify, at a P&L level, the impact of commodity price changes on raw materials impacting the manufacturing process, as well as selling price changes
- Current forecasting system required significant preparation and analysis; it could not adapt to rapid changes in price so did not give management the visibility they needed
Teneo's Approach
- Understanding raw material input ratios for each product produced was key to ensuring an accurate model, so Teneo’s team worked closely with manufacturing and purchasing teams to create and validate feedstock price sensitivity table
- Team liaised with company management to ensure inputs / outputs were fit-for-purpose
- Vigorous model testing was conducted to ensure outputs and sensitivities were in line with expectations
Client Impact
- Model dramatically improved management’s ability to forecast and anticipate risk in the business
UK Fashion Retailer PLC
Situation
- UK fashion retailer was seeking to quantify likely returns to creditors for the 80 entity group under different operational scenarios, including fire sales of assets around the group
- As there was extensive intercompany lending, realisations and creditor returns in any given entity were contingent on realisations in other entities, which were also reliant on inter-group funding
- Creditor security ranking and jurisdictional issues necessitated a complex waterfall calculation
Teneo's Approach
- Team worked closely with senior finance personnel in the business to gain a deep understanding of the Group’s financial position at an entity-by-entity level, understanding likely asset realisations and creditor claims and security rankings
- Detailed waterfall calculations were prepared in the entity priority model, showing returns to creditors for each entity, and formed part of a submission to Court on behalf of the business
Client Impact
- A multitude of scenarios and iterations of the model were run to explore the potential impact of various key assumption changes, with a particular focus on the realisation value of certain assets
- Model outputs supported a Court-approved recovery proposition for the business to avoid insolvency
Teneo Financial Modelling Practice
Teneo’s modelling team serves clients with the goal of delivering fit-for-purpose and commercial financial models that support strategic decision making.
We deploy our in-house pragmatic and professional modelling approach to develop robust and effective models that empower business leaders, CFOs and finance functions to unwind, understand and forecast financial information.
We also work with Teneo’s data and analytics team to understand the data and help transform it into powerful information required to make the right strategic decisions.