Chapter 72: Pricing Products by Using Subjectively Determined Demand | MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

Sometimes I don’t know the price elasticity for a product. In other situations, I don’t believe a linear or power demand curve is relevant. Can I still estimate a demand curve and use Solver to determine a profit-maximizing price?
How can a small drugstore determine the profit-maximizing price for lipstick?

Sometimes I don’t know the price elasticity for a product. In other situations, I don’t believe a linear or power demand curve is relevant. Can I still estimate a demand curve and use Solver to determine a profit-maximizing price?
In situations when you don’t know the price elasticity for a product or don’t think you can rely on a linear or power demand curve, a good way to determine a product’s demand curve is to identify the lowest price and highest price that seem reasonable. You can then try to estimate the product’s demand with the high price, the low price, and a price midway between the high and low prices. Given these three points on the product’s demand curve, you can use the Microsoft Office Excel Trendline feature to fit a quadratic demand curve with the following formula (which I’ll call Equation 1):
```
 Demand=a(price)²+b(price)+c
```
For any three specified points on the demand curve, values of a, b, and c exist that will make Equation 1 exactly fit the three specified points. Because Equation 1 fits three points on the demand curve, it seems reasonable to believe that the equation will give an accurate representation of demand for other prices. We can then use Equation 1 and Solver to maximize profit, which is given by the formula (price–unit cost)*demand. The following example shows how this process works.
How can a small drugstore determine the profit-maximizing price for lipstick?
Let’s suppose that a drugstore pays $0.90 for each unit of lipstick it orders. The store is considering charging from $1.50 through $2.50 for a unit of lipstick. They think that at a price of $1.50, they will sell 60 units per week. (See Figure 72-1 and the file Lipstickprice.xlsx.) At a price of $2.00, they think they will sell 51 units per week, and at a price of $2.50, 20 units per week. What price should they charge for lipstick?

Figure 72-1: Lipstick pricing model
We begin by entering the three points with which we’ll chart our demand curve in the cell range E3:F6. After selecting E3:F6, we click the Charts group on the Ribbon and then select the first option for a Scatter chart. We can then right-click a data point and select Add Trendline. In the Format Trendline dialog box, we choose Polynomial and select 2 in the Order box (to obtain a quadratic curve of the form of Equation 1). Then select the option Display Equation On Chart. See Figure 72-2.

Figure 72-2: Configuring the Format Trendline dialog box for selecting polynomial demand curve
You will see the chart shown in Figure 72-1. Our estimated demand curve (Equation 2) is Demand=–44*Price²+136*Price–45.
Next, we insert a trial price in cell I2. We compute our product demand by using Equation 2 in cell I3 with the formula –44*price^2+136*price–45. (I’ve named cell I2 Price.) Then we compute our weekly profit from lipstick sales in cell I4 with the formula demand*(price–unit_cost). (Cell E2 is named Unit_Cost and cell I3 is named Demand.) Then we use Solver to determine the price that maximizes profit. The Solver Parameters dialog box is shown in Figure 72-3. Note that we constrain our price to be from the lowest through the highest specified prices ($1.50 through $2.50). If we allow Solver to consider prices outside this range, the quadratic demand curve might slope upward, which implies that a higher price would result in larger demand. This result is unreasonable, which is why we constrain our price.

Figure 72-3: Configuring the Solver Parameters dialog box to calculate lipstick pricing
We find that our drugstore should charge $2.04 for a lipstick unit. This yields sales of 49.4 units per week and a weekly profit of $56.24.
The approach to pricing outlined in this chapter requires no knowledge of the concept of price elasticity. Inherently, the Solver considers the elasticity for each price when it determines the profit-maximizing price. This approach can easily be applied by organizations that sell thousands of different products. The only data that needs to be specified for each product is its variable cost and the three given points on the demand curve.