英语论文网

5. For continuous variables do same thing and then combine to get sensible coarse classifying for age
Why does pivot tables not work as well for this.
Can you construct the best monotone coarse classifier?

B: Constructing KS and ROC Curves

Suppose one has constructed a scorecard how does one then calculate the KS and ROC curves?
We will use the linear regression based scorecard we obtained in the previous workshop where

100Y= -1 +17X(18-33) -11X(33-48) -31X(49-54) +19X(55 -63) + 68X(0child) +63X(1child) +67X(2child) +30X(B,Y) +6X(R,H,X) –14X(Z, no response) +23X(P,E,L)

Go to sheet 2 where the data used in constructing this scorecard is. In row 104 type in the scoring values starting in column E. So type in 17 in E104, -11 in F104 , -31 in G104 and so in until one finishes with 23 in O104.
Go to T1 and type in “score”.
In T2 we calculate the score of that person by typing in =SUMPRODUCT(E2:O2,E$104:O$104)
This should give a score of 3.
Copy cell T2 down the column to T101, che
Copy column D, the good=1 column into column U by highlighting the top of column D ( the cell with a D in it , clicking copy and then going to the top of U ( the cell with U) in it and paste.

These, score and whether person was good are the two pieces of information needed to evaluate the ROC and KS curve. We will use another sheet to evaluate the curves on.
Highlight columns T and U. Press copy go to Sheet 4 and with cursor on the first column go to Paste Special in the Edit menu. Click the values box ( otherwise we only get the formulae which don’t make sense in this sheet) and then click on OK.
Highlighting the two columns A and B go to Sort in the Data menu and sort on score column with scores ascending.
In cell B102 ( at bottom of good column) use Summation sign to get =SUM(B2:B101), the number of goods, In c102 type =100-b102 and in d102 type =b102. We will use the C and D column to find how many bads ( C) and How many goods(D) have values less than that customer.
Type 0s in C1 and D1
In C2, type =IF(b2=0,C1+1,C1) which adds one if the b column is 0 ( a bad). Copy this down to C101.
In D2 type =IF(b2=1,d1+1,D1) which does the same thing for the goods.
To get these into percentages in E1 type %of bads, in F1 type % of goods
In E2 type =C2/C$102 and copy down the E column.
In F2 type =D2/D$102 and copy down the F column

To get KS value in g column type in G2 =E2-F2 and copy this down the whole column. The largest value ( 0.66) is the KS statistic

To get KS chart go to Chart Wizard
Choose line and in data range type in or highlight E1 to F101 ( E1:F101). Let this chart be in the current sheet and tidy up the colours and background fill. This is the KS chart

留学生论文https://www.51lunwen.org/StudentPapers.htmlTo get the ROC chart go to Chart Wizard againChoose scatter this time and choose the variant where the data points are connected by a line, The data range is E2:F101 and when it comes to labelling the axes label the x axis % bads and the Y axis % goods.

Exercise: Can you work out how to calculate the Gini coefficient form the numbers given in the spreadsheet?