Analytics Regression Excel Project/Paper

SCM 651: Business Analytics

Homework #3 1

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. 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.

Assignment What’s due:

Submit an analysis before the live class in week 8. Suggested length is five pages, but should not exceed ten pages, single-spaced, 12-point font. Use Excel to analyze the data and document your results in a Word document.

This is a group assignment; each student should upload a copy of the assignment to the Learning Management System. The paper must be a Microsoft Word document. You should also submit the Excel spreadsheet with the regression and optimization analysis. Submit both your Word and Excel files. Name the files HW3_Team# where # is your team number. Be sure to include the names of everyone on the team on the first page of the paper. Late assignments will not be accepted. Failure to follow directions will be penalized.

SCM 651: Business Analytics

Homework #3 2

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%)