Session 2: Chart of Accounts and Data Migration
Financials Accelerated Session 2
In this session, you will learn how to export data from your existing accounting system, reformat and import your chart of accounts into NetSuite.
Information will be needed from your Executive sponsor, and may be needed from your Accountant for the configuration settings chosen in this session.
Adding Standard Segments
Here is an overview of the segments we will build and what they will be used for:
Dimension | Purpose | Example Values |
|---|---|---|
Department | For expenses, indicates which function in the organization spent the funds. For income, indicates who earned the funds or what they were restricted for. | Development, IT, HR, Program |
Location | Used for physical inventory locations. Regions (Optional Custom Segment) will be utilized for Office Locations or Regions across the country. | Chicago, San Francisco |
Class | An additional standard segment to be used as required by the organization (i.e. Cost Center). No specific use is identified here as other segmentation requirements (identified below) will be handled with custom segments. |
|
Projects | Used for Grants, Fund-Programs and Internal Projects when it is required to track Scheduled Tasks, Time, Percentage of Completion, and Expenses. (This may require the purchase of the Project Management Module). |
|
Adding Custom Segments
Here is an overview of the segments we will build and what they will be used for:
Dimension | Purpose | Example Values |
|---|---|---|
Restriction | Determines allowed use of funds. Used to track fund restriction placed on donor or grantor funds. Examples of restrictions include temporarily restricted for use/purpose, unrestricted, permanently restricted, etc. This will be set at the transaction column/line level. | Unrestricted, Temporarily Restricted, Permanently Restricted |
Fund/Program | Used to track the organization’s funds - programs (i.e., initiatives and their children). This will be set at the transaction column/line level. | Program A, Program B, Fund A, Fund B |
Functional Expense | Used to track the functional area for expenses - include: Management and General, Fundraising and Program. Functional Expenses can be used to allocate expenses. | General Administration, Program Services, Fundraising |
Time Restriction | Used to set a use year on a revenue transaction. This segment is not a date-year but rather, a numeric representation of the year for reporting purposes only. | 2016, 2017, 2018, 2019 |
Grant | Specifies each Grant from a Funder. Used to track transactions by Grant; this will be set at the line level for both revenue and expense transactions. | Grant A, Grant B |
Revenue Type | Groups different income into streams | Contributions, Event revenue, Fee for service, Membership, Sale of goods |
Revenue Subtype | Refines list of Revenue Type | Cash Donation, Event Donation, Grant, Individual Membership, Program Service |
Region | Used if there is a need to track transactions by Regions or Offices (use Locations exclusively for Inventory). | United States, Illinois, Chicago, Wicker Park |
Department
This segment is used with your employee records to match your org chart and department structure to the financial transactions and reports.
To create Department and Sub-Department records, go to:
Setup > Company > Department > New
Enter “General Administrative” in the name field
Hover over the down-arrow on the blue Save button to show the Save and New option
Click Save & New
Enter “Executive” in the Name field
Select General Administrative from the Subdepartment of dropdown
Click Save & New
Repeat these steps to add the remaining Department and Sub-Department values, replacing the with your own.
General Administrative
Executive
Information Technology
Human Resources
Legal
Fundraising
Program Service
Program A
Activity 1
Activity 2
Activity 3
Program B
Activity 4
Activity 5
Program C
Activity 6
Locations
Location is available for tracking physical inventory and categorizing entities such as vendors, customers and employees. To create a new Location, go to:
Setup > Company > Locations > New
Enter “North America” in the Name field
Save and New
Enter your Primary Location in the Name field
Choose “North America” from the Sublocations of field
Click Save
NetSuite.org recommends the following structure for Locations. If your organization has multiple locations, continue entering them using the following structure as appropriate:
North America
California
Texas
Latin America
Europe
Africa
Congo
South Africa
Senegal
Asia
Custom Segments
NetSuite now enables the use of “Custom Segments” to further classify and report financial data. For our nonprofit grantees, we will add the following custom segments:
Creating Custom Segments
Restriction
To create a Restriction, go to:
Customization > Lists, Records and Fields > Custom Segments > New
In the Label field enter, Restriction
Check the GL Impact box
On the Values tab, enter the values below for Restriction (do not alter, this structure is need for US reports)
Unrestricted
Temporarily Restricted
Permanently Restricted
On the sub-tab Application & Sourcing
Check the following values on each tab:
Do NOT check any of the boxes on the Transactions tab, we will use the Transaction Columns to allow us to set these at the item level.
On the Transaction Columns tab, Mark all of the boxes for Application and then mark all under record type
On the Entities tab, check the box for Job (Project)
On the CRM tab- leave blank
On the Items tab, click Mark All
On the Other Record Types tab, check the box for Allocation Schedule Destination Line, Allocation Schedule Source Line, Budget Import and Expense Category
Additional Custom Segments
Repeat the steps in the Creating Custom Segments section above to create the following custom Segments and values:
Fund/Program
NOTE! You will need to create a Fund\Program value of "Unrestricted". You will use the "unrestricted" value on income transactions which are not restricted by the donor or expense transactions not tied to a Fund\Program. If you leave the field blank instead of using "unrestricted" it will be impossible to tell which transactions are truly "unrestricted" or are just missing the Fund\Program.
To create a Fund/Program, go to:
Customization > Lists, Records and Fields > Custom Segments > New
In the Label field enter, Fund/Program
Check the GL Impact box
On the Values tab, enter the values for your organization’s respective Fund/Program
Unrestricted
Fund A
Fund B
Fund C
Program A
Program B
And/or (decide best structure to represent your fund\program structure)
Unrestricted
Fund A
Program A
Fund B
Program B
On the sub-tab Application & Sourcing
Check the following values on each tab:
Do NOT check any of the boxes on the Transactions tab, we will use the Transaction Columns to allow us to set these at the item level.
On the Transaction Columns tab, Mark all of the boxes for Application and then mark all under record type
On the Entities tab, check the box for Job (Project)
On the CRM tab- leave blank
On the Items tab, click Mark All
On the Other Record Types tab, check the box for Allocation Schedule Destination Line, Allocation Schedule Source Line, Budget Import and Expense Category
Functional Expense
To create a Functional Expense, go to:
Customization > Lists, Records and Fields > Custom Segments > New
In the Label field enter, Functional Expense
Check the GL Impact box
On the Values tab, enter the values below for Functional Expense (do not alter, this structure is need for US reports)
Fundraising
Program Services
General Administration
On the sub-tab Application & Sourcing
Check the following values on each tab:
Do NOT check any of the boxes on the Transactions tab, we will use the Transaction Columns to allow us to set these at the item level.
On the Transaction Columns tab, Mark all of the boxes for Application and then mark all under record type
On the Entities tab, check the box for Job (Project)
On the CRM tab- leave blank
On the Items tab, click Mark All
On the Other Record Types tab, check the box for Allocation Schedule Destination Line, Allocation Schedule Source Line, Budget Import and Expense Category
Grant
To create the Grant custom segment, go to:
Customization > Lists, Records and Fields > Custom Segments > New
In the Label field enter, Grant
Check the GL Impact box
On the Values tab, enter the value for your organization’s respective Grants
Grant A
Grant B
Grant C
On the sub-tab Application & Sourcing
Check the following values on each tab:
Do NOT check any of the boxes on the Transactions tab, we will use the Transaction Columns to allow us to set these at the item level.
On the Transaction Columns tab, Mark all of the boxes for Application and then mark all under record type
On the Entities tab, check the box for Job (Project)
On the CRM tab- leave blank
On the Items tab, click Mark All
On the Other Record Types tab, check the box for Allocation Schedule Destination Line, Allocation Schedule Source Line, Budget Import and Expense Category
Revenue Type:
Customization > Lists, Records and Fields > Custom Segments > New
In the Label field enter, Revenue Type
Check the GL Impact box
On the Values tab, enter the value for your organization’s respective Revenue Types (we suggest sticking with these values, they are in line with standard reporting needs):
Contributions
Event revenue
Fee for service
Membership
Sale of goods
On the sub-tab Application & Sourcing
Check the following values on each tab:
Do NOT check any of the boxes on the Transactions tab, we will use the Transaction Columns to allow us to set these at the item level.
On the Transaction Columns tab, Mark all of the boxes for Application and then mark all under record type
On the Entities tab- leave blank
On the CRM tab- leave blank
On the Items tab, click Mark All
On the Other Record Types tab, check the box for Budget Import and Expense Category
Revenue Subtype:
Time Restriction:
Customization > Lists, Records and Fields > Custom Segments > New
In the Label field enter, Time Restriction
Check the GL Impact box
On the Values tab, enter the value for your organization’s respective Time Restrictions:
2015
2016
2017
2018
2019
2020
2021
On the sub-tab Application & Sourcing
Check the following values on each tab:
Do NOT check any of the boxes on the Transactions tab, we will use the Transaction Columns to allow us to set these at the item level.
On the Transaction Columns tab, Mark all of the boxes for Application and then mark all under record type
On the Entities tab- leave blank
On the CRM tab- leave blank
On the Items tab, click Mark All
On the Other Record Types tab, check the box for Budget Import and Expense Category
Accounting
This menu introduces several settings and options related to Accounting features. Many of the settings here aren’t required for basic accounting—they’re used for more advanced financial functions. This section describes only those basic required accounting setting.
Accounting Lists
Accounting Lists in NetSuite are essentially values that populate dropdowns throughout the system. The values can be changed and customized to suit your specific business requirements. The types of Accounting Lists include:
|
|
In order to properly account for payments received by cash or check, the Payment Methods must be set to Undeposited Funds.
To set the Payment Methods to Undeposited Funds, go to:
Setup > Accounting > Accounting Lists
Click Edit next to Check
On the Account tab, select Group With Undeposited Funds
Repeat this step for Cash payments
When we import data later, the Terms imported with Customer and Vendor records must already exist in NetSuite, and the verbiage must be identical in the Import file and NetSuite. If the import file has a term called “Net 30 Terms” and the version in NetSuite is called “Net 30,” the import will fail. As with any import where the value is already in NetSuite, the terminology must be exact.
To ensure that the Terms in NetSuite match the Terms you offer and that you may be importing later, go to:
Setup > Accounting > Accounting Lists
On the filter bar directly above the list, choose “Term” from the Type dropdown
Review the names of the Terms included in NetSuite
Review the name of the Terms used in QuickBooks
Click Edit next to any of the preset terms you would like to change
Make your changes and Save
To create new Terms in NetSuite, go to:
Setup > Company > Accounting Lists
From the Type dropdown choose Term
Click the New button
Complete the form and Save.
Expense Categories
Expense categories are not required to set up financials, but they can be helpful later. Expense categories are simple terms used on expense reports and bills that tie to the chart of accounts. Expense Categories will be used to populate Accounts and Department, Class and Location on revenue and expense transactions where users can select an item or expense category.
Example Expense Categories:
Airfare
Hotel
Meals
Membership/Dues
Mileage
Office Supplies
Shipping
Accounting Preferences
Important: This menu features options that are critical to your setup and need to be set accurately before adding transactions to the account.
General Tab
Use Account Numbers – Check this box. Account numbers are recommended for improved management of Accounts and Reporting.
Expand Accounting Lists – Check this box. By default, NetSuite displays only Income and Expense Accounts in Item or Transaction dropdowns. In order to post income and expenses to balance sheet accounts such as Unrestricted Net Assets or Deferred Liability, this box must be checked.
Cash Basis Reporting – Do not check this box. The self-implementation guide was written for organizations that are accrual-based.
The remaining settings are typically assigned or chosen based on organizational policy.
Items/Transactions Tab
The Duplicate Number Warning checkbox found at the bottom of this screen is an important feature that is sometimes disabled because of electronic payments made to multiple vendors. If your accounts payable department uses the same payment ID or confirmation number for multiple vendors, they may receive frequent warnings that the number is already in use. This box will disable that feature.
The Duplicate Number Warning feature may also be disabled on a per-user basis. Users may change this preference by going to:
Home > Set Preferences > Transactions
Uncheck Duplicate Warnings box
Chart of Accounts
The screenshot below lists the accounts and account types that are included with NetSuite when it is provisioned. Some accounts will vary based on the state or country in which your organization is based.
Important: Do not delete these existing accounts. Many are used in system workflows.
Integrating the Unified Chart of Accounts
During the process of setting up the Chart of Accounts, you will import a group of accounts that are based on the Unified Chart of Accounts (UCOA). [1]The Unified Chart of Accounts for nonprofit organizations is a chart of accounts freely available to any nonprofit organization, accountant, or consultant. The system is designed so that nonprofits can quickly and reliably translate their financial statements into the categories required by the IRS Form 990, the federal Office of Management and Budget, and into other standard reporting formats. In order to more easily take advantage of future NetSuite nonprofit product enhancements, we recommend you use the UCOA Chart of Accounts format.
Important: To learn more about the Unified Chart of Accounts, please visit http://nccsweb.urban.org/knowledgebase/detail.php?linkID=400&category=77&xrefID=1070&close=0.
The general process for setting up your Chart of Accounts in NetSuite is as follows:
During this session, we will rename the existing accounts. Following session 2, you will prepare your chart of account import files and import them.
To rename existing accounts, go to:
Setup > Accounting > Chart of Accounts
Click the Edit link to the left of the accounts listed below and add the numbers and change the names exactly as shown:
Original Name | New Name |
|---|---|
Checking | 1000 Cash |
Accounts Receivable | 1100 Accounts Receivable |