Author: Tingting Xia is a Certified Master Anaplanner, EPM Manager and Supply Chain practice lead in Keyrus with 8 years of Anaplan experiences. She has been leading and wrapping up a trade promotion planning solution at one of the top global breweries.
Client challenges prior using Anaplan
First and foremost, the massive data: both master (SKU properties, Banner attributes, pricing structure etc.) and transactional (history sales, commercial guidelines, costs, competitor data, etc.) needed by the client’s KAMs (Key Account Managers, those who oversee trade promotion planning) to conduct trade promotion planning sit in various source systems and come with high volume. It took the KAMs a lot of manual efforts to pull all those data and consolidate them in Excel spreadsheets. Therefore, the promotion planning was only done once a month, mismatching the fact that their promotions are running on a weekly basis. This has led to the lagging and inaccuracies in their promotion planning already.
The inevitable human errors, lack of traceability and auditability throughout the whole processes added to the inefficiencies and inaccuracies of the planning as well.
The Excel spreadsheet-based planning came with another issue whereby every KAM had their own way of planning with guesstimation. It became hard to justify the plan to the management should there be a problem. The whole planning practices were not standardized nor scalable.
Due to the limitation of Excel spreadsheets, there was no advanced data analytics or insights either: scenario simulation to understand various drivers’ impact on promotion outcomes, real-time insights to drive timely action to capture revenue, etc.
The solution roadmap in three releases
We decided to take a phased approach with an MVP (Minimum Viable Product) design in mind and with an end state (link TPM to financial planning to realize revenue growth management) in vision. In this way, we do not flood the KAMs with seemingly endless of discussions, loads of transformations but not seeing a working product in a short time. We guaranteed shorter time to value and earned their trust to guide them step by step away from their excel templates but on to adopting Anaplan.
Release 1
The MVP covers the listed basic functionalities below to remove the manual data consolidation time from the KAMs, automate their products’ baseline forecast with statistical models and standardize their promotion planning practices.
- Datahub: set up data integration with various data sources, perform data checks and then push relevant data into TPM model. With this, Anaplan becomes the single source of truth for the KAMs whenever they need raw data.
- Statistical baseline forecast by SKU by Banner by Week based on weekly history sales. A suite of statistical methods is developed to cater to different products’ selling pattern from simple ones: MA, DMA to sophisticated ones: Winters Additive, Winters Multiplicative, Ensemble, etc. Our final best fit method gives a forecast error as low as 2-3%. This has hugely improved their baseline planning accuracy compared to their old way of applying MA on their past 6 months sales on monthly bucket and then just flat average into weekly bucket.
- Standardized promotion creation template and mass creation (“copy-paste”) capability. We defined how a promotion should be created inside Anaplan properly following the same template across all KAMs to ensure consistency. Error prompt messages and security lock features are also developed to ensure planning accuracy. Instead of being only able to create promotion one by one, we also developed a feature which in layman term called “copy-paste”. It allows the KAMs to select one created promotion and then mass create it into the indicated future period carrying all the planned parameters like promotion price, uplift volume, discount offered etc. This saves the KAMs a lot of time when similar promotions are planned to run in a repetitive pattern into the future.
- Promotion calendar with detailed price and volume view. We custom-built a promotion calendar view instead of just using Anaplan NUX’s Gauntt chart feature because the client wanted more to be displayed in the calendar itself. The KAMs are now able to view their promotions vividly and intuitively in a chart-like view but also contains the detailed information they want to see.
Release 2
Release 2 solution focused on the commercial side of trade promotion planning and more advanced planning features. We standardized client’s pricing structure and price waterfall planning, set up commercial guidelines management and linked it to promotions, automated approval workflow and developed scenario simulation playground for KAMs to simulate how their promotion plan will change if they change their discount, price point, etc.
- Pricing waterfall: Master pricing structure was cleansed from their source SAP systems, keeping only relevant parameters going to TPM price planning. Specific pricing structure changes at SKU x Banner level due to client’s business nature. With this, KAMs can plan their products’ prices at the lowest granularity across weeks from Shelf Price to Net 3 accounting for relevant cost factors in between. Waterfall charts are used to visualize this Shelf Price to Net 3 evolvement to make price planning and each cost factors impact more intuitive and straightforward.
- Commercial guideline management: commercial terms are the pricing rules (i.e. minimum price, max discount, etc. allowed to offer for a specific SKU on promotion) for different depths of promotions across SKU and Banner. This was not properly set up and hence not strictly enforced during KAMs’ promotion planning in Excel era due to the level of granularity and multi-dimensionality it requires. With Anaplan, the commercial guidelines can be mass uploaded or fine tune via NUX direct interaction with ease. System automatic alert message then will be displayed should there be a breach of commercial guidelines when KAMs are planning their promotions.
- Approval workflow: Anaplan’s native workflow is set up with two main purposes. Firstly, the KAMs will submit promotions that are outside of commercial guidelines but the KAMs deem them necessary to achieve for example revenue, depleting aging stock goals etc. to their management for review and accept or reject. Secondly, review of commercial guidelines will be done in a regular basis to cater to changing business and market dynamics. If any revision for commercial guidelines is needed, then this revision will be submitted to higher management for review and approval. Approval workflow plays a critical role to facilitate, automate and make the whole trade promotion planning process auditable.
- Simulation playground: There are three types of simulation capability built for the client to cater to their different what-if analysis requirements.
- Market share percentage simulation: Our client has access to competitors’ sales data at product category level with which historical market share percentage can then be derived coupled with client’s own history sales data. With this knowledge, we built market share percentage simulation for KAMs to simulate forecasted volume based on different target market share percentages at Category, Banner and Week level. Category level simulated percentages will be cascaded down to SKU level to simulate SKU level forecast volume. The simulated forecast volume will connect to below two types of simulations to form a wholistic simulation outcomes on financial impact.
- Promotion parameters simulation: This capability allows the KAMs to play around with promotion parameters like promo price, discount, scan, promo volume etc. to see their impact on financial outcomes in terms of Revenue, GDM, ROI, Customer Margin.
- Pricing simulation: Pricing simulation playground allows the KAMs to simulate different cost / expense factors from Shelf Price to the final Net 3 and eventually their impact on financial outcomes. Additionally, there is a “goal-seek” functionality built whereby the KAMs input their desired NIS (Net into Store) price and Anaplan calculates backwards what the Cycle Discount will be to achieve that target NIS.
All fore-mentioned simulations are connected to promotion planning and therefore generates a wholistic TPM planning scenarios from volume to financials. KAMs or management can then compare the different outcomes across scenarios and make informed decisions on commercial planning as well as final promotion planning.
Release 3
Release 3 solution is to link trade promotion planning outcome to financial planning and analytics and management reporting so that the management team can view the real-time financial outcomes based on KAMs’ updates in planning and hence aide their decision-making. In this release, P&L report together with other key management reports (S&OP, Market share) are developed integrating actualized financial data from the client’s ERP system and forward-looking planning data inside Anaplan. Key managerial KPIs like Revenue/HL, GDM (Net Profit)/HL, YoY (Year over Year), YTD (Year to Date), YTG (Year to Go), etc. in terms of both monetary values and volume are also at the fingertips of the executives.
- P&L report: P&L report is built by SKU, Banner, Week, covering both actualized data & forecast data. It links finalized (meaning after simulation) baseline forecast, price planning and promotion planning to churn out the final report, showing the management the evolution from Gross Revenue, cost / expense items in between, Net Revenue and then finally to GDM. It auto-refreshes once there is a change in the underlying planning. Therefore, whenever the KAMs report to their management, they are always referring to the latest updated planning. No more reporting on outdated data and then the KAMs take another probably half-day if being asked “how about now?” question.
- S&OP report: S&OP report definition by the client reports on volume (both actual and forecasted) in terms of both Hectoliter (HL) and Consumer Unit (CU) and final Revenue/HL by SKU by Banner across both history and future periods. It is a more concise management report dedicated to higher management for them to have a quick understanding on both volume and revenue.
- Actualizations vs Plan Summary: This report allows the management to review key metrics like Volume HL, Revenue, GDM, etc. comparing actuals vs what has been forecasted and planned inside Anaplan. Key comparison metrics like YoY, YTD, YTG are also implemented to give the management an overview of how the business is performing at a quick glance.
Summary
It has been a fulfilling 15-month journey with the client and the solution has helped the client to minimize manual work, changed their once-a-month planning to run weekly or even real-time, added science and advanced analytics to their planning instead of human guesstimation, improved their revenue planning accuracy to as high as over 95%, automated the whole process from data management to final management reporting onto Anaplan. No more crunching numbers in Excel; no more taking 4 hours to address management question on profitability, etc. Every step now is handled systematically and updated real time inside Anaplan with traceable auditability. The linkage from basic trade promotion planning to financial planning & analytics helped the client to truly transform and digitalize their revenue growth management.
