英语论文网

联系方式

using Excel:Credit Scoring and Data Mining

Credit Scoring and Data Mining
Computer Lab Workshop 2 using Excel
Ahttps://www.51lunwen.org/StudentPapers.htmlUsing Pivot Tables in Excel to help coarse classifying variables

We will use the data on 100 customers that is found on cc100.xls.
Open that Excel file and note what the data means.
Most of the data describes the customer attributes but some describe the subsequent history.
DL1 is number of times in year 1 month overdue, DL2 is number of times 2 months overdue etc. Definition of bad=1 is at least once 1 month overdue (i,e. 1 or more in DL1).

We use Pivot Tables in Excel to display analysis. For real scorecard building one would use the Chi module is SPSS, or the Enterprise Miner module in SAS ( though that chooses classes automatically so some just use the standard SAS module) or dedicated credit scoring systems like Paragon’s DSS..

1. To use the pivot tables proceed as follows.
Highlight the whole table from a1: w101.
Go to Pivot Table and Pivot Chart Report in Data
When the first screen of the Wizard appears you want to create to click on data is in Microsoft Excel list and create Pivot table
Then go to next screen and make sure you are using data in A1:W101.
Go to next screen and say you want to go to existing worksheet and chose to start table at A120.
Then scroll down to A120.
Drag res ( residential status ) to pivot column
Drag good=1 and bad=1 to data section.

You should get that res status O ( owner) has 37 goods and 20 bads, residential status P ( with parents) has 10 goods and 7 bads; residential status T( furnished tenants) has 7 goods and 4 bads.
To get ratio of goods to bads in cell b124 type =b122/b123. Copy this across to columns C,D, E and F.

Which outcomes of residential status would you combine? My suggestion would be to have classes of {O}, {P,T,X} and {U}. Do you agree?

2.The pivot table for “children” has already been done just above. What classes would you combine of these?
Similarly spouses employment category has a table above. What classes would you form there? Look at the definitions of the attributes as well as the good:bad ratio.

3. You might want to look at combinations of variables. So create a two dimensional pivot table to look at the interaction between residential status and having a cheque guarantee card.
Again highlight the whole table from a1: w101.
Go to Pivot Table and Pivot Chart Report in Data
When the first screen of the Wizard appears you want to create to click on data is in Microsoft Excel list and create Pivot table
Then go to next screen and make sure you are using data in A1:W101.
Go to next screen and say you want to go to existing worksheet and chose to start table at A130.
Then scroll down to A130.
Drag res ( residential status ) to pivot row and drag cheque to pivot column
Drag good=1 and bad=1 to data section.

You should get that
Of owners with cheque guarantee card 28 are good and 16 are bad
Of owners with no cheque guarantee card 9 are good and 4 are bad;
Of with parents with cheque guarantee card 4 are good and 6 are bad
Of with parents with no cheque guarantee card 6 are good and 1 is bad

Which groups would you put together to form classes now.
I would be tempted to make it { owners}, { P or T or X with no cheque guarantee card}, {P or T with card and U with no card} and { U or X with card}. What groups would you choose.

4. For binary vari论文英语论文网提供整理，提供论文代写英语论文代写代写论文代写英语论文代写留学生论文代写英文论文留学生论文代写相关核心关键词搜索。