Difference between revisions of "Membership analysis.xls"

From Vital Soft Wiki
Jump to: navigation, search
>Jeremyb
(→‎Ranges Sheet)
>Manager
 
(14 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
== Overview ==
 
== Overview ==
  
−
    The Membership Analysis spreadsheet contains Ranges, Dimensions, Fields and Accounts specific to Vital Signs during the nightly reloads     
+
The Membership Analysis spreadsheet contains Ranges, Dimensions, Fields and Accounts specific to Vital Signs during the nightly reloads     
  
  
Line 10: Line 10:
 
The assigned range is >= IM_LOW and < The next IM_LOW value specified
 
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
+
The IM_TIER Column is used for setting a hierarchy for the ranges used below'''
−
''Note: typically this is only used for Credit Tier'''''
+
 
 +
''Note: typically this is only used for Credit Tier''
 +
 
  
 
'''SHARE-BAL'''
 
'''SHARE-BAL'''
Line 18: Line 20:
  
 
     Example:
 
     Example:
−
 
+
    IM_KEY IM_LOW IM_TIER IM_DESC
 
     SHARE-BAL Null No Shares
 
     SHARE-BAL Null No Shares
 
     SHARE-BAL Negative
 
     SHARE-BAL Negative
Line 25: Line 27:
 
     SHARE-BAL 5.00 $5 to $499
 
     SHARE-BAL 5.00 $5 to $499
 
     SHARE-BAL 500.00 $500 - $999
 
     SHARE-BAL 500.00 $500 - $999
−
     SHARE-BAL 1,000.00 $1K - $4,999
+
     SHARE-BAL 1,000.00 $1K - $4,999
−
     SHARE-BAL 5,000.00 $5K - $9,999
+
     SHARE-BAL 5,000.00 $5K - $9,999
−
     SHARE-BAL 10,000.00 $10K - $49,999
+
     SHARE-BAL 10,000.00 $10K - $49,999
−
     SHARE-BAL 50,000.00 $50K - $99,999
+
     SHARE-BAL 50,000.00 $50K - $99,999
−
     SHARE-BAL 100,000.00 $100K and over
+
     SHARE-BAL 100,000.00 $100K and over
  
  
 
'''LOAN-BAL'''
 
'''LOAN-BAL'''
−
    Used to calculate Loan Balance Ranges
+
 
 +
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'''
 
'''TRIAL-BAL'''
−
    Used in the Trial Balance page and should not be modified
 
  
−
'''
+
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 ==
 
== 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 ==
 
== 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 ==
 
== 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

Latest revision as of 23:54, 14 September 2012

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
Retrieved from "https://wiki.vital-soft.com/index.php?title=Membership_analysis.xls&oldid=640"