Forecast Financial Modelling – Forecast

Case study

Campaign Effectiveness Tool

Media, Telecommunications

Completion of the tool allowed the finance team to appraise and optimise their campaign line-up, having confidence that the forecast results are founded in actual customer level data.

Client

Our client is a provider of subscription television, internet and mobile services. They have an international reach totalling over 22 million customers, and a history of developing innovative products and entertainment content.

Purpose

The client required a campaign effectiveness tool to assess the benefits of varying product offer combinations across different routes to market. The key requirements were to;

  • Leverage existing customer data to base the forecast of future behaviour, informing customer decay curves and product attachment rates,
  • Capture all associated costs and revenues associated with the forecasted customer movements,
  • Combine the resulting cash flows over time and condense into NPV, IRR and payback period KPIs,
  • Translation of forecasted results into a P&L view,
  • Provide the ability to compare and contrast the impacts of differing campaigns.

Approach

Working closely with the finance team we were able to capture a comprehensive picture of associated costs and revenue for varying routes to market and product holdings. Using SQL to query data from internal databases we compiled a rich dataset, which allowed us to apply the appropriate customer behaviours based on offer depth/duration and route to market. A campaign capture element allowed quick and easy negotiation and comparison between different offer sets and their resulting KPIs, both at customer and estate P&L level.

Outcome

Completion of the tool allowed the finance team to appraise and optimise their campaign line-up, having confidence that the forecast results are founded in actual customer level data. The layout of the tool and the dashboards combined an easy to use interface with a powerful tool and ability to have oversight with a high degree of granularity.

The key outcomes:

  • Live data connection to a database allowing instant refresh and the ability to draw on 3 years of customer history for each estate, with product holding and offer value KPIs,
  • Users can slice data to look at particular combinations of offer holdings and their offer status,
  • Campaign offers were assessed by NPV, IRR and payback, relative to a scenario chosen by the user,
  • Multiple offers can be stored, overwritten and reinstated using macro controls. Multiple offers are then brought together in an output that helps the user select a group of offers based on a selected outcome.

More like this

Case study

Energy, Retail

Demand & Margin Forecast

Case study

Energy, Mining & Resources

Energy-Mapping-Optimisation-Tool

Energy Mapping & Optimisation Tool

See more case studies