Summary
Learn about the data files and fields required to run a Not-for-profit general ledger analysis with fund within MindBridge.
- General ledger (or a Journal Report if the data is being extracted from Quickbooks Desktop or Quickbooks Online)
- Opening balance
- Closing balance
- Chart of accounts
- MindBridge Account Classification (MAC) code system
- Interim analysis (i.e., a partial general ledger)
MindBridge provides powerful analyses with minimal required data. However, by importing additional data fields, you can access additional features and filtering capabilities (such as searching by company, branch, transaction type, cost center, department, etc.) that allow you to analyze the results more efficiently and effectively.
General ledger data (mandatory)
A general ledger is a record that contains all the journal entries (e.g., transaction data) for a company to create their financial statements. This is the main file required for any general ledger analysis.
The general ledger file should be exported from an ERP or accounting system in either .xlsx or .csv format.
Note that when working with Quickbooks Desktop or Quickbooks Online it is recommended that you export a Journal Report instead of a General Ledger Report.
|
# |
Field name |
Description |
Mandatory |
Control points in effect |
|---|---|---|---|---|
|
1 |
Account ID |
The unique identifier for the given financial account. This information is also found in the chart of accounts. Example: 14028 |
Yes |
|
|
2 |
Account Description |
The name of the given financial account. This information is also found in the chart of accounts. Example: Cash and Cash equivalents |
No |
N/A |
|
3 |
Effective Date |
The date when the journal entry takes effect in the ledger, regardless of when the transaction or entry was posted. This date may also be known as the “accounting date.” Example: 2014-01-01 (YYYY-MM-DD) |
Yes
Note: Out of period entries: Any entries with an effective date outside of your currently selected period will not be included in the analysis. This can cause unbalanced debits and credits, along with completeness issues. Please check you have selected the correct effective date if you believe these entries should be included.
|
|
|
4 |
Debit |
The debit value of the entry. Example: 19,834.22 |
Yes |
|
|
5 |
Credit |
The credit value of the entry. Example: 19,834.22 |
Yes |
|
|
6 |
Amount |
The entry’s value, which may be either positive (i.e., debit) or negative (i.e., credit). Example: 19,834.22 (debit) -19,834.22 (credit) Note: “Amount” is an alternative field for general ledgers that don't split entries into separate debit and credit fields. In MindBridge, it is the equivalent to having "Debit" and "Credit" fields. |
No |
|
|
7 |
Entered Date |
The date the entry or transaction was posted to the ledger, regardless of when it takes effect in the accounting cycle. Example: 2014-01-01 (YYYY-MM-DD) |
No |
|
|
8 |
Transaction ID |
The unique code used to group entries within a given transaction. If none is given, the user will have to chose one or more fields to use for transaction grouping. Example: 6524 |
No |
All transaction-level control points |
|
9 |
Type |
Contains a code denoting what type of transaction the line item falls under. This column can be filtered with the filter builder. Example: CREDIT_PAYMENT |
No |
|
|
10 |
Memo |
A description of the journal entry. It is typically entered manually and provides a description of what occurred. Example: This was paid on Dec 7 from Account XYZ. |
No |
|
|
11 |
Source |
The source of the journal entry. Example: General Ledger (GL) Payroll (PR) Note: An alternative field can specified for the affected control points. |
No |
|
|
12 |
User ID |
The name or code of the user responsible for creating the journal entry. Example: Paul Johnson or 10922 |
No |
N/A |
|
13 |
MAC Code |
The grouping code set up within the Account Grouping template. Contains the MindBridge MAC code for the specified Account ID.
Note: The list of control points are the ones that specifically rely on MAC Codes, however each "Account" provided above will be mapped to a MAC Code. For example, "Duplicates" are identified by matching the "Account" column, even if those accounts have a MAC Code assigned. However only MAC Codes are considered when analyzing "Change in Account Trend". |
No |
|
|
14 |
Fund ID |
Contains an identifier for the fund associated with each line item. This column can be filtered with the filter builder. Example: 0003 |
No |
|
|
15 |
Fund Description |
Contains the name or description for the fund associated with each line item. This column can be filtered with the filter builder. Example: Kids Rock! Summer School Programs |
No |
N/A |
Opening balance data (optional)
This document lists the opening balance for all the general ledger accounts. The opening balance file should include the prior year's post-closing entries (e.g., top-side journal entries, revenue and expense close-out entries). It is best practice to ensure the balances agree to the prior year financial statements.
Importing an opening balance file allows MindBridge to produce a Balance Sheet report.
|
# |
Field name |
Description |
Mandatory |
Control points in effect |
|---|---|---|---|---|
|
1 |
Account ID |
The unique identifier for the given financial account. This information is also found in the chart of accounts. Example: 140002 |
Yes |
N/A |
|
2 |
Account Description |
The name of the given financial account. This information is also found in the chart of accounts. Example: Cash and Cash equivalents |
No |
N/A |
|
3 |
Balance |
The most up-to-date balance for each of the accounts (refer to the note below the “closing balance” table for more information). Example: 18,152.16 |
Yes |
N/A |
|
4 |
Debit |
An alternative field to "Balance" where separate "Debit" and "Credit" fields have to be specified. Example: -18,152.16 |
No |
N/A |
|
5 |
Credit |
An alternative field to "Balance" where separate "Debit" and "Credit" fields have to be specified. Example: 18,152.16 |
No |
N/A |
|
6 |
Fund Description |
Contains the name or description for the fund associated with each line item. This column can be filtered with the filter builder. Example: Kids Rock! Summer School Programs |
No |
N/A |
|
7 |
Fund ID |
Contains an identifier for the fund associated with each line item. This column can be filtered with the filter builder. Example: 0003 |
No |
N/A |
|
8 |
MAC Code |
The grouping code set up within the Account Grouping template. Contains the MindBridge MAC code for the specified Account ID. Example: 1104 |
No |
N/A |
Closing balance data (optional)
This document lists the account identifiers, account descriptions and closing cash balance for each account. The closing balance should reflect the most updated amount in each of the accounts.
Importing an opening and closing balance file allows MindBridge to produce a Pre-Analysis Completeness report and Completeness report. The closing balance is only used in this report and does not take the place of any calculated balances in MindBridge.
|
# |
Field name |
Description |
Mandatory |
Control points in effect |
|---|---|---|---|---|
|
1 |
Account Description |
Contains the names or descriptions of your client’s accounts. Example: Legal fees |
No |
N/A |
|
2 |
Account ID |
Contains codes used to identify your client’s accounts. Example: 140002 |
Yes |
N/A |
|
3 |
Balance |
Contains the balance for each of your client’s accounts as at the end of the period under audit. Example: $18152.16 |
Yes |
N/A |
|
4 |
Debit |
An alternative field to "Balance" where separate "Debit" and "Credit" fields have to be specified. Example: -18,152.16 |
No |
N/A |
|
5 |
Credit |
An alternative field to "Balance" where separate "Debit" and "Credit" fields have to be specified. Example: 18,152.16 |
No |
N/A |
|
6 |
Fund Description |
Contains the name or description for the fund associated with each line item. This column can be filtered with the filter builder. Example: Kids Rock! Summer School Programs |
No |
N/A |
|
7 |
Fund ID |
Contains an identifier for the fund associated with each line item. This column can be filtered with the filter builder. Example: 0003 |
No |
N/A |
|
8 |
MAC Code |
The grouping code set up within the Account Grouping template. Contains the MindBridge MAC code for the specified Account ID. Example: 1104 |
No |
N/A |
In case the balance is adjusted, the general ledger data should be consistent with the adjustments, and also include the adjustment entries. For this reason, the closing balance should be created at the same time as the general ledger data to prevent any differences in transactions as well as in balances.
These formulas demonstrate the concept and the relationship between the closing balances and the general ledger.
- Closing balance = Opening balance + General ledger data activity
- Closing adjusted balance = Opening balance + General ledger data activity + Adjusting entry activity
Chart of accounts data
This document is an index of the financial accounts present in the company’s general ledger.
|
# |
Field name |
Description |
Mandatory |
Control points in effect |
|---|---|---|---|---|
|
1 |
Account ID |
The unique identifier for the given financial account. This information is also found in the chart of accounts. Example: 140002 |
Yes |
|
|
2 |
Account Description |
The name of the given financial account. This information is also found in the chart of accounts. Example: Cash and Cash equivalents |
Yes |
N/A |
|
3 |
Fund Description |
Contains the name or description for the fund associated with each line item. This column can be filtered with the filter builder. Example: Kids Rock! Summer School Programs |
No |
N/A |
|
4 |
Fund ID |
Contains an identifier for the fund associated with each line item. This column can be filtered with the filter builder. Example: 0003 |
Yes |
|
|
5 |
MAC Code |
The grouping code set up within the Account Grouping template. Contains the MindBridge MAC code for the specified Account ID.
Note: The list of control points are the ones that specifically rely on MAC Codes, however each "Account" provided above will be mapped to a MAC Code. For example, "Duplicates" are identified by matching the "Account" column, even if those accounts have a MAC Code assigned. However only MAC Codes are considered when analyzing "Change in Account Trend". |
No |
|
|
6 |
Account Description |
Contains the names or descriptions of your client’s accounts. Example: Legal fees |
Yes |
N/A |
MindBridge Account Classification (MAC) code system
In order to classify your client’s unique accounts, MindBridge uses the MindBridge Account Classification (MAC) code system to map accounts across a wide and diverse range of industries into a single, consistent classification system.
Find out how MindBridge classifies accounts using MAC codes.
This column can be filtered using advanced filtering.
Interim time frame analysis data
Analyses that use the interim time frame use partial general ledger data (for example, the analysis may comprise 9 months of general ledger activity, instead of 12 months). The same concept of a general ledger applies to interim analyses, the main difference being that the period does not include data for the full fiscal period. Once the interim analysis has been completed, you can convert it to a full analysis once the complete general ledger has been received.
For the data fields related to the interim analysis data, refer to the general ledger data section above.
Anything else on your mind? Chat with us or submit a request for further assistance.