Detailed descriptions of all Excel send formulas are provided throughout this chapter, along with examples for reference.

AAROCreateNewJV

The AAROCreateNewJV formula is used to create journal bookings and is an alternative process to manually entering data in AARO.

The Excel formula itself shows very little data, except an indication that data will be sent from Excel to AARO, indicated by 'Formula result = 1'. The reason for this is explained below.

AAROCreateNewJV formula

In Excel, the AAROCreateNewJV formula can only be used in combination with the journal template. This is pasted onto an Excel worksheet by clicking Paste Template in the Journals group on the AARO Reports tab.

Pasting a journal template into Excel

Here is an example of a completed journal template in Excel, ready for sending to AARO:

Journal template example in Excel

The following fields are mandatory in the journal template:

Journal type	Mandatory fields

Journal type

Mandatory fields

all

The following fields should be completed with values in the journal template:
Co – for all journals;
Code – for all journals;
Loc – used for local currency journals and accounts activated in forms defined with Loc only;
Loc&TransAmount – used for local currency journals and accounts activated in forms defined with Loc&TransAmount;
TransAmount – used for local currency journals and accounts activated in forms defined with TransAmount only;
Amount – for group currency journal.
The following fields will get the default values if not completed in the journal template:

FromCo – will get the value from field Co;
Proforma Type, Inv Type, Adjustment Level, AcctType – will get the default values set up in the AARO application (on the menu Utilities/Application Management/Default Values).

Past Equity

LegalType, Amount Type, PEID, PECode, OwnedCo

Excess Value

LegalType, Amount Type, PEID, GWID, PECode, OwnedCo

Recurrent and 'MULTIJV' journals cannot be created from Excel.

Past equity and excess value journals should not normally be created or edited manually, they should be created as system journals. However, if manual adjustment to an existing PE or EV journal is required, make sure the following conditions are met:

The LegalGroup column must be left empty.
One PEID/GWID combination corresponds to one Code/PECode combination.
Existing PEID/GWID only can be used.
Only the following amount types are allowed with past equity journals: Opening, Change and Closing.
Dimensions are not used and not sent from journals in Excel.

Past equity journal template example

Create journal bookings

The process for sending journals to AARO is slightly different from other AARO Excel send formulas, as the menu Create in the group Journals needs to be selected here.

Creating a journal booking

Choose the appropriate submenu:

Submenu	Action
All	Sends journal data from all open Excel workbooks into AARO.
Active Sheet	Sends journal data from the open Excel worksheet into AARO.
Selected Sheets	Sends journal data from multiple Excel worksheets into AARO. In this scenario, the user is presented with a choice of worksheets they would like to send.

Wait until you have received confirmation that all items have been sent successfully:

Create journal status

If one or more journals fail validation, then no journals are sent.

When journal data has been successfully sent to AARO, it can be viewed in the Windows client, Data Entry/Journals menu and Web reports.

Journals created from Excel

AAROSendRate

The AAROSendRate formula is used to set up currency exchange rates for a period. A user must belong to the ABS_Admin group to be able to send rates to AARO.

An example of the AAROSendRate formula is outlined below, with detailed explanations of the parameters underneath.

ABSSendMatch formula example

Field	Description

Field	Description
Rate	Exchange rate
Period	Period for which the data is to be sent
Currency	Currency code
Rate type	Rate type such as 'Opening', 'Average', 'Closing', or manually created rate types.

If exchange rates for the currency and period have already been set up they will be overwritten.

When data has been successfully sent to AARO, it can be viewed in the Windows client, Utilities/Edit Periods menu, Rates tab.

Opening rate sent from Excel to AARO

ABSSendMatch

The ABSSendMatch formula is used to send intercompany transactions to AARO.

An example of the ABSSendMatch formula is outlined below, with detailed explanations of the parameters underneath.

ABSSendMatch formula example

Field	Description

Field	Description
TransCurr	Transaction currency. Should be filled in if it is used in the Match form.
TransAmount	Transaction amount: value in transaction currency, if used in the Match form. If the field is left blank, a zero value will be sent.
LocValue	Amount in the company's local currency. If the field is left blank, a zero value will be sent. For match forms which only have transaction amount, this field is ignored.
Period	Period for which data is sent.
Code	Account code for which data is sent.
Company	Reporting company code.
CounterCo	Counter company code.
<own dimensions>	Dimension value – if the form is to be reported on a dimension level, a value for one of the reporting company dimensions (i.e. business unit) may be entered here.
<counter dimensions>	Dimension value – if the form is to be reported on a dimension level, a value for one of the counter company dimensions (i.e. business unit) may be entered here. Counter dimensions will depend on the system setup.
N/A	Indicates that this field is not used. It is reserved for more dimensions.
AcctType	Accounting type data is sent on.
ProformaType	Proforma type data is sent on.

Data sent with the same field values will be summed. Fields not presented in the form will be ignored. If the sent data matches an existing row, the row will be updated with sent values.

When data has been successfully sent to AARO, it can be viewed in Web Match.

ABSSendOper

The ABSSendOper formula is used for sending data that is reported through input and matrix forms into the AARO database and is an alternative process to manually entering data in AARO input forms.

An example of the ABSSendOper formula:

ABSSendOper formula example

Field	Description

Field	Description
Value	Value to be sent.
Period	Period for which data is sent.
Code	Account for which data is sent.
Company	Company for which data is sent, identified by company code.
<dimensions>	Dimension for which data is sent – if the form is to be reported on a dimension level, an appropriate value may be entered here. Dimensions will depend on the setup of the form, and upon which forms the system administrator has activated for the current period in the AARO application. Values for date dimensions should be sent with quotes, i.e. "yyyy-mm-dd".
NA	Indicates that this field is not used. It is reserved for more dimensions.
CounterCo	Counter company for which data is sent, identified by company code.
<counter values>	Counter values for which data is sent, identified by dimension values. Available counter values depend on the system setup.

When ABSSendOper data has been successfully sent to AARO, it can be viewed in the Web reports.

ABSSendOperFlex

The ABSSendOper formula is used for sending data that is reported through input, matrix and match forms into the AARO database, and is an alternative process to manually entering data in the AARO data entry input screens.

The "Flex" part of the formula allows users to define dimensions themselves, rather than according to predefined criteria – see Dim1 / Dim2 etc.

Here is an example of an ABSSendOperFlex formula in Excel:

ABSSendOperFlex formula example

Field	Description

Field	Description
Value	Value to be sent.
Period	Period for which data is sent.
Co	Company for which data is sent, identified by company code.
Code	Account for which data is sent.
Dim1, Dim2, etc.	Dim1 – Dim20: these fields can be used to define dimension names such as "Market" or "Business Unit" (exact names vary depending on the dimensions setup in each AARO installation). The format for input is "dimension:dimension value". In the example above, the dimension, separator ":", and dimension value were given as "Business Unit:MEDIA". Alternatively, 'CounterCo', counter values as well as 'TransCurr' and 'TransAmount' fields can be used to define for sending data on accounts activated in forms defined with Loc&TransAmount or only TransAmount. E.g. "TransCurr:EUR" and "Currency:TransAmount". In the same way as other parameters, dimensions and dimension values may also be taken from individual cell reference, such as cell reference I48.

An example for sending data on an account activated in a form defined with TransAmount only:

Send to account activated in form defined only with TransAmount

The following example is for sending data to an account activated in a form defined with Loc&TransAmount:

Send to account activated in form defined with Loc&TransAmount