Beginning Balance: Financial Templates

You will use your existing Chart of Accounts to help with the migration of data into CommunitySuite. The Chart of Accounts can typically be exported from your current system and used to complete the Fund Balance Import Template in the next section. 

1. Download the Accounts Import Template located at the bottom of this article.
   
   • The spreadsheet will download as a Microsoft Excel spreadsheet to your computer.

There are three columns in this template and all are required fields: Account Number, Account Name, and Account Type. 

Account Number

The Account Number can be up to 25 characters and can include numbers, letters, and special characters. There is no system requirement that different account types must fall into specific numbering sequences. This will be entered into Column A. All account numbers have to be unique. 

Account Name

The Account Name is entered into Column B. List an account name or label next to each account number to describe the account.

Account Type

The Account Type is entered into Column C. There are five Account Types listed on the template required for migration: Asset (A),  Liability (L), Equity (OE), Revenue (R), and Expense (E). Once your data is in CommunitySuite, you will be able to move your accounts into different sub-account types within the system. Only use the account types that are provided in the template. Do not use General Ledger Accounts that are used to track individual fund balances. For example, each fund cannot have its own equity account or liability account. 

Required Accounts

CommunitySuite will need the accounts listed below for its basic operations. If you do not already have these accounts in your Chart of Accounts, add them to the template and assign them an account number.

• There are three required equity accounts.
  • Principal -This is the historic or endowed portion of a fund balance. It is typically the "permanently restricted" portion or the "historic gift value" of a fund's balance. This is sometimes referred to as the Corpus Balance. 
  • Default -This is where the net income/retained earnings are closed into at the end of each fiscal year and is typically the fund balance where administrative fees are charged. If a fund is truly unrestricted and all of the balance can be granted at any time, its full balance will be in this account.
  • Spendable -This is the amount that the fund has available to spend on grant making or scholarships. Typically, funds with a spendable balance are endowed and/or subject to a spending policy. CommunitySuite will only use this account for funds where the spendable/available balance need to be tracked separately from the principal or default balances. If you are not tracking the principal or spendable separately in your current system, put the full fund balance in the Default Equity Account Column. It is possible to adjust these balances after you have migrated all of your data into CommunitySuite.
• It is required to have a Grants Payable and Scholarship Payable account.

Additional Guidance

• You cannot migrate the relationship between parent/sub accounts (parent/child accounts) into CommunitySuite. Each account has to be listed individually on the template. The parent/sub account (parent/child account) relationships can be setup within CommunitySuite after the migration. 

Chart of Accounts Template Video

The Fund Listing Import template includes basic fund information that can be included in the migration. There will a fair amount of remaining fund setup items once the funds are established in CommunitySuite. This template should be completed by the person who is the most knowledgeable about the funds and the specifics of each fund for your organization.

1. Download the Funds Import Template located at the bottom of this article.
   
   • The spreadsheet will download as a Microsoft Excel spreadsheet to your computer.

Field Descriptions

Fund Record

• fund_code - Short name for the fund that can be used for searching. This is a required field.
  • There is a 30 character limit for the Fund Code. You can use letters, numbers, and special characters for the Fund Code. Instead of using a space between characters in the fund code, it is recommended to use a dash or an underscore. Each Fund Code has to be unique for each fund. This field will translate into the CommunitySuite Fund shortname. CommunitySuite will assign a numeric fund ID upon migration. 
• name - The name that will appear on the Fund Statement. If your organization has a specific fund agreement for your relationships with organizations or fund holders, it typically warrants a distinct fund record. This is a required field.
  • There is a 255 character limit for the Fund Name. Letters, numbers, and special characters are accepted. Spaces are included in the character count. Fund Names do not have to be unique, but it is important that you make each fund name distinctive. Do not use fund names that are for alphabetical sorting. For example, a fund name can be "Matt & Laura Smith Family Fund," not "Smith, Matt & Laura Family Fund."
• public_name - The public name that will appear on the Giving Hub. This can be separate from your standard fund name.
• summary - Short fund summary that describes the purpose of the fund or what it supports; this will appear on the Giving Hub page.
• create_date - Date the fund was created.
• close_date - Date the fund was closed.

Fund Group

Fund groups are how CommunitySuite categorizes funds in the system. It is an easy way to report differences in the types of funds an organization may use and is helpful for reporting purposes. There is a 255 character limit for the Fund Group. Letters, numbers, and special characters are accepted. Spaces are included in the character count. The Examples tab on the template has standard fund group, subgroup, division, and segment examples. All of these fields have the same filtering and reporting capabilities.

• group_name - Typically, this is a broad category for how to group similar funds together. This field will also appear on the Giving Hub Portal.
• subgroup_name - Subgroups are an optional secondary field that can be used to group similar fund records together for reporting purposes.
• division_name - If you have funds that are part of a separate legal entity, like a smaller foundation, we advise that you assign them a division code. 
• segment_name - Segments are an optional tertiary field that can be used to group similar fund records together for reporting purposes.

Additional Guidance

• Required fields for each fund include the fund name, fund code, and the fund group. 
• List all the funds that currently have a balance and any retired/closed funds for which you wish to import donation and grant history.
• Every setting and column can be changed later in CommunitySuite in bulk after you learn more about each of these fields during your training. 
• In the Summary Column, do not put confidential information. Information in this field will be presented on your public facing donation page (if applicable). There are options for importing internal notes on funds in CommunitySuite. 

Fund Listing Import Template Video

The Fund Balance Import template (FBI) is a guide to help identify all the funds and their related general ledger accounts for migration. Balances from this template are used to create the general ledger, which is needed for importing the beginning balances. Only funds that have transactions associated with them from the FBI template will be migrated. This template helps transition from a non-fund accounting system to a fund accounting system. Generally, an accountant or bookkeeper completes this template. In order to fill out this report, you will use your organization's Statement of Financial Position (Balance Sheet), Trial Balance, etc. to document the total fund balance and the amount each fund has in the specific general ledger balance sheet accounts. Foundant’s Beginning Balance migration does not include items from the Statement of Activities (Income Statement) or transaction history. The FBI template will migrate account balances as of a given date. Use the information below and the Examples tab on the template to input your organization’s information. 

1. Download the Fund Balance Import Template located at the bottom of this article.
   
   • The spreadsheet will download as a Microsoft Excel spreadsheet to your computer.
2. On the Template tab, enter your organization’s name and the beginning balance financials' date that you are using (typically the end a recent quarter or month).
3. List all active funds (funds with balances, positive or negative) in Column A, and their corresponding unique fund codes in Column B. Both of these data points should match the fund names and the fund codes in the Fund Listing Import template.
4. Account Numbers and Account Names should be listed in rows 4 and 5 respectively for all asset, liability, and net asset (equity) accounts that have balances. Add or delete columns as needed to the template to accommodate your accounts.
5. Begin entering the total fund balances in Column C for each fund on the template.
6. Enter the consolidated account balance from your Trial Balance or Statement of Financial Position (Balance Sheet) in the row towards the bottom labeled “Balance from Consolidated Trial Balance.” This information will be used to help you allocate account balances to individual funds.
7. Allocate the account balances to each fund row. Typically, Assets are positive values, and Liabilities and Net Assets are negative. Use the Examples tab as a guide. For example, the John Burrows fund has a total fund balance of $25,243.87, all of which is invested in the WF Investment asset account 1111. In the net asset area, $20,000 of the balance is considered principal, $3,200 is spendable/available and the remaining amount is considered default/net income. Each fund may be different. Some funds may have pledge receivables and grants payable, and some may not.
8. Use the "Check" Column on the far right to verify that each fund is balanced Assets=Liabilities+Net Assets (Equity). If the check says anything other than 0.00, the fund is not balanced and requires further review.
9. Use the “Fund Balance Check” Column on the far right to verify Fund Balance = Assets + Liabilities. If the check says anything other than 0.00, the fund is not balanced and requires further review.
10. Use the "Agrees?” row at the bottom to verify that the total account balance is fully allocated. If any of the columns say “FALSE,” the account is either over or under allocated. The difference/variance will be listed below in that column.
11. Once everything balances, the “Check” and "Fund Balance Check" Columns are both 0.00 and the “Agrees?” row has only TRUE statements, upload your template to the Smartsheet for the Foundant team to review.

Additional Guidance

• If you do not know which funds have money in an account, highlight the Account Names and Account Numbers for review with your Foundant team. This is common if any/all funds have money in a checking account or a large investment account. 
• Include an Operating Fund or an Administrative Fund for the fund that pays bills such as an electric bill, salaries, or any other operational expenses. 
• Do not list your income or expense accounts on this template as the accounts and balances will not be a part of the migration.
• CommunitySuite operates best when using three net asset accounts: Principal, Default, and Spendable (use the definitions in the Chart of Account template area of this article for more information). Traditionally endowed funds may have balances in all three columns of the FBI template depending on if the fund has granted out its available balance. Unrestricted funds, where the full balance could be granted tomorrow, do not need a separate spendable/available balance, and thus the full balance should be in the Default Column. Additionally, if the organization does not track spendable/available or principal, or if you are unsure, put the full balance in the Default Column. You can edit these items in CommunitySuite with a journal entry later, if needed.
• If your organization has any outstanding receivables or payables, prepare a listing(s), and then your Foundant team member will provide guidance for inputting that data into CommunitySuite. The listing should include the following: Name, Full Address, Total Amount Outstanding, and Payment Schedule.

Fund Balance Import Template Video