This template is used to provide us with your initial data to get your new Balance database set up. Click HERE to download the blank template!
TABLE OF CONTENTS
- Investment Pools Tab (Required)
- Funds Tab (Required)
- Distribution Rules Tab
- Fund History Tab
- Transaction History Tab
- Pool History Tab
- Investment Portfolio Tab
- Expendable Fund Profile Tab
- Expendable Transaction History Tab
- Comparison of Data Fields: Endowed vs. Expendable vs. Investment Managers
Investment Pools Tab (Required)
Provide the name and total market value of each Investment Pool. The investment pool names in this tab should match identically to the investment pool names given in the Fund Profile tab (column C). 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 Inception 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.
If you provided units on the Fund Profile tab, enter the unit price, or unit value, in column C for the same time period of the data being provided in the Funds tab. 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 Fund Profile 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.
Funds Tab (Required)
Complete all required columns. Fields highlighted in yellow (columns A through G) 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.
General instructions for column entry (e.g. suggested purpose, text limit, required decimal places etc.) can also be accessed by hovering over the header of each column to view comments.
Helpful hints:
- The sum of the market values should tie to the total value of your audited financial statements for the same time period (Balance start/load date).
- 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 ).
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 |
Distribution Rules Tab
Distribution Rules are used for 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 Fundriver.
- 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).
Fund History Tab
Use this optional worksheet to load historical data for individual funds into Balance. If your spending rule looks at individual fund values over a set time period, then we would recommend that you provide enough historical data so Balance will be able to calculate the spending from the start date.
If you are providing units, enter the unit price in column G.
Transaction History Tab
Use this optional worksheet to build transaction history for funds. Multiple dates/types can be loaded on one tab. Please make sure that your transaction history dates (Date column) coincide with your posting frequency in Balance - for example, if you will be posting quarterly in Balance, then "Date" should be populated with quarter-end dates.
Select from the listed transaction types when populating this tab. If you have a transaction that isn't covered by the transaction types listed in this tab, please reach out to our team for assistance. Entries that do not match the transaction types listed will not import properly. This article explains what each transaction type does: Transaction Type Guide.
Pool History Tab
Use this optional worksheet to build investment pool history. The investment pool name in this tab should match the investment pool name in the funds and investment pools tabs.
Investment Portfolio Tab
To use if you purchased the Investment Portfolio module
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 |
| Market Value (Required) | Full market value for each investment managers as of the start date in Balance |
Book Value | Provide the Investment manager book value (cost) as of the start date in Balance |
Investment Type | Select the appropriate Investment type from the drop-down for each manager. |
Custody Account Number | The account number on the custody statement. |
Custodian Name | Name of custody account. |
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. |
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 |
Liquidity | In the drop-down, select the liquidity of each manager (per the ASU 2016-14 Standards). |
Notes: Please make sure that the total of the investment manager market values in column “D” is equal (to the penny) to the total market value of the Pools on the Investment Pools tab and the Funds on the Fund Profile tab for the investment pool(s) that will be tracked at the Manager level.
The Manager Profile information can be edited within the user interface after it’s been loaded. Any changes to the Market values will need to be corrected in the template and sent to Balance for uploading.
Make sure the market values provided for each Manager in column “D” are for the same start date as the market values provided for each Fund in the Fund Profile tab.
If you need to account for a timing issue with cash flow (Due to/from) set up a Manager for this specific purpose and include the due to/from value as of your start date (or posting date).
Expendable Fund Profile Tab
To use if you purchased the Expendable Funds module
Use this optional worksheet to list out your organization’s expendable funds. Fields highlighted in yellow (columns A through D) are required.
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 Transaction History Tab
Use this optional worksheet to provide beginning balances or historical transaction history for your expendable funds.
Multiple dates/types can be loaded on one tab. Please make sure that your transaction history dates (Date column) coincide with your posting frequency in Balance - for example, if you will be posting quarterly in Balance, then "Date" should be populated with quarter-end dates.
Select from the listed transaction types when populating this tab. If you have a transaction that isn't covered by the transaction types listed in this tab, please reach out to our team for assistance. Entries that do not match the transaction types listed will not import properly.
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.