Question

Need solution for 3rd question

I solved first part below is my answers so far. Really need help on second question.

Below is the Background, What I need to do, and the price data to start with. Any help with getting me started is appreciated.   
Background
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
(10%)
iii. The publisher has agreed to sell you the books at $4.00 each if you sell at least 50,000
(10%)
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

Answer 1

2) & 3) Ans. At first I run a regression where my independent variable is price & dependent variable is purchased%. Based on price I want to predict the % of customer going to buy the Harry potter 7 book. That is demand of Harry potter 7 book based on that price. After getting the predicted % of demand of that book I will draw a demand curve & compare to that with the purchased % demand curve. Let me show the regression result , I want to show you my dataset rather than output as I just need to show the predicted demand curve. So I need predicted values. Look at that -

Please look at the predicted values at PRE_1 column. Look at the below demand diagrams

The first diagram I attached is the demand curve of given purchased % (red line) based on prices ( blue line). We see that based on prices demand curve is negatively sloped. So, it follows the laws of demand. That is as price increases demand of a certain thing decreases. But the values on the demand curve are all positive.

Now in the second diagram I have shown that here also as price increases demand decreases. As demand curve is negatively sloped followed by laws of demand. But the risky thing is that as long as we predict our % of people will be going to buy the book at a given price , among that predicted values some of are negative numbers based on the price rise. We should have a negatively sloped demand curve but we cannot have negative no. of individuals. So price rise becomes irrelevant. As in that price we could not sell the book to any individual rather we could have impossible negative no. of individuals. In the second diagram it is indicated by the red line demand curve of predicted percent as some portion of the line reached below the x-axis.

Now if we eliminate the price $22 , $23 , $24 , $25 then we could ignore to predict the negative no. of individuals. See demand curve is negatively sloped but those demand is positive. Like we can come from 25 to 20. But it should not be from 25 to -5. So ignore those 4 prices I would like to run my regression.

Here I would also like to attach my regression output. Please look at that

Regression result shows that from Anova table we got to know overall regression result is significant as p-value = 0.000 < 0.05 at 5% confidence level. Also from co-efficient table it can show that price has significant impact on the predicted no. of individuals by seeing the p = 0.000 of price. Also R^2 = .705. That is 70.5% of the model is explained by price. That is overall fit is 70.5% good.

N.B. I consider 100,000 total no. of people as given in the question.

Now the model regression equation is

predicted regression equation is

So, if we assume price = $5. Then my   

Now for the 2nd question if you want to find the maximum profit at price $4.50. Then you just need to find the predicted sales for 100,000 people at $4.5. That is near about 5000,000. Then just find revenue - cost. You will find your desire thing.