Home

AARO EXCEL CONNECTIVITY 14.0 USER MANUAL

1. AARO Drilldown Report F AARO Input Form settings AARO About Figure 6 1 1 Running consolidation 2 Select period and company and press OK 3 Pressing Refresh consolidation information will show the current and latest consolidations for the selected period and company Consolidation Consolidation information Period Info Entered Scheduled Finished Status Userld Latest consolidation for AALAN DA 124 1072012 11 17 58 AM 141072012 11 19 16 4M Done AAROREF ae Consolidation for SALANDA in queue 12 70 2072 11 21 18 AM Progress Franina Company ARLANDA Refresh consolidation information Cancel Figure 6 1 2 Ordering consolidation from Excel The ordered consolidation appears in the consolidation queue in the AARO application on the menu Utilities Consolidation Paste background data It is possible to paste background data from the AARO application to Excel The following data can be pasted e input form layout AARO SYSTEMS AB 7 be ae A 2 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Ce ADN S Additional AARO Excel options z an Es e report layout e list of dimension values e cash and benchmarking data The user must be logged on to the AARO database To paste background data 1 In Excel select a cell where the data will be pasted 2 Select the menu AARO Paste from AARO Background Data Log n Log Off AARO Retrieve b AARO Send Create Journals b AARO
2. MEDIA a screenshot has been included for reference here AARO SYSTEMS AB 39 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL MS AARO Excel send formulas a A gt STS T B le J Wa kA E En Z e Print Reports K a ed EJ A Fe at esl ma ey moa lY nd m Income Statement gt ANNUAL Ja b MONTHLY b AE 7 BS TANG Tangible bAa EQUITY Specificatii lf CF ALL 1 CASH FI b mies QUARTERLY or Net sales total ae Figure 3 5 17 Data sent from Excel to AARO 3 5 12 ABSSendOS OS is short for Orders and Sales which is what OS forms have historically been used for and OS forms are very similar to text forms OS forms may however be used for any kind of data The ABSSendOS formula is used for sending data that is reported through OS forms into the AARO database and is an alternative process to manually entering data in the AARO input forms For data to be transmitted from the ABSSendOS formula in Excel to AARO an appropriate OS form must be set up to receive the data In the case of the example given below we set up an OS form in AARO specifically to handle this example AARO SYSTEMS AB 40 B fy HAN AARO Excel send formulas ABSSend05 Value 1000 fe 1000 Period 09124 el 09124 Code 3060 Eel 3060 Company ARLANDA e ARLANDA Product FASTFOOD Eel FASTFOOD Business Unit Eel
3. AARO EXCEL CONNECTIVITY 14 0 USER MANUAL AARO SYSTEMS AB ee DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL onan Sa mh Last updated 2014 04 03 STS TEMS Table of Contents Using AARO with Excel an overview Installing a digital certificate for AARO Excel Excel macro security settings Open the AARO Excel add in Log on Log off ABS term oe E aq ulh WN e AARO Excel retrieve formulas Selecting Excel retrieve formulas Entering AARO Excel formulas directly Formula results Cell references and direct data entry Formats for Excel formulas Description of Excel retrieve formulas ABSGetAccountText ABSGetCompanyRate ABSGetMinorities ABSGetName ABSGetRate AARORetrieveFlex ABSRetrieveTextAmount ABSRetrieveTextString Retrieve information from AARO to Excel Validation Further Excel formulas ODNVF AANAADADQWYAWNE CON oO UI BR WN FB AARO Excel send formulas Selecting Excel send formulas Entering formulas directly into the formula bar in Excel Formula results Cell references and direct data entry in Excel Description of Excel send formulas 1 AAROCreateNewJV 2 Create journal bookings 3 Viewing data from AAROCreateNewJV in AARO 4 AAROSendText 5 Viewing data from AAROSendText in AARO 6 ABSSendMatch 8 9 wl wl WW W NNNNNNNNNNNNNNNNN N N m Viewing data from ABSSendMatch in AARO ABSSendOper Viewing data from ABSSendOper in AARO O ABSSendOperFlex 1 Viewing data from ABS
4. ARLANDA Figs ARLANDA Code 3010 E 3010 Dim1 Business Unit MEDIA Fss Business Unit MEDIA Field Value Period Co Code Dim1 Dim2 etc Formula result 10000 Figure 3 5 16 ABSSendOperFlex formula example Description Value to be sent Period for which data is sent Company for which data is sent identified by company code Account for which data is sent Dimi Dim20 these are defined by the dimension name 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 values were given as Business Unit MEDIA In the same way as other parameters dimensions and dimension values may also be taken from individual cell reference such as cell reference I48 If changes for a form are accepted through journals only the JV Adjustment dialog is opened with the template for Adjustment journal to be created For more information on adjustment journals please refer to the section 3 2 12 Adjust financial information in the AARO 14 0 User Manual 3 5 11 Viewing data from ABSSendOperFlex in AARO ABSSendOperFlex data can be viewed in the AARO application in menu Data Entry Input and Web reports in an appropriate form In the example above data was sent to the form IS_ALL for the business unit
5. AARO the AARO installation folder If the file cannot be found contact your system administrator The AARO folder also contains another Excel file AARO Reports xla used internally by ABSXL XLL and thus not advisable to open 4 Ifa Security Warning dialog is shown make sure that the macros are from Aaro Systems AB and if that is the case click the Enable Macros button to activate the AARO Excel add in Depending on the security settings in Excel this dialogue may or may not be seen If Excel 2007 or higher is used the certificate of Aaro Systems AB needs to be imported and trusted separately before the AARO Excel add in is opened Testcases Compatibility Mode Xl 9 S s Insert Page Layout Formulas Data Review View Al Security Warning Active content might contain viruses and other security hazards The following content has been disabled Enable Content Macros You should enable content only if you trust the contents of the file Trust Center Settings Learn more about Active Content Figure 1 3 1 Enabling security Macros 5 Anew menu named AARO appears on the Excel main menu bar In Excel 2007 and Excel 2010 it is to be found in the add ins section AARO SYSTEMS AB 12 1 4 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Using AARO with Excel an overview mm Log On Log Off AARO Send b PastefromAARO AARO Drilldown Report AARO Input Form b Consolidation Set
6. Order amp Sales form A screenshot example of an OS form setup for demonstration purposes is included here AARO SYSTEMS AB 43 3 6 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL m ad AARO Excel send formulas S i af oe aa iv Pa Load rece ms Print Reports Fetch complete Foy Row Replace i Period Company Compare With 09124 e T ARLANDA e Y Load ve ba Product Business Counter Discount Code Unit Company LOC ol 7 b dics TTI gt AE MONTHLY j E QUARTERLY Berens FASTFOOD F Figure 3 5 21 OS form data sent from Excel to AARO Send information from Excel to AARO When the relevant formulas and data cells have been completed information is sent from Excel to AARO or file using the menu AARO Send The user can then choose one of the options Option Action All to Database Send data from all open Excel workbooks to AARO application Sheet to Send data from the open Excel worksheet to AARO Database application Multiple Sheets Send data from multiple Excel worksheets to AARO application In this scenario the user is presented with a choice of which worksheets he or she would like to send to File Send data to a file which can be picked up by AARO or another application later AARO SYSTEMS AB 44 DN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL oy mh S AARO Excel send formulas Log n Log Off x AARO Retrieve All to Dat
7. Pan N TEMS Excel input forms Log n wo lt p gt 4 D Log Off AARO Retrieve d AARO Send d Create Journals Paste from AARO Consolidation Load settings Edit AARO About Settings Visual Refresh Reconcile Save Figure 5 8 1 Changing color palette 2 Choose the appropriate colour palette setting and press Close Palette modification Modify this workbook s palette right now To Excel Standard Figure 5 8 2 Setting up an input form palette in Excel 5 9 Visual refresh Refresh worksheet formatting to the Excel input form default if formatting has been modified by pressing the AARO AARO Input Form Visual Refresh menu AARO SYSTEMS AB 73 5 10 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms Log On Log Off AARO Retrieve AARO Send Create Journals d Paste from AARO Consolidation Load Settings Edit AARO About Settings Color Palette Reconcile Save Figure 5 9 1 Refreshing input form formatting Reconcile The Reconcile menu calls up an Excel version of AARO reconciliation reports Reconciliation within Excel operates in a similar way to reconciliation from the AARO application The only significant exception is that companies and legal groups are chosen from within the settings of an AARO Excel input form when working with reconciliation in the AARO application companies and legal groups are cho
8. Period 0912A Figs 09124 Currency EUR Eis AR Rate Type Clo B Clo Formula result 9 4735 Figure 2 6 5 ABSGetRate formula example Field Description Period Period for which data is to be retrieved Currency Currency code Rate Type Rate translation type e Ope opening e Ave average e Clo closing 2 6 6 AARORetrieveFlex The AARORetrieveFlex formula is used for retrieving operational figures that are reported through Input OS Matrix and Match forms The Flex part of the formula allows users to define dimensions themselves rather than according to predefined criteria see Dim1 Dim2 etc For reference an example of an AARORetrieveFlex formula is outlined below along with a brief explanation of the formula parameters underneath AARO SYSTEMS AB 20 AAR ORetieveFlex Period Co Code Curr Curr Trans AccType AccStd Dimi Dim Dim3 Proformal ype AdjLevel InvType Fs ARLANDA E 3010 Ea Toc Fs z bi Fis Business Unit CLOTHES EA Formula result 200 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL AARO Excel retrieve formulas 091247 ARLANDA Loc Business Unit CLOTHES Figure 2 6 6 AARORetrieveFlex formula example Field Period Co Code Curr CurrTrans AccType AcctStd Description Period for which data is to be retrieved Company code from which data is to be retrieve
9. formula is not completed e Formula result 1 formula is ready to retrieve a string of information according to template criteria e Formula result Net sales external or any other text string or number This directly displays the information received from AARO dependent on the criteria input into the formula 2 4 Cell references and direct data entry In Excel retrieve formulas it is possible to use either direct Excel data entry input data going directly into the function box or refer to a cell reference which contains the relevant information For example here some data has been entered directly using the direct Excel data entry method Period 09124 0912A Figure 2 4 1 Entering Excel data directly However a cell reference containing the relevant information returns exactly the same result Period B2 Fas 09124 Figure 2 4 2 Entering Excel data using cell references Where cell B2 was entered as follows AARO SYSTEMS AB 16 pe Je t g DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ies gt AARO Excel retrieve formulas S TEMS lt Nn fe 0912A A 1 Figure 2 4 3 Selecting cell data Throughout this chapter the first model of direct Excel data entry has been used in order to avoid confusion However users may prefer cell references instead depending on the customers needs 2 5 Formats for Excel formulas It is important to make sure th
10. 0 USER MANUAL mmh AARO Excel send formulas SYSTEMS Es f AAROCreateNewIV Function Arguments AAROCreateNew V Validation No help available Formula result 1 Help on this function Figure 3 5 1 AAROCreateNewJV formula In Excel the AAROCreateNewJV formula can only be used in combination with the journal template This is pasted onto an Excel worksheet using the menu AARO Paste from AARO Journal Template Log On Log Off AARO Retrieve b AARO Send d Create Journals d Background Data AARO Drilldown Report Period Setup AARO Input Form IT Consolidation Settings AARO About Figure 3 5 2 Pasting a journal template into Excel Here is an example of a completed journal template in Excel ready for sending to AARO A B C D E F 6 1 1 2 Period ID Currency JV Type Description 3 0912A Orig investment GROUP Normal Original investment 4 Co FromCo Code Loc Amount LegalGroup Legallype 5 ARLANDA ARLANDA 206104 100000 LEGGROUP JV SEK 6 ARLANDA ARLANDA 208104 100000 MainGroup JV SEK Figure 3 5 3 Journal template example in Excel AARO SYSTEMS AB 30 t ee DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ents B AARO Excel send formulas S TEMS lt Da 4 D The following fields are mandatory in the journal template Journal type Mandatory fields all The following fields should be completed with values in the journal
11. 1 Inserting an input form into Excel AARO SYSTEMS AB 63 DN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ae Excel input forms 2 Choose an appropriate input form IS_ALL Income Statement in the example given here appropriate dimension values to the right WE ANNUAL Annual package Only this subset AE of the input form Business Unit a CF_ALL_1 CASH FLOW 1 H A QUARTER QUARTERLY Protection Look and Feel Settings Figure 5 2 2 Selecting an input form in Excel 3 The Excel input form will then be loaded and available for data entry white cells are open for data entry AARO SYSTEMS AB 64 t ee AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL i a ae a Excel input forms Bookl Dec 2009 GOLD ORIGINAL DO NOT MODIFY Sthlm Arlanda SEK 8 3010 Net sales external 9 3060 Net sales internal 10 3080 Discounts 11 3099 Net sales total 13 3510 Invoiced costs external 14 3560 Invoiced costs internal 15 3599 Invoiced costs 16 3920 Commissions license fees and royalties 17 3960 Realized exchange gains on operating items 18 3961 Unrealized exchange gains on operating items i 19 3990 Other operating income 11817 7633 11817 7633 20280 79460 20 3999 Total income 22 640 8 733 21640 14133 20980 110106 21 M 4 IS_ALL Sheet2 Sheet3 4 SSS o E Figure 5 2 3 AARO input form loaded to Excel 5 3 Edit an input form To add delete columns and enter values 1 To add a c
12. 3567625 501434 2948680 12843617 4999 Costs of m 682 020 741538 27 201 5859866 7310625 T 9 4GP Gross profi 5143 858 2826086 474233 2911186 5532991 Mo 4b H Income Statement Income Statement 2 J Sheet Sheet3 2 Figure 4 7 2 Drill down report on the new worksheet in Excel It is possible to keep drilling down into columns rows or cells many times every time a new drill down operation is performed a new worksheet is opened Run a drill down report in Excel Choosing Run from the AARO Drilldown Report menu either from the AARO menu or by clicking the right mouse button gives user the option to run refresh a drill down report This is particularly useful for refreshing reports when source data in AARO has been changed Column Drilldawn by do Cut Edit 3 Copy Settings Paste Shadow Sheet Paste Special Delete Insert Run All Reports in Workbook Figure 4 8 1 Running a drill down report in Excel Edit a drill down report in Excel To edit a drill down report choose Edit from the AARO Drilldown Report menu either from the AARO menu or by clicking the right mouse button From here it is possible to edit report criteria in the same way as outlined in 4 4 Criteria for a drill down report AARO SYSTEMS AB 56 DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ere Excel drill down reports A E 5 Copy Settings Paste Shadow Sheet Paste Special Delete Inser
13. AARO Input Form Save AARO SYSTEMS AB 66 5 5 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms B 3 Load 3 Edit A Copy Settings 5 WAP rai 7 Paste Color Palette 5 Paste Special Visual Refresh 7 Insert Reconcile 8 3010 Net sales external er 9 3060 Net sales internal one Figure 5 4 2 Exporting input form data using the right mouse button If changes for a form are accepted through journals only the JV Adjustment dialog is opened with the template for Adjustment journal to be created For more information on adjustment journals please refer to the section 3 2 12 Adjust financial information in the AARO 14 0 User Manual If the information has been successfully exported a confirmation message will appear To check the information in the AARO application log on to AARO and go into the menu Data Entry Input and load the relevant form PA T ipu i 7 E E oe Ji gt Erata i em wm R g op e a ee E oB Load Save Saveasi Print f Reports Load erie pa Fetch Complete En ee Replace PA EE ly ne Ely i sl ARLANDA Sthim Arlanda Dec 2005 GOLD ORIGINAL gt TE ANNUAL CLOTHES COSMETICS i TOTAL 4 a Figure 5 4 3 Input form data sent from Excel Reload an input form in Excel Use the Load menu to reload an input form in Excel AARO SYSTEMS AB 67 5 6 AARO EXCEL CONNECTIVITY 14
14. Drilldown Report Feriod Setup AARO Input Form b Journal Template Consolidation Settings AARO About Figure 6 2 1 Pasting background data 3 Select the information source and data to be pasted and press Paste JN Pesterom anno atcha BOARD MEMB Number of Board members and Key officers BS_ ALL Balance Sheet CF ALL i CASH FLOW 1 The info will be pasted into the active sheet from the active cell down and to the right NB This operation cannot be undone Figure 6 2 2 Pasting background data from AARO The following information sources are available AARO SYSTEMS AB 78 6 3 Option Input Layout Report Layout Other Benchmarking DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ae Additional AARO Excel options Ti TE z S Description Input form layouts existing in the database Report layouts existing in the database Dimensions and groups existing in the database Cash and benchmarking data Paste the period setup Period validation setup and period rates can be pasted to Excel from the AARO application 1 In Excel select a cell where the data will be pasted 2 Select the menu AARO Paste from AARO Period Setup Log n Log Off AARO Retrieve d AARO Send d Create Journals d AARO Drilldown Report AARO Input Form Journal Template Consolidation settings AARO About Figure 6 3 1 Pasting period setup from A
15. To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ete B AARO Excel send formulas S TEMS lt Da H D xX v f AAROSendText 0912A ARLANDA SHARESSUBS B c D E F amp H l AAROSendText 0912A ARLANDA SHARESSUBS IARLANDA 123456 Stockholm 55 70 700 1000000 7000000 Figure 3 5 9 AAROSendText formula example The information which is sent against the text form SHARESSUBS in this example has to have the same style as you would find in the form in AARO application menu Data Entry Input Tip the Paste from AARO menu can be very helpful for pasting header information into an Excel worksheet this provides a helpful template for Excel data entry Here is an example where input layout SHARESSUBS was pasted into cell B2 In the example below easy data entry was facilitated for the formula contained in cell A6 Je AAROSendText 0912A4 ARLANDA SHARESSUBS B Cc D E F G H l 1 SHARESSUBS SHARESSUBS SHARESSUBS SHARESSUBS SHARESSUBS SHARESSUBS SHARESSUBS SHARESSUBS 2 13101T 13102T 13103T 1310T 13104T 3 Company Company Registered Share of Share of No of Book Value on 4 Registration office equity votes shares value stock 5 No 0 Jo exchange 6 IlarLAN DA 123456 Stockholm 35 70 700 1000000 7000000 Figure 3 5 10 AAROSendText template example Notes e Dimension Name and Text Field Name values are not sent to the database These fields are required in the template but can be left empty e Tex
16. down field select a report element and press the Change font button Set up font and press OK The selected font will be shown in the preview below This setting overrides report layout settings set up on the menu Utilities Edit Report Layouts Pressing this button sets up report settings to default The Shadow Sheet menu provides options to paste drill down report values into another sheet or sheets in a workbook AARO SYSTEMS AB 59 l r Sh DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ee Excel drill down reports lt P gt 4 Log On Log Off AARO Retrieve AARO Send Create Journals Paste from AARO Consolidation Run Settings Edit AARO About settings Delete Run All Reports in Workbook Figure 4 11 1 Pasting drill down report values to another sheet in Excel After choosing Shadow Sheet enter the sheet number for a shadow sheet Shadow Sheet Enter the name of an existing sheet where a copy of retrieved values will be pasted Sheet2 cenel Figure 4 11 2 Selecting a sheet to paste drill down report values in Excel When Run is pressed data will be pasted onto the shadow sheet sheet2 in the example above as well as updated on the open worksheet This enables users to update multiple worksheets with AARO drill down data simultaneously 4 12 Delete a drill down report from Excel Choosing Delete from the AARO Drilldown Report menu deletes the d
17. the sheet with colours and Modify the workbook to contain AARO colours paints the input form using the AARO colours The default setting for Excel input forms is to have a worksheet coloured so that it is easy to identify editable cells If the Paint the sheet with colours checkbox is cleared when an Excel input form is created the form will be created without any colour being applied In addition it is possible to choose whether standard Windows colours or AARO colours are applied to a worksheet The default setting is to work with AARO colours If the checkbox Modify the workbook to contain AARO colours is cleared when an Excel input form is created standard Windows colours are applied to the worksheet instead of the default AARO colours Note the Look and Feel settings discussed here for colour formatting are forward steps clearing a checkbox does not automatically apply backward steps to change a worksheet to a previous state 5 7 4 Auto fit columns 5 8 If the Auto Fit Columns checkbox is cleared when an Excel input form is created the form will be created without formatting to automatically fit the column widths Colour palette It is possible to change the colour palette used in Excel input forms to either AARO colours or Excel Windows standard colours 1 Select the menu AARO AARO Input Form Color Palette AARO SYSTEMS AB 72 t r 3h AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL
18. 0 USER MANUAL Excel input forms Log On Log Off AARO Retrieve AARO Send Create Journals d Paste from AARO Consolidation Settings Edit AARO About Settings Color Palette Visual Refresh Reconcile Save Figure 5 5 1 Reloading an input form in Excel Edit an input form in Excel Another period company input form and other dimension values can be selected by choosing the menu AARO AARO Input Form Edit In the example below period company and business unit are available for selection Excel worksheet Protection Look and Feel Settings are also accessible from this screen detailed further in the section of this user manual AARO SYSTEMS AB 68 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms Only this subset of the input form BS_ALL Balance Sheet Business Unit EQUITY Specification of Equity lt All gt Lf CF_ALL_1CASH FLOW 1 FS QUARTER QUARTERLY Protection Look and Feel Settings Figure 5 6 1 Editing an input form in Excel 5 7 Settings of input forms in Excel To change input form settings select the menu AARO AARO Input Form Settings AARO SYSTEMS AB 69 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Ter aE E ao SYSTEMS Excel input forms ARO Log On Log Off AARO Retrieve AARO Send b Create Journals Paste from AARO H Paste Report Values d Consolidation Load settings AARO About Color Fale
19. 14 0 USER MANUAL Using AARO with Excel an overview Completing the Certificate Import Wizard The certificate will be imported after you dick Finish You have specified the following settings Cer eee eee Trusted Publishers Con Certificate Figure 1 1 6 Finishing certificate installation The Aaro Systems AB certificate is now located in the Trusted Publishers Certificate folder 1 2 Excel macro security settings For Excel add in to work Excel Macros need to be enabled for use This is dependent on Macro Security settings in Excel This manual assumes that settings are selected as Medium in Excel 2003 or Disable all macros except digitally signed macros in Excel 2007 and higher e Excel 2003 In Excel 2003 the Macro security level is set up on the Excel menu Tools Options and the Security tab Macro Security button AARO SYSTEMS AB AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL oy ah S Using AARO with Excel an overview Options View Calculation Edit General Transition Custom Lists Chart Color International Save Error checking Spelling Security File encryption settings For this workbook Password to open Co O File sharing settings For this workbook Read only recommended Digital Signatures Privacy options Remove personal information From file properties on save Macro security Adjust the security level For files that might contain
20. 7 6 1 Order consolidation 77 6 2 Paste background data 77 6 3 Paste the period setup 79 6 4 AARO Excel settings 80 6 5 About 82 AARO SYSTEMS AB l ee AA a AARO EXCEL CONNECTIVITY 14 0 USER MANUAL YS 1 EM S Using AARO with Excel an overview 1 1 1 ST ST Using AARO with Excel an overview AARO comprises the formula Excel add in that provides the user with multiple options to access AARO data directly from Microsoft Excel There are two options to access data e Input data can be sent from Excel to the AARO database e Consolidated data can be retrieved from the AARO database to Excel Two different techniques exist for these alternatives e Formulas e Excel drill down reports and Excel input forms The following table summarizes the possibilities Overview of possibilities Without formulas Data from Excel into AARO AARO send formulas Excel input forms poe rom AARO retrieve formulas Excel drill down reports AARO into Excel p There are differences between the two techniques and it is advisable to become acquainted with both in order to choose the most appropriate The two techniques can also be mixed In addition to the above major features AARO Excel add in supports the creation of journals from Excel This manual describes the characteristics of all features how to open the AARO Excel add in and how to log on The AARO Excel add in can be used with Excel 2003 and higher versions
21. AARO Retrieve data into the open Excel worksheet from AARO Retrieve data into multiple Excel worksheets from AARO In this scenario the user is presented with a choice of worksheets he or she would like to retrieve An example for retrieving data for multiple worksheets is referenced below All AARO Send i Sheet Only Create Journals an Figure 2 7 1 Retrieving AARO formula data If the user choses Multiple Sheets they will see the next dialog where sheets to be retrieved have to be chosen Explanations for how to use the dialog boxes are provided within the Excel dialog boxes to guide users through the necessary steps An example for retrieving a data for a couple of open worksheets is referenced below AARO SYSTEMS AB 24 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ae 3 AARO Excel retrieve formulas The selected sheets will be calculated and retrieved one by one NB If the sheets refer to each other then you may need to press F9 first to calculate all so that all data is up to date Calculate all F3 Retrieve now Figure 2 7 2 Retrieving multiple sheets 2 8 Validation When data is received from AARO a dialog box appears informing the user whether the data has been successfully received Fil AARO Excel Retrieve Pro Sales sheet 2 of 4 Scanning Excel sheet 0 You have requested figures from 0 peradis for 0 distinct accounts Wait
22. ARLANDA Fis ARLANDA Form EO Es Eo Return field StringValue1 Fs StringValue1 Key names Product iss Product Key values BIKES Fiss BIKES Account 08710 Ess EO8710 Field Period Company Form Formula result Bikes with discount Figure 2 6 9 ABSRetrieveTextString formula example Description Period for which data is to be retrieved Company code from which data is to be retrieved Text form in AARO from which data is to be retrieved AARO SYSTEMS AB 23 2 7 Field Return field Key names Key values Account 3 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL B A 3 AARO Excel retrieve formulas S ToT Nn M Nn Description NamelIntTable value in the text form for the description which will be returned in the formula result e g StringValuel Dimension set up in the form e g Product Several dimensions can be entered divided by comma e g Product Customer Dimension value Value for each dimension should be entered divided by comma e g BIKES CUST_01 Account code Retrieve information from AARO to Excel When the relevant formulas and data cells have been completed information is retrieved from AARO to Excel on the menu AARO Retrieve The user can then choose one of the options Option All Sheet Only Multiple Sheets Action Retrieve data into all open Excel workbooks from
23. ARO 3 Select period company if required the information source and data to be pasted and press Paste AARO SYSTEMS AB 79 6 4 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Additional AARO Excel options C Dimension Company C Rates Business Unit Business Area Customer Market TESTMIXDIM The info will be pasted into the active sheet from the active cell down and to the right NB This operation cannot be undone Figure 6 3 2 Pasting period setup from AARO The following information sources are available Option Description Dimension Validation Dimension validations tab settings will be pasted for the selected period Dimension Validation Company Dimension validations tab Company settings will be pasted for the selected period and company Rates Rates tab settings will be pasted for the selected period AARO Excel settings Some settings affecting the Excel progress when working with the AARO Excel add in are set up on the menu AARO Settings AARO SYSTEMS AB 80 t F Sh AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL b Additional AARO Excel options AWA SY ST EMS aaro Log n Log Off AARO Retrieve AARO Send Create Journals Paste from AARO AARO Drilldown Report AARO Input Form Consolidation AARO About Figure 6 4 1 Accessing AARO plug in settings Tick the required check boxes and press OK Progress dialog options During 44A0 work Excels m
24. Business Area is Customer E Market Es TESTMIXDIM Ee NA g 05 Co HELSINKI Figs HELSINKI Counter BU Eel Counter BA Ee Field Value Period Code Company lt dimensions gt lt counter values gt NA 4 NA Es Formula result 10000 Figure 3 5 18 ABSSendOS formula example Description Value to be sent Period for which data is sent Account for which data is sent Company for which data is sent identified by company code Dimension i e Product 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 Counter values i e 0OS Co counter company for which data is sent if the form contains counter values Counter values depend on system setup Indicates that this field is not used It is reserved for more dimensions and counter values AARO SYSTEMS AB AARO EXCEL CONNECTIVITY 14 0 USER MANUAL 41 t T Sh AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Pran D AARO Excel send formulas S TEMS lt p gt 4 Note in this example there are various parameters such as business unit and business area that did not need to be filled in their contents are skipped in the program logic and therefore not sent to AARO 3 5 13 Viewi
25. Criteria 1 Company Criteria 2 ae r Product Business Unit Business Area Customer Market TESTMIXDIM Acct Standard Proforma Figure 4 4 4 Selecting drill down report criteria in Excel 3 Select value s from the drop down next to the criteria Criteria 2 Period __ Pal 09124 Figure 4 4 5 Selecting values 4 To change criteria press the z button and choose data from the list presented see selection dialog box below 5 To delete the selected criteria press the delete button next to the criteria you wish to delete 6 To move criteria order press move up or move down buttons Cr k Cr i Figure 4 4 6 Move up and move down buttons AARO SYSTEMS AB 51 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL S Excel drill down reports 3 Wa T S ToT an lt Options such as adding or removing report totals grand totals percentages calculated items are also available from the selection dialog box along with options for changing sort order Select Business Area z ie items i d Selection Selected items Business Area Description Normal All selected Ta Eliminations J ToN EL_IC FINANCE Description El Report Total show code E Grand Total Show description Long description Sortorder Selection order Sequence num Figure 4 4 7 Selecting parameter values for a drill down report in Excel Note users may prefer to filt
26. Excel workbook Copy Copy selection Copy all Live copy to Excel New Workbook New Sheet Figure 4 2 1 Live copy to Excel 4 Choose to enable macros if necessary and the new Excel workbook or worksheet will be displayed with the copied report xl W LTE Testcases Compatibility Mode Home Insert Page Layout Formulas Data Review View Al Security Warning Active content might contain viruses and other security hazards The following content has been disabled Enable Macros Content You should enable content only if you trust the contents of the file Trust Center Settings Learn more about Active Content Figure 4 2 2 Enabling macros in Excel 4 3 Create a drill down report in Excel To create a new Excel drill down report 1 Go to the AARO menu AARO Drilldown Report New Report This option is also available using the right mouse button AARO SYSTEMS AB 48 L ie vl 4 4 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel drill down reports AARO Input Form Predefined Figure 4 3 1 Creating a new report 2 In the Report order window enter report heading and select the report type e Regular Drill down report of type Input e Matrix report of type Matrix Weemnone A Report information Heading Balance Sheet Datasource AARO Standard datasource Default page f Grid
27. Installing a digital certificate for AARO Excel Depending on the security settings on Excel installation user might be required to install the signed certificate from Aaro Systems AB This process is described below For Excel 2007 and higher we recommend running Excel using the macro setting Disable all macros except digitally signed macros ensuring the digital certificate is installed as detailed below To install the digital certificate follow these steps 1 Right click the file ABSXL XLL and select properties Select the signature and press the Details button AARO SYSTEMS AB DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL maD Using AARO with Excel an overview Name of signer E mail address Timestamp Aaro Systems AB Not available den 7 december 2012 Figure 1 1 1 ABSXL xIl file properties Digital signatures The Digital Signature Details window appears 2 Click the View Certificate button AARO SYSTEMS AB AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Using AARO with Excel an overview This digital signature is OK Signer information Name E mail signing time den 7 december 2012 18 03 14 View Certificate Countersignatures E mail address Timestamp Not available den 7 december 201 Figure 1 1 2 Digital Signatures Details window The Certificate window appears 3 Click Install Certificate AARO SYSTEMS AB AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Using AARO wit
28. L ae Additional AARO Excel options En an m lt an AARO z Version 14 0 CAAAROMAARO1 14 Standard ABSNav exe 14 0 0 7533 24 949 856 bytes 11 27 2012 6 01 02 PM Version 14 Aaro Systems AB All rights reserved Aaro B pa Systems AARO and the Aaro logotype are a s trademarks of Aaro Systems AB E SYST Figure 6 5 2 About AARO SYSTEMS AB 83
29. MS AARO Excel send formulas lt b gt 4 D 3 AARO Excel send formulas Formulas in Microsoft Excel can be used to send data from Excel workbooks to the AARO application This provides extra flexibility and options for data manipulation before data is sent from Excel to AARO This section of the user manual refers specifically to the Excel formulas used to send data from Excel to AARO For more detailed information about the AARO bookings themselves please refer to the appropriate section of the AARO user manual e g Input Match Journals Edit Input etc 3 1 Selecting Excel send formulas The various AARO Excel send formulas can be accessed by pressing the Insert Function button in Excel Figure 3 1 1 Inserting an AARO formula into Excel worksheet The AARO Excel send formulas are listed under the AARO data send category Search for a function Type a brief description of what you want to do and then dick Go Or select a category Most Recently Used k Lookup amp Reference Select a function Datahase Text Logical Adds all the numbers i AARO data retrieve Help on this function Figure 3 1 2 Selecting AARO data send formula AARO SYSTEMS AB 27 l T Sh AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL eae AARO Excel send formulas 3 2 3 3 3 4 Entering formulas directly into the formula bar in Excel If you are a more experienced Excel user and or working with existing formulas it is pos
30. PE Change Acc 01 LOC PE1310P ATLANTA SEK 7 PARENT PE208104 25000 PE Change Acc 01 LOC PE208104 ATLANTA SEK 8 PARENT PE208604 2118 PE Change Acc 01 LOC PE208604 ATLANTA SEK 9 PARENT RATE 6 PE Change Acc 01 LOC RATE ATLANTA SEK Figure 3 5 4 Past equity journal template example 3 5 2 Create journal bookings The process for sending journals to AARO is slightly different from other AARO Excel send formulas as the menu AARO Create Journals needs to be selected here AARO SYSTEMS AB 31 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL S AARO Excel send formulas d away me an hh S s te Log n Log Off AARO Retrieve AARO Send All Paste from AARO P sheet only AARO Drilldown Report Multiple Sheets AARO Input Form Consolidation Settings AARO About Figure 3 5 5 Creating a journal booking Choose the appropriate submenu to send All Sheet only or Multiple Sheets and wait until you have received confirmation that all items have been sent successfully JEN AARO Excel Create Journal Pro Handling formula type JV lof 1 Summary of Journals being sent ID 1 Normal GROUP Input form Arithmetic 2 2 rows ID 2 ExcessValue LOC 1 rows ID 3 Normal LOC OS form Punctuations rows 4 TT Successtully created journals Figure 3 5 6 Create journal status If at one or more journals fail validation then no journals are sent AARO SYSTEM
31. Report type f Regular Dril down Matrix Figure 4 3 2 Report general information 3 Select report criteria as described in 4 4 Criteria for a drill down report 4 When criteria are defined press OK and the report will be loaded in the sheet Criteria for a drill down report The following criteria are mandatory for any drill down report Period Currency Account or Report Layout or Matrix Layout When creating a drill down report 1 Define report criteria for e Fixed data data for the report output AARO SYSTEMS AB 49 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel drill down reports AARO Standard datasource Grid Regular Drill down Matrix ah CurrencyTranslation Y Criteria 3 E Period e 09124 Criteria 4 ah Legal Group Criteria 5 x Figure 4 4 1 Setting up drill down report criteria in Excel e Rows rows displayed in the report output Fixed Rows Columns 6 GROUP BS show code lon Figure 4 4 2 Setting up drill down report rows in Excel e Columns columns displayed in the report output AARO SYSTEMS AB 50 DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL mN s Excel drill down reports Es Fixed Rows Columns Columns Criteria 1 a Al selected Criteria 2 TT Figure 4 4 3 Setting up drill down report columns in Excel 2 To add or change criteria choose appropriate criteria from the drop down lists
32. S AB 32 2 3 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL A B mm am h AARO Excel send formulas STS TE an Es Note the menus listed under AARO AARO Send are not used when sending journals to AARO the AARO AARO Send menu is against all other formula types except journals 3 5 3 Viewing data from AAROCreateNewJV in AARO For reference when journal data has been successfully sent to AARO it can be viewed in the AARO application using the menu Data Entry Journals Q Journals babas i R H HH r 5 External link lt no link gt i A i a ol 5 D ut t Report aa pare Prep N psert Delate E oy F Expand Sign Filter Attached fie lt no file gt Links Home Journal Id Orig investment Journal Identity 155 Journals Filter Period User ID ID Currency Status Date Last User EER 0912A AARO Orig investment GROUP Save 10 11 2012 6 35 42 A AARO Correction Journal Type Description ecurrent Journa Norma Original investment 5 BN as yes 6 20 09124 1 Prepared By Prepared Time Approved By Approved Time AttestedBy Attested Time Audited By Audited Time 3 N7 156 0912A 1 AARO 10 11 2012 6 35 42 AP 4 at A Co FromCo Code Amount Debit Credit Comment LegalGroup LegalType AdjustmentLevel InvType _ ProformaType AcctType Product rig investmen m diated Active Enable SUM 687335 31 Past Equity ARLANDA ARLANDA 208104 100 000 100000 LEGGROUP JV SEK Comp
33. SendOperFlex in AARO WWWWWWWWWWWNWww UUUUNUUUUNnU YY RW AARO SYSTEMS AB 12 13 14 14 g DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL fT os l EMEN a STS TEMS 3 5 12 ABSSendOS 40 3 5 13 Viewing data from ABSSendOS in AARO 42 3 5 14 ABSSendOSFlex 42 3 5 15 Viewing data from ABSSendOSFlex in AARO 43 3 6 Send information from Excel to AARO 44 3 7 Validation 45 3 8 Further Excel formulas 46 4 Excel drill down reports 47 4 1 Accessing Excel drill down reports 47 4 2 Live Copy to Excel from the AARO application 47 4 3 Create a drill down report in Excel 48 4 4 Criteria for a drill down report 49 4 5 Predefined drill down reports 53 4 6 Right mouse button functions 55 4 7 Drill down options 55 4 8 Runa drill down report in Excel 56 4 9 Edit a drill down report in Excel 56 4 10 Drill down report settings in Excel 57 4 11 Shadow sheet 59 4 12 Delete a drill down report from Excel 60 4 13 Run all reports in a workbook 61 4 14 Further Excel formulas 62 5 Excel input forms 63 5 1 Limitations of Excel input forms 63 5 2 Load an AARO input form into Excel 63 5 3 Edit an input form 65 5 4 Export data to AARO 66 5 5 Reload an input form in Excel 67 5 6 Edit an input form in Excel 68 5 7 Settings of input forms in Excel 69 5 7 1 Protection options 71 5 7 2 Selectable cells 71 5 7 3 Look and feel 72 5 7 4 Auto fit columns 72 5 8 Colour palette 72 5 9 Visual refresh 73 5 10 Reconcile 74 6 Additional AARO Excel options 7
34. Visual Refresh Reconcile Save Figure 5 7 1 Changing input form settings Settings can also be changed when the Protection Look and Feel Settings button is pressed when a new form is selected or an existing form is edited see screenshot above The following window appears AARO SYSTEMS AB 70 Only the inputhable figure cells are editable Everything else on the sheet is protected All changes to the sheet except simple figure editing must be performed through menuz e g adding columns changing fired selections AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms Look and Feel We Paint the sheet with colours W Modify the workbooks palette to contain precise AARO colours O Medium protection Related input form Inputtable figure cells are editable as are column headings tized selections and anything else outside the figure area Only non inputhable cells in the figure area are protected i e sume and grayed out cells Other options f No protection Pure Excel sheet i No protection at allis applied Anything on the sheet can be edited Iv Auto Fit Columns Allow only editable cells to be selected in the sheet Makes TAB or Enter jump to the next editable cell Changing these settings will not tigger a Load That ts it will not cause any changes to the current values in the sheet Cancel Figure 5 7 2 Setting up an input form in Excel For more description on
35. a is outlined below along with a brief explanation of the formula parameters underneath ABSRetrieveTextA mount Period 0912A Figs 09124 Company ARLANDA Ess ARLANDA Form Eo Es Be Account E08710 Fis 08710 Currency Loc LOC Key names Product Fis Product Key values BIKES Es BIKES Formula result 1500 Figure 2 6 8 ABSRetrieveTextAmount formula example AARO SYSTEMS AB 22 Field Period Company Form Account Currency Key Names Key Values 3 Nn lt Nn AARO EXCEL CONNECTIVITY 14 0 USER MANUAL B Aa i AARO Excel retrieve formulas z Description Period for which data is to be retrieved Company code from which data is to be retrieved Text form in AARO from which data is to be retrieved Account code Currency for the data to be retrieved Dimension set up in the form e g Product Several dimensions can be entered divided by comma e g Product Customer Dimension value Value for each dimension should be entered divided by comma e g BIKES CUST_01 2 6 8 ABSRetrieveTextString The ABSRetrieveTextString formula is used for retrieving descriptions that are contained within text forms For reference an example of an ABSRetrieveTextString formula is outlined below along with a brief explanation of the formula parameters underneath ABSRetrieveTextString Period 0912A Ess 09124 Company
36. abase Create Journals b Sheet to Database Paste from AARO Multiple Sheets AARO Drilldown Report m me AARO Input Form Consolidation Settings AARO About Figure 3 6 1 Sending data from Excel to AARO 3 7 Validation When data is transmitted to AARO a dialog box appears informing the user whether the data has been successfully transmitted Fe E eE Vf Scanning Excel sheet Handling formula type Text tof 4 Period company combinations found Vf Trying to send lt gt 0 of 1 part 4 Sending to server for validation PTT TT PPP Successfully sent everything Except any input data with Pernod Account or Company blank Figure 3 7 1 Excel data send progress AARO SYSTEMS AB 45 t r 3h DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Pran D AARO Excel send formulas 3 8 D pe S lt Nn TEMS If data cannot be sent from Excel to AARO a dialog box appears showing validation errors The content of this dialog box will depend on the errors returned from the AARO application When you send data to AARO the information is grouped by period company combinations It is recommended that one period company combination does not contain more than 10000 entries when sending to AARO If you get a validation error all of the records against the period company combination in the error message do not get sent For data to be sent successfully to AARO these erro
37. ain window iz recommended avoids unintended intererence iw Minimized Sound Iw Play a sound when ready iw Repeat the sound Tip IF you want a sound other than the default sound to be played then put a file named S65 ready waw In the A470 4E scel Add In folder Cancel Figure 6 4 2 AARO plug in settings The settings are Setting Description Covered nwith a protecting The progress window is coloured in blue blue AARO splash Minimized The progress window is minimized AARO SYSTEMS AB 81 8 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Ps Additional AARO Excel options 3 Nn lt Nn Setting Description Play a sound when ready Plays a sound when an operation is completed Tip If you want another than default sound to be played place the file ABSXLready wav in the AARO Excel Add In folder Repeat the sound Repeats the sound played when an operation is completed untill the confirmation window is closed 6 5 About Information about the current AARO database can be viewed on the menu AARO AARO About Log n Log Off AARO Retrieve AARO Send Create Journals Faste from AARO AARO Drilldown Report AARO Input Form Consolidation settings Figure 6 5 1 Opening the About information The user is provided with information about the current database used and the installed version AARO SYSTEMS AB 82 DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUA
38. any Normal Normal Normal Excess Value ARLANDA ARLANDA 208104 100 000 100000 MainGroup JV SEK Company Normal Normal Normal Fixed 2461 00 689796 F1 a My Journals 4 All Journals Debit Credit Difference 2 461 00 689 796 31 687 335 31 Figure 3 5 7 Viewing journals created from Excel 3 5 4 AAROSendText AAROSendText is used for data reported through text forms The AAROSendText formula in Excel contains the period company and the name of the text form associated with the entry Data is then picked up in cells directly to the right of the formula and parameters follow the same order as you would find them in the relevant form in the AARO application on the menu Data Entry Input To help explain how the formula works we have included an example of an AAROSendText formula below This example works with the formula arguments of period 0912A company ARLANDA and text input form SHARESSUBS AAROSendText Period 0912A Eel 09124 Company ARLANDA FE ARLANDA Form SHARESSUBS Fe SHARESSUBS Formula result 1 Figure 3 5 8 AAROSendText formula example In the example given below the formula was entered in cell A2 and the data relating to the text input form SHARESSUBS was entered in cells B2 C2 D2 E2 F2 G2 H2 and 12 i e the input form data was entered in the cells directly to the right of the AAROSendText formula AARO SYSTEMS AB 33 t ee DVN
39. anyRate formula displays the exchange rate for the reporting currency of a company based on the period and rate type For reference an example of an ABSGetCompanyRate formula is outlined below along with a brief explanation of the formula parameters underneath ABSGetCompanyRate Period 0912A Figs 09124 Company ATHENS Ess ATHENS Rate Type Clo Fis Clo Formula result 9 4735 Figure 2 6 2 ABSGetCompanyRate formula example Field Description Period Period for which data is to be retrieved Company Company code Rate Type Rate translation type e Ope opening e Ave average e Clo closing 2 6 3 ABSGetMinorities The ABSGetMinorities formula displays the minority percentage for a specified minority type direct indirect or total for a company shareholding within a legal group for a given period For reference an example of an ABSGetMinorities formula is outlined below along with a brief explanation of the formula parameters underneath ABSGe Minorities Period 0912A Ess 09124 Company HELSINKI Figs HELSINKI Minority type DIR Fis R Legal group MainGroup EA MainGroup Formula result 10 Figure 2 6 3 ABSGetMinorities formula example AARO SYSTEMS AB 18 t E 3 DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL eae AARO Excel retrieve formulas Field Description Period Period for which data is to be retrieved Company Company co
40. at criteria in Excel formulas have the right format otherwise problems can occur Note where possible Microsoft Excel tries to interpret criteria as cell references If it can they are then converted to cell references If it can t then they get quoted and become strings Using the similar example to the direct Excel data entry example outlined above if a period is specified as B0803 for example the formula will look for cell B803 This is because of some of the programming and smart formulas contained in Excel However if the data is entered in quotations such as B0803 the value is taken as absolute 2 6 Description of Excel retrieve formulas Detailed descriptions of all Excel retrieve formulas are provided throughout this chapter along with examples for reference 2 6 1 ABSGetAccountText The ABSGetAccountText formula displays the descriptive text for an account code provided An example of an ABSGetAccountText formula is outlined below with a brief explanation of the required parameter underneath ABSGetAccountText Account code 3010 Es 3010 Formula result Net sales external Figure 2 6 1 ABSGetAccountText formula example Field Description Account code Account code which descriptive text is to be retrieved AARO SYSTEMS AB 17 t ee DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL aiaa mee AARO Excel retrieve formulas STS TEMS 4 D 2 6 2 ABSGetCompanyRate The ABSGetComp
41. d Account code from which data is to be retrieved Currency for which the data to be retrieved Currency translation e g Actual Budget LastYear Accounting type e g Normal IFRS or USGAAP Accounting standard a summary of one or more accounting types AARO SYSTEMS AB 21 Field S T Dim1 Dim2 etc i B A ry E Nn AARO EXCEL CONNECTIVITY 14 0 USER MANUAL AARO Excel retrieve formulas TEMS Description These are defined by the dimension group or legal group name such as Market or Business Unit exact names vary depending on the dimensions setup in each AARO installation followed by followed by value In the example above the dimension separator and dimension values were entered directly into the cell Dimi Business Unit CLOTHES Figs Business Unit CLOTHES Figure 2 6 7 AARORetrieveFlex dimension dimension Value formula Alternatively the values may be taken from in individual cell references e g 148 amp amp 149 instead Some dimensions in the Dim field should be written without space i e LegalGroup ProformaType Proforma type e g Normal AdjLevel Adjustment level e g Company InvType Investment type e g Normal 2 6 7 ABSRetrieveTextAmount The ABSRetrieveTextAmount formula is used for retrieving operational figures that are reported through Text forms For reference an example of an ABSRetrieveTextAmount formul
42. de Minority type Minority type e DIR direct e IND indirect e TOT total Legal group Legal group If left empty the default value is assumed 2 6 4 ABSGetName The ABSGetName formula displays the description of a company group or other dimension member based on the dimension name and its member code For reference an example of an ABSGetName formula is outlined below along with a brief explanation of the formula parameters underneath ABSGethame Type company or group Business Unit Fis Business Unit CLOTHES Tat Code of entity CLOTHES Formula result Clothes Figure 2 6 4 ABSGetName formula example Field Description Type Dimension whose description is to be retrieved e company for company e group for group e dimension name for dimension e g Business Unit Make sure to spell this exactly as defined in the database including any spaces and upper lower case Code of entity Company group or dimension member code e g CLOTHES 2 6 5 ABSGetRate The ABSGetRate formula displays the exchange rate for a specified currency for a given period and rate type AARO SYSTEMS AB 19 t ee DVN ve AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Kh a AN e AN S AARO Excel retrieve formulas lt Da H D For reference an example of an ABSGetRate formula is outlined below along with a brief explanation of the formula parameters underneath ABSGetRate
43. e separator on columns ete COM If cleared a regular line will divide multiple columns This setting works only when the Compact headings setting is ticked AARO SYSTEMS AB 58 Section Rows Fixed Printed reports Fonts AARQ STS TEMS mp Y Field button Truncate zero rows on next run Show bold separator on multi row Compact headings Show codes Show descriptions Portrait Landscape Number of columns in report view Change font Defaults 4 11 Shadow sheet AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel drill down reports Description If ticked blank rows will be truncated on next report run If cleared blank rows will be shown This setting is not applied to a parameter with All values selected If ticked the bold line will divide multiple rows If cleared a regular line will divide multiple rows If ticked row headings if several are shown in one column If cleared row headings if several are shown in several columns If ticked codes for fixed report parameters are shown If ticked description for fixed report parameters is shown If selected the report in the print preview mode will be shown in the portrait format If selected the report in the print preview mode will be shown in the landscape format Number of columns printed on one page in the print preview mode Font for the printed report is set up here In the drop
44. el Figure 2 1 1 Inserting an AARO formula into Excel worksheet The AARO Excel retrieve formulas are listed under the AARO data retrieve category Search for a function Type a brief description of what you want to do and then dick Go Or select a category Most Recently Used Lookup amp Reference Select a function Database ae Text Logical Information Compatibility AARO data send a PPE coe eee eee ee eee eee p Help on this function Figure 2 1 2 Selecting AARO data retrieve formulas 2 2 Entering AARO Excel formulas directly If you are a more experienced Excel user and or working with existing formulas it is possible to enter the required AARO retrieve equation directly in the formula bar for example AARO SYSTEMS AB 15 t T 3h AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL a b AARO Excel retrieve formulas S TEMS lt B ope AARORetrieveFlex 0912A ARLANDA 3010 LOC 2 3 Formula results Before entering data using Excel retrieve formulas it is worthwhile noting that when entering formula arguments the formula result highlighted below contains useful information ABSGetAccountText Account code 3010 Fel 3010 Net sales external No help available Formula result Met sales external Help on this function Figure 2 3 1 Viewing AARO data retrieve formula result Some examples of formula results might be e Formula result 0
45. el there is no need to issue the log off command separately as there is an automatic log off from AARO in Excel There will also be an automatic log off from AARO if there is no activity for 2 hours or for some other specific period of time that has been set up by the system administrator Log Off from the AARO menu in Excel is an option if a logoff from AARO in Excel without closing Excel is requested ABS term In this manual there is frequent reference in formulas to ABS This is because the AARO application was formerly known ABS Aaro Business Systems The older product name has been preserved in some of the Excel formulas so that customers who working with existing Excel workbooks do not have to make unnecessary changes From a working point of view the terms ABS and AARO are interchangeable AARO is simply the more modern term AARO SYSTEMS AB 14 t F Sh AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL D AARO Excel retrieve formulas S TEMS lt p gt 4 2 AARO Excel retrieve formulas Formulas in Microsoft Excel can be used to retrieve data from the AARO application into Excel workbooks This provides extra flexibility and options for data manipulation in Excel This section of the manual refers to the Excel formulas used to retrieve data from AARO to Excel 2 1 Selecting Excel retrieve formulas The various AARO Excel retrieve formulas can be accessed by pressing the Insert Function button in Exc
46. ent Office com privacy statement Customer Experience Improvement Program Security amp more Learn more about protecting your privacy and security from Office com Microsoft Trustworthy Computing Microsoft Excel Trust Center The Trust Center contains security and privacy settings These settings help keep your computer secure We recommend that you do not change these settings Figure 1 2 3 Excel 2007 Macro security settings On the Macro Settings menu tick the setting Disable all macros except digitally signed macros and click OK Trust Center Trusted Publishers Trusted Locations Trusted Documents Add ins ActiveX Settings Macro Settings Protected View Message Bar External Content File Black Settings Privacy Options Macro Settings O Disable all macros without notification Disable all macros with notification Disable all macros except digitally signed macros Enable all macros not recommended potentially dangerous code can run Developer Macro Settings Trust access to the VBA project object model Figure 1 2 4 Setting up Macro security level in Excel 2007 AARO SYSTEMS AB 11 3 B AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ane Using AARO with Excel an overview ng an 1 3 Open the AARO Excel add in To open the AARO Excel add in 1 Start Microsoft Excel 2 On the File menu select Open 3 Browse to the ABSXL XLL file and open it It is normally found under C
47. er data using the filter button to refine selection criteria for example by choosing business areas that are in the range 3000 3999 Filter range Figure 4 4 8 Filtering parameter values for a drill down report in Excel AARO SYSTEMS AB 52 D pe t ee 3 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL maaana a Excel drill down reports S TEMS lt Nn 4 5 Predefined drill down reports To execute a predefined drill down report 1 On the AARO menu select AARO Drilldown Report Predefined This option is also available via the right mouse button 3 AARO Input Form Figure 4 5 1 Running a saved drill down report in Excel 2 Select a predefined report Income Statement Group Legal in the example provided here L Company Reports Ay Notes Fy Group Reports 3 Balance Sheet Group Legal 7 Income Statement Group Legal Fe Legal Reports 3 C Operational Reports Ca Management Ey Test Reports Figure 4 5 2 Selecting a saved drill down report in Excel 3 If a report saved in the Web client is not supported in Excel the warning appears AARO SYSTEMS AB 53 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel drill down reports A This report cannot be run in Drilldown due to the following validation problems Web reports containing relative period settings are not supported in drilldown reports Click to execute the report in Web Reports Figure 4 5 3 Warning that the report is
48. h Excel an overview pe pe pa STS TEMS Certificate This certificate is intended for the following purpose s Ensures software came from software publisher Protects software from alteration after publication Issued to Aaro Systems AB Issued by Thawte Code Signing CA G2 Valid from 2012 10 03 to 2013 10 14 Issuer Statement Learn more about certificates Figure 1 1 3 Installing the certificate The Certificate Import Wizard window appears 4 Click Next and then select Place all certificates in the following store and click the Browse button AARO SYSTEMS AB AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Using AARO with Excel an overview p Certificate Import Wizard j Certificate stores are system areas where certificates are kept Windows can automatically select a certificate store or you can specify a location for the certificate Automatically select the certificate store based on the type of certificate Place all certificates in the following store Certificate store Learn more about certificate stores Figure 1 1 4 Selecting the certificate store 5 Select the Trusted Publishers folder and click OK Enterprise Trust Intermediate Certification Authorities Active Directory User Object rir H E Show physical stores Figure 1 1 5 Selecting the Trusted Publishers folder 6 Click Next and then Finish AARO SYSTEMS AB AARO EXCEL CONNECTIVITY
49. im2 etc AARO SYSTEMS AB 42 t ee DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ee AARO Excel send formulas ABSSendOS5Flex Value 1000 Fis 1000 Period 09124 Ee 09124 Co ARLANDA Ee ARLANDA Code 3060 Ess 3060 Dimi Product FASTFOOD Fiss Product FASTFOOD Dim2 05 Co HELSINKI Fs OS Co HELSINKI Dm3 E Field Value Period Co Code Dim1 Dim2 etc Formula result 10000 Figure 3 5 20 ABSSendOSFlex formula example Description Value to be sent Period for which data is sent Company for which data is sent identified by company code Account for which data is sent Dimi Dim20 these are defined by the dimension name such as Buying Co or Customer exact names vary depending on the dimensions setup in each AARO installation The format for input is dimension dimension value In the first example specified above the dimension Wa separator and dimension values were given as Product FASTFOOD OS Co ARLANDA In the same way as other parameters dimensions and dimension values may also be specified from individual cell references 3 5 15 Viewing data from ABSSendOSFlex in AARO For reference when ABSSendOSFlex formula data has been successfully sent to AARO it can be viewed in the AARO application on the menu Data Entry Input and in Web reports in the relevant OS
50. ing for server to handle request Done Writing result to Excel TLE LLL p Successtully completed Figure 2 8 1 AARO data retrieve status If data cannot be received from AARO to Excel a dialog box appears showing validation errors The content of this dialog box will depend on the errors returned from the AARO application For data to be received successfully from AARO these errors must be fixed before the process of receiving data is completed AARO SYSTEMS AB 25 t ee AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL nain b AARO Excel retrieve formulas S TEMS lt p gt 4 For reference an example of an error where an account number was not specified has been included here TEN AARO Escel Retrieve Progress eco Ta scanning Excel sheet You have requested figures from 1 penodi for 1 distinct account s Waiting for server to handle request Done 0 of 1 Processing from to 0 Writing result to Excel The Order does not contain any valid limitation values for Period Figure 2 8 2 AARO data retrieve status 2 9 Further Excel formulas Excel retrieve formulas can be combined with Excel send formulas and Excel drill down reports For further reference to those formulas please refer to the appropriate section of the user manual AARO SYSTEMS AB 26 ay ee AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL i Do D S TE
51. ith full Excel connectivity gives users complete support for all of their reporting requirements Accessing Excel drill down reports There are two ways to access Excel drill down reports e Use Live Copy to Excel on the AARO application menu Reports Drill Down e Login to AARO from Microsoft Excel Go through the menu AARO AARO Drilldown Report Log On Log Off AARO Retrieve i AARO Send Create Journals i Paste from AARO AARO Drilldown Report New Report AARO Input Form i Predefined Consolidation Settings AARO About Figure 4 1 1 AARO drill down reports menu Live Copy to Excel from the AARO application This section describes how to copy a drill down report from the AARO Windows client to Excel keeping all drill down functionality active in an Excel workbook To perform copy to Excel from AARO follows these steps 1 Log in to the AARO application and open the menu Reports Drill Down AARO SYSTEMS AB 47 D pe t ee 3 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL 4 7 gt lt E Jaa meN s Excel drill down reports a an 2 Create a drill down report as required 3 Press the Copy button and choose the Live Copy to Excel option e New Workbook opens the Microsoft Excel application and replaces Sheet 1 with an Excel drill down report containing the AARO drill down report data e New Sheet copies the AARO drill down report data to a new worksheet in the open
52. macro viruses and Macro security ufal specify names of trusted macro developers Figure 1 2 1 Excel 2003 Macro security settings Security Trusted Publishers very High Only macros installed in trusted locations will be allowed to run All other signed and unsigned macros are disabled O High Only signed macros From trusted sources will be allowed to run Unsigned macros are automatically disabled Medium You can choose whether or not to run potentially unsafe macros Low not recommended You are not protected From potentially unsafe macros Use this setting only if you have virus scanning software installed or you have checked the safety of all documents WOU Open Figure 1 2 2 Setting up Macro security level in Excel 2003 e Excel 2007 and higher In Excel 2007 and higher the Macro security settings are located on the Excel menu Excel Options Trust Center Click the Trust Center Settings button AARO SYSTEMS AB General Formulas Proofing Save Language Advanced Customize Ribbon Quick Access Toolbar Add Ins AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Using AARO with Excel an overview healthy Protecting your privacy Microsoft cares about your privacy For more information about how Microsoft Excel helps to protect your privacy please see the privacy statements Show the Microsoft Excel privacy statem
53. ng data from ABSSendOS in AARO For reference when ABSSendOS data has been successfully sent to AARO it can be viewed in the AARO application on the menu Data Entry Input and in Web forms in the relevant OS Order amp Sales form A screenshot example of an OS form setup for demonstration purposes is included here Ginput fj 2 2 2 48 Zo oe Bel eee Load Save f Save ast Print Reports Load cage o Fetch Complete eS pokia Replace A st lt bet byt ry tS in EN gt gt A ANNUAL Product Business Counter Discount ica MONTHLY Code Unit Company Sales LOC 4 QUARTERLY LOC iE SALES_EXT Sales FASTFOOD HELSINKI Figure 3 5 19 OS form data sent from Excel to AARO 3 5 14 ABSSendOSFlex As indicated in the previous chapter OS is short for Orders and Sales which is what OS forms have historically been used for Send formulas containing the OS formula send data into OS forms in AARO For data to be transmitted from an ABSSendOSFlex formula in Excel to AARO an appropriate OS form must be set up to receive the data In the case of the example given below we set up an OS form in AARO specifically to handle this example The ABSSendOSFlex formula is very similar to the ABSSendOS formula except that dimensions defined in the formula are flexible The Flex part of the formula allows users to define dimensions themselves rather than according to predefined criteria see Dim1 D
54. not supported in Windows client Press the Click to execute the report in Web Reports link to run the report in Web client Make further selections if necessary Select Period z Ave Selection Selected items Period E escription Normal A selected 4 All Periods Top 09120 check 1001A Jan 2010 gt 10024 Feb 2010 Zz 10034 March 2010 10044 April 2010 Ss Ki 1005A May 2010 1007A July 2010 1008A Aug 2010 10094A sept 2010 10104 Oct 2010 1011A Now 2010 Calculated 1012A December 2010 Item gt 1101A Jan 2011 aa 11018 Budget Jan 2011 oo 1107A Feb 2011 11026 Budget Feb 2011 y Of All valid Show code Show description Long description Figure 4 5 4 Selecting a period for a drill down report in Excel AARO SYSTEMS AB 54 iE pe t Kr DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL YER s Excel drill down reports 4 6 4 7 S lt Nn The AARO report will then be pasted onto the active worksheet the user can then manipulate and format the data in Excel as desired Right mouse button functions One of the key features of working with AARO in combination with Excel is the ability to work with the right mouse button to call up dynamic functions Depending on the cell selected when the right mouse button is clicked many different features may be accessed such as edit settings delete run a report change report criteria and choose drill d
55. o a cell reference which contains the relevant information AARO SYSTEMS AB 28 t ee DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Kh a AN S TEMS AARO Excel send formulas lt Da 4 D For example here is some data that has been entered directly using the direct Excel data entry method Period 0912A Figs 09124 Figure 3 4 1 Entering parameter value directly However a cell reference containing the relevant information returns exactly the same result Period B2 Ee 09124 Figure 3 4 2 Entering parameter information using cell reference Where cell B2 was entered as follows J 0912A A B 1 E Figure 3 4 3 Selecting cell data Throughout this chapter the model of direct Excel data entry has been used in order to avoid confusion However users may prefer cell references instead depending on the customers needs 3 5 Description of Excel send formulas Detailed descriptions of all Excel send formulas are provided throughout this chapter along with examples for reference 3 5 1 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 AARO SYSTEMS AB 29 t ee 3 AARO AARO EXCEL CONNECTIVITY 14
56. olumn right click the form and select Add Column CLOTHES COSMETICS FOOD HOUSEHOL Delete Column AARO Input Form a Figure 5 3 1 Adding a column to an input form in Excel Select the dimension value and click OK Figure 5 3 2 Selecting a dimension value for a new column 2 To delete a column right click the column and press Delete Column When prompted confirm that you wish to delete the column AARO SYSTEMS AB 65 t ee 3 DN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms wr lt p gt aL m Y Total ENTER FINANCE HOME Total A 47 Add Column AARO Input Form b Figure 5 3 3 Deleting a column 3 To enter values enter data in the white editable cells To save entered values proceed as described in 5 4 Export data to AARO 4 To change parameter value if enabled by sheet settings type in a new value in the cell To load form for changed parameters proceed as described in 5 5 Reload an input form in Excel 5 4 Export data to AARO To export data into the AARO application choose the menu AARO AARO Input Form Save Log n Log Off AARO Retrieve b AARO Send b Create Journals Paste from AARO Consolidation Load Settings Edit AARO About Settings Color Palette Visual Refresh Reconcile Figure 5 4 1 Exporting input form data to the AARO application Alternatively click the right mouse button and choose the menu
57. ompany dimensions i e business unit may be entered here 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 Note 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 overwritten with the values sent from Excel 3 5 7 Viewing data from ABSSendMatch in AARO For reference when data has been successfully sent to AARO it can be viewed in the AARO application on the menu Data Entry Match A screenshot for the ABSSendMatch example illustrated above has been included here AARO SYSTEMS AB 36 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL A e oy Me S AARO Excel send formulas Nn ve Load Save lol Print bet ak a i g Load ak aan Contact om GY wer Gl iL a wee Intercompany Assets j a AE My companies jusiness Arez Counter BA 1320 T ARLANDA LT fin group pe Sis GOTHENBU ater pie a an TransCurr TransAmount ELHESNKA HELSINET Helsinki GOE 12000 122500 lf ATLANTA Figure 3 5 13 Match data sent from Excel to AARO 3 5 8 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 in
58. on of the user manual AARO SYSTEMS AB Je t g DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL paaa ASTE Excel input forms 5 5 1 5 2 S T MS Nn T Excel input forms Excel input forms provide an alternative to direct data input using the AARO application This provides the user with the flexibility to use the wide array of features available within the Excel application before data is uploaded into AARO Limitations of Excel input forms Currently only forms of type Input are available in Excel In addition the required form must have the Use as Output Report setting on the AARO application menu Utilities Edit Input otherwise the form cannot be output into Excel Input responsibilities are not applied to input forms in Excel Opening balances in input forms may include values booked through journals according to system settings made by the administrator Please refer to the 2 9 12 Journal Settings section of the AARO 14 0 User Manual For more information regarding different form types within the AARO application please refer to the 2 4 4 Forms section of the AARO 14 0 User Manual Load an AARO input form into Excel To load an AARO input form into Excel 1 Go to the menu AARO AARO Input Form Input Forms s Log On Log Off AARO Retrieve AARO Send Create Journals Paste from AARO AARO Drilldown Report Consolidation Settings AARO About Figure 5 2
59. own criteria The example below shows how it is easy to change the report currency by pressing the right mouse button on the cell containing currency information in the report header In the report example below clicking right mouse on cell A3 enables the user to change currency AARO Drilldown Report d DEE 5 399 6 4999 4 Cut EUR 7 a Copy agii Figure 4 6 1 Changing currency using the right click menu In the same way the other report criteria can be changed e g period Drill down options Pressing the right mouse button on rows columns or cells in Excel drill down reports allows the user to generate greater report detail as required and drill down into report information This example shows a column being drilled down by reporting currency A C D source 1 Income Statement Group Legal CurrencyTranslation 2 Currency Legal Group Adjustment Level MainGroup Investment Type Proforma Type 3999 Total inc 6 4999 Costs of AARO Drilldown Report l liseri Figure 4 7 1 Drilling down column by reporting currency When a report has been drilled down for refined criteria the drilldown report opens in a new Excel worksheet AARO SYSTEMS AB 55 4 8 4 9 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL an 3 SYSTEMS Excel drill down reports Book B C D E F G 1 Income Statement Group Legal 2 Currency Legal Group Period a a 4 DKK EUR SEK USD GrandTotal 5 3999 Total inco 5825878
60. put forms Here is an example of an ABSSendOper formula ABSSendOper Value 10000 mel 10000 Period 09124 BE 0912A Code 3010 mE 3010 Company ARLANDA Fis ARLANDA Product Sel Business Unit MEDIA Fas MEDIA Business Area 7 cas Customer g Fas Market BE NA BE Formula result 10000 Figure 3 5 14 ABSSendOper formula example Field Description Value Value to be sent Period Period for which data is sent Code Account for which data is sent Company an for which data is sent identified by company code AARO SYSTEMS AB 37 t r 3h DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL iaiia D AARO Excel send formulas AWA a SYSTEMS Field Description lt dimensions gt 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 NA Indicates that this field is not used It is reserved for more dimensions If changes for a form are accepted through journals only the JV Adjustment dialog is opened with the template for Adjustment journal to be created For more information on adjustment journals please refer to the section 3 2 12 Adjust financial information in the AARO 14 0 User Manual 3 5 9 Viewing data from ABSSendOper in AARO For refe
61. rence when ABSSendOper data has been successfully sent to AARO it can be viewed in the AARO application on the menu Data Entry Input and in Web reports In the example above data was sent to the form IS_ALL for the business unit MEDIA a screenshot has been included for reference here Input Som m ae gel ee eee Load Save I la Em I F Reports Load He 8 umn Fetch Complete Row Row Replace Period Company Compare With 09124 e T ARANDA eT B EY RRS Income a are TB ANNUAL k MONTHLY AE BS_INTANG Intang 3919 Net sales external 10 000 gt Ee BS TANG Tangible 3060 Net sales intemal gt dE EQUITY Specificatii 3080 Discounts hs tee CF_ALL_1 CASH FI Figure 3 5 15 Input data sent from Excel to AARO 3 5 10 ABSSendOperFlex 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 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 AARO SYSTEMS AB 38 t ee DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ots oe i AARO Excel send formulas pe Je YSTEMS ABSSendOperFlex Value 10000 Figs 10000 Period 09124 Fis 09124 Co
62. rill down report from the excel sheet Follow the instructions to confirm deletion AARO SYSTEMS AB 60 l r Sh DVN To AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ee Excel drill down reports lt P gt 4 Log On Log Off AARO Retrieve AARO Send Create Journals Paste from AARO Consolidation Run Settings Edit AARO About Settings Shadow Sheet Run All Reports in Workbook Figure 4 12 1 Deleting a drill down report from Excel Note although values may still appear in the current worksheet after they have been deleted through this method right mouse button formulas such as drill down will not be available 4 13 Run all reports in a workbook Choose AARO Drilldown Report Run All Reports in Workbook to update all of the various drilldown reports that may be contained in worksheets in an open Excel workbook AARO SYSTEMS AB 61 t ee 3 DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL ah Excel drill down reports SYSTEMS ARO Log On Log Off AARO Retrieve AARO Send create Journals Paste from AARO b Consolidation Run Settings Edit AARO About Settings Shadow Sheet Delete Figure 4 13 1 Running all drill down reports in Excel 4 14 Further Excel formulas Excel drill down reports can be combined with Excel send and Excel retrieve formulas For further reference to those formulas please refer to the appropriate secti
63. rs must be fixed before the process of sending data is completed For reference an example containing some validation errors has been included below Data was sent for some period company combinations but validations failed for others AD ANDA Cell reference Validation error TestBook 14 xls Send 0 35 Buying Customer is required TestBook 14 xls Send 0534 Counter Company is required Copy to dipboard Figure 3 7 2 Excel data send validation errors Further Excel formulas Excel send formulas can be combined with Excel retrieve formulas and Excel drill down reports For further reference to those formulas please refer to the appropriate chapter of the user manual AARO SYSTEMS AB 46 t ee DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL 4 4 1 4 2 m Y SYSTEMS Excel drill down reports Excel drill down reports Excel drill down reports can be used in a similar way to drill down reports in the AARO application Working with Excel instead of the AARO application brings various advantages and disadvantages depending on which task the user is working with For example in Excel drill down reports you can also take advantage of all Excel features such as formatting calculations connectivity to the Excel chart wizard and so on However some users may find that it easier to drag and drop report criteria and visualize information within AARO drill down reports AARO s drill down reports combined w
64. sen in the left pane For more details on the AARO Reconciliations functionality please refer to the Reconciliations section of AARO 14 0 User Manual To run a reconciliation report 1 Select the menu AARO AARO Input Form Reconcile AARO SYSTEMS AB 74 Log On Log Off AARO Retrieve d AARO Send b Create Journals Paste from AARO Consolidation settings AARO About AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms Load Edit Settings Color Palette Visual Refresh Save Figure 5 10 1 Running the reconciliation report 2 Make reconciliation report settings and press OK AARO SYSTEMS AB 75 AARO EXCEL CONNECTIVITY 14 0 USER MANUAL Excel input forms TOTAL ASSETS BS_ALL ZILE TOTAL EQUITY A Y Extraordinary inc EO E08710 EO income Extraordinary ex EO E08750 EO costs Figure 5 10 2 Reconciliation report settings AARO SYSTEMS AB 76 D pe t F Sh AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL HAN Additional AARO Excel options 6 6 1 6 2 S ng an Additional AARO Excel options The AARO Excel add in provides some additional options that can be helpful when working with drill down reports input forms and AARO formulas Order consolidation One can order a consolidation from Excel 1 Select menu AARO Consolidation Log n Log Off AARO Retrieve d AARO Send Create Journals Paste from AARO
65. settings see the following sections 5 7 1 Protection options Various protection options are available for the Excel worksheet which is being used as an AARO input form choose the option best suited to your needs e Maximum protection Strict input form style Only enabled figure cells are editable Everything else on the sheet is protected All changes to the sheet except simple figure editing must be performed through the menus e g adding columns changing fixed selections e Medium protection Relaxed input form Enabled figure cells column headings fixed selections and other cells outside the figure area are editable Only not disabled cells in the figure area are protected i e sums and greyed out cells e No protection Pure Excel Sheet No protection at all is applied Anything on the sheet can be edited 5 7 2 Selectable cells Ticking the box to Allow only editable cells to be selected in the sheet makes Tab or Enter jump to the next editable cell is useful if you want to restrict the form to editable cells only it may be useful for manual AARO SYSTEMS AB 71 t g AA rC AARO EXCEL CONNECTIVITY 14 0 USER MANUAL mp v SYSTEMS Excel input forms entry of a large number of figures as each entry press of Enter or Tab moves the cursor onto the next editable cell Note This feature is used with maximum or medium level protection only 5 7 3 Look and feel Using the Paint
66. sible to enter the required AARO send equation directly in the formula bar for example ABSSendOperFlex 5555 0912A ARLANDA 3010 Business Unit FOOD Formula results Before entering data using Excel send formulas it is worthwhile noting that when entering formula arguments the formula result highlighted below contains useful information AAR OSendText Period 09124 Company ARLANDA Form EO No help available Formula result 1 eo on this function Figure 3 3 1 Viewing AARO data send formula result Some examples of formula results might be e Formula result 0 formula is not completed e Formula result 1 formula is ready to send a string of information according to template criteria e Formula result 4869 or any other number This refers to a specific number which will be sent from an Excel send formula to a corresponding field in AARO Note no validation has taken place in AARO at the stage checks such as whether an account or period is valid do not take place until the user has sent the data from Excel to AARO Note Values reported using send formulas are reported on default values set up in AARO on the menu Utilities Application Management folder Default Values section Default input values Cell references and direct data entry in Excel In Excel send formulas it is possible to use either direct Excel data entry input data going directly into the function box or refer t
67. t Run All Reports in Workbook Figure 4 9 1 Editing a drill down report in Excel 4 10 Drill down report settings in Excel Report settings can be changed as follows 1 Choose Settings from the AARO Drilldown Report menu Column Drilldown by F Run do Cut ja Copy Fet Faste Shadow Sheet Paste Special Delete Insert Run All Reports in Workbook Figure 4 10 1 Setting up a drill down report in Excel 2 Choose to change report layout AARO SYSTEMS AB 57 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL m m D Excel drill down reports Printed reports Portrait Landscape E Truncate zero columns on next run Show bold separator on multi column Rows Number of columns in report view F Truncate zero rows on next run Show bold separator on multi row Compact headings Landscape 15 oh Portrait Fixed Show codes Show descriptions Fonts Sample Heading Sub heading Sweden Norwa Blue things 123 000 90 000 Green stuff 45 500 12 250 Figure 4 10 2 Changing drill down report settings in Excel Report settings have the following fields and buttons Section Columns Field button Description Truncate zero If ticked blank columns will be truncated on columns on next next report run run If cleared blank columns will be shown This setting is not applied to a parameter with All values selected Show bold If ticked the bold line will divide multipl
68. t Field with Content USERID values are ignored the current user is always identified 3 5 5 Viewing data from AAROSendText in AARO For reference when text form data has been successfully sent to AARO it can be viewed in the AARO application using the menu Data Entry Input and choosing the relevant form the same form as referenced in the Excel formula for AAROSendText Here is a screenshot of how the information looks in a SHARESSUBS text input form in AARO AARO SYSTEMS AB 34 AARO AARO EXCEL CONNECTIVITY 14 0 USER MANUAL mD AARO Excel send formulas A w a eng oe ace Replace Fetch Complete ae nt RL alli Load eo me RA Em Company omre ARLANDA Sthlm Arlanda O91 24 Y ARLANDA Dec 2009 GOLD ORIGINAL SEK Links Home Shares in subsidiaries E Company Company Registered Share of Share of Woof Book Value on liia AUDITCOSTS Audit co Registration office equity votes shares value stock BOARD MEME Numbe exchange CONT_LIAB Contingent ARLANDA 123456 Stockholm 55 70 700 1 000 000 7 000 000 TOTAL lB SALARIES Salaries and ER SHARESSUBS Shares i wolf STAFFNUMBE Average Figure 3 5 11 SHARESSUBS form data sent to AARO from Excel Note the order of the data entry fields in AARO from left to right is exactly the same as those entered in Excel for a text form 3 5 6 ABSSendMatch The ABSSendMatch formula is used to send intercompany transac
69. template Co Code Loc for local currency journal or Amount for group currency journal The following fields will get the default values if not completed in the journal template e FromCo will get the value from field Co e 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 GroupCurr Excess Value LegalType Amount Type PEID GWID PECode OwnedCo GroupCurr Notes e Recurrent journals cannot be created from Excel e MULTIJV journals cannot be created from Excel e 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 e The LegalGroup column must be left empty e One PEID GWID combination corresponds to one Code PECode combination e Existing PEID GWID only can be used e Only the following amount types are allowed with past equity journals Opening Change and Closing B C D E F 6 H l K L 1 2 ID Currency JV Type Description 3 FPE1 LOG Normal Fast equity 4 FromCo Code Loc LegalGroup LegalType Amount Type PEID GWID PECode OwnedCo GrouptCurr 5 PARENT PE1310L 27118 PE Change Ace 01 LOC FE1310L ATLANTA SEK 6 PARENT PE1310P 175366
70. tings AARO About Figure 1 3 2 Opening AARO menu from Excel Note prior to Excel 2010 the AARO Excel add in should be started before any changes are made in the Excel workbook Log on Logon is necessary before any data can be accessed in the AARO database To log on to AARO from Excel after opening add in follow these steps 1 On the AARO menu in Excel select Log On The logon dialogue box will be displayed 2 Select a database or just accept the default choice and enter user name and password Log on to AARO in the same way as uSual Press Login rovers D AARO Version 14 0 SYSTEMS Database username Password Figure 1 4 1 Logging on the AARO application AARO SYSTEMS AB 13 l ee DVN ve AARO EXCEL CONNECTIVITY 14 0 USER MANUAL i Fa V a 1 5 1 6 SYSTEMS Using AARO with Excel an overview 3 The application will now operate and download some lists from the database Initially this may take some time but subsequent logon attempts will be much faster 4 After logging on to the AARO database from Excel all the AARO Excel features can be accessed Note It is recommended to quit and restart Excel once after having logged on to a certain AARO database for the first time This is because certain help texts in the formula wizard of Excel relating to AARO formulas will be updated at the first logon but displayed only after Excel has been restarted Log off When closing Exc
71. tions to AARO An example of an ABSSendMatch formula is outlined below with detailed explanations of the parameters underneath ABSSendMatch TransCurr EUR Fas EUR TransAmount 12000 Fas 12000 LocValue 1272500 Fas 122500 Period 09124 Fas 0912A Code 1320 Fas 1320 Company ARLANDA Fa ARLANDA CounterCo HELSINKI Fac HELSINKI Business Unit Fas Business Area FINANCE Fa FINANCE Counter BU Bj Counter BA ENTER FR ENTER N A e N A Fas Formula result 122500 Figure 3 5 12 ABSSendMatch formula example AARO SYSTEMS AB 35 Field TransCurr TransAmount LocValue Period Code Company CounterCo lt own dimensions gt lt counter dimensions gt p lt Nn DVN te AARO EXCEL CONNECTIVITY 14 0 USER MANUAL D AARO Excel send formulas i TEMS Description Transaction currency This field should be completed in if it is used in the Match form Transaction amount value in transaction currency if used in the Match form If the field is empty and transactions amounts are used in the match form a zero value will be sent Amount in the company s local currency If the field is empty a zero value will be sent Period for which data is sent Account code for which data is sent Reporting company code Counter company code Dimension value if the form is to be reported ona dimension level a value for one of the reporting c

AARO EXCEL CONNECTIVITY 14.0 USER MANUAL

Contents

Download Pdf Manuals

Related Search

Related Contents