What are statistical accounts?
Statistical accounts contain non-monetary data – such as headcount, hospital beds, square footage, or hotel rooms - that allow you to establish and maintain your operational data and combine it with financial data in your financial reports.
Statistical accounts serve as the primary drivers used to calculate against and report on key non-financial business metrics. For example, a statistical account can be established to maintain the employee headcount in a department in your organization. You can then use this statistical account to allocate the total amount of expenses in a GL account for a given time period amongst various departments, such as allocating rent expenses for different departments by headcount.
Why work with statistical accounts?
Statistical account support in SIP not only addresses an industry-standard functionality, but allows you the ability to budget, control, and manage non-financial business metrics that can help your company strategy and operational work.
Since statistical accounts are used to track non-monetary data, such as unit count, or price per unit, and aren’t directly presented as GL accounts on any financial statements, using statistical accounts in your calculations and reporting provides invaluable insight into your business that may otherwise be invisible. These data calculations can later be used to create your GL account data. Tracking key metrics with the use of statistical accounts also allows you to perform sophisticated analyses in financial reports.
How to work with statistical accounts
Statistical accounts are supported and represented in SIP in the form of models. The statistical models and model lines can be created manually.
When you create statistical accounts, you essentially map them to model lines for use in your statistical account models. These models are actually representations of your statistical accounts and contain the model lines you create based on your statistical accounts, which can then be split according to their dimensions.
The statistical account (represented as models) can be used in other model calculations to result in a currency value. These values can then be projected on your budget tree and embedded in your budget as budget lines.
Statistical accounts, like P&L and Balance sheet data types, are visible across various areas of SIP like actuals, inputs, reports, and more. SIP also simplifies the way you manage all of your accounts from the Accounts mapping table in the Actuals > Mapping tab.
As with P&L and Balance sheet data, you can also import statistical actuals data and use the actuals data for budget comparison in your Sheets view.
Working with statistical accounts is explained in more detail in this article.
Create your statistical accounts and use them in models
Inputs > Model tab
Create your statistical accounts model lines in the Statistical Accounts section of the Inputs > Model tab. You can customize and reorganize your lines not only to improve readability but to improve the manner in which you work with models and statistical accounts. See the Customize your model line article to learn more.
Models and statistical model groups
The tree view of the Inputs > Model tab is divided into 2 sections that allow you to distinguish between general model lines and the statistical account lines in your budget. The general Models section consists of your models and model groups.
The Statistical accounts section consists of model groups (not mandatory), models, and model lines. The recommended usage is as follows:
Statistical account model groups (not mandatory) - a group of accounts associated with a specific dimension
Statistical account models - the statistical account
Statistical account model lines - the statistical account represented in the model that’s divided into the respective dimensions
For example:
Statistical account model groups represent your organization’s location (such as New York, London, or Rome)
Statistical account models represent your statistical account (such as headcount)
Statistical account model lines representing the headcount for each department in your location (such as the R&D department in Rome)
📝 You can drag and drop model lines and statistical accounts within their respective sections, but not from one section to another.
Manually create statistical account models and model groups
To create a statistical model manually, click on the plus (+) sign of the Statistical accounts section from the Inputs > Model tab. Select Add new or select Add new group and type a name for the statistical model or model group.
When you add a model group, the + Add model button and Dimensions section are displayed. Once you add models to the model group, the models are displayed in a preview pane, along with the model lines they contain.
If you define dimension values at the group level, the values are automatically inherited by the models you add to the group.
Manually create a statistical account model line
To manually add a statistical account model line, select a model in the Statistical accounts section and click + Add model line. A flyout window opens where you can name, define and customize your model line.
Work and add statistical accounts to your models and incorporate them into your budget
If you want to base your model line calculation on a statistical account, click the fx icon to display the formula editor, or press the equals key (=) on your keyboard. In the formula editor window, you first see the model section with their assumptions and model lines available in your current model (the model that you’re working in, or “this model”), and then the statistical accounts section.
Click on the labels to create your formula. A green checkmark (✔) appears to the right of your equation to show that your formula is valid. If the formula is not valid, the checkmark appears gray. If you want to delete the formula, click the X left of the equation to clear it.
Now you can add your model line to your budget. When doing so, the budget line appears as a monetary value.
See Use the formula editor in models to learn more about how you can use the formula editor to calculate values in assumptions and amounts in model lines.
Work with statistical accounts and models in the Actuals tab
Actuals > Mapping tab
From the Actuals > Mapping > Accounts tab, you can add and view your statistical accounts in the Statistical tab. The accounts mapping table lets you map your statistical accounts to the model lines that appear in your budget in SIP. For each mapped account, the following information is displayed:
Account
The account that appears in Sage Intacct.
Account description
A description of the account received from Sage Intacct.
Mapping type - the following options are available:
Single model line (default) — Actual amounts are imported to a single budget line in SIP.
Multiple model lines — Actual amounts can be split across multiple budget lines according to the dimensions you choose.
No mapping — Actuals in this account are imported, but won't be mapped to any budget line.
Model lines (for Statistical)
Map your accounts to Model lines from the dropdown that appears in SIP. If you've chosen No mapping, the model line field will remain blank.
📝 The same model line can't be selected for more than one account.
Reporting type
The following options are available:
For the period — Reports the net change for the period, similar to how income and expense amounts are reported (Sum).
Cumulative — Reports the balance of the account for the period, similar to how balance sheet amounts are reported (Last).
Dimensions
If you've selected Multiple model lines as the Mapping type, you can select one or more dimensions from the dimensions you have in your budget.
Keep in mind that mapping is unique for model lines and the same model line can’t be mapped more than once. So, once an account is mapped to a model line (or a few model lines), other accounts can’t be mapped to the same model line.
Additionally, if you choose to duplicate a statistical account line, the line in the table is duplicated without a mapped model line. Instead, you’ll need to manually map the line to a model line appearing in the dropdown.
Add statistical accounts to your budget
Statistical accounts can be created manually. You can map them directly to model lines when you create a statistical account.
Manually create a statistical account
1. Begin from the Actuals > Mapping tab. Click + Add account and select Create an account.
2. Add your account number and description and select Statistical account from the Account type field.
3. Select the Mapping type. The following options are available:
Single model line (default) — Actual amounts are imported to a single model line in SIP.
Multiple model lines — Actual amounts can be split across multiple model lines according to the dimensions you choose.
No mapping — Actuals in this account are imported, but won't be mapped to any model line.
4. Select a model line or lines from the Model lines dropdown. Once an account is mapped to a model line (or to a few model lines), other accounts can’t map to the same model line. That is, mapping is unique for model lines, and model lines can’t be used more than once.
5. The Report type lets you decide how you want to report the per-period amounts on your financial reports. Once you’ve mapped a statistical account to a model line, the reporting type automatically influences the Period value of the model line. For example, if you select For the period, the model line period value becomes Sum. If you select Cumulative balance, the model line period value becomes Last.
From the Report type dropdown, select one of the options:
For the period: Reports the net change for the period, similar to how income and expense amounts are reported (Sum).
Cumulative balance: Reports the balance of the account for the period, similar to how balance sheet amounts are reported (Last).
📝 The Period value can be manually changed, but if you change the Reporting type of the account, the Period values are automatically updated accordingly in the model line.
Actuals > Details tab
The Actuals > Details > Model tab displays the transactions that are relevant to your statistical accounts. Once a transaction is added to the Model tab, the transaction can’t be moved to the P&L and Cash and Balance sheet tabs.
The Unit type in a model transaction represents the unit of measure of your data, such as the area in square footage, a currency that includes USD or EUR, a number, decimal number, or percentage. The Unit type value is determined by the model line to which you map your transaction. If the transaction isn’t mapped, the unit type remains empty. If you change the unit type of the model line, the unit type is automatically updated in the Actuals > Details tab along with its calculations.
📝 If a loop exists between two (or more) model lines, the unit type remains empty and the transactions aren’t calculated. For more information on identifying and resolving loops, see What should I do if I encounter a loop?.
When the unit types have the same value for all Model transactions, such as the same currency, or a number or decimal number value, a total of the transactions appears at the bottom of the window. If the unit types are different or if they are percentages, then the total of the transactions doesn’t appear.
Add model actuals
You can manually add actual transactions to your models or import transactions from Excel from the Details tab.
Manually create actual transactions
To manually add a single transaction:
1. Click + Add manually from the Add transactions dropdown in the upper-right corner of the Actuals > Details > Model tab. When you manually add transactions, the transaction appears in the table of the tab you selected (for example, P&L & Cash, Balance sheet, or Model).
2. In the Model table, enter the description of the transaction, the account number that represents the model transaction or balance, the month of the transaction, amount, unit type, and model line mapping.
3. Select or multi-selecting allows you to perform different actions on one or multiple transaction lines, such as duplicate, reverse, or delete.
Import actual transactions from Excel
📝 Before you begin, download the Model template from the Add transactions dropdown on the Actuals > Details tab. Your actuals must be set up in the Excel sheet according to the template. See Import model actuals from Excel for more detailed information.
To import transactions from Excel:
1. Click Add transactions > Import from Excel. When you import transactions, the transaction appears in the table of the tab you selected (for example, P&L and Cash, Balance sheet, or Model).
2. Drag your Excel file to the upload window or click on the link to browse for the file.
3. Select either the Model transactions or Model balances template, depending on which type of transaction you want to import.
4. In the Setup, select your header row and title column and click Next.
5. In the Data-Columns, click on the dropdown of each data column you want to import, map to the correct column type, and click Next.
6. From the Data-Rows, select the lines of actuals you want to import. The Unit type of each transaction is determined based on the line to which the transaction is automatically or manually mapped.
7. Preview your data for import and make any relevant changes.
8. Click Import.
In the Inputs > Model tab, you can show your lines with Budget, Actuals, or Rolling data. The Rolling view displays a combination of your actuals data (if actuals exist for previous months) and your budget in an updated view of the budget period. A sub-header for each month indicates if the column contains actuals or budget data.
📝 Displaying your model lines with actuals or rolling data is only available if your budget contains actuals data.
Actuals > Summary tab
Use the Model tab from the Actuals > Summary tab to see a summary of your models and statistical accounts. Like in the Cash, P&L, and Balance sheet options, you have the option to click on the Actuals column of the months that have actuals and select Close month, Remove column, or Exclude from forecast from the Model tab.
The statistical account tab that appears in the Inputs > Model view is displayed as a statistical accounts Model group at the end of the model list in the Actuals > Summary > Model tab. The models and models lines appear in an identical format to that of the statistical account tab in the Inputs > Model view.
View your statistical accounts and models in the Sheets tab
You can see the combined views of your actuals and budget data from the Sheets > Model tab. See Work with sheets for more information on budget reports and side-by-side comparisons of your different budget versions and scenarios.
From the Sheets > Model tab, you can see the same display of models and model lines under the Statistical accounts model group as displayed in the Inputs > Model tab.
Your model data in the Sheets > Model tab can be filtered to display your actuals data by Budget vs. Actuals or Forecast vs. Actuals in addition to the other filter options available in SIP. See Work with sheets to learn more about the side-by-side comparisons available for different budget versions.
When you filter data for your working budget, for example, you can select to display your data by Budget only, Rolling, or Budget vs. Actuals. If actuals exist for your budget, a combination of your budget data and actuals data is displayed and updated as new actuals data becomes available and added to SIP. See Get a combined view of your actuals and budget for more information.
📝 The Rolling and Budget vs. Actuals views are available only when there is actuals data (for the budget date range) in the budget.