Cumulative Balances versus Budgets across Exact Online companies

Summary

Exact Online provides balances and budgets also through the API. This note explains how you can use Invantive SQL to easily compare actuals versus using a full outer join. Also, accumulated numbers can easily be reported across multiple companies using a query.

Exact Online Balance and Budget

In the accounting package Exact Online, you can manage your actuals and budgets. Exact Online offers one accounting approach for actuals, but using reporting schemes you can create custom groups of accounts which allows you to reorder, regroup and filter actuals. Multiple budgets per fiscal year can be registered using scenarios.

You can verify the progress of your business according to the plan by combining figures from budgets and balance of actuals.

Recommended APIs and Performance

The simple approach to determine the balance is to retrieve all general ledger transactions and sum these to determine the balance by any available dimension. Such an approach scales poorly since the opening balance is composed of all transactions in all preceding fiscal years. As many other accounting, Exact Online real-time maintains a list of summed actuals for a specific combination of dimensions such as company, period, general ledger account and the analytical dimensions cost center and cost unit. These pre-calculated totals do not include dimensions such as Item, Project or Asset, so in general they are less suitable for operational reporting depending on your set up.

It is highly recommended to use the pre-calculated totals instead of summing the transactions. It is not uncommon for large companies that the totals can be retrieved in seconds through the APIs, whereas the transactions take hours.

Exact Online offers two APIs for actual balances, which scale well in number of transactions: the Invantive SQL table BalanceLinesPerPeriod in the ExactOnlineXML catalog and ReportingBalance in the ExactOnlineREST catalog. Even with the improved performance of the pre-calculated totals, the use of BalanceLinesPerPeriod is recommended over ReportingBalance. ReportingBalance is significantly notoriously slower due to a number of design choices. However, ReportingBalance offers some additional information such as the number of transactions that constitute the total. This number can be valuable for analytical purposes and query optimization.

The use of BalanceLinesPerPeriod is recommended over ReportingBalance

For budgets, the recommended API is Budgets. The related XML API works similarly and performs identical.

Actuals versus Budgets

Using an Invantive SQL query you can easily compare actuals versus budget across multiple companies.

First let’s select all authorized Exact Online companies to perform the analysis upon:

--
-- Select all Exact Online companies.
--
use all

Note that an actual amount might be present on a general ledger account for which budget has been established. And vice versa: a budget might be present for a general ledger account for which no transactions have been registered. The full outer join covers both scenarios; a full outer join returns a row for every combination where either the left side or right side of the join has a row.

The data selection becomes:

select ...
from   exactonlinexml..balancelinesperperiod ble
full
outer
join   exactonlinerest..budgets bud
on     bud.division           = to_number(ble.division_code)
and    bud.glaccountcode      = ble.periods_year_years_balance_code_attr
and    bud.reportingyear      = ble.periods_year_reportingyear_attr
and    bud.reportingperiod    = ble.reportingperiod_attr
--
-- Optional budget scenario code filter using Invantive Control syntax.
--
and    bud.budgetscenariocode = coalesce($p{p_budgetscenario}, bud.budgetscenariocode)

The values returned need to take into account that values from both sides of the join are present or only of one side. Therefore it is necessary to use a SQL function like coalesce: coalesce evaluates to the first not null value in the list given. The order in which the columns are listed is not relevant in general.

The resulting Invantive SQL query which reports actuals versus budgets across multiple companies is:

select coalesce(to_number(ble.division_code), bud.division) 
       division
,      coalesce(bud.glaccountcode, ble.periods_year_years_balance_code_attr) 
       glaccountcode
,      coalesce(bud.reportingyear, ble.periods_year_reportingyear_attr) 
       reportingyear
,      coalesce(bud.reportingperiod, ble.reportingperiod_attr) 
       reportingperiod
,      bud.amountdc 
       budgetamountdc 
       label 'Budget (EUR)'
,      ble.balance 
       actualsamountdc 
       label 'Actuals (EUR)'
from   exactonlinexml..balancelinesperperiod ble
full
outer
join   exactonlinerest..budgets bud
on     bud.division           = to_number(ble.division_code)
and    bud.glaccountcode      = ble.periods_year_years_balance_code_attr
and    bud.reportingyear      = ble.periods_year_reportingyear_attr
and    bud.reportingperiod    = ble.reportingperiod_attr
--
-- Optional budget scenario code.
--
and    bud.budgetscenariocode = coalesce($p{p_budgetscenario}, bud.budgetscenariocode)

Note that the query assumes that all companies have the same structure for periods, chart of accounts and division currency.

Cumulative Actuals versus Budgets

The query shown above only reports the actuals and budgets from a specific period. They are neither accumulated across the periods nor related to the opening balance. For many applications it is more useful to study cumulative totals since deficits can be compensated by surpluses in other periods.

Despite no Exact Online APIs exist that accumulate the amounts across all preceding periods in a fiscal year, Invantive SQL enables you to derive these totals easily. The essence is to replace BalanceLinesPerPeriod by a cumulative variant and dito for Budgets. After that, you just add to opening balance to the totals. The opening balance includes all previous fiscal years.

A cumulative variant of BalanceLinesPerPeriod can be written as:

select ble.division_code
,      ble.periods_year_years_balance_code_attr
,      ble.periods_year_reportingyear_attr
,      ble.reportingperiod_attr
,      ble.periods_year_open
,      sum(bleprev.balance) balance
from   exactonlinexml..balancelinesperperiod ble
join   exactonlinexml..balancelinesperperiod bleprev
on     bleprev.division_code = ble.division_code
and    bleprev.periods_year_years_balance_code_attr = ble.periods_year_years_balance_code_attr
and    bleprev.periods_year_reportingyear_attr      = ble.periods_year_reportingyear_attr
--
-- Include also all preceding periods.
--
and    bleprev.reportingperiod_attr                 <= ble.reportingperiod_attr
group
by     ble.division_code
,      ble.periods_year_years_balance_code_attr
,      ble.periods_year_reportingyear_attr
,      ble.reportingperiod_attr
,      ble.periods_year_open

Compared to just retrieving the actuals from the table, the query above joins the table with itself on all uniquely identifying columns (the primary or business key). However, using the following join condition it select all preceding periods in the same fiscal year:

and bleprev.reportingperiod_attr <= ble.reportingperiod_attr

For example, with the following amounts per period for a specific combination of company, general ledger account and fiscal year:

Period Amount
1 25
2 13
3 8
4 37

and period 3, the join condition ensures that bleprev returns the amounts 25, 13 and 8, which is exactly the needed total.

The full cumulative actuals versus budgets query for all selected Exact Online companies is:

select coalesce(to_number(ble.division_code), bud.division) 
       division
,      coalesce(bud.glaccountcode, ble.periods_year_years_balance_code_attr) 
       glaccountcode
,      coalesce(bud.reportingyear, ble.periods_year_reportingyear_attr) 
       reportingyear
,      coalesce(bud.reportingperiod, ble.reportingperiod_attr) 
       reportingperiod
,      bud.amountdc 
       budgetamountdc 
       label 'Budget (EUR)'
,      ble.balance 
       + ble.periods_year_open 
       actualsamountdc 
       label 'Actuals including Opening Balance (EUR)'
from   ( select ble.division_code
         ,      ble.periods_year_years_balance_code_attr
         ,      ble.periods_year_reportingyear_attr
         ,      ble.reportingperiod_attr
         ,      ble.periods_year_open
         ,      sum(bleprev.balance) balance
         from   exactonlinexml..balancelinesperperiod ble
         join   exactonlinexml..balancelinesperperiod bleprev
         on     bleprev.division_code = ble.division_code
         and    bleprev.periods_year_years_balance_code_attr = ble.periods_year_years_balance_code_attr
         and    bleprev.periods_year_reportingyear_attr      = ble.periods_year_reportingyear_attr
         --
         -- Include also all preceding periods.
         --
         and    bleprev.reportingperiod_attr                 <= ble.reportingperiod_attr
         group
         by     ble.division_code
         ,      ble.periods_year_years_balance_code_attr
         ,      ble.periods_year_reportingyear_attr
         ,      ble.reportingperiod_attr
         ,      ble.periods_year_open
       ) ble
full
outer
join   ( select bud.division
         ,      bud.glaccountcode 
         ,      bud.reportingyear 
         ,      bud.reportingperiod 
         ,      sum(budprev.amountdc) amountdc
         from   exactonlinerest..budgets bud 
         join   exactonlinerest..budgets budprev
         on     budprev.division           = bud.division 
         and    budprev.glaccountcode      = bud.glaccountcode 
         and    budprev.reportingyear      = bud.reportingyear 
         and    budprev.budgetscenariocode = bud.budgetscenariocode
         --
         -- Include also all preceding periods.
         --
         and    budprev.reportingperiod    <= bud.reportingperiod 
         where  bud.budgetscenariocode     = coalesce($p{p_budgetscenario}, bud.budgetscenariocode)
         group
         by     bud.division
         ,      bud.glaccountcode 
         ,      bud.reportingyear 
         ,      bud.reportingperiod                   
       ) 
       bud
on     bud.division        = to_number(ble.division_code)
and    bud.glaccountcode   = ble.periods_year_years_balance_code_attr
and    bud.reportingyear   = ble.periods_year_reportingyear_attr
and    bud.reportingperiod = ble.reportingperiod_attr

Invantive SQL

The Invantive SQL used in this note with Exact Online is a Dutch innovation provided by Invantive. Invantive SQL is supported across a wide range of Invantive products and covers over 50 platforms (cloud and on-premise). For Exact Online, software can be downloaded from https://exact-online-apps-by-invantive.com. The Invantive Query Tool is a simple tool to start using Invantive SQL. The grammar is given on documentation.invantive.com.