SI.OPENINGBALANCE

Created by Harry Lewis, Modified on Thu, 04 Apr 2024 at 07:10 PM by Harry Lewis

Applies to:

  • Velixo NX
  • Sage Intacct

The SI.OPENINGBALANCE function returns the opening balance of a GL account (at the beginning of the specified reporting period or date).

Syntax

SI.OPENINGBALANCE(
Connection,
AccountGroup,
AccountCode,
StartDate,
EndDate,
LocationId,
Books,
Dimensions,
UserDefinedDimensions, IncludeChildDimensionBalances)

 

Parameters

The SI. OPENINGBALANCE function uses the following parameters:


ParameterRequired/OptionalDescription
ConnectionRequiredThe name of the connection as configured in the Connection Manager
AccountGroup

Optional if AccountCode is specified


The name of the account group for which to return the aggregated opening balance
AccountCode

Optional if AccountGroup 

is specified

The code of the general ledger account, or multiple account codes defined by a Velixo range expression
StartDateRequiredThe start date (inclusive) in YYYY-MM-DD format or a cell reference to a date.
EndDateRequiredThe end date (inclusive) in YYYY-MM-DD format or a cell reference to a date.
LocationIDRequiredThe ID of the Sage Intacct entity or location.
BooksOptionalThe reporting book or global consolidation book to be used for retrieving the balance.  One or more user-defined adjustment book IDs may be included*
DimensionsOptionalEither:

  • A two-column Excel range where the first column contains the name of a predefined analytical dimension (e.g., department, customer, project, etc.) and the second column contains a semicolon-separated list of values for that dimension.
or
  • A multi-column array where the first row (or column) of the array contains the name of a predefined analytical dimension, and the remaining rows (or columns) contain the individual values for that dimension.

For more in-depth information about filtering by Dimensions, see Using Dimensions.

For user-defined dimensions, use the UserDefinedDimensions parameter.

UserDefined DimensionsOptionalEither:

  • A two-column Excel range where the first column contains the name of a user-defined analytical dimension and the second column contains a respective value (or semicolon-separated list of values) for that dimension.
or
  • A multi-column array where the first row (or column) of the array contains the name of a predefined analytical dimension, and the remaining rows (or columns) contain the individual values for that dimension.

For more in-depth information about filtering by Dimensions, see Using Dimensions.

For predefined dimensions, use the Dimensions parameter.


IncludeChild Dimension Balances*Optional
True (Default)
or
False

For dimensions which have child dimensions, determines if the balances of child dimensions are included in the calculation of the total balance.

This parameter only affects balances if:
  • The Location parameter is defined and/or
  • Other dimensions are specified in the Dimensions or UserDefinedDimensions parameters.


*The IncludeChildDimensionBalances parameter performs similarly to the Include subdimensions checkbox in Sage Intacct's stock reports such as Account Balances:


Books parameter:

  • If you leave the parameter blank, the default reporting book will be used (ACCRUAL or CASH depending on your company settings).

  • If you specify just one reporting or global consolidation ("GC") book, that's what will be used to retrieve balances.

  • If you specify more than one reporting / GC books (e.g. "ACCRUAL;GCBOOK") an error will be thrown - it is not allowed in Sage.

  • If you specify a reporting / GC book and one or more predefined/user-defined adjustment books (e.g. "ACCRUAL;TAX;GAAP") then you will retrieve balances from ACCRUAL plus any adjustments defined by each of the adjustment books.

 



Examples

 

Revenue and Expense accounts

Consistent with Sage Intacct's Trial Balance and Account Balances reports, this function may always return zero for both revenue and expense accounts. 


To calculate the starting balance of a revenue or expense account as of a particular date, calculate the Account Turnover from THE beginning of the appropriate financial year until one day before the report date.




Example 1

=SI.OPENINGBALANCE(
"Sage",
,
"10100",
"2022-04-01",
"2022-04-01",
"100",
"ACCRUAL",
$C$6:$D$8)

Description

Returns the opening balance of the account "10100 - Cash" at the beginning of April 2022, for entity "100", filtered down by dimensions specified in the C6:D8 range (for example, by Department and Employee).

 

Result

441,692.00

 

(this example uses cell references for some of the parameters)

 

Example 2 - Dimension Array

(dimension arrays are available in version 2023.8 and higher)

 

The dimensions for Customer, Item, and Location each contain 3 values in an array where the first row of the array contains the dimension names.

 

Vertical

=SI.OPENINGBALANCE("Sage",,"10010","2019-12-01","2019-12-31",, "ACCRUAL",I2#)

 

Description

Returns the opening balance for account 10010 as of 2019-12-01, in the Accrual reporting book, based on the three dimensions displayed by the vertical array function in cell I2.

 

Results

 

Horizontal

 

Description

Returns the opening balance for account 10010 as of 2019-12-01, in the Accrual reporting book, based on the three dimensions displayed by the horizontal array function in cell L2.

 

Results

 

Example 3

While it is generally required that all of the applied dimensions have the same number of values, it is possible for one of the dimensions to have a single value.

 

In this example, the dimensions for Customer, and Item each contain 3 values while the dimension for Location contains only 1 value.

 

These functions return the opening balance for account 10010 as of 2019-12-01, in the Accrual reporting book, based on the single location (100) and the other two dimensions displayed by the array:

 

Vertical

 

 

Horizontal

 


 

Example 4 - Empty Account Group

 

Assuming this below configuration within Sage Intacct:

(note that no accounts are assigned to the Accumulated Depreciation account group)

=SI.OPENINGBALANCE("Sage", "Accumulated Depreciation",,"2019-01-01","2019-12-31",
"100", "ACCRUAL")

 

Description

The various Velixo balance functions (SI.OpeningBalance, SI.Turnover, SI.ClosingBalance, and SI.BudgetTurnover) will return zero when there are no accounts assigned to the referenced account group.

 

Results

 

 


Example 5a - Include Child Dimension Balances with Location

 

Below are balances where the Dimensions and UserDefinedDimensions are not used, but where a Location specified with the IncludeChildDimensionBalances parameter:



 



Example 5b - Include Child Dimension Balances or not

 

Below are balances for a specific Location, where Dimensions and UserDefinedDimensions are not used.  


Customer 10007 is defined as a child account of customer 10001.


 


In row 6, when excluding the balance for customer 10001, you can choose whether or not to also exclude the balance for child account 10007.


Note that rows 3 and 7 use two techniques (blank and "*") to achieve the same result.




Unsupported Example

The case where more than one dimension contains less than the maximum number of rows in the array is not supported.

 

 

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article