Difference between revisions of "Membership analysis.xls"

From Vital Soft Wiki
Jump to: navigation, search
>Jeremyb
(→‎Ranges Sheet)
>Jeremyb
(→‎Ranges Sheet)
Line 24: Line 24:
 
     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
  
  
Line 40: Line 40:
 
     LOAN-BAL 0.00 Zero
 
     LOAN-BAL 0.00 Zero
 
     LOAN-BAL 0.01 $0.01 to $999
 
     LOAN-BAL 0.01 $0.01 to $999
−
     LOAN-BAL 1,000.00 $1K to $4,999
+
     LOAN-BAL 1,000.00 $1K to $4,999
−
     LOAN-BAL 5,000.00 $5K to $9,999
+
     LOAN-BAL 5,000.00 $5K to $9,999
−
     LOAN-BAL 10,000.00 $10,000 - $49,999
+
     LOAN-BAL 10,000.00 $10,000 - $49,999
−
     LOAN-BAL 50,000.00 $50,000 - $99,999
+
     LOAN-BAL 50,000.00 $50,000 - $99,999
−
     LOAN-BAL 100,000.00 $100K and over
+
     LOAN-BAL 100,000.00 $100K and over
  
  
Line 59: Line 59:
 
     TRIAL-BAL 100.01 $100.01 - $500.00
 
     TRIAL-BAL 100.01 $100.01 - $500.00
 
     TRIAL-BAL 500.01 $500.01 - $1,000
 
     TRIAL-BAL 500.01 $500.01 - $1,000
−
     TRIAL-BAL 1,000.01 $1,000.01 - $2,000
+
     TRIAL-BAL 1,000.01 $1,000.01 - $2,000
−
     TRIAL-BAL 2,000.01 $2,000.01 - $3,000
+
     TRIAL-BAL 2,000.01 $2,000.01 - $3,000
−
     TRIAL-BAL 3,000.01 $3,000.01 - $5,000
+
     TRIAL-BAL 3,000.01 $3,000.01 - $5,000
−
     TRIAL-BAL 5,000.01 $5,000.01 - $10,000
+
     TRIAL-BAL 5,000.01 $5,000.01 - $10,000
−
     TRIAL-BAL 10,000.01 $10,000.01 - $20,000
+
     TRIAL-BAL 10,000.01 $10,000.01 - $20,000
−
     TRIAL-BAL 20,000.01 $20,000.01 - $40,000
+
     TRIAL-BAL 20,000.01 $20,000.01 - $40,000
−
     TRIAL-BAL 40,000.01 $40,000.01 - $100,000
+
     TRIAL-BAL 40,000.01 $40,000.01 - $100,000
−
     TRIAL-BAL 100,000.01 $100,000.01 and Over
+
     TRIAL-BAL 100,000.01 $100,000.01 and Over
  
  
Line 102: Line 102:
 
     ACCT-AGE 11 11 – 20 Years
 
     ACCT-AGE 11 11 – 20 Years
 
     ACCT-AGE 21 21 Years +
 
     ACCT-AGE 21 21 Years +
 +
 +
 +
'''CREDIT-SCORE'''
 +
 +
Used to calculate Credit Score Ranges
 +
 +
    Example:
 +
    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:
 +
    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:
 +
    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:
 +
    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:
 +
    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:
 +
    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:
 +
    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:
 +
    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 ==

Revision as of 19:21, 4 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:
   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:
   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:
   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:
   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:
   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:
   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:
   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:
   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:
   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:
   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:
   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:
   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:
   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

Fields Sheet

Accounts Sheet

Retrieved from "https://wiki.vital-soft.com/index.php?title=Membership_analysis.xls&oldid=629"