Logistic Regression in Excel using examples and scenarios

How to do Logistic Regressions in Excel

1. The Case of Book Binders Club

2. Book Binders Club Dave Lawton Marketing Executive Targeted book offers to their members Sending each offer → 50 cents New book for art buffs → Art History of Florence Niche offering Which customers should receive this offer?

3. Book Binders Club Economics Mailing offer cost $0.50 Selling price $18 Publisher cost $9 Shipping cost $3 Profit per book $6 Sending an offer to customer A is profitable if: (Probability of buying by A) x $6 > $0.50 Probability of buying by A > 0.083 ⇒ Send offers to members that will take it with at least 8.3% probability 10% x $6 > $0.50 ⇒ $0.10 profit 1% x $6 < $0.50 ⇒ No profit

4. Strategy Collect Data Analyze Data Deploy Results Evaluate Results Considers s ending the mailing to 1,000,000 leads E xclude unprofitable leads (less than 8.3% response rate) C onduct a test mailing to random 1% of leads: 10,000 mailers Records responses from test mailing to identify good leads U se logistic regression to learn from test mailing

5. Data Collect Data Analyze Data Deploy Results Evaluate Results

6. Logistic Regression Collect Data Analyze Data Deploy Results Evaluate Results

7. Outcome and Predictors Collect Data Analyze Data Deploy Results Evaluate Results

8. Collect Data Analyze Data Deploy Results Evaluate Results Multicollinearity

9. Collect Data Analyze Data Deploy Results Evaluate Results Data: Another Look

10. Collect Data Analyze Data Deploy Results Evaluate Results Model: Next Try

11. Profitability Assessment Derive profitability estimate on an untargeted mailing → Multiply results by 100 (test mailing was 1% of the total population) Derive profitability estimate on a targeted mailing → Compute profit for hypothetical mailing by excluding recipients

12. Coefficients Collect Data Analyze Data Deploy Results Evaluate Results

13. Targeting the Mailing Collect Data Analyze Data Deploy Results Evaluate Results

14. Determine Buyers in Targeted Mailing Collect Data Analyze Data Deploy Results Evaluate Results

15. Unt argeted Mailing Collect Data Analyze Data Deploy Results Evaluate Results

16. T argeted Mailing Collect Data Analyze Data Deploy Results Evaluate Results

17. Recap Collect Data Analyze Data Deploy Results Evaluate Results Untargeted mailing Targeted mailing Profits per book $6 $6 Mailing cost $0.5 $0.5 Mailing size 1,000,000 287,000 Replied 83,800 61,000 Total profits $116,280 $222,500 ROI 1% 155%