Our client required a budget and forecasting model, with in-depth modelling of acquisition scenarios and flexing different legal entities. The immediate need for the model was to underpin funding presentations and to support operational business forecasting and scenario analysis.
The key requirements were:
- Forecast the revenue and costs for several business units, allowing the user to adopt different methodologies to build and recognise the financial statement impact.
- Ability to model individual legal entities, activate or deactivate them in financial statement reporting, and flex business units falling within the different entities.
- Simple, robust method for incorporating historical data and to update for actuals on a monthly basis.
- Inclusion of a scenario handler to flex key business drivers and various acquisition scenarios.
- Ability to change presentation currency throughout the model.
Working closely with the finance director we gained insight into the key revenue and cost drivers and agreed an approach to forecast the business cash flows. The model layered in historical data, actuals and budgeted figures, as well as producing a 5-year forecast. Each legal entity could build up its relevant revenues and costs independently, which allowed the user to activate and deactivate each using a series of checkboxes.
The model allowed for the selection of presentation currency to flow through all relevant areas and update the model outputs to reflect the actual or forecast exchange rate.
Completion of the model within a short time frame enabled the finance director to use the outputs for an impending funding presentation. Additional functionality for acquisition scenarios was subsequently developed to provide the management team an ability to appraise the short and long-term impact of potential new legal entities and acquisitions. All of the assumptions and calculations made throughout the model fed into 5-year financial statements to provide oversight of KPIs such as asset bases, profitability and cash flow.