Welcome, everyone. This afternoon, we're going to learn how Excel
can be used to determine the best price for a product or service. The best price is the one that maximizes our
profits - not necessarily the price that sells the most units. Excel's Solver is a powerful tool that lets
us construct several models that are useful in pricing. We're going to use it to determine the best
price for a particular product - an HP inkjet printer. We're also going to use it to help answer
the oft-asked question: why are printers so cheap, and ink cartridges so expensive? This is the dialog box used to set the three
parts of an Excel Solver model. The objective cell sets the value we want
to minimize (like manufacturing cost) or maximize (like profit margin). The changing variables cells contain the variables
Solver can adjust to optimize the objective cell. We're going to let Solver adjust product prices. Constraints are restrictions we can place
on how Solver changes the variable cells. For example, we might be selling a product
at Walmart, and set a constraint that doesn't allow a product price higher than Target charges
for similar products. Once we've set our target cell, variable cells,
and constraints, Solver tries all of the reasonable solutions that fit our model. It decides on an optimal solution - the values
for the variable cells that yield the best value for the target cell. So let's pretend we work for HP, and we're
selling inkjet printers. We want to build a demand curve for the printer,
then use Solver to find the price we should charge that maximizes our profit margin. Let's say we charge $75 for the printer, and
we can sell 5,000 of them at that price. Each printer costs us $59 to manufacture,
the price elasticity for the printer is 2, and the demand curve is linear. What price should we be charging for each
printer? We know the two points on our linear demand
curve are demand equal to 5,000 at price $75, and demand equal to 4,900 at a price of $75.75. Open Excel, and enter our values for price
and demand. Select the data cells, and insert a scatter
chart with only markers. Switch the row and column to fix the slope
of the linear demand curve. Right-click either of the data points, and
choose Add Trendline. Select a Linear trend line, and check the
option to display the equation of the line on the chart. Click the Close button. Since price is on the x-axis and demand is
on the y-axis, we can substitute the values from the line's equation into our demand curve
formula. This is the formula we're going to use as
the basis for our model in Excel Solver. Enter the manufacturing cost for each printer. Let's give Excel a starting guess for the
best price. We can use anything that seems even remotely
reasonable, since we're going to let Solver come up with the correct value for us. Let's try an initial guess of $70. Next, we need to set up a cell that has the
demand formula in it. Enter the formula in the cell, then accept
it. Under that cell, let's set up a cell that
has the profit formula in it. Recall that we're using a basic definition
of profit - the price we charge for a printer, minus what it costs to manufacture the printer,
multiplied by the number of printers we can sell at that price according to the demand
curve. Enter the profit formula in the cell, then
accept it. Now, we can use Solver to determine the price
that maximizes our profits. Navigate to the Data tab on the ribbon, and
choose Solver from the Data group. We want to maximize the value in the cell
containing the profit margin, by changing the cell that contains the price we charge
for each printer. Now we need to choose the right solving method. We're going to accept the default GRG Nonlinear
option, because the formulas that make up our model aren't linear. Think way back to your high school algebra
classes, and you'll remember that the graph of a formula that contains multiplication,
division, fractions, or exponents is a curve rather than a straight line. That's how we know to choose the GRG Nonlinear
option in this case. Click the Solve button, and Solver zips off
to determine the correct answer. When it finishes, it'll display this results
dialog and put the correct values in our worksheet. For our example, it found that by charging
$86 per printer, we can maximize our profits. (And those profits would be around $95,500.) Click OK to close the results dialog. So let's make our example a little more interesting. Some products, including our ink jet printer,
have strong relationships with tie-in products. Some examples of complementary products are
DVD players and DVDs, razors and blades, cell phones and car chargers, and flashlights and
batteries. With ink jet printers, the strongest complementary
product is replacement ink cartridges. If we include the profits from selling ink
cartridges in our price calculations, the price that maximizes the profits for the printer
itself is going to decrease. Let's assume that, on average, someone who
buys our printer keeps it for 3 years and buys a replacement cartridge every 6 months. So we're going to sell about 6 replacement
ink cartridges for every printer we sell. To keep our example from getting too out of
hand, let's also assume HP's management has told us we have to sell the ink cartridges
at a price of $34. At that price, we see a profit of $12 for
each replacement cartridge. Let's go to a new tab in our Excel workbook,
and enter the data we need for this problem. We already have most of the information we
need from our work optimizing the price of the printer by itself. The cost to manufacture the printer is $59. We're going to sell an average of 6 replacement
ink cartridges for each printer we sell, at a $12 profit per cartridge. Let's enter a guess for the profit-maximizing
price for a printer when we take the profits from the cartridges into account. Again, it's not important that the guess is
anywhere near correct - Excel just needs a place to start the Solver calculations. Enter the formula for demand that we determined
earlier, and accept it. We need to set a cell to show the total profit
from both printers and ink cartridges. From our earlier work, we already know the
formula to calculate the profit margin for printers is the price we charge for a printer,
minus the cost of manufacturing the printer, multiplied by the number of printers we can
sell at that price. Let's add in the profits from selling the
ink cartridges: the number of printers we sell, times the number of ink cartridges we
sell per printer, times our profit per ink cartridge. Enter the formula in the cell, then accept
it. Navigate to the Data tab in the ribbon, and
choose the Solver from the Analysis group. Our model for Solver is the same as our previous
example - but this time our profit formula includes the profits from selling the replacement
cartridges. We're going to maximize our profit by changing
the price we charge per printer. Click the Solve button, and again the Solver
zips off to do the hard work for us. This time around, we can maximize our profit
by actually selling printers at a loss! Factoring in our profits from the replacement
ink cartridges, we make over five times more money at the end of the day by selling the
printers for $9 less than it costs us to make them. We're substantially better off trying to get
as many people as possible to buy our printer, even though we lose $9 on each printer we
sell.
