The IDL data collecting form enables the subsidiaries of a group to collect data in an Excel form and to provide it to the parent company for the consolidation financial statement. The data collecting form supports the same computational logic as IDL Konsis The standardized form sheets supplied by IDL are password-protected against changes. In case the protection is forced, it is most likely that the phased macro routines of the IDL data collecting form can no longer work correctly. In such a case IDL cannot provide any support.
The standard setting of the Excel Macro Security is 'high' or 'very high' at every installation of the Microsoft Office package. This Microsoft setting serves the security of the operation system in order to avoid a virus attack through an Office folder. In order to be able to work with the IDL data collecting form, you should at least set the macro security on "medium". Thus the user can decide for each Office folder, whether he wants to activate or deactivate macros. Without the macro routines, the IDL data collecting form is not ready for operation.
For this case applies the following guideline: Only activate macros from trusted sources. If, e.g., you do not know the dispatcher of an e-mail, you should deactivate the macros and maybe contact the dispatcher, in order to avoid a virus attack.
You can check or change the macro security of your Microsoft Excel installation as follows:
Figure: Macro Security Check in Microsoft Excel
If the macro security is not set on 'medium', as shown in this example, please change the setting to 'medium', if necessary after consulting your IT-department. If the data collecting form is opened, it has to be closed and restarted. You do not need to restart Excel.
Figure: Macro Security Levels
| Release: | comp: | Remarks: |
|---|---|---|
| Excel2000 | YES | with Service Release 2 (or higher); see compatibility restriction |
| ExcelXP | YES | with Service Release 2 (or higher) |
| Excel2003 | YES | with Service Release 2 (or higher) |
| Excel2007 | Yes | with Service Release 2 (oder höher) |
| Excel2010 | Yes | 32-Bit |
| Excel2010 | No | 64-Bit --> this restriction is only for the Data entry decentral |
According to Microsoft® the French Government has decreed a regulation, which prohibits password protections in digital documents, such as Microsoft® Excel sheets. The French users have no possibilities either to place a password. If these users, however, receive a sheet protected by a password e.g. from a German user, the French Microsoft® Excel releases 97 and 2000 display an error warning, because they cannot manage the placed passwords. The following table displays the compatibility/incompatibility of diverse French Excel releases:
| Release: | comp: | Remarks: |
|---|---|---|
| Excel2000 | YES | with Service Release 2 (or higher) and update for Service Release 2 |
| ExcelXP | YES | with Service Release 2 (or higher) |
| Excel2003 | YES | with Service Release 2 (or higher) |
| Excel2007 | Yes | with Service Release 2 (oder höher) |
| Excel2010 | Yes | 32-Bit |
| Excel2010 | No | 64-Bit --> this restriction is only for the Data entry decentral |
Excel2000 SR1 incl. update 2 and higher ignore the password protection. That means the French users can open the data collecting forms without problems. This, however, involves the disadvantage that the sheet protection is completely deactivated, which enables the French users to arbitrarily change the structure and contents of the IDL data collecting form. If someone changes the IDL data collecting forms, they might become unusable making it necessary for the French user to complete them anew. For data collecting forms modified in such a way, IDL cannot provide any technical support. Please instruct you French subsidiaries not to manually modify IDL data collecting forms under no circumstances.
Up to now we are not aware of further incompatibilities with Excel releases in other countries.
Please download the corresponding updates on the respective Micorosft® website. If the following list does not include your required country/language, please use the last link - worldwide - to get to the corresponding national Microsoft® website.
| Language | Link: |
|---|---|
| German | http://www.microsoft.com/germany/ |
| English (UK) | http://www.microsoft.com/uk/ |
| English (US) | http://www.microsoft.com/ |
| French | http://www.microsoft.com/france/ |
| - worldwide - | http://www.microsoft.com/worldwide/ |
Microsoft provides an Office Release Excel:Mac for the operation system Apple Macintosh, but the Visual Basic Macro code is not compatible. 'Excel:Mac' modifies the data collecting form in such a way that it does not work on 'normal' Windows operation systems and Office releases anymore. That's why the IDL Connector and the data collecting form in connection with 'Excel:Mac' are not supported!
The menu of the IDL data collecting form is subdivided into three issues:
Figure: Menu structure in the IDL data collecting form
Here the user can create and check details, such as fixed assets transactions or IC-account balances. The IDL data collecting form adds a respective table sheet in Excel for the user to enter the details.
Here the user can only see those details activated in IDL Konsis by the parent company. If you miss any movements, please contact your parent company.
Here the user can change the online language of the IDL data collecting form to those the parent company has selected at generating the data collecting form. If the application does not display 'online language', the parent company has only chosen one language for the form.
This functionality enables the user to add several complement sheets to the data collecting form. These table sheets do not contain a password protection and can arbitrarily be modified.
Up to now the IDL data collecting form supports the following three plausibilities, which can be provoked by => 'details' => 'account balances' => 'check':
The IDL data collecting form accumulates all fixed assets transactions with posting key 05 from the table sheet 'fixed assets transactions' and compares this sum with the sum of the depreciation accounts (account flag 1 = D) from the account balance sheet. A log is then displayed at the end of the account balance sheet.
This check is not performed, if the fixed assets transactions are deactivated in the IDL data collecting form, e.g. because they are generally not maintained in the IDL Konsis, or because the account master file does not contain an account flag 1 = D.
The IDL data collecting form accumulates all provisions with posting key 03 from the table sheet 'provisions' and compares this sum with the sum of the dissolution accounts (account flag 1 = Y) from the account balance sheet. A log is then displayed at the end of the account balance sheet.
This check is not performed, if the provisions are deactivated in the IDL data collecting form, e.g. because they are generally not maintained in the IDL Konsis, or because the account master file does not contain an account flag 1 = Y.
The IDL data collecting form accumulates all provisions with posting key 04 from the table sheet 'provisions' and compares this sum with the sum of the accounts for provision additions (account flag 1 = Z) from the account balance sheet. A log is then displayed at the end of the account balance sheet.
This check is not performed, if the provisions are deactivated in the IDL data collecting form, e.g. because they are generally not maintained in the IDL Konsis, or because the account master file does not contain an account flag 1 = Z.
You can find the log on the account balance sheet below the data entry and the reconciliation block. This log displays the statuses of the plausibilities. If the status is not correct, the user has to check or correct the entered data. After performing the correction you should repeat the check in order to set the status on green.
In the following figure the statuses of the plausibility check are correct. The user has performed the data collection correctly.
Figure: Correct statuses of the plausibility check
In the following figure the statuses of the plausibility check are incorrect. The user has not performed the data collection correctly and has to once more check the entered values.
Figure: Incorrect statuses of the plausibility check
The different data collecting form types are explained in the following screen shots. Definition of the differently colored arrows on the screen shots:
The standard data collecting form serves the collection of data in column G (blue arrow) of the data collecting form. Column F displays the values from the previous period (grey arrow). Only values from the current period can be entered in the accounts (white fields). Position and total lines are automatically calculated. An occurring difference is displayed in the data collecting form in cell G2. For further information please see the reconciliation block at the end of the account balance sheet.
Figure: Standard data collecting form
Just as in the standard form the balance sheet accounts are entered in column G in the cost of sales method. A subdivision by cost centers is not possible. The P/L accounts have to be collected in the cost centers from column I to ... (white fields). The sum of all cost centers is automatically accumulated in column G. Position and total lines are automatically calculated. An occurring difference is displayed in the data collecting form in cell G2. For further information please see the reconciliation block at the end of the account balance sheet.
Figure: Cost of sales data collecting form
This form works similar to the previously described cost of sales method. This modified data collecting form requires the additional entry of the account balance in column G for the comparison with the details via the cost center balances. If the entered account balance does not concur with the detail from column I to ..., the field of the account balance is colored red, just as shown in the following screen shot.
In case of a red field the user has to check, whether the account balance in column G is correct, the details from column I to ... are incorrect / incomplete, or whether the details are correct and the account balance is incorrect.
Figure: UKV-X- data collecting form
This form supports the collection of data with business units. The collection is performed from column I to ... (white fields). The sum of the business units for each account is automatically accumulated in column G. Position and total lines are automatically calculated. An occurring difference is displayed in the data collecting form in cell G2. For further information please see the reconciliation block at the end of the account balance sheet.
Figure: GB data collecting form
This form works similar to the previously described form for the collection of business units. This modified data collecting form, however, requires the additional manual entry of the account balance sum in column G for the comparison with the details via the individual business units. If the entered account balance does not concur with the detail from column I to ..., the field of the account balance is colored red, just as shown in the following screen shot.
In case of a red field the user has to check, whether the account balance in column G is correct, the details from column I to ... are incorrect / incomplete, or whether the details are correct and the account balance is incorrect.
Figure: GB-X- data collecting form
In this form the user can enter the balance according to the general ledger in column I and perform repostings in column J. Divergent from the other data collecting forms, this one contains two previous period columns: F and K. Column F displays the previous period values after possible repostings. Column K displays previous period values before repostings.
Figure: HBI/II data collecting form
This form enables the user to collect data from column I to ... for several periods in only one data collecting form. In column G
Figure: Multiple period data collecting form
This form works similar to the standard form. The only difference is the entry of the transaction details into columns instead of lines.
Just as shown in the screen shots under point 4.1 to 4.8, the values for the current period have to be entered in the respective columns (blue arrows). In cell G2 a possible difference is displayed, which has to be eliminated by the user. Column H displays the respective debit/credit-code of the account. The following scheme was used for this purpose:
| P/L-code | Type | debit/credit-code | P/L-code | Type | debit/credit-code | |
|---|---|---|---|---|---|---|
| 1 | Assets | S | 6 | stat. Assets | S | |
| 2 | Liabilities | H | 7 | stat. Liabilities | H | |
| 3 | Income | H | 8 | stat. Income | H | |
| 4 | Expenses | S | 9 | stat. Expenses | S | |
| 5 | stat. Mass | S |
If, however, an account balance turns, i.e. the value is converse to this standard S/H code, the user has to enter this value accordingly with a negative sign.
Since the IC account balances comprise two different account types, the user has to check the inter-company relations. Column D contains these account flags. Account flag 'J' means that the user can perform an IC-detail for this account (not obligatory), which means that only one part of the account balance may have resulted from intercompany. Account flag 'I' obliges the user to distribute the complete balance from column G to the different companies. If two companies have agreed intercompany deals with a transaction currency, this currency can be entered in the quick entry line.
The next screen shot shows that the account-no. field of the first account with the account flag 'J' is not colored red. The fields for the 'I' accounts, however, are colored red, because of yet existing differences.
Figure: I-Accounts with differences
After clearing up the differences the red coloring is removed.
Figure: I-Accounts after clearing up the differences
The cost center balances have to be maintained identically.
Normally transactions are entered into lists, as you can see in this fixed assets transactions example. In contrast to the IC account balances and the cost center balances, the user only needs to clear up the difference between the current account balance and the account balance from the previous period, because the closing balance has been carried-forward in IDL Konsis. This means that the remaining difference has to be cleared up (explained) with one or several transactions. The account numbers in column B is colored red, if this account shows a difference. If an account (or the Excel line) is active, the user can see the respective difference of each account in the grey quick entry line.
The quick entry line serves the entry of transactions. Here the user has to enter the account number (obligatory), the posting key (obligatory), a possible IC-company (optional) and the value. The IC-company should only be maintained, if this account contains the account flag 1 = 'I' or 'J' in column F. Please ask your parent company, whether the IC-company is to be maintained or not.
Figure: Quick entry line for the entry of transactions
After entering the transactions in the quick entry line, the data collecting form can no longer detect any differences and removes the red coloring from column D.
Figure: Transactions without differences
The details for capital transactions, provisions and possible development transactions have to be maintained identically. Points 4.1 and 4.7 describe variants, which require the entry of further fields, such as a business unit or a field for the period in the quick entry line.
Point 4.8 describes the entry of transactions in columns rather than in lists. The user receives all posting keys in columns thus facilitating the direct entry of the values in the columns.
Figure: Entry in columns
Via the function 'insert detail lines' by clicking the right mouse tab, the user can perform a complete detail in the transaction sheets, the IC account balances and the cost center balances, i.e. the application inserts all postings keys for this account.
Figure: Insert detail lines
Afterwards the application has automatically inserted all lines. The user can now chose to insert the values directly in column G.
Figure: Detail lines inserted
If the user wants / has to use a posting key twice for the same account, a line can be doubled by placing the cursor on the white line, which is to be doubled, and performing the function 'insert detail lines'.
The user can place remarks regarding the maintained data in the account balances and the diverse details (only in the white fields!) via the 'right mouse tab' - 'remark'.