WELCOME TO GMS ACCOUNTING ONLINE HELP
The help you need at your fingertips.
You are here: General Ledger > G/L Supplements > #402 FASB 117 Worksheets
#402 FASB 117 Worksheets
Function
This supplement produces worksheets to assist you and your auditor in preparing the Statement of Financial Position, Statement of Activities, Statement of Functional Expenses and Statement of Cash Flows. These statements are required to be included in audit reports for all not-for-profit organizations in accordance with the SFAS No. 117 issued by the Financial Accounting Standards Board (FASB).
Even though the format appearance of these statements may vary in different audit reports, the FASB 117 Worksheets supplement will provide you with the information to be used. All category descriptions and assignments are user defined, thus allowing you and your auditor to determine which accounts in your agency belong to each grouping. We recommend that you and your auditor establish the setup files jointly the first year.
Things You Should Know
Instructions for Establishing the Setup Files
GL Categories
From this tab, you can set up your category codes and descriptions that will be used to assign balance sheet accounts and revenue and expenditure codes to.
Setup Categories Code #
Enter 100-199 for asset categories
Enter 200-299 for liability categories
Enter 300-390 for net asset categories
Enter 400-499 for revenues
Enter 500-999 for expenses
597 is the preset code for Indirect Costs
599 is the preset code for Common Costs
Preset net asset accounts are:
300 Net Assets Without Donor Restrictions
390 Net Assets With Donor Restrictions
Even though these net asset accounts are preset, they need to be activated by selecting edit, entering one of the four codes, and saving. Repeat this procedure for the remaining four codes.
Note: On the Statement of Activities, in order to have your projects’ R&E appear, they must be assigned to one of the 2 pre-set net asset accounts.
Description
Up to fifty characters will print on the Statement of Financial Position, up to 26 characters will print on the Statement of Activities but only 20 characters will be printed on the Statement of Functional Expense due to the format of the report. For revenue and expenditure category descriptions, make sure that the first 20 characters entered are defined appropriately.
Examples of category descriptions are:
Assets:
Cash and cash equivalents
Accounts and interest receivable
Inventories and prepaid expenses
Contributions receivable
Fees receivable
Short-term investments
Assets restricted to investment in land, buildings and equipment
Land, buildings and equipment
Long-term investments
Liabilities:
Accounts payable
Refundable advance
Deferred revenue
Grants payable
Notes payable
Annuity obligations
Long-term debt
Net Assets:
Net Assets Without Donor Restrictions
Net Assets With Donor Restrictions
Revenues:
Contributions
Fees
Income on long-term investments
Other investment income
Net unrealized and realized gains on long-term investments
Other
Assign GL Categories
Click Edit. Assign a Cost Category Code to each GL Code. Once you click on Save, it will automatically assign Cost Category Code 0 to any unassigned GL Codes. You can then edit and change the category in the grid.
Note: When assigning GL codes to categories the system will only allow preset GL categories 300 & 390 to be used when assigning projects and net asset accounts.
Function Codes
From this tab you can set up function codes and descriptions and assign projects or elements to the functions. These will be used for the Statement of Activities and the Statement of Functional Expense.
Code:
Enter 1-88 for program services
Enter 90-94 for indirect (common costs and M&G)
Enter 96-98 for fundraising activities
Preset function accounts are:
89 Total Program Services
95 Total Indirect - M & G Services
99 Total Fund Raising Services
Description:
Up to 20 characters will print on the Statement of Functional Expense. Abbreviate each description as well as you can to make it identifiable.
Examples of function descriptions are:
Program Services:
Supporting Services
90 M & G (Management and General)
96 Fundraising
If you use Supplement #403 Dual Indirect Pool, you may set up 90 Common Costs and 91 Mgt & Gen.
If more than one code is set up for indirect activities, a Total Support column will print which has a preset code of 95.
Assign by Project
All projects that have expenditures coded to them will be included in the combo box. From this screen, you can click edit and assign each project to a function code. When you click on Save, all projects that haven’t been assigned to a function code will be assigned to function code 0.
Assign by Element
Even though most function code assignments will be done by project code, there may be certain cases such as dual indirect pool where you need to designate certain elements within the same project to different function codes. In those cases, you would click on Assign by Element and assign those elements to function codes. You cannot assign an element to a function code if the project it is under is already assigned to a function code.
Equipment
From this screen you can check the expense codes used for equipment purchases. Amounts coded to the equipment expense codes listed will be reflected next to "Satisfaction of equipment acquisition restrictions" on the Statement of Activities.
The total amount of equipment purchases coded to projects assigned to temporarily restricted net assets will be reflected as a debit, reducing total revenues under the Temporarily Restricted column on this worksheet. The total amount will also be reflected as a credit under the Unrestricted column, increasing total revenues in that column.
Operating Instructions
FASB 117 Worksheets tab
Period:
The month end date displayed is the month ending date when the Agencywide Revenue and Expenditure Report was last printed.
Statement of Financial Position Worksheet
Include account detail:
Check this box to include all of the balance sheet account information under each account heading. If left unchecked, it will only print the category description and the amount of all of the GL codes assigned to each category.
Statement of Activities Worksheet
Include 59700 in Cost of Programs
Check this box to include the amount of indirect costs allocated to programs in all applicable functional categories.
Leave this box unchecked if you want to include all indirect costs in the functional category that you assigned to elements 999000-999900, such as management and general.
Include 59900 in Cost of Programs
This option will only be available if Supplement #403 Dual Indirect Pool is installed. Check this box to include the amount of common costs allocated to programs 59900 in all applicable functional categories.
Leave this box unchecked if you want to include all common costs in the functional category that you assigned to elements 999000-999400, such as Common Costs.
Statement of Functional Expenses Worksheet
Include 59700 in Cost of Programs
Check this box to include the amount of indirect costs allocated to programs in the columns for all applicable functional categories. This will be reflected as a debit to all applicable columns and a credit to the functional category that elements 999000-999900 were assigned to.
Leave this box unchecked of you want to include all indirect costs in the column for the functional category that you assigned to elements 999000-999900, such as management and general without the debits and credits shown for allocated amounts.
Include 59900 in Cost of Programs
This option will only be available if Supplement #403 Dual Indirect Cost Pool is installed. Check his box to include the amount of common costs allocated to programs in the columns for all applicable functional categories.
Leave this box unchecked to include all common costs in the column for the functional category that you assigned to elements 9990-9994, such as common costs.
Statement of Cash Flows Worksheet
Edit Setup
Note: It is very important that you work closely with you auditor in setting up this statement as you want it to be prepared in the format your auditor uses in their audit report.
Note: Once the setup has been completed and the worksheet generated, you must save the worksheet Excel file as that is what will be used to prepare the statement.
Note: There are six default fields on the setup grid that should not be edited. If the description of the following six fields are edited it will cause the corresponding amounts to appear in the wrong section of the Statement. Once you are finished editing the setup and preparing the worksheet, you will save the worksheet with a new file name corresponding to your FY ending period. At that point any of the descriptions and amounts may be edited on the Excel file to match the terminology that your auditor uses in your audit report. Later in these instructions, it will also instruct you on which amounts need to be manually entered in the Excel file.
Note: If multiple codes are used in a field make sure they are of the same type i.e. Assets, Expenses, etc.
The setup form is initially populated with default descriptions. Since the format of this statement can vary based on auditors, you can edit, add to or delete the default entries.
Description: The entry in this field represents the description(s) that will appear on the Statement of Cash Flows Worksheet.
Account: Identify the GL Account numbers that correspond with this field. If multiple accounts are needed separate them with commas, i.e. 13500,13600. Note: Do not place a comma before the first code or after the last when using multiple codes.
O/F/I: The choices available here are Operating, Financing and Investing. This is used to identify in which section of the statement these amounts will be included.
Save: Once the setup is complete, click on Save.
Prepare Worksheet: This will prepare the Statement of Cash Flows Worksheet (Excel file) based on the saved setup file.
Editing the Worksheet
Due to the required format(s) of the statement it will be necessary to edit the Excel file in certain areas.
Example one: Property and Equipment
When assigning the account number on the set up sheet for Property and Equipment, you will identify the appropriate asset account number(s). The program will then pull the beginning and ending values for those account(s) and populate the worksheet accordingly. The value in column E of the Excel file represents the increase/decrease of the beginning and ending values of those account(s).
Note: The net of four other amounts should equal the amount in column E for Property and Equipment. These amounts are:
Example Two: Long Term Debt
Long term debt is required to be broken down into two portions: 1) Current Portion of Long Term Debt, which represents the total principal amount to be paid in the first 12 months following the date of this statement and, 2) Long Term Debt Net of Current Portion.
As you are editing the setup sheet for this statement, on the line called Long Term Debt, enter the corresponding liability account number(s) for this debt. Then when you prepare the worksheet, edit the Excel file by adding the calculated amount of Current Portion of Long Term Debt line, and enter it in the appropriate cell, then adjust the amount in Long Term Debt accordingly.
When editing the Excel file, make sure you have the positive and negative signs correct. Following is a guide to follow:
Increase from Last Year Decrease from Last Year
Assets Negative Amount Positive Amount
Liabilites Positive Amount Negative Amount
Net Assets Positive Amount Negative Amount
Depreciation for FY Shown as a Positive Amount
Loss on Disposal of Assets Shown as a Positive Amount
Purchase of Assets and Equipment Shown as a Negative Amount
Accumulated Depreciation on Disposal of Assets Shown as a Negative Amount
Note: Once the Excel file has been edited and is complete, make sure you save the file as it is necessary to prepare the Statement of Cash Flows.
Print: When you click on print, you will be asked if you prepared the Cash Flows Worksheet. If the worksheet has been prepared and saved, click on Yes, then identify the saved Excel file. This will prepare the Statement of Cash Flows Worksheet.
This supplement produces worksheets to assist you and your auditor in preparing the Statement of Financial Position, Statement of Activities, Statement of Functional Expenses and Statement of Cash Flows. These statements are required to be included in audit reports for all not-for-profit organizations in accordance with the SFAS No. 117 issued by the Financial Accounting Standards Board (FASB).
Even though the format appearance of these statements may vary in different audit reports, the FASB 117 Worksheets supplement will provide you with the information to be used. All category descriptions and assignments are user defined, thus allowing you and your auditor to determine which accounts in your agency belong to each grouping. We recommend that you and your auditor establish the setup files jointly the first year.
Things You Should Know
- Before you use this supplement at Year End, make sure all entries have been completed to close the control accounts to the general ledger for all pools. Also, make sure you display all four financial reports in Monthly Processing\Financial Reports including the Year End version of the Balance Sheet or select the Post All Financials button.
- The first time you try to print a report, you will be alerted if you have not established the categories and assignments.
- Make sure that all asset, liability, project, revenue and expenditure codes are assigned to categories.
- If a category code and description was entered in error or incorrectly, highlight the code and click on delete. This will automatically remove it from the category table and any assignments made to it.
- If you had assigned a code to the category entered in error, click on Assign GL Categories, Edit and change the code in the grid.
- If you enter a new GL code, the next time you print the worksheets, all unassigned GL codes will be assigned to code zero.
- The date displayed in the Period field will be the month end date used when the Agencywide R&E report was run.
- Each fiscal year, all assignments made must be carefully reviewed and revised when necessary for chart of account changes.
- Instructions for printing the reports are after the instructions for establishing the setup files.
Instructions for Establishing the Setup Files
GL Categories
From this tab, you can set up your category codes and descriptions that will be used to assign balance sheet accounts and revenue and expenditure codes to.
Setup Categories Code #
Enter 100-199 for asset categories
Enter 200-299 for liability categories
Enter 300-390 for net asset categories
Enter 400-499 for revenues
Enter 500-999 for expenses
597 is the preset code for Indirect Costs
599 is the preset code for Common Costs
Preset net asset accounts are:
300 Net Assets Without Donor Restrictions
390 Net Assets With Donor Restrictions
Even though these net asset accounts are preset, they need to be activated by selecting edit, entering one of the four codes, and saving. Repeat this procedure for the remaining four codes.
Note: On the Statement of Activities, in order to have your projects’ R&E appear, they must be assigned to one of the 2 pre-set net asset accounts.
Description
Up to fifty characters will print on the Statement of Financial Position, up to 26 characters will print on the Statement of Activities but only 20 characters will be printed on the Statement of Functional Expense due to the format of the report. For revenue and expenditure category descriptions, make sure that the first 20 characters entered are defined appropriately.
Examples of category descriptions are:
Assets:
Cash and cash equivalents
Accounts and interest receivable
Inventories and prepaid expenses
Contributions receivable
Fees receivable
Short-term investments
Assets restricted to investment in land, buildings and equipment
Land, buildings and equipment
Long-term investments
Liabilities:
Accounts payable
Refundable advance
Deferred revenue
Grants payable
Notes payable
Annuity obligations
Long-term debt
Net Assets:
Net Assets Without Donor Restrictions
Net Assets With Donor Restrictions
Revenues:
Contributions
Fees
Income on long-term investments
Other investment income
Net unrealized and realized gains on long-term investments
Other
Assign GL Categories
Click Edit. Assign a Cost Category Code to each GL Code. Once you click on Save, it will automatically assign Cost Category Code 0 to any unassigned GL Codes. You can then edit and change the category in the grid.
Note: When assigning GL codes to categories the system will only allow preset GL categories 300 & 390 to be used when assigning projects and net asset accounts.
Function Codes
From this tab you can set up function codes and descriptions and assign projects or elements to the functions. These will be used for the Statement of Activities and the Statement of Functional Expense.
Code:
Enter 1-88 for program services
Enter 90-94 for indirect (common costs and M&G)
Enter 96-98 for fundraising activities
Preset function accounts are:
89 Total Program Services
95 Total Indirect - M & G Services
99 Total Fund Raising Services
Description:
Up to 20 characters will print on the Statement of Functional Expense. Abbreviate each description as well as you can to make it identifiable.
Examples of function descriptions are:
Program Services:
- Transportation Svcs
- Emergency Assistance
- Health Services
- Child Services
Supporting Services
90 M & G (Management and General)
96 Fundraising
If you use Supplement #403 Dual Indirect Pool, you may set up 90 Common Costs and 91 Mgt & Gen.
If more than one code is set up for indirect activities, a Total Support column will print which has a preset code of 95.
Assign by Project
All projects that have expenditures coded to them will be included in the combo box. From this screen, you can click edit and assign each project to a function code. When you click on Save, all projects that haven’t been assigned to a function code will be assigned to function code 0.
Assign by Element
Even though most function code assignments will be done by project code, there may be certain cases such as dual indirect pool where you need to designate certain elements within the same project to different function codes. In those cases, you would click on Assign by Element and assign those elements to function codes. You cannot assign an element to a function code if the project it is under is already assigned to a function code.
Equipment
From this screen you can check the expense codes used for equipment purchases. Amounts coded to the equipment expense codes listed will be reflected next to "Satisfaction of equipment acquisition restrictions" on the Statement of Activities.
The total amount of equipment purchases coded to projects assigned to temporarily restricted net assets will be reflected as a debit, reducing total revenues under the Temporarily Restricted column on this worksheet. The total amount will also be reflected as a credit under the Unrestricted column, increasing total revenues in that column.
Operating Instructions
FASB 117 Worksheets tab
Period:
The month end date displayed is the month ending date when the Agencywide Revenue and Expenditure Report was last printed.
Statement of Financial Position Worksheet
Include account detail:
Check this box to include all of the balance sheet account information under each account heading. If left unchecked, it will only print the category description and the amount of all of the GL codes assigned to each category.
Statement of Activities Worksheet
Include 59700 in Cost of Programs
Check this box to include the amount of indirect costs allocated to programs in all applicable functional categories.
Leave this box unchecked if you want to include all indirect costs in the functional category that you assigned to elements 999000-999900, such as management and general.
Include 59900 in Cost of Programs
This option will only be available if Supplement #403 Dual Indirect Pool is installed. Check this box to include the amount of common costs allocated to programs 59900 in all applicable functional categories.
Leave this box unchecked if you want to include all common costs in the functional category that you assigned to elements 999000-999400, such as Common Costs.
Statement of Functional Expenses Worksheet
Include 59700 in Cost of Programs
Check this box to include the amount of indirect costs allocated to programs in the columns for all applicable functional categories. This will be reflected as a debit to all applicable columns and a credit to the functional category that elements 999000-999900 were assigned to.
Leave this box unchecked of you want to include all indirect costs in the column for the functional category that you assigned to elements 999000-999900, such as management and general without the debits and credits shown for allocated amounts.
Include 59900 in Cost of Programs
This option will only be available if Supplement #403 Dual Indirect Cost Pool is installed. Check his box to include the amount of common costs allocated to programs in the columns for all applicable functional categories.
Leave this box unchecked to include all common costs in the column for the functional category that you assigned to elements 9990-9994, such as common costs.
Statement of Cash Flows Worksheet
Edit Setup
Note: It is very important that you work closely with you auditor in setting up this statement as you want it to be prepared in the format your auditor uses in their audit report.
Note: Once the setup has been completed and the worksheet generated, you must save the worksheet Excel file as that is what will be used to prepare the statement.
Note: There are six default fields on the setup grid that should not be edited. If the description of the following six fields are edited it will cause the corresponding amounts to appear in the wrong section of the Statement. Once you are finished editing the setup and preparing the worksheet, you will save the worksheet with a new file name corresponding to your FY ending period. At that point any of the descriptions and amounts may be edited on the Excel file to match the terminology that your auditor uses in your audit report. Later in these instructions, it will also instruct you on which amounts need to be manually entered in the Excel file.
- Cash
- Net Assets
- Property and Equipment
- Loss on Disposal of Assets
- Interest Expense
- Interest Payable
Note: If multiple codes are used in a field make sure they are of the same type i.e. Assets, Expenses, etc.
The setup form is initially populated with default descriptions. Since the format of this statement can vary based on auditors, you can edit, add to or delete the default entries.
- To edit an existing field, click on it, then hit the space bar. That will put the cursor at the end of the field and you can move around from that point.
- To insert a line above an existing entry, double click on the line and select insert.
- To delete an existing line, double click on it and select delete.
Description: The entry in this field represents the description(s) that will appear on the Statement of Cash Flows Worksheet.
Account: Identify the GL Account numbers that correspond with this field. If multiple accounts are needed separate them with commas, i.e. 13500,13600. Note: Do not place a comma before the first code or after the last when using multiple codes.
O/F/I: The choices available here are Operating, Financing and Investing. This is used to identify in which section of the statement these amounts will be included.
Save: Once the setup is complete, click on Save.
Prepare Worksheet: This will prepare the Statement of Cash Flows Worksheet (Excel file) based on the saved setup file.
Editing the Worksheet
Due to the required format(s) of the statement it will be necessary to edit the Excel file in certain areas.
Example one: Property and Equipment
When assigning the account number on the set up sheet for Property and Equipment, you will identify the appropriate asset account number(s). The program will then pull the beginning and ending values for those account(s) and populate the worksheet accordingly. The value in column E of the Excel file represents the increase/decrease of the beginning and ending values of those account(s).
Note: The net of four other amounts should equal the amount in column E for Property and Equipment. These amounts are:
- Loss on Disposal of Assets – If you have a revenue or expense account that you use to record the loss of an asset when disposed, enter that GL code in the Accounts field for this item. If you do not track the loss or gain in this manner, you must calculate that amount and enter it on Loss on Disposal of Assets line in the Operating column.
- Accumulated Depreciation on Disposal of Assets – (This description is in column J of the Excel sheet.) This amount represents the total accumulated depreciation for all assets disposed of during the fiscal year. Enter the amount on this line in the Operating column.
- Purchase of Property and Equipment – (This description is in column J of the Excel sheet.) This represents the value of all Property and Equipment purchased during the fiscal year. Enter the amount on this line in the Investing column.
- Depreciation – (This description is in column J of the Excel sheet.) This represents the YTD amount of your Depreciation expense account on the Agencywide Revenue/Expense Report. You can enter the expenditure code for depreciation under the setup and it will populate this field or enter the amount on this line in the Operating column.
Example Two: Long Term Debt
Long term debt is required to be broken down into two portions: 1) Current Portion of Long Term Debt, which represents the total principal amount to be paid in the first 12 months following the date of this statement and, 2) Long Term Debt Net of Current Portion.
As you are editing the setup sheet for this statement, on the line called Long Term Debt, enter the corresponding liability account number(s) for this debt. Then when you prepare the worksheet, edit the Excel file by adding the calculated amount of Current Portion of Long Term Debt line, and enter it in the appropriate cell, then adjust the amount in Long Term Debt accordingly.
When editing the Excel file, make sure you have the positive and negative signs correct. Following is a guide to follow:
Increase from Last Year Decrease from Last Year
Assets Negative Amount Positive Amount
Liabilites Positive Amount Negative Amount
Net Assets Positive Amount Negative Amount
Depreciation for FY Shown as a Positive Amount
Loss on Disposal of Assets Shown as a Positive Amount
Purchase of Assets and Equipment Shown as a Negative Amount
Accumulated Depreciation on Disposal of Assets Shown as a Negative Amount
Note: Once the Excel file has been edited and is complete, make sure you save the file as it is necessary to prepare the Statement of Cash Flows.
Print: When you click on print, you will be asked if you prepared the Cash Flows Worksheet. If the worksheet has been prepared and saved, click on Yes, then identify the saved Excel file. This will prepare the Statement of Cash Flows Worksheet.