Using BCE Excel Functions

BCE Excel functions are supported from BCE v1.8.0 using local and online editions of Excel.

Introduction

This is a brief introduction to using BCE Excel functions covering the following topics:

• Selecting BCE Excel functions
• Example BCE Excel function
• BCE Excel Update functions
• BCE Excel STRICT functions
• Excel Cell Protection
• List General Ledger Accounts
• Data Drill Down
• Summary of BCE Excel Functions

Note: the Business Cloud Essentials Excel functions Add-in must be installed and configured.

See 'BCE Excel Functions - Introduction'

A pdf copy of this document can be downloaded from here

Selecting BCE Excel functions

BCE Excel functions are used by typing: '=BCE' to open the list of all functions prefixed with BCE:

The list of functions is refined as each of the leading characters are entered:

Alternatively, scroll through the list of the functions to locate the required function.

As each function is highlighted, a tip provides a brief explanation of the function.

When a function is selected, it will appear within the selected cell:

Clicking on the highlighted tip opens the BCE on line help documentation for guidance on the functions use:

See list of all BCE Excel functions below

Example BCE Excel function

Each BCE Excel function requires at least one parameter.

For example, to return a GL account name the BCE.GL.ACCOUNT.NAME function requires the account code:

The account code can be entered directly into the parameter enclosed by the opening and closing bracket:

Alternatively the account code can be located from a cell anywhere within the spreadsheet by clicking on the cell or entering the cell reference:

When combined with a list of General Ledger account codes, it is possible to rapidly complete the names for all account codes by using excel features to click and drag the content of the BCE Excel function down through the list:

BCE Excel Update functions

BCE Excel functions include two update functions that are used for setting an account as a budget type and for setting or updating a budget value.

These update functions are particularly useful for planning and revising budgets within a spreadsheet where the revised budgets can be updated once agreed and finalised.

An update function is entered by completing the following parameters:

=BCE.BUDGET.UPDATE($B$3,$A10,M$7,O10) (Budget code, Account code, Period, Revised value)

However, when using an update function, it is probably best to apply control over when the content of the revised values are saved back to BCE.

This can be achieved by using the Excel IF function where a designated cell must be set to "Y" to commit the revised updates.

Using the same BCE Excel update function, the IF control can form part of the function:

=IF($C$6="Y",BCE.BUDGET.UPDATE($B$3,$A10,M$7,O10),"Not SAVED")

This checks the status of cell C6 for the uppercase character of "Y" before updating BCE. Any other content other than "Y" returns the text "Not SAVED" and the live update function is disabled:

BCE Excel STRICT functions

The STRICT parameter is used where the parameter value entered must be explicitly used.

For example, when running a reporting within BCE that requires the option to enter a department or project code, if no code is entered the report returns the results for all departments and projects.

BCE does not apply mandatory rules for entering department or project codes, therefore it is permissible to have no code.

Using STRICT functions will respect the actual value entered, so if a blank department and/or project code is entered, on those actuals with blank codes will be returned.

The use of the STRICT parameter is applied by entering "Y" or "N" within the function:

When the 'STRICT' flag is set as "N".

BCE.GL.ACTUAL.PERIOD.DEPT_PROJ.STRICT(C6,B2,B3,B4,"N")

Entry of no department/project code returns the financial values for every department/project from BCE regardless of whether a code is valid or blank This is consistent with the reporting filters used in BCE.

When the 'STRICT' flag is set as "Y"

BCE.GL.ACTUAL.PERIOD.DEPT_PROJ.STRICT(C6,B2,B3,B4,"Y")

Entry of no department code will return the financial values for every department from BCE with a blank code.

Excel Cell Protection

The standard Excel cell features using the '$' symbol can be used effectively to lock cell's position on the vertical or horizontal axis or both.

For example:

Using the BCE Excel function to retrieve the GL Actual balance for a specific period requires the parameters for 'account code', 'period' and 'year'.

Each of these fields are stored in their respective cells within the Excel spreadsheet:

Using the Excel features to lock the cell position when using the drag content functions allows the following:

Prefixing the column with the $ locks the column.

Prefixing the column and row with $ locks the columns and row.

Prefixing the row with $ locks the row.

List General Ledger Accounts

The ‘List General Ledger Accounts’ option allows users to prefill their spreadsheet a list of account codes.

Once populated, the codes can be used in conjunction with the BCE Excel functions to compile accounts reports:

The list of account codes can be retrieved as a complete list or by selecting specific account names, types or the status of account codes.

List Full GL Account Record

Returns the full list of accounts together with the properties of the account including the description, type and values:

Return Budget Accounts Only

Returns accounts marked as budget type

Account Code

Returns the specified account code

Account Type

Returns accounts for the selected account type:

Status

Permits selection of active or inactive accounts only

Parent Account Code

Returns accounts within the specified parent summary account code

Data Drill Down

For data sourced from BCE a data drill down option allows the underlying data to be inspected.

From the BCE menu panel, select 'Actions / Data Drill Down':

The 'View in BCE' button becomes active whenever a cell is selected that contains BCE data.

Details of the cell selected and the formula content are displayed for reference:

Clicking on the 'View in BCE' button will open the underlying data in a separate tab:

Summary of BCE Excel Functions

General Ledger Account Codes (Chart of Accounts)

• BCE.GL.ACCOUNT

Returns Chart of Account general ledger codes, names, descriptions, type and status.

Includes an option to save accounts back to BCE as a budget account

• BCE.GL.PARENT.ACCOUNT.CODE

Returns the parent account code for the account code entered

See BCE.GL.ACCOUNT functions

Actual Financial Balances

• BCE.GL.ACTUAL.PERIOD

Returns the actual values for the financial period and year entered with options to filter by departments and projects

• BCE.GL.ACTUAL.SYSTEM

Returns the total actual value ever recorded for the selected account

• BCE.GL.ACTUAL.SYSTEM_TO_YEAR_END

Returns the actual value from the start of the system to end of the financial year entered with options to filter by departments and projects

• BCE.GL.ACTUAL.SYSTEM.DEPARTMENT

Returns the actual value from the start of the system to end of the system with options to filter by departments and projects

• BCE.GL.ACTUAL.YEAR_TO_PERIOD

Returns the actual value to the financial period and year entered with options to filter by departments and projects

• BCE.GL.ACTUAL.YEAR_TOTAL

Returns the actual value for the financial year entered with options to filter by departments and projects

See BCE.GL.ACTUAL functions

General Ledger Budgets

• BCE.BUDGET

Returns budget codes, names and descriptions with options to retrieve the live and total values

Includes an option to save budget back to BCE for setting and revising budgets

See BCE.BUDGET functions