The Data Load Template is used to provide us with your initial data to get your new Balance database set up. General instructions for column entry (e.g. suggested purpose, text limit, required decimal places etc.) can be accessed by hovering over the header of each column to view comments. Any tab or column that is highlighted in yellow is required to set up your database.
TABLE OF CONTENTS
- Initial Funds Setup Tab (Required)
- Investment Pool History Tab (Required)
- Endowment Fund History Tab
- Spending Rule Exceptions Tab
- Manager Profile Tab
- Manager History Tab
- Expendable Fund Tab
- Expendable Fund History Tab
Initial Funds Setup Tab (Required)
This is where all your individual fund information will come from. Columns A through E (highlighted in yellow) are required. All other optional fields can be populated and/or changed later via your database in the event it is determined something is needed later, or after the implementation process.
Helpful hints:
- Each fund entry must have a unique GLID number (does not have to be a general ledger number but must be a unique account number, as two funds cannot have duplicate GLID numbers in Balance).
- Investment Pool - name given to the investment pool fund is participating in. Must match pools listed on "Investment Pools" tab.
- Net Asset Classification - We are looking at whether the fund is Perm Rest, Temp Rest, Unrest, Board Designated. Each fund must have one Net Asset Classification
- Spending Rule - We are looking up for the Spending Rule Name. We will build out the rule once in the database.
Investment Pool History Tab (Required)
Provide the name, Post Date, total market value and Unit Price for each Investment Pool. The investment pool names in this tab should match identically to the investment pool names given in the Initial Funds Setup tab. You can have more than one pool, as long as funds are not co-mingled within separate investments. Read this article for more information on the relationship between funds, managers, pools, and endowments.
Your Balance Post Date, also known as the Load Date or Start Date, should be the "as of" date that you are loading your data into Balance. We recommend this date being the date of your most recent audited financial statements. The reason for this is that it's important to load data that is approved and finalized.
Investment Pool Market Value should equal the your investment statement
If you are not currently unitized, we will populate your starting unit price as $100.
Important to note: The Total Pool Market Value on this tab should tie to the sum of your investment statements. The individual market values of the funds within each pool (listed on the Endowment Fund History tab) should tie out to your audited financial statements. If there is a difference between the two (i.e. cash-in-transit), a Due To Due From can be set up to account for the cash.
This article will help to explain this concept.
Endowment Fund History Tab
This is where you will provide your individual fund Historical Gift and Market Value information will come from.
- The Post date is the same date used in the Investment Pool History Tab.
- GLID Number is the same used in the Initial Funds setup tab
- The sum of all Fund market values should tie to the total market value of your Investment pool .
The table below details the columns found on the funds tab:
*The ability to "group" the field by different categories for reporting purposes
| Column | Description | Data Limits | Grouping?* |
| GLID Number (Required) | Typically this is the principal account number in the general ledger, but it does not have to be. It must be a unique number as you cannot have two named funds with the same GLID numbers. This number can be alpha/numeric and we would suggest not using spaces. | Max length: 255 Data type: Any | No |
| Fund Name (Required) | The name of the fund as it will appear in Balance and on reports. | Max length: 200 Data type: Any | No |
| Investment Pool Name (Required) | Name given to the Investment Pool the fund is participating in. This name will appear in Balance. | Max length: 75 Data type: Any | Yes |
| Net Asset Classification (Required) | The corpus restriction of the fund. For example: Permanently Restricted, Temporarily Restricted, Unrestricted, or Board Designated. This is a "group by" field on standard reports. | Max length: 100 Data type: Any | Yes |
| Spending Rule | Use this field to differentiate between distribution methods. For example, use this column to mark the funds that require a “manual” distribution calculation. If all funds participate in the same spending rule then enter “standard spending rule.” Any funds that do not participate in spending, enter “none.” This is a “group by” field on standard reports. | Max length: 255 Data type: Any | Yes |
| Historical Gift (Required for PR funds) | Principal, Corpus, or total accumulated historical gifts as of the same date as of the start date in Balance. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Market Value (Required) | Full market value for each fund as of the start date in Balance (note this is the full market value, so includes gift value, and is usually greater than historical gift). Note: No negative values. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Units (Required if Balance is taking over existing unit balances)Balance | If Balance is taking over your existing units, provide the units for each fund as of the start date in Balance. If you are providing units, enter the unit price in the investment pools tab as of the start date in Balance | Max length: N/A Data type: Number (floating) | No |
| Book Value | Represents gifts, plus income, plus realized gains, minus distributions. Not used by all organizations. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Pledge Balance | Pledge balance for the fund as of the start date in Balance. Note that Balance does not include this balance for earnings or spending allocations, or for endowment footnote reporting. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Spending Bucket Balance | Pledge balance for the fund as of the start date in Balance. This field does not apply to all clients, check with your Onboarding Advisor if you are unsure what data to provide. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Non-unitized assets | Assets that belong to a specific fund but are not part of the investment pool. For example, real estate gifted to a specific fund. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Description | Use to provide a general description for the fund. | Max length: 250 Data type: Any | No |
| Auxiliary I.D. | An additional account number such as a donor I.D. number. This number does not have to be unique and can be alpha/numeric. | Max length: 200 Data type: Any | No |
| Inception Date | Date fund was established (mm/dd/yyyy). | Max length: N/A Data type: Date (mm/dd/yyyy) | No |
| Campuses | Use this to designate different campuses. This is a “group by” field on standard reports and can be renamed. | Max length: 100 Data type: Any | Yes |
| Purpose/Use | Short description (250 character limit) of Purpose category of the fund. For example scholarship, professorship, or maintenance. This is a “group by” field on standard reports and can be renamed. | Max length: 250 Data type: Any | Yes |
| Secondary Category | Generic field. This is a “group by” field on standard reports and can be renamed. | Max length: 200 Data type: Any | Yes |
| Department | The designated department of the fund. This can also be used to identify organization codes. This is a “group by” field on standard reports and can be renamed. | Max length: 100 Data type: Any | Yes |
| User Category 2 | Generic field. This is a “group by” field on standard reports and can be renamed. | Max length: Unlimited Data type: Any | Yes |
| Purpose Description | This field is used for Stewardship reporting purposes and should define the purpose of the fund in detail. This information will be placed on a standard Donor Report. Unlimited text field. | Max length: Unlimited Data type: Any | No |
| Business Office Notes | Text field. This field can be renamed. | Max length: Unlimited Data type: Any | No |
| Secondary Description | Generic text field. This field can be renamed. | Max length: Unlimited Data type: Any | No |
| Fee Schedule | Use this field to differentiate between fee calculation methods. For example, if some funds are subject to a 1% of market value fee and others are subject to 0.5%, this field could be filled in with "1% Admin Fee" and "0.5% Admin Fee" respectively. | Max length: 255 Data type: Any | No |
| Distribution Reinvest Threshold - HG | This is used to limit spending from a fund until a fund’s historic gift or corpus reaches a certain value. For each fund, fill in only this field or the next field. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Distribution Reinvest Threshold - MV | This is used to limit spending from a fund until a fund’s market value reaches a certain value. For each fund, fill in only this field or the prior field. | Max length: N/A Data type: Dollar amount with 2 decimal places (amounts past 2 decimal places will be rounded) | No |
| Authorized Date | This is used to limit spending from a fund until a future date in time (mm/dd/yyyy). | Max length: N/A Data type: Date (mm/dd/yyyy) | No |
| Distribution Destination ID | The primary spending account number for the distributed spending. If you have more than one “spending account” number for a fund please use the “Spending Exceptions” tab. | Max length: 25500 Data type: Any | No |
| Distribution Fund | This is used for additional components of the spending account number's account coding for the distributed spending. If you have more than one “spending account” number for a fund please use the “Spending Exceptions” tab. | Max length: Unlimited Data type: Any | No |
| Distribution Project | This is used for additional components of the spending account number's account coding for the distributed spending. If you have more than one “spending account” number for a fund please use the “Spending Exceptions” tab. | Max length: Unlimited Data type: Any | No |
| Distribution Program | This is used for additional components of the spending account number's account coding for the distributed spending. If you have more than one “spending account” number for a fund please use the “Spending Exceptions” tab. | Max length: Unlimited Data type: Any | No |
| Distribution Dept | This is used for additional components of the spending account number's account coding for the distributed spending. If you have more than one “spending account” number for a fund please use the “Spending Exceptions” tab. | Max length: Unlimited Data type: Any | No |
| Income Acct (for GL Exports) | If the income account numbers used vary between different funds, this field can be used to specify the account code to be used for income. | Max length: 50 Data type: Any | No |
| Gain Acct (for GL Exports) | If the gains account numbers used vary between different funds, this field can be used to specify the account code to be used for gains. | Max length: 50 Data type: Any | No |
| Invest Acct (for GL Exports) | If the investment account numbers used vary between different funds, this field can be used to specify the account code to be used for investments. | Max length: 50 Data type: Any | No |
Spending Rule Exceptions Tab
These is where you will establish exceptions to your spending rule. They work in conjunction with your automated spending rule.
- For example: A donor stipulates that the fund is allowed to participate in spending but a percentage should be reinvested back to principal or go to another fund. The Distribution rule accommodates this type of exception via fund specific reinvestments (either a fixed amount or a percentage) and can be provided on the Distribution Rules tab
- This optional worksheet can be used to assign multiple spending (distributed income) GL account numbers to a fund (GLID number) and Expendable fund account numbers in Balance. Expendable fund accounts to a single principal account (GLID number) in Balance.
- Please note, if you have a one-to-one relationship between the principal account (GLID number) in Balance and the spending (distributed income) account you would not use this tab and instead would put that information in the main “Funds” tab (Columns AB-AF).
Manager Profile Tab
To use if you purchased the Investment Portfolio module. This module will give you the ability to track investment detail by Manager.
This is where you will provide each investment Manager for each pool. The Market Value of each of the investment managers must total the Market Value of the Pools on the Investment Pool History tab.
Note: If you need to account for a timing issue with cash flow, set up a Due to/from Manager for this specific purpose and include the due to/from value as of your Post Date.
The table below details the columns listed on the Investment Portfolio tab.
Column | Description |
Investment Pool Name (required | Name given to the Investment pool the Manager is participating in. This name should match the Investment Pool name on the Investment Pools tab and the Fund profile tab. |
Manager Name (required)
| Name of Investment Manager. |
MGLID (required) | This must be the unique identifier of the Manager in Balance. This can be a random number assigned (alpha/numeric) or it can be the GL Account Number for interfacing GL activity in/out of Balance |
Custodian Name | Name of custody account. |
Custody Account Number | The account number on the custody statement. |
| Due-to-due-from | |
Inactive | |
Investment Type | Select the appropriate Investment type from the drop-down for each manager. |
FASB Level | In the drop-down list select Level 1, 2, or 3 for the Manager. Per FASB 157, level 1 assets are the easiest to value accurately based on standard market-based prices and level 3 assets are the most difficult. |
Liquidity | In the drop-down, select the liquidity of each manager (per the ASU 2016-14 Standards). |
Asset Class | In the drop-down list select the Asset Class that would apply to the specific Manager. If you don’t see an option that applies write in your own or leave blank. |
Target Weight | Provide the Manager’s target weight in the Investment Pool. |
Initial Capital Commitment | Provide the initial capital commitment of the Manager. |
Capital Commitment Remaining | Provide the remaining capital commitment balance of the Manager as of the start date within Balance |
The Manager Profile information can be edited within the user interface after it’s been loaded.
Manager History Tab
Here is where you will provide the Ending Market Value for each manager as of your Post Date.
Notes: Please make sure that the total of the investment manager market values in column “K” is equal (to the penny) to the total market value of the Pools on the Investment Pool History tab.
Any changes to the Market values will need to be corrected in the template and sent to Balance for uploading.
Expendable Fund Profile Tab
The Expendable Funds Module was developed to help clients track the balances of spendable funds within Balance, that are not part of the Endowment Pool. Funds within this module will not receive any investment earnings or allocations. Funds in this module will not be unitized. These funds tracked in this module are for informational purposes.
Use this worksheet to list out your organization’s expendable funds.
Optional fields can be populated/changed later in the implementation process if determined to be needed during the review of the database.
The table below details the columns listed on the Funds tab.
Column | Description |
GLID | This must be a unique number for each fund (these cannot exactly match the GLIDs used for the Endowed funds; it must be unique among all funds). This number can be alpha/numeric; we advise against using spaces. This field is required. |
Name | The name of the fund as it will appear in Balance and on reports. This field is required. |
Net Asset Classification | This can be populated with any restriction classification (for example, Temporarily Restricted or With Donor Restriction) that applies to the fund; however, it cannot be an exact duplicate of the Net Asset Classifications used for the Endowed Funds. We recommend listing, for example, "Expendable Unrestricted" to differentiate. This field is required. |
Campuses | This is a "group by" field on standard reports and can be renamed. This field is required, but can be populated with "None". These can be the same as the Campuses used for the Endowed funds. |
Department | This is a "group by" field on standard reports and can be renamed. This field is required, but can be populated with "None". These can be the same as the Departments used for the Endowed funds. |
Purpose/Use | This is a "group by" field on standard reports and can be renamed. This field is required, but can be populated with "None". These can be the same as the Purpose/Use listings used for the Endowed funds. |
Secondary Category | This is a "group by" field on standard reports and can be renamed. These can be the same as the Secondary Categories used for the Endowed funds. |
User Category 2 | This is a "group by" field on standard reports and can be renamed. These can be the same as the User Category 2's used for the Endowed funds. |
Auxiliary ID | An additional account number such as a donor I.D. number. This number does not have to be unique and can be alpha/numeric. These can be the same as the Auxiliary IDs used for the Endowed funds. |
Inception Date | Date fund was established (mm/dd/yyyy). |
Description | Use to provide a general description for the fund. |
Purpose Description | This field is used for Stewardship reporting purposes and should define the purpose of the fund in detail. This information will be placed on a standard Donor Report. Unlimited text field. |
Secondary Description | Generic text field. This field can be renamed. |
Business Office Notes | Text field. This field can be renamed. |
Expendable Fund History Tab
Use this worksheet to provide beginning balances for your expendable funds.
To load beginning balances for a single fund you need 3 lines:
Comparison of Data Fields: Endowed vs. Expendable vs. Investment Managers
For organizations that have purchased the Expendable Funds Module and/or the Investment Portfolio Module, the below table is a useful comparison of fields that are found in each of these modules vs. the fields available for tracking endowments in Balance. Endowed and Expendable funds share the majority of their data fields; Investment Managers, however, shared very few fields, and have several unique fields.
| Data Field | Description | Endowed Funds? | Expendable Funds? | Investment Managers? |
|---|---|---|---|---|
| Investment Pool Name | Name given to the Investment pool which contains fund(s) and manager(s) | Yes | No | Yes |
| GLID/MGLID Number | Unique identifier of a specific fund (endowed or expendable), or specific manager | Yes | Yes | Yes |
| Name | The name of the fund or manager as it will appear in Balance and on reports. | Yes | Yes | Yes |
| Market Value | Full market value for each fund or manager as of the start date in Balance (note this is the full market value, so includes gift value, net earnings, etc., and is usually greater than historical gift) | Yes | No | Yes |
| Book Value | Represents gifts, plus income, plus realized gains, minus distributions. Also may be called "tax cost". Not used by all organizations. | Yes | No | Yes |
| Investment Type | Investment Type for each investment | No | No | Yes |
| Custody Account Number | Account number on the custody statement | No | No | Yes |
| Custodian Name | Name of custody account | No | No | Yes |
| FASB Level | FASB 157 valuation level of investment managers; Level 1, 2 or 3. Per FASB 157, level 1 assets are the easiest to value accurately based on standard market-based prices and level 3 are the most difficult. | No | No | Yes |
| Asset Class | Asset Class that applies to each manager. | No | No | Yes |
| Target Weight | Manager’s target weight in the Investment Pool | No | No | Yes |
| Initial Capital Commitment | Initial capital commitment of the Manager | No | No | Yes |
| Capital Commitment Remaining | Current capital commitment of the Manager | No | No | Yes |
| Liquidity | Liquidity of each manager (per the ASU 2016-14 guidance) | No | No | Yes |
| Net Asset Classification | The corpus restriction of the fund. For example: Permanently Restricted, Temporarily Restricted, Unrestricted or Board Designated. | Yes | Yes | No |
| Description | Use to provide a general description for the fund. | Yes | Yes | No |
| Auxiliary I.D. | An additional account number such as a donor I.D. number. This number does not have to be unique. | Yes | Yes | No |
| Inception Date | Date fund was established (mm/dd/yyyy). | Yes | Yes | No |
| Campuses | Use this to designate different campuses. | Yes | Yes | No |
| Purpose/Use | Short description (250 character limit) of Purpose category of the fund. For example: scholarship, professorship or maintenance. | Yes | Yes | No |
| Secondary Category | Additional grouping field; use varies by organization | Yes | Yes | No |
| Department | The designated department of the fund. This can also be used to identify organization codes. | Yes | Yes | No |
| User Category 2 | Additional grouping field; use varies by organization | Yes | Yes | No |
| Purpose Description | This field is used for Stewardship reporting purposes and should define the purpose of the fund in detail. This information will be placed on the standard Donor Report. | Yes | Yes | No |
| Business Office Notes | Text field often used for notes from the Business Office relating to a specific fund | Yes | Yes | No |
| Secondary Description | Additional text field; use varies by organization | Yes | Yes | No |
| Spending Rule | Used to differentiate between multiple spending calculations | Yes | No | No |
| Historical Gift (Required for PR funds) | Principal, Corpus, or total accumulated historical gifts as of the same date as of the start date in Balance. | Yes | No | No |
| Units | Units owned by a specific fund | Yes | No | No |
| Pledge Balance | Outstanding pledge receivable | Yes | No | No |
| Spending Bucket Balance | Spending calculated but not yet removed from the endowment balance (this doesn't apply for all clients) | Yes | No | No |
| Non-unitized assets | Assets that belong to one specific fund, and not to the rest of the pool | Yes | No | No |
| Fee Schedule | Used to differentiate between multiple fee calculations | Yes | No | No |
| Distribution Reinvest Threshold - HG | This is used to limit spending from a fund until a fund’s historic gift or corpus reaches a certain value. | Yes | No | No |
| Distribution Reinvest Threshold - MV | This is used to limit spending from a fund until a fund’s market value reaches a certain value. | Yes | No | No |
| Authorized Date | This is used to limit spending from a fund until a future date in time (mm/dd/yyyy). | Yes | No | No |
| Distribution Destination ID | The primary spending account number for the distributed spending. | Yes | No | No |
| Distribution Fund | This is used for additional components of the spending account number's account coding for the distributed spending. | Yes | No | No |
| Distribution Project | This is used for additional components of the spending account number's account coding for the distributed spending. | Yes | No | No |
| Distribution Program | This is used for additional components of the spending account number's account coding for the distributed spending. | Yes | No | No |
| Distribution Dept | This is used for additional components of the spending account number's account coding for the distributed spending. | Yes | No | No |
| Income Acct (for GL Exports) | If the income account numbers used vary between different funds, this field can be used to specify the account code to be used for income. | Yes | No | No |
| Gain Acct (for GL Exports) | If the gains account numbers used vary between different funds, this field can be used to specify the account code to be used for gains. | Yes | No | No |
| Invest Acct (for GL Exports) | If the investment account numbers used vary between different funds, this field can be used to specify the account code to be used for investments. | Yes | No | No |
Confidential
Copyright © 2014 Fundriver, Inc.
US Patent No. 7,849,108
These materials constitute Fundriver confidential proprietary information.