Forecast Financial Modelling – Forecast

Case study

Sales Pipeline Model

Technology

Completion of the model provided the client with clarity over the impacts that the funnel conversion rates have on revenue.

Client

Our client was one of the first software providers to automate the collection and data extraction of receipts and invoices for small businesses. Today, they provide technology that unlocks the value of accounting data, automates the bookkeeping process and helps build enhanced accountant-client relationships.

Purpose

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.

Approach

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.

Outcome

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.

More like this

Case study

Property

Consolidation & Refinancing Model

Case study

Financial Services

Pricing & Profitability Model

More case studies