In several large market segments, especially
those that deal with a time-sensitive good or service, you can segment customers into
valuation groups. High valuation groups are willing to pay more
for the product or service, while low valuation groups are only willing to pay a lesser amount. This afternoon, we're going to explore how
the airline industry segments customers who are willing to pay more for a ticket from
those who will only fly on discounted fares. You're probably familiar with the old method
airlines used for this - if you were willing to spend a Saturday night at a destination,
you could get a significantly cheaper fare. That method segmented business travelers from
leisure travelers. Unfortunately for most airlines, the public
now sees them as a replacement for driving long distances or riding buses. The Saturday night stay method has fallen
out of use, in favor of methods that use how far in advance the ticket was booked. In general, the closer people book a ticket
to the day of the flight, the more they're willing to pay. Let's pretend we've been hired by Malaysia
Airlines to help them maximize their revenue on flights between Amsterdam and Kuala Lumpur. We've conducted some basic research, and asked
2% of potential passengers their valuation of the flight. We only had to ask 10 people - the airline's
business seems to have dropped off sharply for some reason. This is the table of the raw data from our
survey. The 10 valuations we have are functionally
the same as 10 points on the demand curve for the flight, which is enough for us to
calculate the curve. What we need to do is sum up the number of
people who would buy a ticket at each of the 10 price points - that's the demand at each
of the price points. For example, if we charged a single price
of $700 for the flight, 5 of our surveyed passengers would buy a ticket (because 5 of
our surveyed passengers value the flight at more than $700). Let's open a new Excel workbook, and enter
the 10 fare valuations our research turned up. We can have Excel sum up the number of passengers
who would purchase at each price point, using the COUNTIF function. Enter the formula, and accept it. Select the cell containing the formula, and
drag the selection box down using the handle at the bottom right corner of the cell. This will extend the formula to the remainder
of the demand count cells. Since our survey data only covers 2% of the
potential passenger market, we need to multiply our demand levels by 50 to make them represent
the entire market. Enter the formula to multiply the first demand
level by 50, and accept it. Select the cell containing the formula, and
drag the selection box down using the handle at the bottom right corner of the cell. This extends the formula to the rest of the
cells in the column Select the cells containing the prices, and
the demand levels at each price. Navigate to the Insert tab, click the Scatter
option in the Charts group, and choose Scatter with Only Markers. Our next step is to get Excel to draw the
demand curve for us based on the data points. Recall our basic formula for linear demand
curves, where d represents the demand level at a given price p. Excel will automatically
calculate the values for a and b for us as part of drawing the demand curve. Right-click any of the data points in the
chart, and choose Add Trendline from the context menu. In the Format Trendline dialog, choose the
Linear radio button and check the Display Equation on chart checkbox. Then click the Close button. Excel gives us the plot of the line connecting
our two points, along with the equation for the line. If we plug our values for a and b into the
linear demand curve formula, we end up with this equation. To illustrate the effect customer segmentation
can have on revenue, let's calculate our maximum revenue if we charged every passenger the
same price. We're going to cheat just a little bit, and
ignore the fact that our airplanes have a fixed number of seats to keep our model simple. Start by entering a guess for the optimized
price. It doesn't have to be accurate - Excel just
needs a place to start working from. I'm going to guess $400, but you can use whatever
you like. Next, enter our demand curve's formula, and
accept it, to calculate the demand level for tickets based on the price. Our total revenue is the optimized price of
a ticket, multiplied by the demand level for tickets at that price. Enter the formula to calculate revenue, and
accept it. Now we're ready to let Excel's Solver tool
find the optimal price for our plane tickets. Navigate to the Data tab in the ribbon, and
choose the Solver option. We want to maximize our revenue by changing
the price we charge for tickets. We're going to use the GRG Nonlinear solving
method. Click the Solve button to put Solver to work. When it completes, it's going to tell us the
price we should charge for a ticket to maximize our revenue. Click the OK button to dismiss the status
dialog Solver displays when it finishes. If we charged the optimized price of $599
for tickets, we'd see around a quarter million dollars in revenue. Let's move on to what we really want to do
- charge some customers more for the same ticket to increase our revenue. In an ideal world, from the airline's perspective,
we could charge each customer a different price based on how rich they are - or how
desperate they are. But that's basically illegal, at least here
in the US - it's considered discrimination. What's not illegal is using a price segmentation
strategy called yield management. As long as we can identify a variable that's
strongly correlated to how much a passenger is willing to pay, we can charge different
prices based on this variable. In the case of an airline, our strongly correlated
variable is how early a passenger buys a ticket. In other words, low-valuation customers tend
to purchase a ticket well before a flight, while high-valuation passengers buy tickets
at the last minute. We can model this behavior by incorporating
two price points into our model. The demand formula for our high price point
is going to be the same as our basic one-price demand formula. But the demand formula for our low price point
is going to be our basic demand formula, minus the demand for tickets sold at the higher
price point. Let's go back to Excel, and start working
in a blank worksheet. Enter guesses for the high and low price point. Again, it's not important that these be even
remotely close to the actual prices - Excel just needs a place to start working from. These are my guesses - feel free to enter
your own. We're all going to end up at the same place. Enter the demand formula for our low price
point, and accept it. Enter the demand formula for our high price
point, and accept it. For both price points, the revenue is the
optimal price multiplied by the demand level at that price. Enter the revenue formula for our low price
point, and accept it. Enter the revenue formula for our high price
point, and accept it. Our total revenue is just the simple sum of
both revenue streams. Enter the SUM formula, and accept it. Now we're ready to optimize our pricing, using
Excel's Solver tool. Navigate to the Data tab in the ribbon, and
choose the Solver option. We want to maximize our total revenue by changing
our high and low price points. We're going to continue using the GRG Nonlinear
solving method. Click the Solve button, and Solver will zip
off to find our solution. Click the OK button to dismiss the status
dialog Solver displays when it finishes. Our optimized model indicates that we should
charge a high price of $799 for last-minute passengers, and a low price of $399 for advance
reservations. We've just increased the airline's revenue
by 33% for this flight. Our model still has one problem we need to
fix - it's working under the assumption that there are an unlimited number of seats on
the plane. Since we don't want people sitting on the
wings and hanging out of the landing gear bays, we need to add a capacity constraint
to our model. The plane Malaysia Airlines flies on this
route, a Boeing 777, holds 341 passengers. We want to make sure we don't sell more than
341 tickets for the flight. The total number of passengers in our model
can be calculated by adding the low price demand level to the high price demand level. Enter the formula, and accept it. Navigate to the Data tab in the ribbon, and
restart the Solver tool. Click the Add button to add our constraint. We want to limit our total number of passengers
to be less than or equal to 341 - the number of passengers the plane can hold. Click the OK button to set the constraint. Click the Solve button, and Solver will re-optimize
our model, taking into account the constraint on the total number of passengers. When Solver completes, click the OK button
to dismiss the status dialog. Now, we should charge a high price of $955
and a low price of $711. These updated prices force our total demand
to fit inside the airplane's capacity. Notice that the addition of the capacity constraint
dramatically increased the price of the tickets, which you would expect. Optimizing Sales Models Pricing Analytics0Page 5
