Analytics Regression Excel Project/Paper

SCM 651: Business Analytics W E E K 6

BUSINESS ANALYTICS 1

Agenda – Week 6 • Homework #3 Optimizing Product Pricing:

Overview • Break-out Discussions • Review of concepts:

o Optimization o Vlookup o Formulas in pivot tables o Named Ranges o GetPivotData o Pivot Table Grouping

BUSINESS ANALYTICS 2

HW#3 - Regression

BUSINESS ANALYTICS 3

Question 1a, 1b, 1c

Price: Sale Price to Customer

HW#3 – Predicted % Purchased

BUSINESS ANALYTICS 4

Question 1b i

HW#3 – Predicted Sales Qty

BUSINESS ANALYTICS 5

Question 1c

Predicted Sales = Predicted % Purchased * Number of Customers

HW#3 - Revenue

BUSINESS ANALYTICS 6

Question 1d

HW#3 - Profit

BUSINESS ANALYTICS 7

Question 1e

HW#3 – Conditional Formatting

BUSINESS ANALYTICS 8

Question 1f

Interpretation?

HW#3 – Profit Margin

BUSINESS ANALYTICS 9

HW#3 – Optimization – 2ai

*** Tips - Perform each of the 3 optimizations on their own tab. For first optimization copy entire sheet tab = “Price vs. Demand”, keep 1st 2 rows (headers & 1 row of data). Modify for first optimization. ***

• Calculate the price point for the highest profit possible

• The publisher will sell the books to you at $5.00 each with no minimum order. How does this compare with results from Question 1?

10

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

Price Predicted % Purchased

Predicted Sales Qty Revenue Profit Profit Margin Book Cost

% Purchased = a * Price ^ b Book Cost 5.00$ a Number of Customer 100,000 b

HW#3 – Optimization - 2aii

BUSINESS ANALYTICS 11

Calculate the price point for the highest profit possible The publisher has agreed to sell you the books at $4.50 each if you sell at least 30,000

2. Optimization analysis (with constraints) a. Calculate the price point for the highest profit possible ii. The publisher has agreed to sell you the books at $4.50 each if you sell at least 30,000

Price Predicted % Purchased

Predicted Sales Qty Revenue Profit Profit Margin Book Cost

Minimum Book Sale Amt

% Purchased = a * Price ^ b Book Cost 4.50$ a Number of Customer 100,000 b Sell at least # books 30,000

HW#3 – Optimization - 2aiii

BUSINESS ANALYTICS 12

Calculate the price point for the highest profit possible The publisher has agreed to sell you the books at $4.00 each if you sell at least 50,000.

2. Optimization analysis (with constraints) a. Calculate the price point for the highest profit possible iii. The publisher has agreed to sell you the books at $4.00 each if you sell at least 50,000

Price Predicted % Purchased

Predicted Sales Qty Revenue Profit Profit Margin Book Cost

Minimum Book Sale Amt

% Purchased = a * Price ^ b Book Cost 4.00$ a Number of Customer 100,000 b Sell at least # books 50,000

HW#3 - Scenario Summary – 2b

Suggestion: Summarize all three scenarios in 1 table to help compare. Which cost point should you accept from the publisher? Discuss Pros and Cons of accepting each scenario looking at results from table above, as well as overall business impacts. Think about what a cross functional team would discuss when selecting a scenario.

BUSINESS ANALYTICS

13

Scenario Summary

Scenario Price Predicted % Purchased

Predicted Sales Qty Revenue Profit Profit Margin

Book Cost

Minimu m Book Sale Amt

i 5.00$ ii 4.50$ 30,000 iii 4.00$ 50,000

HW#3 – Question 3a Discussion

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%) • Tip – Consider Risks from each of the scenarios,

as well as Pros and Cons of accepting each scenario.

BUSINESS ANALYTICS 14

Academic Integrity Full Screen Shot pasted in word doc showing • Solver Parameters for Optimization 2aiii. • Date

BUSINESS ANALYTICS 15

Break-out Discussions

BUSINESS ANALYTICS 16

Article #2: A Process of Continuous Innovation: Centralizing Analytics at Caesars 2.1 Why does Caesars use analytics? (breakout group 1) 2.2 What are 1st 2/4 lessons learned from their experience? (breakout group 2) 2.2 What are Last 2/4 lessons learned from their experience? (breakout group 3)

Break-out Discussions

BUSINESS ANALYTICS 17

Optimization Discussion (Time Permitting): You are a cell phone company and you want to optimize your marketing to maximize profit. What might be some factors to include in your optimization?

Review of Concepts • Optimization - Asynchronous • Optimization – Class Demo + Other Excel Functions

BUSINESS ANALYTICS

18

Compare Goal Seek & Solver Goal Seek Solver

What Ribbon Command?

Data tab, What if Analysis, Goal Seek

Data tab, Solver (be sure enabled)

Set Goal Set 1 cell to Value Set 1 cell to Value, Max, or Min

By Changing 1 Cell Range of cells

Constraints None Multiple Allowed

Command

BUSINESS ANALYTICS 19

Goal Seek - Asynchronous Example • Change 1 variable – price to get profit = 0 • If starting price = 3, then get 1.29 • If starting price = 4, then get 6.38 • How can you get 2 different answers?

20

Goal Seek – 2 Solutions

The profit as a function of Unit Cost is a curve – and crosses at profit = 0 in 2 places – $1.29 & $6.38 What if you didn’t accidentally stumble upon these 2 solutions? How would you be aware there could be multiple solutions?

21

PLOT THE DATA !

Solver – Max Profit

• For our example, max profit occurs at $3.84 price, no constraints.

22

Solver – Max Profit

For our example, max profit occurs at $3.84 price, no constraints.

23

Class Demo - Optimization • Demo will include Optimization • Other Excel functions

o Vlookup o Formulas in pivot tables o Named Ranges o GetPivotData o Pivot Table Grouping

BUSINESS ANALYTICS 24

VLOOKUP: Age Description & Age Ranges • Tab = “DirectMarketing Data” • Populate K2, L2 (AgeDesc, AgeRange).

• K2=VLOOKUP(A2,$O$4:$Q$6,2,FALSE) • L2=VLOOKUP(A2,$O$4:$Q$6,3,FALSE) • F4 toggles absolute reference.

• Drag formula to populate entire column.

BUSINESS ANALYTICS 25

Create Pivot Table: Customer Spend per Catalog by # Shipped per Year

• Tab = Direct Marketing Data • Insert, Pivot table,

• Range: 'DirectMarketing Data'!$A$1:$L$1001, • Location: 'Optimization'!$K$6

• Rows = AgeDesc, CatalogsSent-Year. Re-arrange AgeDesc (Young, Middle, Old)

• Create formula: Analyze tab, “Fields, Items, & Sets”, Calculated Field. • Name: CustomerSpend/Catalog • Formula: ='Customer Spend'/Catalogs • Click “Add”, “OK”

• Insert a chart to inspect behavior of metric. • To list all formulas within pivot table, click in pivot table, Analyze tab,

“Fields, Items, & Sets”, List Formulas.

BUSINESS ANALYTICS

26

GETPIVOTDATA: Populate Spend per Catalog by # Sent

• Tab = Optimization • Cell B15, type “=“, then click in pivot table,, Catalogs=6,

AgeDesc=Young. This creates the GETPIVOTDATA formula.

• Adjust formula to Cell References Young, 6 Cataglogs sent = =GETPIVOTDATA("CustomerSpend/Catalog",$H$6,"CatalogsSent- Year",$A15,"AgeDesc",B$14)

• Drag formula to copy B15-D16. Check a few to be sure it’s looking up properly.

BUSINESS ANALYTICS 27

Named Ranges • Create any named ranges to clarify running the optimization. This is not

required. • Some have already been completed, we’re going to add a named range for

cells we want to vary in optimization

• Select B19:D20 • In Cell Reference type “VaryCatalogsSent”

BUSINESS ANALYTICS 28

https://support.of fice.com/en- us/article/Define- and-use-names- in-formulas- 4d0f13ac-53b7- 422e-afd2- abd7ff379c64#b mchange_a_nam e

Run Optimization

• Data tab • Solver • Per Goal & Constraint

listed in cells A6-B8 • Click “Solve” • Depending upon

starting values, can get multiple solutions.

BUSINESS ANALYTICS 29

Pivot Table Grouping • Select & Copy Pivot Table on tab = “Optimization” • Paste in tab = Salary Grouped • Rows = Salary • Values = CustomerSpend/Catalog • How to group

• Click on any "Salary" value in pivot table. • Right Click "Group...“ • Use Auto values for group or adjust. In this cause adjust "Starting at:"

to 10,000, Group by 10,000.

• Insert Chart • Columns = Bring various fields in to try for quick review of data

BUSINESS ANALYTICS 30

Wrap-Up of Analytics 2. Predictive analytics: Regression, mathematical modeling, and forecasting

31

1. Descriptive analytics: Visualization of data to develop a high level of understanding

3. Prescriptive analytics: Optimization and solutions

Close Class • Next Week - Week 7 • Introductory R & R Commander. • Install early!

• Homework #3 due prior Class Week 8

BUSINESS ANALYTICS 32