1

let us suppose we have following data with binary response output(coupon)

enter image description here

annual spending is given in 1000th unit, my goal is to estimate whether if customer spend more then 2000 and has Simmons card, will also have coupon, first of all i have sorted data according to response data, i got following picture

enter image description here

at next stage i have calculated logit for each data, for those initially i choose following coefficient

B0  0.1
B1  0.1
B2  0.1

and i have calculated L according to the following formula

enter image description here

at next stage i have calculated e^L (which in excel can be done easily by exp function )

=EXP(D2)

after that i have calculated probability 

=E2/(1+E2)

enter image description here

and finally using formula enter image description here

i have calculated log likelihood function

then i have calculated sum and using solver i have calculated coefficient that minimize this sum( please pay attention that values are given in negative value) , but i have got all coefficient zero

enter image description here

i am wrong ? or does it means that i can'predict buying of coupon on the base of Annual spending and owning of Simmons card? thanks in advance

1 Answers1

1

You can predict the buying of a coupon on the base of Annual spending (and knowing Simmons card doesn't help).

Admittedly I didn't solve it in Excel, but I suspect the problem might be that your optimization didn't converge (i.e., failed to reach the correct coefficients through the solving process) -- the correct coefficients are B0 = 5.63, B1 = -2.95, and B2 = 0. I found an online reference for the Excel logistic regression procedure at http://blog.excelmasterseries.com/2014/06/logistic-regression-performed-in-excel.html.

I ran the logistic regression myself and found that Annual spending is significant (at the 0.05 level) whereas Simmons card is not. Re-running the model with Simmons card removed yields the following equations:

    L = 5.63 - 2.95 * Annual spending
    P(1)  =  exp(L)/(1 + exp(L))
    If P(1) > 0.5 => coupon = 1

Although the entropy Rsquare is low at 0.39 (and the number of data points is very low), the model is statistically significant.

dmb
  • 567
  • 5
  • 17