Difference between revisions of "Reconciling to the Trial Balance"
>Johno (Created page with "=== 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 follow...") |
>Johno (→Member Counts) |
||
| (13 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | + | == 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. | 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 Counts == | + | === Member / Account Counts === |
| − | To be counted, the | + | 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: |
{| class="wikitable" | {| class="wikitable" | ||
| − | | ''' | + | | '''Account Status''' |
| '''Explanation''' | | '''Explanation''' | ||
|- | |- | ||
| Line 19: | Line 19: | ||
|- | |- | ||
| Other | | Other | ||
| − | | The | + | | The account does not meet any of the above criteria. |
|- | |- | ||
| Dropped | | Dropped | ||
| − | | The | + | | The account existed in a prior period but no longer exists in the current period. |
|} | |} | ||
| − | The following macros are used to determine the | + | The following macros are used to determine the account status: |
| − | Macro VS-ACTIVE-MBR | + | Macro VS-ACTIVE-MBR = (MEMBER-FILE.REST-FLAG-11 <> 80) |
| − | Macro VSMBR-DORMANT | + | Macro VSMBR-DORMANT = (MEMBER-FILE.REST-FLAG-11 = 79) |
| − | == Share/Deposit Counts and Balances == | + | 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: | 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: | ||
| Line 55: | Line 57: | ||
The following macros are used to determine the share status: | The following macros are used to determine the share status: | ||
| − | Macro VS-ACTIVE-SHARE | + | Macro VS-ACTIVE-SHARE = (SHARE-FILE.REST-FLAG-11 <> 80) |
| − | Macro VS-CHGOFF-SHARE | + | Macro VS-CHGOFF-SHARE = (SHARE-FILE.DESC-ABRV="COS") |
| − | == Loan Counts and Balances == | + | === 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: | 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: | ||
| Line 83: | Line 85: | ||
The following macros are used to determine the share status: | The following macros are used to determine the share status: | ||
| − | Macro VS-ACTIVE-LOAN | + | Macro VS-ACTIVE-LOAN = (LOAN-FILE.REST-FLAG-11 <> 80) |
| − | Macro VS-CHGOFF-LOAN | + | Macro VS-CHGOFF-LOAN = (LOAN-FILE.ACTION = "O") |
| − | Macro VS-OPEN-LOAN | + | 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. | ||
| + | |||
| + | [[File:Membership Analysis Default View.jpg | 600px]] | ||
| + | |||
| + | === 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. | ||
| + | |||
| + | [[File:Membership Analysis Status Bar.jpg | 800px]] | ||
| + | |||
| + | ==== 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: | ||
| + | |||
| + | #In the Membership_Analysis document, click '''Unlock and Clear All'''. | ||
| + | #Choose the Deposit Status: '''Open''' | ||
| + | #Choose the Account Status: '''Closed''' and '''Other''' (Any possible status other than Open or Dormant) | ||
| + | #The ''missing'' deposits should now be selected | ||
| + | #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: | ||
| + | #Closed Suffixes: This section reports all closed suffixes that qualify for the Trial Balance. | ||
| + | #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. | ||
| + | #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. | ||
| + | #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 | + | :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. | |
Latest revision as of 22:48, 17 March 2017
Contents
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.
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:
- In the Membership_Analysis document, click Unlock and Clear All.
- Choose the Deposit Status: Open
- Choose the Account Status: Closed and Other (Any possible status other than Open or Dormant)
- The missing deposits should now be selected
- 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:
- Closed Suffixes: This section reports all closed suffixes that qualify for the Trial Balance.
- 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.
- 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.
- 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.
