How do I change from 2 to 3 fund balances?

Scenario:  You have the traditional 2 balance system (Q1 - Principal and Q2 - Income) for endowed funds.  You want to go to 3 balances (Q1 – Historic Gifts, Q2 – Accumulated Earnings, Q3 – Available to Grant).  The easiest method with the least amount of changes is to simply add a Q3 fund balance (plus new account types of Q3, R3, T3, X3).  For this example we'll say they want this to take effect in 2017 (fiscal year end is 12/31/16).
 
Things to Think About Before Doing This:  Decide what year you want this to take effect.  For best results, use the "Period 13" method described below.  This method uses journal entries to period 13 to break apart the balances.  The beauty of this is that EOY can be run without harming the split in beginning balances in the new year.  If you already use period 13 in your calendar on an ongoing basis (not typical) , we would have to figure out if that method will work or not.  (If this method cannot be used, then this would have to be started in a new year where EOY won't be run ever again for the previous year.  This is because beginning balances would need to be manually split out among fund balance accounts.)  If the "Period 13" method is used, you can run reports for the old year through period 12 without seeing the split.  If you wanted to see the split, run reports through period 13.
 
A review of how your Master Chart is set up is crucial to understand how your General Ledger calculates each fund balance.  You will need to understand what is represented in each fund balance account in order to know how to proceed.  Besides account and account type changes that will need to be made in this process, this is a good time to make sure that account types and fund classes are assigned correctly for all GL accounts going forward.
 
Q1: Up to this point, has this fund balance only been representing gifts to principal?  Or has it also included interest, gains, and fees?  It will have the task of only holding the historic gifts going forward.  You will need a report of what your historic gifts should be for each fund.  One method to get this is to run a Gift History Report.  How do those numbers compare to whats in FIMS right now as shown in the Fund Balance Report in the Fund Balance 1 column as of 12-31-2008?  If they agree, then no journal entries need to be made to move anything out of that fund balance.  If they don’t agree, then entries need to be made so that Q1 only shows historic gifts.  Theoretically, any differences need to be moved into the Q2 fund balance.  This needs to be discussed for your particular situation.
 
Q2: Up to this point, has this fund balance been representing all accumulated earnings, or only a calculated spending policy amount minus grants?  Going forward it needs to hold only accumulated earnings: interest and dividends, realized gain/loss, unrealized gain/loss, fees.  Theoretically, for endowed funds the calculated spending policy for 2009 should be moved out of this fund balance into the Q3 fund balance.  Do you have those amounts in a spreadsheet so we can make journal entries to do this transfer? 
 

Net Income Funds:  For net income funds, we only need to tweak the master chart account type setup to get interest minus fees to show in the Q3 fund balance for the available to grant amount – no journal entries will need to be made for them.  This needs to be discussed for your particular situation.  Here is an explanation of how you can track what is available to grant for net income funds without doing journal entries:
 

• Old Account Type Coding for GL that has only Q1 & Q2 balances:
  • Interest and fee accounts may be coded with a 2 (R2, X2, T2)

 

• New Account Type Coding for GL that has 3 fund balances, Q1, Q2, and Q3:
  • Interest, fee, and grant expense accounts are coded with a 3 (R3, X3, T3)
  • Other accumulated earnings (if any) would be coded with a 2 (R2, X2, T2)

Q3: This is the new fund balance account that will hold what’s available to grant from the fund.
 
 
Steps to Changing (Period 13 Method):
 
1.  Master Chart - Add the new Q3 fund balance account to the master chart.  Map it to all classes that need it.  Also add a new transfer account with account type T3.  Change descriptions: In the master chart, change the description of the fund balance account Q1 to say something like "Historic Gifts").  Pick 2010-2011 to change description in actual GL accounts.  Then change account types for new year 2011 only: Go down and change all other GL account types and descriptions as needed (look for any other accounts mapped to 2 that should be changed to 3) or add any other accounts needed. For example, Gifts to Available Income will need to be R3 and Grant Expense will need to be X3.
 
2.  GL Mass Add/Delete - Add new GL accounts added in master chart for old and new years.
 
3.  Run Calibrate GL Account Types – For 2011 only.  This program will review and calibrate G/L account type codes back into synchronization with your Master Chart of Accounts.  This program will essentially loop through each fund and account specified comparing the account type codes in existing GL accounts with those mapped in the Master Chart of Accounts. The program will only review records in the master chart that are mapped appropriately with a fund class code that matches the code in the fund being processed.  So, before running this process, be sure that your master chart accounts are mapped with the correct fund class codes.  Further, take a moment to think about any manual changes you might have made to GL account types codes in the past, and any impact this routine might have.
 
4.  GL Calendar - Adjust GL Calendar so that Period 13 is 12-31-2010.  (Period 12 can still end at 12-31-2010 as well.)  Make sure no one is posting in GL until this is procedure is completed.
 
5.  Available to Spend – We need a spreadsheet of the Available to Spend amounts for each endowed fund (not net income funds).  We will import entries from the spreadsheet.  The spreadsheet should have at least two columns: 1) FIMS fundid, 2) journal entry amount.  Debit a T2 transfer account and credit a T3 transfer account.  Date them 12-31-2010.  The effect will be to move Available to Grant amounts out of Q2 to Q3.  Post the entries.
 
6.  GL Calendar - Change the GL Calendar back to just 12 periods as before.
 
7.  EOY - Run EOY for 2010.
 
8.  Run Calibrate GL Account Types one more time – For 2011 only. 
 
9.  Financial Reports & Fund Statements - Make any necessary changes in financial reports you’ve built in Design Statements.

See the attached document for further information.