Excel Add-in: explanation of the CU_BAL function

Introduction

With the CU_BAL (acc_nr;cu;co;FY;period;balance_type;value;calc;reportdate) function you can include the balance sheet amount for a cost unit per general ledger account in the worksheet.

Explanation of the fields

Company
Select the administration from which you want to retrieve the Exact data. If you select 'Default', the data will be taken from the default company. If you select a company, the data from the selected company will be displayed.

The advantage of not selecting a company is that the same report can be used for each company. You only need to change the default company to get the right data.

Function
Select the function you want to use. In this case  'CU_BAL'.

G/L account
Select the general ledger account for which you want the balance to be displayed. You can also choose to enter the account number. Multiple accounts are selected as follows: 0050:3000. In this case, the balances of accounts 0050 through 3000 will be displayed.

Cost unit
Select the cost unit for which you want the balance to be displayed. Select multiple cost units as follows: a:bzzzz. In this case the balance for all the cost units that begin with an 'A' or a 'B' will be displayed. If you leave the field empty, the balance of all the cost units will be included.

Financial year
Enter the financial year for which you want the amounts to be displayed.

Period
Enter the period for which you want the amounts to be displayed.

Unprocessed
You can retrieve information after entry. This means that information from unprocessed entries will also be displayed (unprocessed=1; processed= 0).< /SPAN>

Value
Select which value you want to have displayed in the worksheet. You have the following options:

Calculation method
Indicate which calculation method should be used. You have the following options:

• Period figures (1): Only the figures from the selected period will be displayed, in the default currency
• Cumulative (2): The cumulative amount, so all the transactions up to and including the selected period in the default currency

Range
If you select 'Date', entries for which the entry date falls within the range will be included in the result. If you select 'Reporting date', entries for which the reporting date falls within the range will be included in the result. 

Example

CU_BAL("3000";"ABON";100;2002;"12";1;3;2;"31/12/2002";1)

The cumulative balance 'after entry' for the 'ABON' cost unit in financial year 2002 and period 12, with entry dates through 31 December 2002 will be included, based on the amounts entered in general ledger account 3000 in company 100.