Business Analytics Assignment: Optimizing Product Pricing
Background
The Book Emporium wants to price books to optimize profits. The spreadsheet for this homework has sales data on Harry Potter book 7. 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
1. Regression analysis
a. Graph the percent purchased against price
b. Perform a regression using power regression to determine the predicted % column.
i. Graph the new curve
ii. Estimate the equation of the line
iii. What does the R2 mean?
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)
d. Calculate the revenue column (price * predicted sales)
e. Calculate the profit column ((price - book cost) * predicted sales)
f. Use conditional formatting to highlight the profit values for all prices
2. Optimization analysis (with constraints)
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
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
a. What are the risks of using Harry Potter 7 data in predicting your new demand curve for the Harry Potter sequel?
b. What other data would you like to have to perform your analysis?
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Book-Prices.rar