Price Bundling with Excel
Most often, bundles are an opportunity to increase the average order value, but when you think about it, there are more benefits to price bundling such as pricing opacity, product line expansion, marketing simplicity and subsidized feature development.
Yesterday I came across an article that explained how price bundling is essentially utilizing the consumer surplus and if we have the data set for consumer surplus, we will be able to find the optimal bundle price.
Although generating data set for consumer surplus, or consumer willingness to pay, can be fairly difficult for some product categories, it is doable for others. One example I can think of is how cable companies have the data of the prices each individual is paying for different services, which more or less represents consumer willingness to pay; thus, they can bundle landlines, cell phone service, TV service and Internet service to extract consumer surplus. Another example is how Disney world can take the most popular and least popular rides and bundle them together, then it’ll be able to not only increase revenue but also improve quay wait times.
Today we’ll be building this model using a sample dataset mobilecarrier.xls, it gives the amount 77 representative are willing to pay per month for each service. The key to this model is to set up a spreadsheet that tells me, for any set of prices for each possible product combination, how much revenue we can obtain from this sample of customers. Then we’ll will use the Solver plug-in to determine the set of prices for the product combinations that maximizes the revenue.
First, we start with this data set of consumer willingness to pay for the three services, as we can see in the illustration on the right, the sample customers have various or sometimes bipolar preferences on these services.
To find how much revenue we can generate from any set of product combination prices, we create four more possible product combinations: Internet +TV, Internet + Cell phone, TV + Cell phone, and all three together. There will be a total of 7 possible combinations.
After labelling the possible combinations, we create cells to test out the trial bundle prices. This is to examine which prices can extract the greatest consumer surplus. To do that, we subtract the trial prices by the consumer willingness to pay data as shown above. Copy the formula to cover the whole range within the border. I always like to drag to save time, but make sure to double check that the formula is correct.
Now determine the maximum consumer surplus for each product combination using the =MAX function as show above. In the column beside where we compute maximum surplus, we use a combination of MATCH function and IF statement to determine which product combination each customer will purchase. In here, we will use product combination 0 to denote no purchase, whereas the actual product combinations are indicated by the integers 1 to 7. Use the formula =IF (K6<0, 0, MATCH ( K6, D6 : J6, 0)) to yield the product combination (if any) bought by each customer. Use =IF ( L6=0, 0, HLOOKUP( L6, $D$3: $J$4, 2) to compute for each person the revenue generated. Calculate your total revenue in any given cell by calculating the SUM of the maximum surplus column.
Now we have complete the setup, and get to use my favorite excel plug-in: the Evolutionary Solver. Set up the parameters by maximizing the revenue cell. The constraints will be the 7 trial prices being less than or equal to 100, and greater than or equal to 0. Wallah! We now have the optimum set of bundle prices that maximizes revenue and is based on consumer preferences.
Most of the results make sense, there is however an interesting price reversal in one of the result. We cannot go to market charging $74.35 for Internet and $69.99 for Internet + TV as it is unreasonable to provide two services for a lower price than a single service. Always remind ourselves to double check if the number makes business sense. We can prevent Solver to yield results with price reversals by penalizing the target cell for each dollar of price reversal. This will yield a different result by brining the Internet price down to $47 and keep the Internet +TV at $70. I did not include the set up it in here so message me if you would like to see the whole thing.
If you are interested in playing around with this data set or have other ways of setting up the price bundling model, feel free to ask for the password for my analytical models file.
