Promotional planning and evaluation

Uni_ml_ss_p_mcs_c_smallRunning effective trade promotions is essential to success in today’s FMCG marketplace, accounting for around half of total marketing spend. For Unilever UK, we developed a system to allow account managers to design and plan promotions, monitor them as they happened and conduct pre- and post-evaluation of their effectiveness and Return on Investment (ROI). With the ability to phase promotional sales over time and links to financial and other systems, the system reported expected weekly sales over a year in advance, including a complete profit and loss account from total sales down to gross profit.

Every weekend, as new sales and baseline information was loaded, the system recalculated the forecast sales for all planned promotions which were based on a fixed uplift or incremental volume, and realculate a weekly profit and loss account, from individual promotion up to total company level. As well as inbound feeds from other systems, the tool also generated outputs to other systems, such as SAP APO (incremental sales volume) and Oracle Financial Analyzer (financial forecasts).

The detailed database of promotional information and actual sales volumes stretching back many years also represents a huge source of competitive advantage by allowing evaluation of past promotional effectiveness to optimise future planning.

The system was written in Delphi and Oracle Express Server (which is now Oracle OLAP) and provided the following key features:

  • Combined multiple sources of data for planning/reporting (SAP, OFA, manual input)
  • Export to SAP APO of promotional volumes
  • Fixed volume, uplift factor or incremental volume promotional planning
  • Phasing of promotional sales over the promotional window
  • Scenario planning/what-if capabilities
  • Handling of multiple on- and off-invoice pricing mechanisms
  • Full P&L calculation from promotion up to total company level as soon as any changes are made
  • Recalculation of remaining volumes for running promotions as actual sales are loaded
  • Recalculation of planned incremental volumes for future campaigns on change of baseline
  • Pre- and post-promotion calculation of effectiveness/ROI
  • Powerful and flexible multi-dimensional reporting
  • Analytical tools to explore promotional effectiveness at an aggregate level
  • Handling of banded packs and on-pack gifts; ability to report on either banded packs or their constituents
  • Full audit trail for changes to promotional details