Membership analysis.xls
Overview
The Membership Analysis spreadsheet contains Ranges, Dimensions, Fields and Accounts specific to Vital Signs during the nightly reloads
Ranges Sheet
This sheet contains range information used by the Vital Signs Membership_Analysis Application
These ranges are reapplied each time the Membership_Analysis application is reloaded The assigned range is >= IM_LOW and < The next IM_LOW value specified
The IM_TIER Column is used for setting a hierarchy for the ranges used below
Note: typically this is only used for Credit Tier
SHARE-BAL
Used to calculate Share Balance Ranges
Example: IM_KEY IM_LOW IM_TIER IM_DESC SHARE-BAL Null No Shares SHARE-BAL Negative SHARE-BAL 0.00 Zero SHARE-BAL 0.01 $0.01 to $4 SHARE-BAL 5.00 $5 to $499 SHARE-BAL 500.00 $500 - $999 SHARE-BAL 1,000.00 $1K - $4,999 SHARE-BAL 5,000.00 $5K - $9,999 SHARE-BAL 10,000.00 $10K - $49,999 SHARE-BAL 50,000.00 $50K - $99,999 SHARE-BAL 100,000.00 $100K and over
LOAN-BAL
Used to calculate Loan Balance Ranges
Example: IM_KEY IM_LOW IM_TIER IM_DESC LOAN-BAL Null No Loans LOAN-BAL Negative LOAN-BAL 0.00 Zero LOAN-BAL 0.01 $0.01 to $999 LOAN-BAL 1,000.00 $1K to $4,999 LOAN-BAL 5,000.00 $5K to $9,999 LOAN-BAL 10,000.00 $10,000 - $49,999 LOAN-BAL 50,000.00 $50,000 - $99,999 LOAN-BAL 100,000.00 $100K and over
TRIAL-BAL
Used in the Trial Balance page and should not be modified
Example: IM_KEY IM_LOW IM_TIER IM_DESC TRIAL-BAL Negative TRIAL-BAL 0.00 Zero TRIAL-BAL 0.01 $0.01 - $10.00 TRIAL-BAL 10.01 $10.01 - $25.00 TRIAL-BAL 25.01 $25.01 - $100.00 TRIAL-BAL 100.01 $100.01 - $500.00 TRIAL-BAL 500.01 $500.01 - $1,000 TRIAL-BAL 1,000.01 $1,000.01 - $2,000 TRIAL-BAL 2,000.01 $2,000.01 - $3,000 TRIAL-BAL 3,000.01 $3,000.01 - $5,000 TRIAL-BAL 5,000.01 $5,000.01 - $10,000 TRIAL-BAL 10,000.01 $10,000.01 - $20,000 TRIAL-BAL 20,000.01 $20,000.01 - $40,000 TRIAL-BAL 40,000.01 $40,000.01 - $100,000 TRIAL-BAL 100,000.01 $100,000.01 and Over
MBR-AGE
Used to calculate Member Age Ranges
Example: IM_KEY IM_LOW IM_TIER IM_DESC MBR-AGE Zero (No age) MBR-AGE 1 Under 16 MBR-AGE 16 16 to 20 MBR-AGE 21 21 to 25 MBR-AGE 26 26 to 30 MBR-AGE 31 31 to 35 MBR-AGE 36 36 to 40 MBR-AGE 41 41 to 45 MBR-AGE 46 46 to 50 MBR-AGE 51 51 to 55 MBR-AGE 56 56 to 60 MBR-AGE 61 61 to 65 MBR-AGE 66 66 to 70 MBR-AGE 71 71 to 75 MBR-AGE 76 76 and over
ACCT-AGE
Used to calculate Account Age Ranges - how long the member has been a member
Example: IM_KEY IM_LOW IM_TIER IM_DESC ACCT-AGE Less than 1 Year ACCT-AGE 1 1 – 2 Years ACCT-AGE 3 3 – 5 Years ACCT-AGE 6 6 – 10 Years ACCT-AGE 11 11 – 20 Years ACCT-AGE 21 21 Years +
CREDIT-SCORE
Used to calculate Credit Score Ranges
Example: IM_KEY IM_LOW IM_TIER IM_DESC CREDIT_SCORE F F (No Score) CREDIT_SCORE 1 E E (Less than 520) CREDIT_SCORE 520 D D (520 - 580) CREDIT_SCORE 581 C C (581 - 620) CREDIT_SCORE 621 B B (621 - 700) CREDIT_SCORE 701 A A (701 - 720) CREDIT_SCORE 721 A+ A+ (Over 721)
DELQ-DAYS
Used when displaying Delinquent Loans
Example: IM_KEY IM_LOW IM_TIER IM_DESC DELQ-DAYS Current DELQ-DAYS 1 1-15 Days DELQ-DAYS 16 16-30 Days DELQ-DAYS 31 31-60 Days DELQ-DAYS 61 61-90 Days DELQ-DAYS 91 91-120 Days DELQ-DAYS 121 121-180 Days DELQ-DAYS 181 6 Mths - 1 Year DELQ-DAYS 366 Over 1 Year
CHANGE-BAL
Used to calculate balance Changes for both Share and Loan Ranges
Example: IM_KEY IM_LOW IM_TIER IM_DESC CHANGE-BAL Neg: Over $100K CHANGE-BAL -99,999.99 Neg: $50K to $100K CHANGE-BAL -49,999.99 Neg: $10K to $50K CHANGE-BAL -9,999.99 Neg: $5K to $10K CHANGE-BAL -4,999.99 Neg: $1K to $5K CHANGE-BAL -999.99 Neg: .01 to $1K CHANGE-BAL 0.00 Zero CHANGE-BAL 0.01 .01 to $1k CHANGE-BAL 1,000.00 $1K to $5K CHANGE-BAL 5,000.00 $5K to $10K CHANGE-BAL 10,000.00 $10,000 - $50K CHANGE-BAL 50,000.00 $50,000 - $100K CHANGE-BAL 100,000.00 $100K and over
RATES
Used to calculate rate ranges for both shares (DIV_RATE) and loans (APR)
Example: IM_KEY IM_LOW IM_TIER IM_DESC RATES Negative RATES 0.00% Zero RATES 0.00% <= 0.50% RATES 0.50% >= 0.50% < 1.00% RATES 1.00% >= 1.00% < 1.50% RATES 1.50% >= 1.50% < 2.00% RATES 2.00% >= 2.00% < 2.50% RATES 2.50% >= 2.50% < 3.00% RATES 3.00% >= 3.00% < 3.50% RATES 3.50% >= 3.50% < 3.75% RATES 3.75% >= 3.75% < 4.00%
DTI-RATIO
Used for the Debit To Income Ratio
Example: IM_KEY IM_LOW IM_TIER IM_DESC DTI-RATIO Negative DTI-RATIO 0.00% Zero DTI-RATIO 0.01% <= 25% DTI-RATIO 25.01% 25.01% - 40% DTI-RATIO 40.01% 40.01% - 46% DTI-RATIO 46.01% Over 46%
LTV
Used for Loan-to-Value
Example: IM_KEY IM_LOW IM_TIER IM_DESC LTV Negative LTV 0.00% Zero LTV 0.01% <= 80% LTV 80.01% 80.01% - 90% LTV 90.01% 90.01% - 100% LTV 100.01% Over 100%
MAT-DAYS
Used for the Maturity Days
Example: IM_KEY IM_LOW IM_TIER IM_DESC MAT-DAYS 1-30 Days MAT-DAYS 31 31-60 Days MAT-DAYS 61 61-90 Days MAT-DAYS 91 91-120 Days MAT-DAYS 121 121-180 Days MAT-DAYS 181 6 Mths - 1 Year MAT-DAYS 366 1 – 2 Years MAT-DAYS 731 2 – 3 Years MAT-DAYS 1,096 3 – 4 Years MAT-DAYS 1,461 4 – 5 Years MAT-DAYS 1,826 Over 5 Years
LATE-PMTS
Used for counting Late Payments
Example: IM_KEY IM_LOW IM_TIER IM_DESC LATE-PMTS None Late LATE-PMTS 1 One Late LATE-PMTS 2 2 – 3 Late LATE-PMTS 4 4 – 5 Late LATE-PMTS 6 6 – 10 Late LATE-PMTS 11 > 10 Late
Dimensions Sheet
This sheet can be used to create custom dimensions for output by tables and charts in Vital Signs
The columns in this sheet are DIMNAME, GROUPNAME, GROUPLABEL and GROUPSORTBY
DIMNAME
The name of the Dimension - "MBR, SH or LN"
GROUPNAME
The name of the field in Vital Signs
GROUPLABEL
The description of the dimension that appears in Vital Signs in the drop down list
GROUPSORTBY
The custom sort for this dimension
Default Groups
DIMNAME GROUPNAME GROUPLABEL GROUPSORTBY
MBR MBR_BRANCH_NAME Mbr Curr Branch =MBR_BRANCH_SORT
MBR MBR_ORIG_BRANCH_NAME Mbr Orig Branch =MBR_ORIG_BRANCH_SORT
MBR MBR_ORIG_OPEN_MTH Orig Open Mth Ascii
MBR MBR_ESCHEAT_MTH Mbr Escheat Date Ascii
MBR MBR_AGE_RANGE Mbr Age Range =MBR_AGE_RANGE_SORT
MBR MBR_ACCT_AGE_RANGE Acct Age Range =MBR_ACCT_AGE_RANGE_SORT
MBR MBR_RELATE_FLAG Mbr Relate Flag Ascii
MBR MBR_TOT_SHBAL_RANGE Mbr Total Dep Balance =MBR_TOT_SHBAL_RANGE_SORT
MBR MBR_TOT_LNBAL_RANGE Mbr Total Loan Balance =MBR_TOT_LNBAL_RANGE_SORT
MBR MBR_SH_BAL_CHANGE_RANGE Mbr Dep Bal Change =MBR_SH_BAL_CHANGE_RANGE_SORT
MBR MBR_LN_BAL_CHANGE_RANGE Mbr Loan Bal Change =MBR_LN_BAL_CHANGE_RANGE_SORT
MBR MBR_CITY Mbr City Ascii
MBR MBR_ZIP Mbr Zipcode Numeric
MBR MBR_DEPARTMENT Mbr Department Ascii
MBR MBR_SELLER_NAME Mbr Seller Name Ascii
DIMNAME GROUPNAME GROUPLABEL GROUPSORTBY
SH MBR_AGE_RANGE Mbr Age Range =MBR_AGE_RANGE_SORT
SH MBR_ACCT_AGE_RANGE Acct Age Range =MBR_ACCT_AGE_RANGE_SORT
SH MBR_RELATE_FLAG Mbr Relate Flag Ascii
SH SH_BRANCH_NAME Share Branch =SH_BRANCH_SORT
SH SH_ORIG_BRANCH_NAME Share Orig Branch =SH_ORIG_BRANCH_SORT
SH SH_BAL_RANGE Share Balance =SH_BAL_RANGE_SORT
SH SH_BAL_CHANGE_RANGE Share Bal Change =SH_BAL_CHANGE_RANGE_SORT
SH SH_OPEN_MTH Open Date Ascii
SH SH_DAYS_NEG_RANGE Neg Days =SH_DAYS_NEG_RANGE_SORT
SH SH_CERT_MAT_DATE_MTH Cert Maturity Date Ascii
SH SH_DESC_ABRV_LONGDESC Desc Abrv Ascii
SH SH_DIV_RATE_RANGE Div Rate =SH_DIV_RATE_RANGE_SORT
SH SH_SELLER_NAME Share Seller Ascii
SH SH_RECORDER_NAME Share Recorder Ascii
SH =SH_TYPE_PROD Share Type Numeric # Use an equals for expressions or groups.
DIMNAME GROUPNAME GROUPLABEL GROUPSORTBY
LN MBR_AGE_RANGE Mbr Age Range =MBR_AGE_RANGE_SORT
LN MBR_ACCT_AGE_RANGE Acct Age Range =MBR_ACCT_AGE_RANGE_SORT
LN MBR_RELATE_FLAG Mbr Relate Flag Ascii
LN LN_BRANCH_NAME Loan Branch =LN_BRANCH_SORT
LN LN_ORIG_BRANCH_NAME Loan Orig Branch =LN_ORIG_BRANCH_SORT
LN LN_BAL_RANGE Loan Balance =LN_BAL_RANGE_SORT
LN LN_BAL_CHANGE_RANGE Loan Bal Change =LN_BAL_CHANGE_RANGE_SORT
LN LN_COLLATERAL_DESC Collateral Ascii
LN LN_ENTRY_MTH Loan Open Date Ascii
LN LN_APR_RANGE Loan APR =LN_APR_RANGE_SORT
LN LN_SELLER_NAME Loan Seller Ascii
LN LN_OFFICER_NAME Loan Officer Ascii
LN LN_VI_DEALER Dealer Name Ascii
LN LN_CREDIT_TIER_DESC Credit Tier Ascii
LN =LN_TYPE_PROD Loan Type Numeric # Use an equals for expressions or groups.
Fields Sheet
This sheet defines which fields are loaded into the Vital Signs applications
Table Column
The internal table name in Vital Signs
FieldName Column
The internal field name in Vital Signs
InitialLoad Column
Yes/No flag for loading the field into the Summary and Detail Membership applications
SummaryLoad
Yes/No flag for loading the field into the Summary (Monthly_Membership_Summary, Weekly_Membership_Summary and Daily_Membership_Summary) applications
DetailLoad
Yes/No flag for loading the field into the Detail (Membership_Analysis_by_ACCOUNT) application
DataChng
Yes/No flag for loading the field into the Data Change (Spectrum_Data_Change) application
LoanApp
Yes/No flag for loading the field into the Loan App (Loan_App) application
Accounts Sheet
This sheet allows for listing Member Accounts that should be dropped from Vital Signs
MBR_ACCOUNT
The member account number
EXCLUDE_ACCOUNT
Yes/No flag to exclude the MBR_ACCOUNT
