Our client required a whole-of-business reporting and forecasting model, with in-depth modelling of the sales pipeline. This gave greater visibility of the pipeline and allowed more targeted marketing spend for future public events. The key requirements were:
- Create a flexible sales and marketing forecast tool.
- Ability to model by pricing cohort with functionality to move pools of clients between cohorts.
- Split the model by product, channel, tier and country.
- Scenario handlers enabling the flexing of key business drivers.
- Simple, robust method for incorporating actual transaction data.
- Actual to forecast variance reporting.
- Integrated three-way financial statements.
Working closely with the finance team we were able to acquire an in-depth knowledge of the 4-tier sales funnel that drives revenue. The funnel was modelled across 10 global locations and the sales for each, were allocated to products, channels and pricing cohorts based on extrapolation of historical data. All of the historical data in the model was imported using CSV files, which gave the users control over when the actuals were updated.
The conversion rates within the sales pipeline are the most important driver of revenue. For forecasting purposes, the ability to flex these rates provides deeper insight into likely future outcomes. To this end, we built a scenario handler into the model which allowed the users to flex, store and compare up to 10 forecast scenarios.
Completion of the model provided the client with clarity over the impacts that the pipeline conversion rates have on revenue. The detailed analysis of the historical and forecast sales allowed effective decision making and budget allocation to the sales areas that could have the most valuable impact.
As well as the very detailed revenue calculation, the model forecasted all company expenses and key balance sheet lines. These calculations provided all of the outputs necessary for 3-way integrated financial statements which provided an integral control mechanism for the model.