# Price Elasticity Models and Optimizations

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)

## 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-(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.

1. Round the result to the nearest integer, since we can’t sell a fraction of a widget.
2. Minimum of 0 so that we don’t sell a negative number of units.
3. 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 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.