Excel’s Solver tool can be used to solve difficult price discrimination problems. Imagine that there are two groups of customers with the following demand curves:

Where is the quantity demanded by Group 1 when it faces price P₁ and is the quantity demanded by Group 2 when it faces price P₂. We could think of these markets as Europe and Africa or as business travelers and vacationers, similar to the way we did in the text. The monopolist has the following costs:

where Q is the quantity produced by the monopolist.

The monopolist’s goal is simple: It wants to choose prices P₁ and P₂ in order to maximize its profits. We will assume that the two markets are distinct so arbitrage is not possible. Although the goal is simple, the solution is difficult. In fact, this problem is considerably more difficult than any of the problems we dealt with in the text. In the text, we assumed that marginal cost was constant (a flat MC curve). Assuming constant marginal costs simplified the problem because it meant that when the monopolist produced more in Market 1, the costs of producing another unit in Market 2 didn’t change. In our problem here, marginal cost is increasing—which means that when the monopolist produces more in Market 1, its cost of producing an additional unit in Market 2 also increases. In an intermediate or graduate economics class, you would use calculus to solve a problem like this.

In the real world, business managers and entrepreneurs must solve problems like this every day and they don’t all know calculus, so we will show you how to solve the problem using Excel. First, let’s write down what we know. In Figure A14.1, we highlight the equation for , which we enter as “=330−2*B2”. We put the price for Group 1 in cell B2. We want to find the profit-maximizing price for Group 1 but we don’t know what it is, so for now we just put a zero in cell B2. The equation and price for Group 2 are entered similarly.

278

Now we enter the formula for the monopolist’s cost. The total quantity produced by the monopolist is simply the quantity produced for Group 1 plus the quantity produced for Group 2. Thus, we can rewrite the monopolist’s costs as

In Figure A14.2, we have entered the monopolist’s costs in cell B5 as “=1000+(B3+C3)^2”.

It is important to see that what matters here is the formula for costs; the number in the picture, $706,600.00, is simply the monopolist’s costs if the monopolist set P₁ and P₂ at zero and produced everything its customers demanded at those prices!

Finally, we enter the formula for profits, as shown in Figure A14.3.

Profits are revenues minus costs so we enter into Excel “=B2*B3+C2*C3− B5”, which is price times the quantity demanded for Group 1 plus price times quantity demanded for Group 2 minus total costs. Excel now has enough information to solve this problem. In Excel 2007, the Solver function is found under the Data tab (but you may first have to add-in the Solver application—see Excel help for instructions on how to do this). Clicking on the Solver button produces Figure A14.4.

279

Our target is profits so in the Solver box next to “Set Target Cell”, we enter B6. We want a maximum of profits, so make sure the “Equal to” button is filled in on Max. Finally, we are going to maximize profits by changing prices, so in the box for “By Changing Cells”, we enter “B2:C2”. Now we click Solve and Excel finds the answer shown in Figure A14.5.

280

Excel tells us that the profit-maximizing prices are $142.50 for Group 1 and $123.75 for Group 2. At these prices, Group 1 customers buy 45 units, Group 2 customers buy 15 units, and monopoly profits are $3,668.75.

Once you understand the basic ideas, it’s easy to make these models even more realistic by adding bells and whistles such as more groups. Notice that we have solved this problem with a combination of economic principles and practical skills (in this case, a bit of Excel know-how). An important lesson to learn is that this combination of principles and practical skills is very powerful and eagerly sought out by employers in a variety of fields.