Reconciling to the Trial Balance

Membership Analysis Default View

To be counted (and reported) by default in the Vital Signs Membership Analysis document, members shares and loans must meet the following criteria. These criteria are determined by macro definitions which can be customized for each client.

Member / Account Counts

To be counted, the Account status must be Open or Dormant and must have at least one open share or one open loan. The macro VS-ACTIVE-MBR is used to determine whether or not the member's account record has been closed. The following statuses are possible:

Account Status	Explanation
Open	Not closed, not dormant and has at least one open share or loan.
Dormant	Dormant (not closed) and has at least one open share or loan.
Closed	Closed and has a valid CLOSED-DATE.
Other	The account does not meet any of the above criteria.
Dropped	The account existed in a prior period but no longer exists in the current period.

The following macros are used to determine the account status:

 Macro VS-ACTIVE-MBR   = (MEMBER-FILE.REST-FLAG-11 <> 80) 
 Macro VSMBR-DORMANT   = (MEMBER-FILE.REST-FLAG-11 = 79)

Note: Member counts are similar to account counts, but instead of counting individual Spectrum accounts, the number of unique primary SSNs are reported.

Share/Deposit Counts and Balances

To be counted, the share must not be closed and must not be charged off. The macros VS-ACTIVE-SHARE and VS-CHGOFF-SHARE are used to determine if a share should be counted. If an open share is associated with a closed member record, the share will not be included in the charts. The following statuses are possible:

Share Status	Explanation
Open	Not closed and not charged off.
Charged Off	The share has been charged off.
Closed	Closed and has a valid CLOSED-DATE.
Other	The share does not meet any of the above criteria.
Dropped	The share existed in a prior period but no longer exists in the current period.

The following macros are used to determine the share status:

 Macro VS-ACTIVE-SHARE  = (SHARE-FILE.REST-FLAG-11 <> 80)
 Macro VS-CHGOFF-SHARE  = (SHARE-FILE.DESC-ABRV="COS")

Loan Counts and Balances

To be counted, the loan must not be closed, must not be charged off and must qualify as countable. The macros VS-ACTIVE-LOAN, VS-CHGOFF-LOAN and VS-OPEN-LOAN are used to determine if a loan should be counted. If an open loan is associated with a closed member record, the loan will not be included in the charts. The following statuses are possible:

Loan Status	Explanation
Open	Not closed, not charged off and countable (see VS-OPEN-LOAN).
Charged Off	The loan has been charged off.
Closed	Closed and has a valid CLOSED-DATE.
Other	The loan does not meet any of the above criteria.
Dropped	The loan existed in a prior period but no longer exists in the current period.

The following macros are used to determine the share status:

 Macro VS-ACTIVE-LOAN  = (LOAN-FILE.REST-FLAG-11 <> 80)
 Macro VS-CHGOFF-LOAN  = (LOAN-FILE.ACTION = "O")
 Macro VS-OPEN-LOAN    = ((LOAN-FILE.BALANCE > 0 OR LOAN-FILE.CREDIT-LIM > 0) AND LOAN-FILE.ACTION = "D","3")

Setting the Default View

When using the Membership Analysis document in Vital Signs it is important to be aware of the default view: which date is currently selected, and what Account, Deposit and Loan statuses are selected.

The Status Bar

Each page of the document shows the status bar which displays the currently selected members, accounts, deposits and shares and compares them to the overall possible total. The labels indicate which overall total is being displayed. In the default view, the total reflects Open/Dormant Accounts, Open Deposits and Open loans as shown below.

Locating Missing Deposits/Loans

As mentioned above, it is possible for some Open deposits and loans to be missing from the default view. This occurs when there are open deposits and loans that are associated with a account record that is not open. These deposits can be easily identified as follows:

1. In the Membership_Analysis document, click Unlock and Clear All.
2. Choose the Deposit Status: Open
3. Choose the Account Status: Closed and Other (Any possible status other than Open or Dormant)
4. The missing deposits should now be selected
5. Click View Details and choose the Deposit Details for selected Members chart

The same process will work for selecting any missing loans. Simply start be selecting a Loan Status of Open and view the Loan Details when done.

Trial Balance Report

The main purpose of the Trial Balance Reconciliation chart is to tie back the totals reported by Vital Signs to the Spectrum Trial Balance (PGNTB) Report. Like the Spectrum report, the Vital Signs chart is split into two sections: Balance by Account and Balance by Suffix. The grand total of both of these sections should tie back to the Spectrum Report (*). These total will not, however, match the Vital Signs totals displayed on the Status Bar. This is because the Spectrum Trial Balance report includes closed shares and loans with a non-zero balance, as well as closed zero-balance shares with the share-draft flag set. It will also report zero balance loans if they have a non-zero escrow balance.

To fully reconcile the differences between the Spectrum report and the Vital Signs Status Bar totals, you need to look at the Balance by Suffix section. This section is further broken down into four sub-sections:

1. Closed Suffixes: This section reports all closed suffixes that qualify for the Trial Balance.
2. Other Suffixes: This section reports any shares or loans that qualify for the Trial Balance but they are not open and they are also not closed. Examples are loans with a negative balance (zero credit limit), or improperly closed shares that still have the share-draft flag set.
3. Excluded Suffixes: Shares and loans in this section are shown with a negative count and negative balance. These are shares and loans that are not included in the Trial Balance, but are included in the Open count displayed on the status bar. Zero balance open shares will be reported here, as well as any description abbreviations or collateral codes that have been specifically excluded from the trial balance.
4. Open Suffixes: This total should match the Vital Signs status Bar, it includes all open shares and loans that are included in the Trial Balance as well as any open products excluded from the Trial Balance (the ones reported separately in 3. Excluded Suffixes.

The sum of these 4 sections: Closed + Other + (-Excluded) + Open should match the Spectrum Trial Balance and the chart Grand Total.

(*) In some cases the total balances will match, but the counts will be slightly different. The difference, if there is one, will be in the number of zero balance closed loans being reported.