Price Elasticity Models and Optimization

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*(salesPrice-tp)

Price Elasticity Charts Reports

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

Price Elasticity Report Data

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

pros-cons-acqusition-planning

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 NPV

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.

Widget Elasticity Optimization

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:

Warehouse Sales Graphic
Product Mix Table

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.