Price Elasticity Models and Optimizations
8m Read
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*(salesPricetp)
Price Elasticity Example
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(150168) = 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.
 Round the result to the nearest integer, since we can’t sell a fraction of a widget.
 Minimum of 0 so that we don’t sell a negative number of units.
 Maximum of what’s in inventory, so that we don’t sell more units than what we have.
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 affects units sold and profitability. Look at the profit line and note that the peak profit occurs when the sales price is $168.
Units Sold 
103 
98 
93 
88 
83 
78 
73 
68 
63 
58 
53 
48 
Price per Unit 
133 
138 
143 
148 
153 
158 
163 
168 
173 
178 
183 
188 
COS per Unit 
100 
100 
100 
100 
100 
100 
100 
100 
100 
100 
100 
100 
Revenue 
13,699 
13,524 
13,299 
13,024 
12,699 
12,324 
11,899 
11,424 
10,899 
10,324 
9,699 
9,024 
Total COS 
10,300 
9,800 
9,300 
8,800 
8,300 
7,800 
7,300 
6,800 
6,300 
5,800 
5,300 
4,800 
Profit 
3,399 
3,724 
3,999 
4,224 
4,399 
4,524 
4,599 
4,624 
4,599 
4,524 
4,399 
4,224 
Finding The Optimal Price Point with an Optimizer
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:
 A model that can calculate values
 An objective, such as profit, that can be maximized (or minimized depending on the situation)
 A set of decision variables, for example, the sales price of products
A set of constraints (limitations on variables or other values, for example you can’t sell a negative number of units)
Different kinds of solvers can be used in different situations. Three broad classes of solvers are:
 Linear (all relationships are linear relationships)
 Non Linear
 Non smooth
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 Synario's 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.
Units Sold 
81 
78 
76 
73 
71 
68 
66 
63 
60 
58 
55 
53 
Optimal Price per Unit 
155 
158 
160 
163 
165 
168 
170 
173 
176 
178 
181 
183 
COS per Unit 
75 
80 
85 
90 
95 
100 
105 
110 
115 
120 
125 
130 
Revenue 
12,518 
12,324 
12,118 
11,899 
11,668 
11,424 
11,168 
10,899 
10,618 
10,324 
10,018 
9,699 
Total COS 
6,038 
6,240 
6,418 
6,570 
6,698 
6,800 
6,878 
6,930 
6,957 
6,960 
6,937 
6,890 
Profit 
6,480 
6,084 
5,700 
5,329 
4,970 
4,624 
4,290 
3,969 
3,660 
3,364 
3,080 
2,809 
How Does Optimal Price Change as Elasticity Changes?
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.
Units Sold 
51 
54 
58 
61 
65 
68 
71 
75 
78 
82 
85 
88 
Optimal Price per Unit 
202 
191 
183 
176 
172 
168 
165 
162 
160 
158 
157 
155 
COS per Unit 
100 
100 
100 
100 
100 
100 
100 
100 
100 
100 
100 
100 
Revenue 
10,303 
10,371 
10,554 
10,804 
11,097 
11,423 
11,774 
12,144 
12,526 
12,916 
13,315 
13,725 
Total COS 
5,101 
5,438 
5,782 
6,122 
6,460 
6,799 
7,140 
7,481 
7,822 
8,160 
8,498 
8,841 
Profit 
5,202 
4,932 
4,773 
4,682 
4,637 
4,624 
4,635 
4,663 
4,704 
4,756 
4,817 
4,884 
Elasticity 
0.5 
0.6 
0.7 
0.8 
0.9 
1.0 
1.1 
1.2 
1.3 
1.4 
1.5 
1.6 
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.
Jan 
Feb 
Mar 
Apr 
May 
Jun 
Jul 
Aug 
Sep 
Oct 
Nov 
Dec 

Units purchased 
678 
69 
69 
0 
0 
0 
0 
0 
0 
0 
0 
0 
Units in Inventory 
678 
679 
680 
612 
544 
476 
408 
340 
272 
204 
136 
68 
Units Sold 
68 
68 
68 
68 
68 
68 
68 
68 
68 
68 
68 
68 
Price per Unit 
168 
168 
168 
168 
168 
168 
168 
168 
168 
168 
168 
168 
Revenue 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
Total COS 
67,768 
6,916 
6,916 
0 
0 
0 
0 
0 
0 
0 
0 
0 
Profit 
56,344 
4,508 
4,508 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
11,424 
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 in the last 10 months.
Jan 
Feb 
Mar 
Apr 
May 
Jun 
Jul 
Aug 
Sep 
Oct 
Nov 
Dec 

Units purchased 
498 
69 
69 
0 
0 
0 
0 
0 
0 
0 
0 
0 
Units in Inventory 
498 
499 
500 
450 
400 
350 
300 
250 
200 
150 
100 
50 
Units Sold 
68 
68 
50 
50 
50 
50 
50 
50 
50 
50 
50 
50 
Price per Unit 
168 
168 
186 
186 
186 
186 
186 
186 
186 
186 
186 
186 
Revenue 
11,424 
11,424 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
Total COS 
49,768 
6,916 
6,916 
0 
0 
0 
0 
0 
0 
0 
0 
0 
Profit 
38,344 
4,508 
2,384 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
9,300 
Price Elasticity and Net Present Value (NPV)
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.
Jan 
Feb 
Mar 
Apr 
May 
Jun 
Jul 
Aug 
Sep 
Oct 
Nov 
Dec 

Units purchased 
68 
68 
500 
0 
0 
0 
0 
0 
0 
0 
0 
0 
Units in Inventory 
68 
68 
500 
445 
392 
339 
288 
237 
188 
139 
92 
45 
Units Sold 
68 
68 
55 
54 
53 
52 
51 
50 
48 
47 
46 
45 
Price per Unit 
168 
168 
181 
182 
183 
184 
185 
186 
188 
189 
190 
191 
Revenue 
11,424 
11,424 
9,899 
9,774 
9,646 
9,514 
9,378 
9,238 
9,094 
8,945 
8,791 
8,633 
Total COS 
6,800 
6,800 
50,000 
0 
0 
0 
0 
0 
0 
0 
0 
0 
Profit 
4,624 
4,624 
40,101 
9,774 
9,646 
9,514 
9,378 
9,238 
9,094 
8,945 
8,791 
8,633 
The following graph shows warehouse usage and price per unit.
Price Elasticity and 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:
Product 
Widgets 
Gadgets 
Thingamajigs 
COS per Unit 
100 
120 
144 
Space requirement 
2 
3 
4 
Elasticity 
1 
3 
.4 
Months Purchasable 
Jan thru Mar 
Feb thru May 
Jan thru Mar 
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 finetuning 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.