Using historic data to improve planning and forecasting
This presentation is the property of its rightful owner.
Sponsored Links
1 / 14

Using historic data to improve planning and forecasting PowerPoint PPT Presentation


  • 51 Views
  • Uploaded on
  • Presentation posted in: General

Using historic data to improve planning and forecasting. TFM&A 2014 David Lockwood: Direct Wines Terry Hogan: Golden Orb. Context. UK planning based on multiple linked spreadsheets Other countries followed different approaches Budget process took up a lot of resource

Download Presentation

Using historic data to improve planning and forecasting

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Using historic data to improve planning and forecasting

Using historic data to improve planning and forecasting

TFM&A 2014

David Lockwood: Direct Wines

Terry Hogan: Golden Orb


Context

Context

  • UK planning based on multiple linked spreadsheets

    • Other countries followed different approaches

    • Budget process took up a lot of resource

  • No single reporting tool or data warehouse

    • UK business on different systems from the rest of the world

    • Assembling data for marketing meetings very time-consuming

  • Forecasting continuity sales cumbersome and inflexible

  • Different terminology/KPIs in use in different parts of the business

    • In some cases the same term being used to mean something different in different countries

  • Decided to build a global marketing system to support marketing across all countries


Outline system fundamentals

Outline – system fundamentals

A single system to store and report on past campaigns

  • Automatic feeds from UK and international transactional systems

    One system for reporting and planning future campaigns

  • Learnings from past campaigns feed into planning future ones

    Shared database rather than Excel

  • Single version of the truth

  • One set of definitions, terminology

  • Standard, automated methodology for calculating/forecasting


Modules

Modules


Campaign level sales reporting

Campaign-level sales reporting

Gross/net Orders – by day, order channel, response code...

Comparison with budget/ plan

Detailed campaign financials

Numbers split out in various ways

  • Response code

  • Test

  • List/publication

    Products sold


Time based crosstab reporting

Time-based crosstab reporting

Aggregates the results of multiple campaigns over time

Can split out results across several dimensions

Typical ‘dimensions’ for reporting

Campaign

Response code attributes (activity, media type, list...)

Time – various levels

Order channel

Ability to view top-level numbers and then to drill into the detail


Sales reporting considerations

Sales reporting - considerations

Need a feed of order information from transactional systems

  • Orders/revenue

  • Product costs

  • Aggregate numbers by e.g. response code, day

  • Doesn’t need details of individual orders or customers

    Probably a daily feed needed for campaign sales (overnight)

  • Other data can be updated/stored weekly

    Don’t over-complicate or request too much detail

  • There are always trade-offs between detail and performance

  • Different levels of detail are appropriate for different measures


Campaign planning summary of the process

Campaign planning: summary of the process

VolumeXResponse rate %

Cases per order

P&P, AOV and product cost

Existing customer%

Response curve & channel split

Phasing

Orders

Cases

Revenue/margin

Recruits

Application of direct/indirect fulfilment and marketing costs gives net contribution

To phase the sales forecast over time, you need to build a sales profile/response curve


Building a response curve

Building a response curve

Choose a number of representative campaigns

Express each response code’s sales in terms of days after start date

Calculate the overall percentage of orders received by day 1, 2, 3...

Build different curves for different campaign types and media

Requires good data, correct campaign details


Phasing the campaign forecast

Phasing the campaign forecast

Apply sales profile to the top-level campaign numbers

Plus order channel split if relevant

For Direct Wines, we generate a daily sales forecast by order channel

The system can build an aggregate curve if the start dates are different

Unless you have a very simple product range, this sort of tool is not appropriate for detailed product planning

Primarily a tool for planning marketing activity


Reforecasting a campaign after it has started

Reforecasting a campaign after it has started

The response curve can be applied to the actual sales to date to create a revised forecast of final sales

Can apply a different percentage to orders through each order channel

Ideally needs to be done every day for each response code and order channel

Day-of-week adjustments may be needed for an accurate reforecast

Changes in forecast flow through to customer service, merchandising, finance


Modelling non campaign sales

Modelling non-campaign sales

Direct Wines have a continuity business in addition to ‘standard’ customer marketing

The continuity business generates sales without an associated ‘campaign’ that needs planning

Sales to existing continuity members can be modelled on the basis of their existing memberships

We can start to forecast continuity back-end sales when planning recruitment or ‘upgrade’ campaigns

The main continuity forecast is rebuilt weekly

However changes to recruitment or upgrade campaigns have an immediate knock-on effect


Continuity modelling

Continuity modelling

A complex area – the full details are beyond the scope of this talk

Continuity behaviour is quite predictable over time for a reasonably large group of customers

Simple approach – apply a curve to the initial recruits

Two enhancements:

  • Actions at cycle n depend on actions at cycle n-1

  • For current cycle, we can use the actions received to date to adjust our estimate


Using historic data to improve planning and forecasting

Thank You

www.golden-orb.ltd.uk

@GoldenOrbLtd


  • Login