Price Elasticity Models and Optimization

Price elasticity refers to the property that the price of an object will influence the number of units sold. In general, higher prices mean fewer sales, and lower prices mean more sales. If you chart price and units sold, you can draw a line with a negative slope to show the relationship. The revenue can also be plotted and shows a curve. If you set the price too low or too high, you won't have much revenue, but if you price it just right, you'll do fine. Optimization is a method to find the best price to maximize profit.

In the model I constructed, I use a very simple linear formula to model elasticity with 3 constants that determine the relationship (tp=typical price, tu=typical units, and e=elasticity which is the slope). So, in the model, you decide the sales price, and the formula computes how many units will be sold.

unitsSold = tu + e*(salesPrice-tp)

For example, let’s create a Widgets product that if you set the sales price to $168, then you will sell 68 of them in a month. So in the formula, tp=168, tu=68. If the elasticity is -1 then if you change the sales price to $150, then the units sold is 68-(150-168) = 86. Note that if you set the sales price too high you eventually won’t sell any units, and if you set the price too low (less than the cost of sales) you won’t earn any money. To maximize your profit (revenue minus cost of sales) is somewhere in between. Finding that optimal sales price is an optimization problem. In this example, if the cost of sales per unit is $100, then the optimal sales price is $168.

There are 3 additional constraints we should add to the above formula.

These restrictions are what we typically need to put into a model so that it behaves as expected no matter what values the other parameters are set to. Unfortunately as we shall see later in this article, these restrictions cause many problems for optimizers.

Here is a chart of how sales price effects units sold and profitability. Look at the profit line and note that the peak profit occurs when the sales price is $168.

To find this optimal price point in a variety of situations we can use a Solver, or Optimizer, that will calculate the optimal price to maximize profit. A solver will need the following:

Different kinds of solvers can be used in different situations. Three broad classes of solvers are:

Most financial model optimization problems will fall into either the Non Linear or Non smooth classes. It is better to try “fit” the problem into a non linear class than a non smooth class because the calculations are faster and are more likely to find a solution. However, trying to fit the problem into a Non linear Solver class can be challenging. For example, the 3 additional constraints (rounding, minimizing and maximizing) that we added to the basic formula above are problematic.

What I found is that one approach to resolving these problems is to create a dual set of formulas (although this is difficult to do in Excel, it is easy using Whitebirch Software scenario overlays . One set is used for optimization and the other set is used for modeling. The optimization formulas would remove the three additional constraints above. This creates a “nice” smooth model that is easy to optimize. The minimize and maximize constraints can be rewritten as optimization constraints, so although they are not in the model, the optimizer will attempt to satisfy those constraints. The problem with keeping the minimize and maximize functions as part of the model rather than the constraints is that they create flat areas and optimizers don’t really know how to handle these flat areas because they don’t know which direction they should go.

The rounding operation could also be added as an integer constraint, but this does not scale up in NLP optimizers and will only work on a very limited number of decision variables, so we will ignore that for now and assume we can sell fractional number of units. After the optimization, we can restore the modeling formulas that include rounding. What this means is we may not have an exact solution, but that only becomes a problem when the numbers are small (say less than10).

To see some simple examples of what an optimizer can tell us, let us first ask a couple of simple questions. As COS per unit changes, how does the optimal price change? We get the following results, which shows that as COS goes down it is better to lower the price and sell more units.

Another question of interest is how does the optimal price change as elasticity changes. The closer to zero, the less sensitive units sold becomes, so as the chart below shows, we can charge higher prices with less effect on units sold. Again, the optimizer is used to compute the optimal price for each column.

And now for a more challenging optimization problem. In the examples above, we simply optimized a single month at a time. However, things change from month to month, and decisions in one month may affect other months, so it is generally better to optimize across many months to maximize the total profit over a span of time. Here is a simple example. Suppose you can only purchase units the first 3 months of the year. If you optimize month by month, then you won’t sell any the last 9 months. The decision variables for the optimizer is now how many widgets should be purchased (for each of the first 3 months) and what price should they be sold (one for each of the 12 months of the year). The optimizer provides the answer below. The first 3 months you buy the optimal number of widgets that will last the entire year, selling them at the optimal price of $168. Note the huge loss in the first month.

In the above model the size of the Warehouse (where the widgets are stored) is unconstrained. Suppose you can store at most 500 of them. The optimizer provides the following answer. The supply is constrained for the last 9 months so to achieve maximum profit you can sell at a higher price the last 10 months.

We’ve been optimizing by total profit for the entire year. A slightly better measure is the NPV (with discount rate at 20%) where dollars in January are worth more than dollars in later months. The huge loss suffered in the first month can be put off to the third month. In addition, the limited supply for the remaining 10 months allows us to increase the price in such a way as to provide a little more cash a little bit sooner. Trying to compute this optimal solution by hand is obviously much harder.

The following graph shows warehouse usage and price per unit.

Product Mix

Let’s expand our problem to include 3 different products, with different elasticities, cost of sales, space requirements, and months of the year you can purchase those products, as follows:

As before with the goal of maximizing NPV (same discount rate of 20%) how many units should be purchased each month, and what prices should be set for these products? This comes to 46 decision variables (10 variables for purchase amounts, and 36 price variables over 12 months). As before let the warehouse be limited to 1000 units of space. What is the optimal mix of products to maximize profit using NPV with a 20% discount rate? The optimizer finds the following solution:

Using optimizers is much more an art than a science. To get good results you will need to find the right mix of strategies and fine tuning a variety of initial values and formulas. Slight changes may lead to different results. In the above example, even slight changes to the NPV discount rate can make a big difference between finding a solution or not, so running the optimizer on a variety of examples may be needed. For more great stuff on NLP see John Chinneck’s Gentle Introduction to Practical Optimization.