The Book Emporium wants to price books to optimize profits. The spreadsheet for this homework has sales data on Harry Potter book 7. For each week, the Book Emporium varied prices on Harry Potter 7 to determine a demand curve. The percent of customers who visited BookEmporium.com and purchased Harry Potter book 7 is shown in the spreadsheet. J.K. Rowling has announced a sequel to the Harry Potter series. Determine the price for the sequel.
Definitions
Price what you will charge each customer who purchases the new book Book Cost  
what you must pay the publisher for each book
% purchased in your pricing test, the percent of people who bought at that price Predicted %  
your regression model estimate of the percent sold based on price Predicted sales estimate of number of customers who buy the book from you Revenue total revenue generated (price * predicted sales)
Profit (price – book cost) * predicted sales
Assumptions
1. Assume that the demand for the book sequel will be similar to Harry Potter 7.
2. Assume that 100,000 customers will consider purchasing a book from you
3. The data is not an entirely accurate prediction of the demand, but a regression on the data using a power model will give a reasonable prediction
4. Assume that you pay the publisher $5.00 for each book.

Outline and grading criteria:

1. Regression analysis (40%)
a. Graph the percent purchased against price (5%)
b. Perform a regression using power regression to determine the predicted % column.
i. Graph the new curve (5%)
ii. Estimate the equation of the line (5%)
iii. What does the R2 mean? (5%)
c. Assuming there are 100,000 customers who visit your website and the publisher cost is $5.00, estimate the number of books sold (predicted sales column) (5%)
d. Calculate the revenue column (price * predicted sales) (5%)
e. Calculate the profit column ((price – book cost) * predicted sales) (5%)
f. Use conditional formatting to highlight the profit values for all prices (5%)
2. Optimization analysis (with constraints) (30%)
a. Calculate the price point for the highest profit possible
i. The publisher will sell the books to you at $5.00 each with no minimum order (10%)
ii. The publisher has agreed to sell you the books at $4.50 each if you sell at least 30,000

iii. The publisher has agreed to sell you the books at $4.00 each if you sell at least 50,000

b. Run a constrained optimization for each of the above situations to determine which cost point (from the publisher) and price (to your customer) maximizes your profit. Which cost point should you accept from the publisher?
3. Discussion (30%)
a. What are the risks of using Harry Potter 7 data in predicting your new demand curve for the Harry Potter sequel? (15%)
b. What other data would you like to have to perform your analysis? (15%)

Price	% Purchased	Predicted %	Predicted Sales	Revenue	Profit
$     5.00	65%
$     6.00	50%
$     7.00	40%
$     8.00	32%
$     9.00	25%
$   10.00	20%
$   11.00	16%
$   12.00	13%
$   13.00	11%
$   14.00	10%
$   15.00	8%
$   16.00	7%
$   17.00	6%
$   18.00	6%
$   19.00	5%
$   20.00	5%
$   21.00	5%
$   22.00	4%
$   23.00	4%
$   24.00	4%
$   25.00	4%
Book Cost	$             5.00