Marketing Analytics: Data-Driven Techniques with Microsoft Excel (2014)

Part II. Pricing

Chapter 5. Price Bundling

Chapter 4, “Estimating Demand Curves and Using Solver to Optimize Price,” discussed situations in which a customer pays the same price for each unit she purchases of a product. When a customer pays the same price for each unit the seller is using linear pricing. This chapter and Chapter 6, “Nonlinear Pricing,” describe some nonlinear pricing models in which the total amount a customer pays for a set of products is not equal to the sum of the individual product prices. One of the most common instances of nonlinear pricing is price bundling. This chapter shows that analytic models can help companies use bundling to maximize their profits.

Why Bundle?

Companies often bundle products in an attempt to get customers to purchase more products than they would have without bundling. A few examples of bundling include the following:

· Cable companies bundle landlines, cell phone service, TV service, and Internet service.

· Automobile companies often bundle popular options such as navigation, satellite radio, and keyless entry.

· Computer mail order companies often bundle computers with printers, scanners, and monitors.

· Microsoft Office has been a highly successful bundling of software products such as Excel, Word, Access and Outlook.

In this chapter you will make the assumption that customers make decisions based on the concept of consumer surplus. The consumer surplus of a product is simply the value a consumer attaches to an available product minus the actual cost of the product.

NOTE

In Chapters 16, “Conjoint Analysis” and 18, “Discrete Choice Analysis,” you learn how to determine the value a consumer attaches to a particular product or combination of products.

The consumer's goal is to make a choice that maximizes her consumer surplus. Therefore, if each product combination has a negative surplus, no product combination is purchased; however, as long as at least one option has a non-negative consumer surplus, the consumer will choose the option with the largest consumer surplus. The possibility of ties can be ignored. This section examines three examples that portray how bundling can increase profits.

Bundling Products to Extract Consumer Surplus

Suppose you own Disney World and each customer values the five rides in the park as follows:

· Space Mountain: $10

· Matterhorn: $8

· Tower of Terror: $6

· Small World: $4

· Mr. Toad: $2

How can Disney World maximize revenue from these rides? First assume a single price is charged for each ride. If Disney World charges $10 a ride, each customer would go on only Space Mountain and you make $10 per customer. If Disney World charges $8 per ride, each customer would go on two rides, and you make $16. If you charge $6 per ride, then each customer goes on three rides and you make $18. If you charge $4 per ride, then each customer goes on four rides and you make $16. Finally, charging $2 per ride, you make $10 in revenue. Therefore with a single price for each ride, revenue is maximized at $18 per customer.

Now suppose Disney World does not offer per ride tickets but simply offers a five-ride ticket for $30. Because the consumer values all five rides at $30 (the sum of the ride values) he will pay $30 for park admission. This is a 67 percent increase in revenue. Of course, single park admission is the strategy Disney World has adapted. Single park admission has other benefits such as reducing lines, but this simple example shows that product bundling can help companies “extract” consumer surplus from customers. Later in the chapter you see that the assumption that all customers are identical is not important to your analysis.

Pure Bundling

Now consider another example. Suppose you sell two products and have two potential customers that value each product, as shown in Table 5.1:

Table 5.1 Pure Bundling Works Well with Negatively Correlated Valuations

For simplicity, assume the cost of producing each product is negligible and can be assumed to equal $0. Therefore revenue and profit are identical. In this example customer valuations are negatively correlated; Customer 1 values the computer more than Customer 2 but values the monitor less. If you price each product separately, you would charge $1,000 for each product and make $2,000 revenue; however, if you charge $1,500 for a bundle of both products, you make $3,000. Thus, when customer valuations for products are negatively correlated, bundling can result in a significant increase in profit.

The reason bundling works in this case is that bundling enables the seller to entirely extract the value the consumers attach to the products. If the seller only offers the customer a choice between purchasing all products or nothing, the situation is called pure bundling. Movie rental companies usually give theatres a choice between renting an assortment of some blockbuster movies and some not-so-popular movies, or renting no movies at all. In this case if the theatre owner wants the blockbuster movies she has to also rent the less-popular movies.

If customer valuations are positively correlated then bundling usually offers no benefit, because bundling does not allow the seller to extract more surplus than separate prices. For example, in Table 5.2 valuations are positively correlated because Customer 2 is willing to pay more for each product than Customer 1. In this case, if the seller offers only separate prices she would charge $1,000 for the computer and $500 for the monitor. Then each customer buys both products and total revenue is $3,000. If the seller offers a pure bundle she should charge $1,500 for the bundle. Then both customers purchase the bundle for $1,500 yielding a total revenue of $3,000. In this this case pure bundling and separate prices yield identical profits.

Table 5.2 Pure Bundling Works Poorly with Positively Correlated Valuations

Mixed Bundling

Mixed bundling means the seller offers a different price for each available combination of products. For the data in Table 5.3 mixed bundling is optimal.

Table 5.3 Mixed Bundling is Optimal

To maximize profits, the seller can do the following (see also Exercise 7):

· To maximize profit from separate prices the seller charges $600 for the monitor and $1,300 for the computer. In this scenario Customers 3 and 4 buy the computer and Customers 1 and 2 buy the monitor yielding a profit of $1,200.

· To maximize profit with mixed bundling the seller can charge $799 for the monitor, $1,499 for the computer, and $1,700 for the bundle. Then Customer 1 buys just a monitor, Customer 4 buys just a computer, and Customers 2 and 3 buy the product bundle. The seller earns a profit of $1,798.

· If only the monitor and computer pure bundle is offered, the seller should sell the pure bundle for $1,700. Then each customer will purchase the pure bundle and the seller earns a profit of $1,600.

Using Evolutionary Solver to Find Optimal Bundle Prices

When you combine the consumer surplus decision-making assumptions explained in the previous section with the power of the Evolutionary Solver Engine of Excel 2010 or later, you can easily solve complex bundling problems.

For this section's example, suppose Verizon sells cell phone service, Internet access, and FIOS TV service to customers. Customers can buy any combination of these three products (or not buy any). The seven available product combinations include the following:

· Internet (Combination 1)

· TV (Combination 2)

· Cell phone (Combination 3)

· Internet and TV (Combination 4)

· Internet and cell phone (Combination 5)

· TV and cell phone (Combination 6)

· All three products (Combination 7)

The file phone.xls gives the amount 77 representative customers are willing to pay per month for each service. Use the model in the initial solver worksheet to follow along with the example (also shown in Figure 5.1). In row 6 you see that the first customer is willing to pay up to $3.50 per month for Internet, $7 per month for TV service, and $3.50 per month for cell phone service. (It's hard to make money off this customer!) You can use the Evolutionary Solver Engine and the willingness to pay data to determine a price for each product combination that maximizes revenue.

Figure 5-1: Verizon Internet example

The key to your model is to set up a spreadsheet that tells you, for any set of prices for each possible product combination, how much revenue you can obtain from your sample of customers. Then you can use the Evolutionary Solver to find the set of prices for the product combinations that maximize your revenue. To find how much revenue you can generate for any set of product combination prices, proceed as follows:

1. In D4:J4 enter trial prices for each of the possible seven product combinations.

2. In Row 6, determine the first's customer's consumer surplus by computing her value for the products in a combination and subtracting the cost of the product combination. For example, the first customer's consumer surplus for the Internet +TV product combination is computed in cell G6 with the formula =A6+B6-G$4. Copy the formulas in row 6 to the cell range D7:J82 to compute each customer's consumer surplus for each product combination.

3. Determine for the set of prices in row 4 which product combination, if any, is purchased. Copy the formula =MAX(D6:J6) from K6 to K7:K82 to find each consumer's surplus.

4. Now here's the key to your spreadsheet. In Column L you use the MATCH function (introduced in Chapter 2, “Using Excel Charts to Summarize Marketing Data”) combined with an IF statement to determine which product combination each customer purchases. Use product combination 0 to denote no purchase, whereas the actual product combinations are indicated by the integers 1–7. Copy the formula =IF(K6<0,0,MATCH(K6,D6:J6,0)) from L6 to L7:L82 yields the product combination (if any) bought by each customer.

5. Copy the formula =IF(L6=0,0,HLOOKUP(L6,$D$3:$J$4,2)) from M6 to M7:M82 to compute for each person the revenue generated.

6. In cell M4, compute your total revenue with the formula =SUM(M6:M82).

To find the product combination prices that maximize revenue you need to use the Evolutionary Solver, so some discussion of the Evolutionary Solver is in order.

Introduction to the Evolutionary Solver

As explained in Chapter 4, “Estimating Demand Curves and Using Solver to Optimize Price,” the Evolutionary Engine is used when your target cell and constraints contain nonsmooth functions that reference changing cells. For example, if your target cell and constraints contain IF, SUMIF,COUNTIF, SUMIFS, COUNTIFS, AVERAGEIF, AVERAGEIFS, ABS, MAX, or MIN functions that reference the changing cells, then the Evolutionary engine probably has the best chance to find a good solution to your optimization problem. The model makes extensive use of IF statements, so it is a good choice to use the Evolutionary Solver. The target cell is to maximize revenue (cell M3) by changing the product prices (cell range D4:J4).

When using the Evolutionary Solver, you should follow these rules:

· Place upper and lower bounds on each of your changing cells. This makes the Solver's search for an optimal solution much easier. In the Verizon bundling model use a lower bound of 0 for each price and an upper bound of 100.

NOTE

If, when running Evolutionary Solver, a changing cell hits an upper or lower bound, you should relax that bound because Solver was trying to push the changing cell outside the range you specified.

· Use no constraints other than the bounds on the changing cells. You will soon see how to use penalties in the target cell to enforce constraints.

· In the Options tab increase Max Time to 3600 seconds. This causes Solver to run for 60 minutes, even if you leave the room. This should be plenty of time for Solver to find a good solution. Also add a few 000s to Max Subproblems and Max Feasible Solutions. This ensures that Solver does not stop when you leave the room. Figure 5.2 summarizes these settings.

· In the Options tab select the Evolutionary Solver tab and change Mutation Rate to 0.5 and Maximum Time Without Improvement to 300 seconds. (Mutation rate is explained soon.) Setting a Maximum Time Without Improvement to 5 minutes ensures that if Solver fails in 5 minutes to improve the current solution, the Solver stops. Hitting the Escape key at any time stops the Solver.

Figure 5-2: Evolutionary Solver settings

Evolutionary algorithms (often called genetic algorithms) were discovered by John Holland, a Michigan computer science processor. Evolutionary Solver begins with between 50 and 200 “solutions,” which are randomly chosen values of the changing cells that lie between the lower and upper bounds for each changing cell. The exact number of solutions used is specified in the Population Size field in Figure 5.3. The default value is 100, which is fine. Then the target cell value is computed for each solution. By a process of “reproduction” explained in David Goldberg's textbook Genetic Algorithms (Addison-Wesley, 1989), a new set of 100 solutions is obtained. Previous solutions that have “good” values (large in a Max problem and small in a Min problem) have a better chance of surviving to the next generation of solutions. This is the mathematical implementation of Darwin's survival of the fittest principle.

Figure 5-3: Changing the Mutation rate

The Excel Solver also enables solutions to improve via Mutation. To understand how Mutation works you need to know that Excel codes each value of a changing cell in binary notation. For example, the number 9 is coded as 1001 (1 * 2³ + 0 * 2² + 0 * 2¹ + 1).

A Mutation in the Evolutionary Solver changes a 0 to a 1 or a 1 to a 0. A higher value for the Mutation rate moves you around more in the feasible region but exposes you to a larger probability of being led on a wild goose chase after moving to a part of the feasible region with poor changing cell values. A Mutation rate of .5 usually yields the best results. Figure 5.3 shows the Mutation rate changed to .5. The amazingly simple Evolutionary Solver search procedure based on survival of the fittest can solve extraordinarily complex problems! When solving a problem the Evolutionary Solver must make many random choices. This means that if two different people run the same model, then after 5 minutes or so they may see different optimal solutions. Eventually they should see target cell values that are virtually identical.

Finding the Optimal Bundle Prices

The Solver window shown in Figure 5.4 enables you to find revenue maximizing the set of prices for each product combination. A maximum profit of $3,413.70 (see worksheet initial solver) is found with the product combination prices, as shown in Figure 5.5.

NOTE

Unlike the GRG or Simplex Solver engine, the Evolutionary Solver engine is only guaranteed to find a near optimal solution (as opposed to an optimal solution). Therefore, when you run the Evolutionary Solver on the book's examples, do not be surprised if your optimal target cell value differs slightly from the target cell value shown in the book.

Figure 5-4: Initial bundling Solver model

Figure 5-5: Solution to initial bundling model

Unfortunately, you could not go to market charging $74.35 for Internet service and $69.99 for Internet + TV service because it is unreasonable to provide two services for a lower price than a single service. This situation is called a price reversal. As you soon see, the reason Solver charged a higher price for Internet service than for Internet and TV service is because the revenue maximizing prices involve nobody just buying Internet service, so a high price helps ensure that nobody buys just Internet service.

Take a look at the final solver worksheet, and you see it contains the same prices found in the initial solver worksheet. The cell range P70:Q81 in the final solver worksheet (as shown in Figure 5.6) computes the price reversals for each possible combination of a product with a larger combination of products. In column P, the price of the smaller product combination is subtracted from the price of the larger product combination. For example, in P71 the formula =D4-G4 computes the Internet Price – (Internet + TV Price). The difference of $4.36 indicates that the Internet + TV price is $4.36 lower than the Internet price. To ensure that no price reversals occur, the target cell is penalized $500 for each dollar of price reversal. Then, survival of the fittest should ensure that no price reversals survive. The choice of penalty amount is an art, not a science. Too large a penalty (such as $1 million) seems to work poorly, and too small a penalty does not “kill off” the things you want to avoid.

Figure 5-6: Price reversals for initial Solver solution

You can copy the formula =IF(P70>0,P70,0) from Q70 to Q71:Q82, to track the price reversal because it yields a value of 0 if the product comparison does not have a price reversal. With the formula =SUM(M6:M82)-500*Q82 you can incorporate your penalty in the target cell. The Solver window is the same (refer to Figure 5.4). After running the Solver you can find the optimal solution, as shown in Figure 5.7.

Figure 5-7: Final bundling solution

Therefore, after completing all the calculations, a maximum profit of $3,413.90 is obtained by charging the following:

· $47.41 for Internet service

· $35 for TV service

· $67.87 for cell phone service

· $70 for any two product combination

· $89.95 for all three products

The consumer is given a substantial discount if she buys two or more products. You can copy the formula =COUNTIF($L$6:$L$82,N9) from O9 to O10:O16 to find the number of people purchasing each product combination. As shown in Figure 5.8, 25 people buy nothing; nobody buys just the Internet; 19 buy just TV; nobody buys just the cell phone; 8 buy the Internet + TV; 2 buy Internet + cell phone; 1 buys TV + cell phone; and 22 people buy the bundle! Your pricing helped extract the high value people place on the Internet and cell phone service by incentivizing these people to buy more than one service. Note the high prices for the Internet and cell phone are designed to give customers an incentive to purchase more products.

Figure 5-8: Number of purchases of each product combination

This method will automatically determine whether separate prices, mixed bundling, or pure bundling is optimal.

Summary

In this chapter you learned the following:

· Bundling products often allows companies to extract more consumer surplus from customers by incentivizing the customers to purchase more products.

· If you assume that each customer will purchase the product with the largest (non-negative) surplus, then you can set up a spreadsheet that tells you the revenue obtained from your customers for any set of prices.

· You can use Evolutionary Solver to maximize Revenue (or Profit) obtained from customers.

· When using Evolutionary Solver you need to implement bounds on changing cells, use a Mutation rate of 0.5, and handle other constraints by incorporating penalties in the target cell.

Exercises

1. A German machine company sells industrial machinery and maintenance policies for the machine. There are four market segments. Figure 5.9 shows the size of each market segment and the amount each segment will pay for the machine separately, maintenance separately, or the bundle of machinery + maintenance. The variable cost is $550 per machine and $470 per maintenance agreement. What set of prices maximize profits?

Figure 5-9: Data for Exercise 1

The file songdata.xlsx gives the values several hundred people place on 15 downloadable songs. A subset of the data is shown in Figure 5.10.

Figure 5-10: Data for Exercise 2

a. If you charge a single price for all songs, how can you maximize revenue?

b. If you can charge two different prices for songs, how can you maximize revenue?

3. Kroger is trying to determine which types of Vanilla Wafer cookies to stock. The wholesale cost Kroger is charged for a box of each type of cookie is shown in the following table:

The wholesale price of a box of cookies is assumed equal to the product's quality. Each customer assigns a different value to quality, as shown in file Wafersdata.xlsx and Figure 5.11. For example, Customer 7 would value National 1 at 1.02 * (1.5), and so on. Your task is to determine how to price each type of cookie and then to recommend which brands Kroger should stock to maximize profit from cookies.

Figure 5-11: Valuations for Exercise 3

4. Microsoft is going to sell a Student version of Office (excluding Access and Outlook) and a full version of Office. Assume there are three market segments whose size and valuations for each version of Office are given in the following table. What price for each version of Office can maximize Microsoft's revenue?

5. The New York Times wants to price home subscriptions and web access. Of course, people can buy both home subscriptions and web access. Assume there are three market segments whose size and valuations (per month) for each product combination are given here. The cost to provide a home subscription is $15 and the variable cost to provide web access is $1.

5. What prices maximize monthly profits?

6. Before publishing a hardcover book, a publisher wants to determine the proper price to charge for the hardcover and paperback versions. The file paperback.xlsx contains the valuations of 50 representative potential purchasers of the book. Suppose the unit cost of the hardcover book is $4 and the unit cost of the paperback is $2. If the bookstore charges double what it pays the publisher, what price should the publisher charge the bookstore for each version of the book?

7. For the data in Table 5.3 verify that the given mixed bundling strategy maximizes the seller's profit.

8. Describe a situation in which a bank can benefit from a bundling strategy.

9. Suppose your company sells four products and during a year a customer will buy each product at most once. Your company would like to give an end-of-year rebate of 10 percent off to customers who purchase at least $1,000 of products. How can the methods of this chapter be used to maximize your company's profits?