This spreadsheets calculates the area under the receiver operating curve for a model for a bank wishing to make a profit from their customers who regularly can’t pay off their credit card bills each month. Some Default and have to pay a penalty and others can only pay the minimum amount. Of course there is a certain percentage who continue to default and the bank must turn the case over to a collection agency. The bank has found that they can make a profit from these defaulting customers and a larger profit than from customers who pay their card off every month.

In this spreadsheet the model was developed and improved until the AUC=0.78. The maximum profit that the bank can make occurs at a threshold of -2.05 on thresholds ranging from-3.5 to 3.5. In this model a 1 is taken to be a non-profitable customer and a 0 a profitable customer. The cost for a false negative is $4800 and the profit for a true negative is $4100. 

Other variables that the spreadsheet calculates are:

1.Max Profit per event

2.Threshold at maximum profit per Event

3.Condition incidence

4.Base Rate Cost per Event

5.Incremental value of the model (savings per event)