Author: JC Bennavail

The management of prices within the pricing strategy, and the resulting piloting of revenue and margin performance to ensure that financial targets are being reached, is a critical function for any retailer. The design and functional capabilities of pricing and revenue management tools need to meet the requirements of performance, efficiency, reliability and scalability expected from an effective system.  

 1. Decisions stakeholders’ requirements

As in any development project the initial step when building or upgrading an application such as a pricing decision support system is to elicit and document the expectations of the key stakeholders, whose decisions will be aided by the new application. The key stakeholders are not the pricing analyst, but the senior managers who have the responsibility to the Board to ensure that financial performance targets are consistently met. Across all banners and store formats, product hierarchies, zones or territories and distribution channels, a pricing decision support system must provide:


 a) A reliable, detailed estimation of future sales, revenues and profits per product and selling points across each sales period of a rolling forecast horizon (typically 12 to 24 weeks).


 b) Sales, revenues and profits forecasts to be updated after each sales period taking into account the current and future prices, costs and competitor price checks, planned promotions and ad campaigns as well as product assortments changes.


c) Tracking of the forecasts reliability through a quantitative measure of its accuracy for each pricing period.


d) Ability to monitor the advancement towards achieving the company financial targets by analysing after each sales period the variance between on one hand the achieved sales together with the updated forecasts for the remaining periods between now and the end of the reporting period (forecast horizon), and on the other the targets / budgets to attain, as illustrated in the graph below.


 Monitoring the company’s prices compliance with all the price positioning rules and financial targets

--Identifying future risks to be mitigated and opportunity situations to be exploited


 2. Pricing team’s main requirements

The senior management team responsible for piloting the company’s performance, relies on the analysis and evaluations undertaken by the pricing team, as well as on sound recommendations the analysts may have identified for driving sales, revenues and profits towards the company’s financial goal.  The members of the pricing team are the day to day users of the pricing application. Hence the tool needs to have all the necessary functions and technologies to efficiently, collectively and accurately support the pricing team to carry-out their tasks, which can be summarised as:


a) Collect and easily combine all the relevant information describing the products, sales, competitors, suppliers and customers’ intelligence


b) Create, validate and apply all price positioning rules and financial targets which represent the company pricing strategy and financial objectives


c) Resolve any rules conflicts which may arise


d) Regularly update the information and models held within the pricing system


e) Build and update pricing and demand forecast models


f) Run different price simulations and their sales forecasts in order to evaluate different pricing scenarios over the forecast horizon


g) Prepare and distribute reports to the different stakeholders involved along the pricing and revenue management decision process


An efficient pricing solution should assist the pricing team in automating as much as possible the menial “manual” tasks required particularly for the administration and maintenance of the data and models stored within the system. Pricing analysts should spend most of their valuable time, not only evaluating past performance, but more importantly proactively identifying risks or opportunities, proposing optimum price changes or tactics adjustments, in response to demand variations, planned promotions, calendar events, competitor price moves, new suppliers conditions or new products launches. Hence, the pricing solution should be built around three analytical engines as illustrated below:

3 engines

Pricing tools which are not built around these analytical engines, cannot successfully meet the demands of a retailer to proactively manage its revenue and profits.


Multiple dimensions and levels

 When evaluating different pricing scenarios and calculating KPI’s statistics, pricing analysts should be able to cut and slice at will through the multiple dimensions representative of the business organisation such as:


-       Different distribution channels

-       Networks, banners, format

-       Territories, zones

-       Departments, categories, sub-categories, families, price sensitivity classifications

-       Customer segments (assuming that sales can be categorised by customer segments)

-       Suppliers, brands

-       etc.


An effective pricing solution should not have any “hard coded” hierarchies’ limits (e.g. a product hierarchy using a fixed three levels hierarchy [department, category, subcategory]), which will severely restrict the pricing analysts’ abilities to evaluate multiple pricing scenarios along the different business dimensions and/or levels.


Calculating the number of minimum price changes


Our experience shows that many price management systems today still do not meet the expectations of a pricing team and in particular they do not address the following request:

 “To compute the smallest price changes (in numbers and value), which satisfy over the forecast horizon, both all price positioning rules AND financial targets, including rules defined for groups of products (e.g. average % margin rate, average price index or total revenues). In the process, to relax some of the rules as little as possible and for as few prices or price combinations as possible in order to solve any conflicts, which may arise between some of the rules, and in doing so to maximise the prices compliance with the price positioning and financial rules.”


Many pricing applications propose the following price calculation methods:


a) Rule Based Pricing (RBP): Adjust the prices, in order to comply only with the pricing rules defined at the product level, using implicative logic (e.g. if, then, else rules), not taking into account rules defined for groups of products and particularly financial performance rules.


b) Price Optimisation: Compute the optimum price changes which maximise a selected financial indicator (e.g. % margin rate), subject to all price positioning rules, without limiting the number of price changes.


 The crippling deficiency of most RBP engines is that the implicative logic used to sequentially check and adjust prices one at a time, considering solely price positioning rules by order of priority, cannot deal with either group of products or sales related rules to be applied over the forecast horizon. Also, the rules relaxation logic used to resolve conflicts between some of the rules, is arbitrary and does not ensure that the necessary relaxations are minimised. Price analysts have to run RBP many times and in between each sequence to “visually” check a posteriori, if the sales or revenues rules are met. Even if only price positioning rules are applied and the number of products is small, it is very difficult to calculate prices that satisfy all rules, when not only rules limiting individual prices but also price parity rules are involved. Clearly, such an approach is inaccurate, ineffective and leads to unreliable price recommendations.


The Price Optimisation quandary is that such methods do not take into account that not only it is required to achieve the company’s financial targets over the forecast horizon, but it is also required to do so with the minimum possible price changes. Price Optimisation always suggests a very large number of price adjustments, which will deliver the highest performance. However, when managing prices and piloting revenues, one does not want to change unnecessary too many prices too often. In terms of performance, the objective is to meet the company’s financial targets, not to systematically “hunt” for the highest revenue and/or profit after each sales period, running the risk of quickly destabilizing the market and the customers.


Optimising the pricing rules


We have found that what retailers are actually most interested in, within the framework of their pricing strategy, pricing image and positioning in the market and against competitor networks, is to optimise the tactical pricing rules and price accordingly, to exploit any opportunities arising as the market evolves overtime. Price image and price positioning rules are set by the marketing team on the basis of information and processes that are qualitative as well as quantitative. This implies that the rules can be used as guidelines, and then within those guidelines best adjustments may be sought for specific products or groups of products. For instance, the marketing team may decide that the retailer’s own brand should be priced between 20% and 30% cheaper than the leading national brand. However, should those exact limits be applied to all products? And if slightly varying limits are applied for different products, then what should they be and why? Is 31% or 29% better than 30% and why? The answer to this type of questions is given by rules optimisation. Rules optimisation considers the entire set of rules, their priorities and conflicts at the level of the individual products and product groups, and identifies the smallest rules adjustments, which will generate the best performance gain over the forecast horizon.


For instance:


a) Adjusting the minimum/maximum spreads between:

  • Private labels and national brands
  • Own and competitor prices
  • Small vs large store formats
  • Different territories


b) Pitching the size parity rules


c) Fine-tuning the trade-offs between the revenue, profit and price image rules


Price Defender™ is the only pricing application available today, which enables the price analysts to compute the minimum price changes, which satisfy both all price positioning and financial targets, and identify and quantify additional performance gain by optimising the rules and targets.


Reporting and BI tools


The information managed by a pricing tool is very diverse and large. Pricing analysts need to be able to generate smart dynamic reports, whose layout and content will best take into account the specifics of the company’s organisation, allowing them or other beneficiaries to interactively navigate through the different dimensions and levels peculiar to the business operations. Therefore, all the information administered by the pricing tool, including all price scenarios and recommendations have to be automatically accessible from and editable by an authentic multi-user, client-server designed BI tool, which not only scales in order to cope with the large quantity of data, but also provides all the necessary versioning, confidentiality and communication capabilities much beyond the simple transmission of Excel sheets as an email attachment.


 3. Non-functional requirements

Retail is a very dynamic business involving high volumes of price-rules combinations. A price and revenue management solution needs not only to be smart by offering accurate demand forecasting models and price calculation engines, but also to be able to scale up to  cope with very large number of price variables and multiple sources of information, as well as to maintain satisfactory response and calculation time.

Requirements for high scalability, cost effectiveness and fast performance cannot be an “after-thought”, these features need to be at the heart of the principles, which shape the architectural design of the application. Processing and calculation times need to be evaluated from different problem sizes (load testing).

Retailers need to be satisfied that the principles sustaining the architecture of the application will deliver on the performance requirements such as:


-       Thin client - server architecture design to parallel process large scaled calculation requests (horizontal and vertical scaling)

-       System resilience

-       True multi-user capabilities

-       Automatic loading / updating of data from different information sources

-       System administration, usage tracking and confidentiality enforcing

-       Information protection and safeguard


4. Is MS Excel the right platform on which to implement an effective pricing tool?

 Today, many retailers still use MS Excel as their main tool to manage prices and pilot revenue. In light of the requirements outlined in this discussion, can we consider that MS Excel provides a suitable platform?


Clearly the answer is no. From a functional aspect, MS Excel does not provide the modelling capabilities to manage numerous, various, interdependent rules or build reliable retail demand models from which sales forecasts can be calculated, taking into account the impact of price changes, planned promotions, calendar events, cross-effects, seasonal demand variations, onto the sales, revenues and profits of each product, across the chosen forecast horizon.

 MS Excels lack of solid modelling capabilities, forces developers (price analysts themselves) to “cut-corners” and use crude alternatives (e.g. using last year sales as a surrogate for forecasted sales, applying simplistic “if-then-else” rules to adjust prices, not combining price positioning rules and performance targets to be validated across the forecast horizon, etc.). These inaccurate approximations lead to unreliable recommendations. It has to be remembered that Price Analysts are no software developers, or modelling experts. They should not spend a large proportion of their time, maintaining cumbersome and error prone sheets, ensuring that after every product assortment change, competitor price update or suppliers costs increase, the many cells of each of the sheets they use are all correct. Their job is to evaluate and analyse, with the view of spotting opportunities within the framework of the current pricing rules, or even of identifying needs to change some of those rules. For that, they need easily accessible and already processed information at their fingertips.

 MS Excel is typically the tool used by default not by design, by analysts who have been asked to study the sales performance and monitor price moves without being given a “proper” tool to do the job. The absence of documented business pricing process, supported by well-defined requirements is a tell-tale sign of an underperforming process and tool. The effectiveness of the spreadsheets supporting pricing and revenue management depends on the MS Excel development skills of the pricing analysts. The logic of the programmed spreadsheets and their effectiveness in making reliable recommendations are not reviewed and regularly monitored either by the retailer’s Head of Information Systems or the senior managers responsible for achieving the company’s financial targets. When pricing analysts move on to work for another company, their Excel “expertise” at developing the retailer’s pricing tool go with them.

 On the contrary, other knowledge workers such as management accountants, production engineers, supply and warehouse management teams are relying on purpose built, well designed systems to assist them in performing their tasks successfully. They are not being asked to manage their operations solely on spreadsheets they have developed on the fly.


Excel does not scale; it is not a multi-user, client/server business application. Individual instances are installed on the analysts’ computers. The more the prices, zones, banners, categories to manage, the more and bigger the spreadsheets, the higher the maintenance, the slower the loading and computing time, and the more, and more inevitable, the errors. Updating programs or safeguard procedure cannot be run in the background.


If you use MS Excel as your pricing tool, we recommend first that as an organisation, you question why.


a) Is it a choice based on well-defined and documented requirements, which have been validated by senior management, IS leadership, and pricing team, or is it just because it happened that way, by default?


b) Do you regularly benchmark the performance of the MS Excel, home grown tool, with purpose built solutions?


c) Are the benchmark’s recommendations regarding MS Excel, and the consequences clearly explained to senior management?


 Pricing and revenue management practice in retail


When looking at other mass market, B2C, business sectors such as air transport, hotels or telecom, it is interesting to note that the very difficult market conditions these companies operate in are quite similar to the ones faced by retailers:


-       High volume, low margin sales

-       High number of complex product and price offers

-       Highly competitive, aggressive low-cost operators

-       Business models and practices, more and more information and technology driven

-       Advent of technology focused new entrants


Airline companies manage their complex price offers and revenues using sophisticated yield management solutions. The well documented difficulties endured today by some of the major retailers (TESCO, Morrisons, Carrefour, Group Casino, Safeway US, etc.), are clear indications that management practices are changing. Regarding pricing and revenue management, demand driven systems designed at supporting proactive decisions are being implemented by successful technology driven operators. Do you think that Amazon or eBay are managing their prices and revenues on some MS Excel spreadsheets?

Validating price positioning and performance rules. 

To make sure that prices do comply with the agreed price positioning rules and also achieve, within the agreed forecast horizon, the revenues and profits targets, representing the company's financial goals, it is critical to make sure that the price positioning rules and financial targets within which prices have to be maintained are compatible with each other. Typical price positioning and financial targets rules are:

 Price positioning rules:

-       Min/max price bounds

-       Min/max spreads between brands or between own and competitor prices

Min/max parity price per UOM differences between large and small size products

- Min/max weighted average price index (weighted with unit sales)

 Financial targets:

-  Min % margin for each product in a group

-   Min average % margin for a group of product

-   Min total revenue or profit to be generated by a group of product

Clearly, many of the price positioning and financial rules are dependent upon each other. For instance, the minimum total revenue or profit rule to be achieved for a group of products, depends on the price changes to be allowed by the price bounds, spreads and parity rules applicable to products’ prices of the group. How can one check that all price positioning and financial rules are compatible with each other?

 A retail specific demand model

 To solve this problem one first have to build and maintain overtime a retail specific causal demand forecast model, which quantifies the relationship between prices and sales, and in particular to predict how many sales units are to be sold taking into account the prices of the products, together with associated sales cross-effects and potential promotions taking place over the forecast horizon. Specifically, the demand model form needs estimate the direct and cross price/sales elasticity coefficients. Obviously, forecasting methods such as simply using the past sales data as a surrogate for a calculated sales forecasts, or exponential smoothing based methods such as Holt Winter cannot be used as they do not calibrate the sales / prices relationship.

 Optimisation method to validate all the rules

 The validation of the price positioning and performance rules can be ascertained by implementing an optimisation method, which uses causal demand models, determines if the price positioning and performance rules are compatible with each other, and whether prices can be calculated, which satisfy both types of constraints (rules). The additional benefit of using an optimisation engine to validate the rules, is that conflicts between incompatible rules are resolved by computing the necessary minimum rules’ bounds values adjustments for the products where the incompatibilities manifest themselves.

Contrary to most of the Rules Based Pricing solutions, which evaluate rules using some implicative logic (i.e. “if, then, else “type of rules) and arbitrarily defined relaxation thresholds and priorities, our rules management optimisation based approach allows for a complete and most effective validation of all types of rules and in particular:

 - Takes into account sales related performance rules

-  Considers both price positioning and performance rules

-  Evaluates together rules defined for either individual prices, pairs of prices or group of products

- Minimise the amount of rules relaxation which is necessary to reconcile conflicting rules of different priorities

 Point to takeaway

Ensuring that all price positioning and performance rules are compatible with each other is a compulsory prerequisite of a pricing solution for evaluating prices and managing performance.


After the "price optimisation" hype of 10 years ago, one can only see that most retailers have not invested in any of the price solutions proposed by the different software vendors, although their operations became significantly more complex and their respective markets much more competitive. Contrary to companies operating in other mass market and service based sectors such as air transport, hotels, retail banking or telecom, our experience shows that only few retail companies have developed and implemented integrated pricing and revenue management processes and dedicated tools aimed at best piloting their performance and achieving their goals.


To illustrate this paradox, let’s compare the typical pricing and revenue management processes and tools of a mainstream airline company with the ones from a grocery retailer.


Similar markets profile and complex product offering

Both businesses operate in highly competitive and low margin mass markets. The ranges of products or services they offer are very large and the corresponding number of price variables can reach several 100,000s. Many prices changes occur regularly (dynamic pricing) to particularly take into account costs fluctuations, competitor prices variations, or changes in the products’ assortment. In addition, retailers and airlines do rely on frequent promotions to boost the demand.


Differences in their respective pricing and revenue management practices

In the last 30 years,  airline companies put in place specific and integrated pricing and revenue management procedures supported by purposely built decision support systems (yield management), aimed at forecasting future sales with respect to the current and future prices and ultimately assisting them in piloting their financial performance. It is interesting to recall that yield/revenue management methods and tools were implemented by airline companies in the mid 80’s following the deregulation of air transport in the late 70’s and the subsequent increase in competitive pressure and the ultimate arrival of low-cost new operators. The early attempts in the 80’s of some historical carriers to “price-out” (aggressive blanket price reductions) some of their competitors failed. The airline companies, which did not adapt their pricing strategy and practices to the market transformations and did not implement revenue management methods, either merged or simply went out of business. Today, pricing and yield/revenue management expertise are certainly recognised as a core asset with which airline companies can successfully pilot their performance.


On the contrary, with the exception of technology focused online retailers such as Amazon or eBay, many retailers do not seem to have fully “redesigned” from the ground up their pricing and revenue management processes and made the necessary investments in the suitable expertise and tools, to fully take on board the market and customers behaviour evolutions. Some of the typical symptoms which exemplify the characteristics of an underperforming pricing and revenue management process can be listed as:


  1. Pricing and revenue management process by default not by design

Pricing, promotions planning and revenue management processes are not documented and evaluated. KPI’s or benchmarking scores (e.g. sales forecast accuracy, price compliance rates, success rate to achieve targets, etc.) are not regularly calculated to assess the efficiency of the procedures and tools used as well as the quality of the decisions made.


  1. No ownership of the pricing process and tools by senior managers

Often senior managers do not take full ownership of the approach, methods, tools, business rules and procedures making up the pricing and revenues management process, whose performance and reliability is not measured and monitored. The pricing function is seen more as an administrative/reporting function than a key lever to pilot performance.


  1. Reactive pricing

A pricing process solely focused at evaluating past performance and only reacting to either competitor prices or costs changes for justifying the retailer’s own price moves. The inability of anticipating future demand variations characterising “Reactive Pricing” leads to making underperforming and/or overreacting price changes decisions. Price decisions should be based not only on analysing past performance but also on estimating future revenue and margin calculated from reliable sales forecasts. This can be described as “Proactive Pricing”. The management team is inherently handicapped by having to play the pricing “game” with a systematic and debilitating delayed price move.


  1. Simple “cost plus” method to pilot margin rates and profits

Margin rates and profits are only piloted by applying for each product a given margin coefficient to the cost (cost plus). Such a simple approach cannot evaluate the profits variations from either a unit margin change (e.g. price increase) and/or a sales volume increase or decrease, and it cannot optimise the trade-off between high volume/low margin and low volume/high margin products.


  1. No reliable sales forecasting process and tool

No implementation of a retail specific demand forecasting tool, taking into account the impact of price and costs changes, promotions, cross-effects, calendar events, seasonal variations over the forecast horizon. Still today, a majority of retailers use last year 12 months sales as a poor surrogate for estimating their future sales volumes, although the products assortment, competitive environment, own and competitor prices, etc. were very much different between then and now.


  1. MS Excel - The only pricing tool

MS Excel seems to be the sole pricing tool for many retailers. Many sheets are developed by the pricing team, without much validation of the approach and pricing methods used. The limitations of MS Excel are well documented. To mention just a few: It does not support the implementation of a reliable sales forecasting system or advanced price calculation engine (e.g. RBP) ensuring compliance with both price positioning and financial rules; it does not scale to high volumes of data; it is not multi-user; as the maintenance of numerous sheets requires of lot of manual and repetitive work, it is error prone and time ineffective.


  1. No integration of the pricing and promotion evaluation processes

Revenues and margin projections are not systematically and regularly (e.g. weekly) updated, taking into account the impact of price changes, together with planned promotions or changes in the products assortment over the forecast horizon.


  1. Price positioning rules and financial targets are not managed together

The lack of reliable retail focused sales forecasts, which formalise the relationship between sales volumes, prices and costs, revenues and profits, prevents the pricing team to proactively validate the coherence between the price positioning rules (pricing strategy) and the financial targets over the forecast horizon.


In April 2014, Mr Dalton Philips, Morrisons' boss, likens the shift in the UK grocery market to the changes seen in the airline industry, when new disruptive players - the budget airlines - came on the scene. “The lesson is that once you see this disruption and fragmentation, the clock is not going to be turned back….  And those who felt this was a passing fad or part of the economic cycle and failed to adapt, paid a heavy price."


Until recently, large retailers have tried to fight-off adverse market conditions and fend-off much stiffer competition by “investing” massive amount of cash in blanket price reductions programs. For example in the UK:





  • January 8, 2015 – “ Tesco confirms wave of price reductions”



However, although a change of the pricing strategy was required, these considerable and somewhat indiscriminate price cuts, had the prejudicial effect of triggering severe price wars, dragging the whole market down, with no benefits gained. A typical example of knee-jerked “reactive pricing”. Today, the business situation of the UK major retailers do seem to have improved, as illustrated in the Telegraph article referenced below.



“Three of Britain’s four largest supermarkets cannot afford to make any more price cuts, with sales and profits set to fall for another 12 to 18 months according to a new report by Moody’s. In findings that highlight the pressure on the grocery industry, the credit rating agency said that only Asda has the financial capacity to make more cuts. The “Big Four” of Tesco, Asda, J Sainsbury and Wm Morrison have already committed hundreds of millions of pounds to lowering prices to try to stop falling sales and the rise of Aldi and Lidl. However, although the price cuts have helped to bring back customers and boost spending in supermarkets, it has damaged profitability and not stopped Aldi and Lidl growing. The Moody’s report comes after Sainsbury’s reported a 2.1pc fall in like-for-like sales but claimed the market was in better shape than expected. Analysts at BESI said Sainsbury’s boss Mike Coupe was “appealing for calm” and trying to persuade his rivals not to embark on further rounds of price cuts. Sven Reinke, senior analyst at Moody’s, said: “In a rational market we would expect the pace of cuts to slow because of the impact on the ‘Big Four’s’ margins, which have halved since fiscal 2013/14. “However there is a risk that some market participants will continue to try to defend their market shares by further substantial price cuts, provoking further countermeasures by other ‘Big Four’ retailers in the form of yet more cuts. “Apart from Asda, which arguably has the strongest financial resources of the ‘Big Four’ because it is part of the world’s largest retailer Walmart, none of the other three players really has the capacity to engage in many more rounds of price cuts.”


As it happened for the airline industry 30 years ago, retailers will have to develop and implement pricing and revenue management methods, processes and tools, which will give them the ability to pilot their performance, within a much more global and competitive market.