How to Use Statistical Accounts for Expense Allocations in NetSuite ERP

This article discusses the use of statistical accounts to perform expense allocations in NetSuite ERP.

Statistical accounts are non-monetary accounts that can be used to maintain statistical information for a variety of metrics. For example, a statistical account could be established to maintain the employee headcount by the department in an organization. This statistical account could then be used to allocate the total amount of expenses in a general ledger account for a given time period amongst various departments, for example, allocating rent expense amongst the departments by head count.  The statistical accounts functionality is included with the NetSuite Advanced Financials module.

Below we will discuss the following:

• Enabling features for statistical accounts
• Creating statistical accounts
• Viewing statistical accounts
• Creating statistical journal entries to establish the balances in the statistical accounts
• Performing expense allocations using statistical accounts

Enabling Features for Statistical Accounts

You must enable statistical accounts in the enabled features menu. Please note, by enabling the statistical account feature the multiple units of measure feature is automatically enabled.

If you want to perform statistical account allocation by financial dimensions (example: department or location), you must also enable and configure those dimensions.

Creating Statistical Accounts:

Statistical accounts are created in the same area as the other general ledger accounts: Setup > Accounting > Chart of Accounts. Again, please note that statistical accounts are not general ledger accounts.

1. Name the account based on the metric you will be reporting in the account (example “Head Count”).
2. Set the type field to "Statistical".
3. Once you select the "Statistical” value in the type field you will notice that two new fields appear relating to the “units of measure”:
   • Unit Type
   • Default Unit
4. In our example, we will select a unit of measure of “Each.”

Viewing Statistical Accounts:

To view the statistical account, navigate as follows:

Setup > Accounting > Chart of Accounts

Next, set the “View” filter in the upper left-hand corner of the screen to show all accounts or statistical accounts.

If you click on the name of the account, you will be directed to the account register which shows the balance of the account over time.

Creating Statistical Journal Entries:

Statistical accounts are populated with balances and information by using statistical journal entries. Statistical journal entries can be established to increase the balance of the account or decrease the balance of the account.

Navigate to Transactions > Financial > Make Statistical Journal Entries.

When creating the statistical journal entries, you may notice it is different from a standard journal entry form in that it is one sided. This means it does not have the debit and credit columns. With the one-sided journal, we instead have a single column for an amount that can be positive or negative.

The first step is to enter the unit of measure, which then determines what statistical accounts we can select on the form.  Next enter the date, which will determine when the balance of the statistical account is changed.

To adjust the balance of the statistical account we can either record the incremental change in the statistical account or we can use a feature called “Absolute Update.” Absolute Update is a checkbox on the statistical journal entry form (seen in the image below). The absolute update overwrites all previous values for the statistical account with the desired ending balance for the statistical account the specified date.

For example:

On 6/30/2016 you enter a statistical journal entry for specific balances:

• 5 head count for the Accounting department, 8 for the Admin department, and 3 for the Sales department.
  

On 7/31/2016 your company has grown and you need to update the department headcounts to the new balances below:

• 8 head count for Accounting, 10 for Admin, and 3 for the Sales department.
  • Incremental Method - Enter a statistical journal entry for:
    • 3 Accounting department (giving a new total of 8)
    • 2 Admin department (giving a new total of 10)
      • 0 for Sales department (new total of 3 – unchanged)
    • Absolute Update Method – check the absolute update box and Enter a statistical journal entry for:
      • 8 head count for Accounting department
      • 10 for Admin department
      • 3 for Sales department

Performing Expense Allocations using Statistical Accounts

NetSuite can leverage the balance information from the statistical account to perform expense allocations using allocation schedules functionality.  The allocation percentages on the allocation schedule will be weighted based on the balance of that statistical account.

Below, we will continue our example of allocating an expense to departments based on headcount.  To perform this the following features must be enabled: Expense Allocations and Accounting Periods.

Understanding Expense Allocations:

Expense allocations are managed by “Allocation Schedules”, which are used to allocate or transfer balances from expense accounts into one or more other accounts. Allocation schedules distribute expenses across departments, locations, classes, and/or custom segments.  This allows the user to set a date range and frequency of the allocation schedule to be performed.

Creating Expense Allocation Schedules:

To setup Allocation Schedules navigate to the following:

Transactions > Financial > Create Allocation Schedules

Allocation Body Fields

• Frequency – How often Schedule is to be processed.
• Next Date – Next date the allocation is to be processed.
• Subsequent Date - Enter the date of the following allocation.
• Remind forever vs number remaining – Select as desired, this is similar to memorized transactions.

Allocation Mode - Fixed rate vs dynamic allocation.

• Fixed Rate – user is able to define the percentage allocation amounts and therefore does not require a statistical This is useful, but not the topic of our discussion.
• Dynamic Allocation – Once selected a number of fields will become available for population.
  • Weight Source – Select the statistical account the dynamic allocation is based upon.
  • Date Basis– Chose the method in which NetSuite should aggregate the statistical journals values for the weight calculation. Options include:
    • As of Date – The system sums all statistical journals from the beginning date to the day before the date you enter in the Next Date
    • Period to Date – The system sums all statistical journals from the first day of the Accounting Period to the day before the date you enter in the Next Date field.
    • Quarter to Date – The system sums all statistical journals from the first day of the quarter to the day before the date you enter in the Next Date
    • Year to Date – The system sums all statistical journals from the first day of the year to the day before the date you enter in the Next Date

Source Subtab Fields

Credit Account – When allocating from a source account to a destination account you have the option to automatically repost that amount back to the source account (see example below).  If you want to create an offsetting credit, select the account for credit to post to.

• If you do choose a Credit Account: The schedule creates an offsetting credit to balance the allocation so the amount remains in the source account and is not transferred out of the source account by the allocation journal entry. The schedule adds the amount to the destination account for reporting purposes but allocates the actual balance back in the source account. This enables you to look at the expense multiple ways on the Income Statement.
  • For example, you can allocate portions of the rent expense (from the source subtab) account into the rent expense with several department segmentations (destination subtab) and then select the credit account as rent – This will allow you to view expenses by department for budgeting or planning, but leaves the actual expense amount in the source account.
• If you do not choose a Credit Account: The schedule re-classes the balance of the source account when the amount is transferred into the destination account by the allocation journal entry because an offsetting credit is not created. Meaning, the general ledger impact is shifted to the destination account (i.e. the source account is zeroed out).
• If you do want to use a credit account, the following fields become available for population:
  • Credit Name - link credit to a project or customer entity record.
  • Credit Department | Credit Location | Credit Class (separate fields) – Associate the offsetting credit with a department, class, and location by selecting them in the Credit Location, Credit Class, and Credit Department fields.
  • Column Fields:
    • This is the main section users will want to address in the source tab as this defines what criteria NetSuite will be required when assessing the source account/segment for the allocation schedule. Select the account and segment information you would like to aggregate and allocate for the given period in the lines.
    • If this schedule pertains to multiple account/segment combinations, enter that information here.

Destination Subtab Fields

The method of allocating expenses depends on allocation mode in the body section (fixed rate or dynamic allocation).

• Fixed Rate:
  • “Values are percentage” check box – If the box is checked, you can allocate to various accounts, departments, locations, classes based on the percentage set on each line.
    • Normalize – Makes the total weight add up to 100%
    • Even Spread – Makes all the weights equal.
  • “Use Source/Credit Accounts” - If you want to allocate to custom segments, departments, classes, and locations within the source account, check the Use source/credit accounts If you check this box, you cannot choose destination accounts because the allocation distributes the expenses among custom segments, departments, classes, and locations rather than different accounts. To clarify further, this means the allocation will pull the information from the lines of the source subtab and allocation to the credit account and segment fields selected.
• Dynamic Allocation:
  • In the columns/lines, enter the various accounts you want to allocate to. Enter the various departments you wish to allocate the expenses to here as well.
  • If used, an “Update sample Weights” button appears. Click this button for sample weight and a balance to populate on each line.  Sample weight is the percentages to allocate and the balance displays the balance of your statistical account (contains balances of the statistical head count generated from the journal above).

To create the journal from the allocation schedule, click “create journal” button at the top of the allocation schedule.

In the below image, we are allocating rent expense of $21,000 to the rent expense account based on departmental headcount established in our statistical account. Under the history subtab of the allocation, you can view the journal entries created from the allocation schedule. You can setup reminder for allocation schedules due as well on the dashboard.

FMT is Here for You

If you have any questions or would like to learn more about NetSuite, FMT is here for you. Simply contact us using the form below and we’ll be in touch soon!