Please note that the Report formulas (new) is currently only available in new Procountor environments that have been created by Accountor Finago sales after the version update on April 17, 2021. Report formulas (new) will be made available later this year to all current customers when existing environments are updated with the new chart of accounts changes.
Report formulas (new) allows creating and editing custom report formulas that can be used on the Accounting reports (new) view in the Accounting report type menu. Report formulas view can be accessed by clicking the Report formulas (new) button on the Accounting reports (new) view.
Creating a new report formula
To create a new report formula, click the New button. This opens a new window where you can select a template for the new report formula:
The template can be one of the following:
- Income statement
- Balance sheet, or
- Use empty template, that opens a blank report formula editing view.
When the template is selected from the drop-down menu, click Continue to open the report formula editing view. This view is more closely explained in the next section.
Custom report formulas will be visible on the Report formula view:
Report formulas can be removed and copied using the Delete and Copy buttons after selecting the report formula row. Edit button leads to the report formula editing view, which is more closely explained in the next section.
Report formula editing view
This view is for editing the report formula, and the formula can be edited either
- By editing each row and field directly, which is explained further in the following sections, or
- By editing the formula as text through the Edit as text button, which is explained further in its own section.
On the right side of the view is the Account list section, which is used to edit certain types of report formula rows. More information on this can be found here.
The report formula name is specified in the Name field. This name will be visible in the Accounting report type drop-down menu on the Accounting reports (new) view, where this newly created custom report formula can be selected.
Type selection determines whether the accounting report type is Income statement, Balance sheet, or Cash flow.
Change number signs for account values? selection switches all the report formula values to opposite values.
Changes made to the report formula are saved with the Save button at the top of the page.
Report formula editing view has the following buttons:
- 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 accounting 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:
- 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 Procountor interface language is changed. If the fields are left empty, the rows will be translated according to the chart of accounts defaults when the Procountor interface language is changed. Please note that if the default accounting 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.
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.
- 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 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 (new) 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 (new) 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.
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 ranges 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 cannot be edited within the column itself. These changes must 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.
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 (/), and numbers up to four decimal places (e.g. 12500,1663).
The calculations cannot, however, contain parentheses that would add for example the balances of two different rows together before dividing this result with a certain number, for example (R2+R5)/2. This kind of calculation can be done as follows:
- Create a new row in the report formula and select Row function from the Type column. Enter the calculation formula for the necessary rows (e.g. R1+R5). Hide the row from the printable report by selecting the Hide checkbox. In this case, the row will not be visible on the report but will be used in calculations. This row’s number would be R7.
- Then create the actual calculation row to be shown on the printable report. This row is also added using the Row function option in the Type column. Enter the following calculation in the Formula column: R7/2. This divides the sum of rows R1 and R5 (that has been separately calculated on row R7) by two.
KPI selections on the graphical reports
Please note that for now, the KPI selections are only available on the Default accounting report formulas views. Even though KPI selections can be made on custom report formulas, 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 new graphical reports compatible with the new chart of account features have the (new) suffix, such as Management reporting (new).
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 accounting 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 accounting report formulas KPI selections. The KPI selections on the Default accounting report formulas override the KPI selections on other reports in cases where these selections have been made on multiple reports. If you want to make KPI selections on a custom report formula, the Default accounting report formula KPI selections must be disabled on these reports.
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.