Default report formulas

 

Please note that the Default report formulas is currently only available in those Procountor environments that have the new chart of accounts functionalities in use. In most environments these functionalities are already in use, but the current estimate for updating these functionalities to the rest of our customers is during the summer 2022. 

Default report formulas allows the editing the Procountor environment-specific default report formulas used on the Accounting reports and as the income statement and balance sheet of the Closing of accounts tools. To open the Default report formulas view, go to the main menu and click Management > Accounting info > Default report formulas and select either Income statement and balance sheet or Other default reports.

This example shows the Income statement and balance sheet view. The Other default reports view applies the same logic and functions the same way as the income statement and balance sheet editing view.

If needed, rights to access and edit the Default report formulas views can be determined for each user with Chart of accounts and default reports setting of the user rights.

 

Overview

 

The default report formula to be edited is selected by clicking either the Income statement or the Balance sheet tab. The Income statement tab is selected by default. To edit the contents of the default report formulas, either

• Edit the rows and columns or each formula directly, which Is explained further below, or
• Edit the formulas as text through the Edit as text button, which is explained further here.

At the right side of the view is a section called Account list, which is used to edit certain types of report formula rows. More detailed information about this section can be found here.

 

Default report formula version history and restoring

Default report formulas may be edited several times. This view contains a few key functions where these changes can be tracked and, if necessary, restored to a previous version or to the unedited Procountor default report.

The Save button in the top left corner has two options: Save and Save version as. If the changes made to the default report formula are saved normally with the Save option, the changes are saved to the version history with a time stamp and the name of the user who made the save. If the report is saved with the Save version as option, the version history will show the version name and description.

The Version history button located at the top of the view shows the income statement version history:

 

 

Current version shows the current version of the report. History versions contains all the previous versions of the same report, each on their own row. All the rows show the time stamp and the name of the user who saved the report. If the version was saved with a specific name and a description, these will be included. Please note that the earliest version (row 1) is always the unedited Procountor default report formula. This row is saved in the version history when the environment is created. If there is a need to restore the default, unedited report formula, it can be done by restoring the earliest version in the version history.

Earlier report versions can be restored by clicking the Open preview after the version row is first selected. This opens a separate preview that shows the contents of the selected version. The version is restored by clicking the Use this version button at the top. This button opens a confirmation:

 

 

The restored version adds a new row to the version history, where the Additional information column shows the version number from which this version has been restored.

 

 

Unedited income statement or balance sheet can also be restored through the View Procountor defaults button that opens the preview of the default report. The Restore to Procountor defaults button restores the report to the unedited default report in the exact same way as restoring it through version history. After restoring defaults, the version history will show that the current version was restored through the Procountor defaults.

 

 

Table buttons and options

The default report formula editor contains the following buttons and options:

• New row is added with the Add row button. The new row is added underneath the row that was selected before clicking the Add row button.
• A selected row can be deleted with the Delete row button or copied with the Copy row button.
• Move up and Move down buttons move the selected row up and down.
• Show / hide graphical report KPI selection determines if the KPI column will be shown in the table. KPI’s are Key Performance Indicators used on Management reporting and Management reporting by dimension, and these indicators can be edited through the default report formulas. More information and examples on how to make KPI selections can be found here. When the KPI column is selected, the following options are shown:
  • KPI1
  • KPI2
  • KPI3
  • KPI4
  • Assets
  • Liabilities
• Show / hide translations shows and hides the fields for translations to different languages.
  • These columns show translations for all rows that are included in the default chart of accounts.
  • If the default translations are changed, the new translation will be used as the primary translation when reports are generated in different languages.
  • When reports are created in different languages, the name of each report row is generated by the corresponding translation set in the column of different languages, and not by the general row name set in Name column.
  • Please note that if the default report formulas include accounts or titles that differ from the default chart of accounts, these will not be automatically translated, and the fields left blank as they do not have existing translations. These translations must be manually filled out.
• Change number signs for account values? selection changes the accounting values on the report to opposite values.

 

Table columns

The default account report formula rows can be edited through the following columns:

• Row column indicates the row number. First row is R1, second R2, third R3, and so on. The Row column identifiers (such as R1, R2, and R3) can be used in the Formula column to create formulas that connect to or reference other rows on the report.
• Name column is for selecting a name for the row.
  • When reports are created in different languages, the name of each report row is generated by the corresponding translation set in the column of different languages, and not by the general row name set in Name column.
  • Default report formula's language is determined by user's chosen language for the software. If the user has chosen for example English as language, default report formula is in English.
• Indentation column determines how much indentation the row will have. Indentation value is selected from numbers 1 to 6, where number 1 means the row will have no indentation, and number 6 means the indentation is the largest possible. By selecting different levels of indentation, the report can be structured in different ways.
• Type column determines the row type:
  • Title selection means the row is a report title row that will not have any balance. These types of rows can be, for example, the Assets or Liabilities title of the balance sheet.
  • Blank selection creates a blank row that will not have any content. This type of row will show up as an empty row on the report. These empty rows can be used to structure the report in a certain way, if for example certain parts of the report need to be separated by empty lines.
  • Balance selection means the sum of the selected accounts will be shown as the row balance. The accounts can be selected either
    • directly from the Formula column by using the [XXXX:XXXX] format account selection or
    • from the right-hand side Account list section, which is explained further in its own section.
  • Row function selection can be used to create functions related to other rows in the Formula column. More specific examples of row functions are introduced later in its own, separate section.
• Hide column checkbox hides the selected row from the report.
• Bolded column checkbox bolds the selected row on the report.
• Hide if zero checkbox hides rows that have zero balance. If for example a certain account has no balance, and this selection is checked, this account row will not be shown on the report.
• In default length determines if the row is to be included in a default length report. The report length can be determined in the Accounting reports search criteria.
• In short length determines if the row is to be included in a shortened report. The report length can be determined in the Accounting reports search criteria.
• Invert checkbox inverts the row value from a negative to a positive value, or vice versa.
• Formula column determines how the row balance is formed. The Formula column can include, for example, certain account numbers in the format [XXXX:XXXX], meaning that the row balance is the sum of these selected accounts. the Formula column can also include references to other rows of the report, and the referenced rows are shown e.g. in the following way: [R21:R25]. More detailed information about the calculations and applications enabled by the Formula column can be found in this section.
• CoA hierarchy level is only visible on the Income statement and balance sheet view underneath the Default account report formulas selection. This column is explained more closely in the following section.

 

CoA hierarchy level

CoA Hierarchy Level column determines the hierarchy levels in the chart of accounts visible on the Chart of accounts (new) view. Accounts can be displayed either hierarchically or as an account list on the Chart of accounts (new) view, and a row-specific drop-down menu is used to select the suitable hierarchy option for each row. Only options that fulfill the following rules are shown in the drop-down menu for each row:

General rules for hierarchy selection:

• A row will become a parent row to a consecutive row below if one number higher value is selected in the CoA Hierarchy Level column of the consecutive row below. Therefore, all children rows of a parent row have one number higher CoA hierarchy value than their parent row. For example:
  • Row number 1: selected value in the CoA Hierarchy Level column is 1. This row is a parent row for row number 2.
  • Row number 2: selected value in the CoA Hierarchy Level column is 2. This row is a child row for row number 1.
• Hierarchy is always determined from top to bottom. This means that, for example, row number 5 cannot be a parent row to any row above it (for example, rows 3 and 4). Row number 5 can be a parent row only to rows below it (for example, rows 6 and 7). However, there is one exception to this with rows that have the selection of Row function in Type column (see more details below).
• Hierarchy level 1 is set on default for the following rows (that cannot be edited):
  • The first row of the income statement
  • Assets / Liabilities row of the balance sheet
• If Empty has been chosen as the row type in Type column, the only possible selection is Not in CoA.
• If a row is hidden by activating the selection in Hide column, the only possible selection is Not in CoA.
• If Balance has been chosen as the row type in Type column, the row cannot be a parent row for any rows below it.
• If Balance has been chosen as the row type in Type column, the row will automatically be labeled as Not in CoA if the selection in Hide column is activated for the row.
• If Title has been chosen as the row type in Type column, the row must have a child row below it and the child row must have the selection of Balance in Type column.

Rules for rows with the selection of Row function in Type column:

• The rows a row function is referring to in Formula column must be children of the row function row. Other rows (that are not in the row function) cannot be children of the row function row.
• Row function row must always have at least one child row that has Balance set in Type column.
• If a row function refers only to rows with selection in Hide column, Not in CoA must be selected in the CoA Hierarchy Level column for the row function row.
• If a row function refers only to rows above the row function row, Not in CoA must be selected in the CoA Hierarchy Level column for the row function row. Basically, this means that the row function row is still a parent row for the rows above it (since the row function refers to those rows above, and the rows that the row function is referring to must be children of the row function row) even though generally a row can be parent row only to rows below it. In another example, if the rows a row function is referring to in Formula column are located both above and below the row function row, the value in CoA Hierarchy Level column of the row function row can be, for example, 2 (and the corresponding value in CoA Hierarchy Level column of the balance rows above and below the row function row that are referred to in the row function can be normally 3, in accordance with the basic child row logic).
• If row function reference to a certain child row is removed from row function row’s Formula column, the selected value in the child row’s CoA Hierarchy Level column is also simultaneously automatically removed. If the row reference of this child row is later added back to the Formula column of the row function row, the previously seen value appears again in the CoA Hierarchy Level column of the child row.

 

Account list section

 

Report formula editing view has a section called Account list on the right-hand side. This section is active and available when the selected report row has Balance selected in the Type column. The Account list will be inactive if the selected report formula row has a row type other than Balance.

Accounts and account groups are selected to the balance rows in the Account list section. If accounts belonging to a certain account group are added to the chart of accounts after creating the report formula, they will be automatically included in the balance of that row.

There are two options in the Account list section that affect the selection of accounts: Use expandable ranges and Use exact ranges:

• Use expandable ranges means the account or account range selected from the account list will include all existing accounts and accounts created in the future in the selected account range, as well as all future accounts that are created in this account range with an account number that is greater than the last account in that account range, and before the next account in the chart of accounts.
  • Example 1: Only one account is selected from the account list, account number 4000. The next account in the account list is 4090. When Use expandable ranges option is selected, selecting account number 4000 will create the formula [4000:4089]. This means that if a new account is created within this range, for example account number 4010, this would be included in the formula.
  • Example 2: Accounts 4000, 4090, and 4110 are selected from the account list. When Use expandable ranges option is selected, selecting these three accounts creates the formula [4000:4129]. Because the next account following account 4110 is 4130, the expandable formula is created in such a way that all accounts that are possibly created after 4110 (all the way up to account number 4129) would be included in the formula.
• Use exact ranges selection means the accounts or account ranges selected from the account list will not include any accounts besides the existing ones, and the ones possibly created in the future in this account range.
  • Example 1: Only one account is selected from the account list, account number 4000. When Use exact ranges option is selected, selecting account number 4000 will create the formula 4000.
  • Example 2: Accounts 4000, 4090, and 4110 are selected from the account list. When Use exact range option is selected, selecting these three accounts will create the formula [4000:4110]. If an account is created in the chart of account, for example 4050, it will be included in this formula. Any accounts added to the chart of accounts after 4110, for example 4115, will not be included in the Use exact ranges type formula.

There is a Selected formula field at the bottom of the Account list section:

 

 

When Balance is selected from the Type column, the contents of the Formula column can be edited within the column itself by using the [XXXX:XXXX] format account selection. After editing, the formula is updated with the Save -button. These changes can also be made in the Account list section, in the Selected formula field and using the Update formula button.

When accounts are selected from the accounts list, the contents of the Selected formula field will update according to the selection. After the selections have been made, the formula is updated to field in the Formula column with the Update formula button.

The contents of the Selected formula field update automatically when accounts are selected from the list, so creating a formula is the easiest using the account list. The field contents can be manually edited; in this case, typing in a formula in the Selected formula field automatically selects the corresponding accounts from the Account list. When the contents are updated manually, the number on the left-hand side must be greater than the number on the right. If the field contains errors, it will be highlighted in red. Hovering the cursor over the field shows the more detailed reason for the error.

The field contents can be formed in the following ways (when Use exact range is selected):

• If a unified account range is selected (and, for example, some accounts are not skipped) the formula is in the format [XXXX:XXXX].
• If an account range that skips certain accounts is selected from the list (in other words, two separate account ranges have been selected from the list) the formula is in the format [XXXX:XXXX]+[XXXX:XXXX].
• If an account range along with a single account is selected, the formula is in the format [XXXX:XXXX]+XXXX.
• If only a single account is selected, the formula is XXXX.
• If two single (but not consecutive) accounts are selected, the formula is in the format XXXX+XXXX.

The Selected formula field contents differ from the above-mentioned, for example, when Use expandable ranges is selected. In this case, selecting a single account would create the formula in the format [XXXX:XXXX], since the formula will include any accounts that may be created in the future with an account number that comes after the selected account, and before the next account in the chart of accounts.

When Use exact ranges is selected, singular, non-consecutive accounts that do not form an account range in the [XXXX:XXXX] format can be selected in the formula. If, however, individual accounts  in consecutive order are selected, the account range in the [XXXX:XXXX] format will form automatically according to the selected accounts. This means that if an account that belongs to this account range is added to the chart of accounts in the future, this account will be included in this formula.

• If the intention is to create a formula that does not form the [XXXX:XXXX] type formula when consecutive accounts are selected, follow these steps: fill out the necessary consecutive accounts in the Selected formula field separated by plus signs. For example, 4000+4090+4110. The formula is updated in the Formula column when Update formula button is clicked. If an account, such as account no 4005, is added later to the chart of accounts this will not be included in the formula, since it only consists of singular, already existing accounts.

If you need to make changes in the chart of accounts, select View chart of accounts at the top of the page to edit the accounts.

 

Calculations with row functions

Different calculations can be created on the rows when Row function  is selected from the Type column of that row. In this case, calculations can be created in the Formula column in the following ways:

• Addition, subtraction, multiplication, and division of other rows and row ranges. The Row column information is used as row identifiers (R1, R2, R3 and so on). If, for example, one report formula row needs to be divided by another row, these rows are entered in the Formula field as follows: R1/R2. If two row ranges are to be added together, these are entered in the Formula column as follows: [R1:R5]+[R10:R15].
• Addition, subtraction, multiplication, and division of other rows and row ranges that utilize manually entered sums, multipliers, or divisors. To add a certain sum to a report formula row, enter the row and sum in the Formula column as follows: R20+32500. To divide a row interval with a certain number, enter these in the Formula column as follows: [R6:R15]/2,5.
• Addition, subtraction, multiplication, and division that do not relate to other rows or row ranges, and different sums, multipliers and divisors are entered in the Formula column manually.

Please note that direct references to certain accounts or account ranges cannot be made in the Formula column by using the account number (such as 4000/2):

• All values added to the Formula row are considered normal integers, and the result of the previous example would always be 2000, and not based on the balance of account number 4000.
• To add a row that divides the balance of account 4000 by two, it must be done by referring to the row number (for example R5/2).
• If the report formula does not yet have a row that only contains account 4000, this can be added by selecting Balance in the Type column and adding just account 4000 to the Formula column (using the Account list section on the right-hand side).

The calculations on the row function row Formula column can include references to other rows (e.g. R3), references to row ranges (e.g. [R10:R20]), plus signs (+), minus signs (-), multiplication signs (*), division signs (/), parentheses () and numbers up to four decimal places (e.g. 12500,1663).

 

KPI selections on the graphical reports

Please note that, the KPI selections are only available on the Default report formulas views. Even though KPI selections can be made on custom report formulas on the Accounting reports view, these will not transfer to the graphical reports via the custom report formulas.

New versions of the graphical reports have been created along with the chart of accounts updates, even though their basic functions remain the same. 

The KPIs are Key Performance Indicators used on the Management reporting and Management reporting by dimension and can be modified through the report formulas as needed.

• Income statement reports include four different KPI options corresponding to the KPI options of these specific reports.
• Balance sheet includes the Assets and Liabilities options that determine the Assets and Liabilities balances displayed on the graphical reports.

Each KPI selection (KPI1, KPI2, KPI3, KPI4, Assets, Liabilities) should only be selected on a single report in a certain Procountor environment. If the KPI selections are made in the Default report formulas to the Income statement and Balance sheet, the graphical report KPIs will come from these reports. If KPIs were to be selected on custom report formulas created later on, the graphical report KPI selections would still be determined by the Default report formulas KPI selections. The Default report formulas is the only place where to determine KPI selections. 

When a KPI is determined, for example, a row named Salaries and wages, the row name will be shown on the graphical report along with the corresponding KPI value. The KPI boxes visible on the graphical reports can therefore be freely modified by editing the row names on the report formula.

 

Editing the report formula as text

Editing the report formula as text is a practical solution for when a report formula created in Procountor environment A needs to be moved to Procountor environment B:

• Click Edit as text in environment A. The report will be shown in text format in the opened window.
• Select the entire contents of the window (Ctrl + A) and copy it (Ctrl + C). The text format report formula can be temporarily pasted (Ctrl + V) and saved, for example, to a separate Word file.
• Switch to environment B and click Edit as text on the report formula view. Empty the contents of the current formula and replace it with the text format report formula copied from environment A. Click Continue.

As a result, the report formula imported to environment B as text from environment A will have all the corresponding rows and columns as the report formula in environment A.

 

Important to take into consideration

Companies may use a variety of accounts and there might be a need to present accounts on the company's default report formulas differently compared to how Procountor by default would present them.  Before taking official reports the company should make sure that their default report formulas suit their needs and accounts are included in those rows where they should be.

It should be noted for example that the default report formulas include some accounts on hidden rows, that do not appear on the reports but are used as some other row's row function. Making sure that the desired accounts or row ranges are included on these rows as well is important.

The following picture demonstrates how Raw materials and services in total is calculated with a row function from the next row R26. The balance to row R26 is taken from the account range [4000:4999]. If the desired outcome regarding the chart of accounts and reporting for this company is so that for example some other accounts are included in the purchases during the financial year, the default report formula should be modified so that the row R26 includes these as well, if the account range provided is not correct.

By clicking the image it can be opened larger on the browser.