Getting Started with the Financial Consolidation Model

Return to the Financial Consolidation Model Overview.

To familiarize yourself with the Financial Consolidation model, you can install it with a sample configuration that includes sample data. Please note that you need to install the Jedox platform and the Demo content before installing models with sample data. You can find directions on configuring the model with sample data in Setting Up the Financial Consolidation Model. The model can also be set up without sample data. Follow the steps below to configure and populate the model with your company's data.

The following instructions focus on using an Excel workbook to compile relevant data and figures into worksheets, which will then be uploaded to the Jedox database. Although Jedox offers multiple methods to achieve this—such as manually editing elements in the Modeler or creating a job in the Integrator to connect to your ERP—this guide specifically covers the Excel workbook method. However, feel free to choose the approach that best fits your needs.

The Financial Consolidation model includes an Excel Workbook that provides a sample configuration and data set. You can access this file through Jedox Web by navigating to Designer > Models > Financial Consolidation > Files > Sample Data > Financial Consolidation Sample Data (IFRS). To customize this workbook with your own configuration and data, first save a local copy by selecting "Export" from the context menu, then proceed to modify it accordingly.

To configure dimensions and cubes, upload your modified Excel workbook into the model using the following steps:

Open the Modeler and navigate to [Database Name] > Dimensions or Cubes. Right-click on the specific Dimension or Cube name, and select Upload file from the context menu.
The Dimension/Cube Upload Wizard opens and guides you through the steps.
Select the modified copy of the workbook from your local file system. For details, see Uploading and Downloading Dimensions and Cubes.

Use the steps below to configure the model and modify individual worksheets in the Excel workbook:

1. Setting up the Model	Install the model without sample data. For detailed instructions, see Setting Up Financial Consolidation Model.
2. Setting up languages	To remove unnecessary languages from the model, follow these steps: 1: Navigate to Modeler → [Database Name] → Internationalization. 2: Select the language(s) you wish to remove, and then click Delete (-). After removing the languages from the model, you can ignore any attributes in the workbook that used those languages. Columns that have been localized will have the attribute name followed by an @ symbol and the country code for the language in the format xx_XX. For example, the attribute `Label` in the `Version` worksheet has localized columns `Label@de_DE`, `Label@fr_FR`, and `Label@es_ES` for translations in German, French, and Spanish, respectively. To add a language, follow these steps: 1: Navigate to `Modeler > [Database Name] > Internationalization`. 2: Click New (+) and choose the desired language from the menu. 3: After adding the new language, open the worksheet of the dimensions or cubes you want to update with translations. 4: Add a new column with the translation label you added. For instance, if you need to add a Portuguese (Brazil) translation for the `Version` dimension's `Name` attribute, add a new column labeled `Name@pt_BR` to the worksheet. Note that updating the model does not impact newly added languages. However, it will reset all existing language settings and restore any languages that were previously deleted.
3. Setting up the Scope of Consolidation	The Legal Entity dimension is essential for generating accurate reports, as it identifies a company or group of companies within the report. You can modify this dimension in the `Legal Entity` worksheet and then upload it to the Legal Entity dimension using the upload wizard. Be sure to use the wizard’s standard settings to populate the dimension accurately, and ensure that the `Currency` attribute is populated with local currencies. The Scope dimension provides a comprehensive flat list of all target elements needed for financial consolidation. The Partner Entity dimension includes elements representing business relationships with others companies in a group. Lastly, the Scope of Consolidation report displays additional parameters for the groups included in the `Scope of Consolidation` cube.
4. Adjusting the time span	The time span of the model can be adjusted to fit your organization's historical data and planning horizon in the Time Editor tab of the Month_YTD and Day dimension.
5. Maintaining Versions	The elements in the Version dimension helps you to compare figures for reporting and planning. You can also adjust forecasts and other versions as needed.
6. Uploading chart of accounts	You can upload a chart of accounts from any dimension involved in cash transactions, such as the `BS Account` dimension, `CF Account` dimension, or `PnL Account` dimension. These dimensions include hierarchies for ledger accounts or reporting positions, and you can perform calculations on these accounts within the model. Account details can be edited directly within the account worksheets and then uploaded to their respective dimensions using the upload wizard. When uploading, be sure to select the `"Allow multiple parents within one hierarchy"` option to enable individual accounts to roll up into multiple reporting positions.
7. Setting up the currency conversion	When a company or a group of companies stores accounting data, one currency, such as the Euro or Dollar, must be chosen. This currency, referred to as the Source Currency, will be indicated under the `Currency` attribute within the `Legal Entity` dimension. You need to update the source currencies in the `Currency` worksheet and upload them to the `Currency` dimension of the model. Use the standard settings provided by the upload wizard to populate this dimension. Currency conversion is required when a source currency needs to be converted into another currency for reporting purposes. The set of currencies available for conversion is called the Target Currency. To define target currencies, you need to update and upload the `Currency` and `Target Currency` worksheets to the `Currency` and `Target Currency` dimensions. You can import actual exchange rates into the model using the import exchange rates interfaces. All planning versions (`Budget` and `Forecast` versions) can be set using the January average rate. You can load planning Exchange Rates_YTD via the `Exchange Rates_YTD` worksheet and upload them into the `Exchange Rates_YTD` cube.
8. Setting up the consolidation rules	You must set up the consolidation rules according to the attributes of the chart of accounts in the `Intercompany Account` dimension, which includes the CDIS hierarchies (Capital, Debt, Intra-group Results, and Sales).
9. Adding logo	You can replace the Jedox logo in the web reports with your company's logo. See Configuring Design Elements.
10. Changing cell styles	You can customize the default cell styles in the web reports to align with your company's branding. See Configuring Design Elements.

The basic configuration is now complete! Follow the steps below to use the models:

11. Initial Data Import	To import separate financial statements (e.g., historical data up to the most recent month) and existing plans, use the `Upload File` option within the `Profit and Loss` cube. Alternatively, you can use the Excel workbook provided in the Sample Configuration as a template for your own configuration. Navigate to `Designer → Models → Financial Consolidation → Files/Sample Data/Financial Consolidation Sample Data (IFRS)`. Save a local copy and replace the sample configuration and data with your own. You can also use the interface to manually import journal entries into the posting journal.
12. Bring Balances Forward	You can bring the closing balances of the prior reporting period forward as the opening balances of the current reporting period. You can do this in the Data Manager report by entering manual journal entries into the posting journal.
13. Importing Figures	You can import figures for any version (actual, budget, etc.) on a monthly basis by using the `Upload File` option on the `Profit and Loss` cube or the `Balance Sheet` cube. This method provides a quick way to get started. However, if you want to run consolidations regularly, you can enhance this functionality by using an Integrator job that connects directly to your existing accounting system and loads the data automatically.
14. Reviewing Imported Figures	After importing your figures, you may need to add multiple currencies into the system before reviewing them. To do this, access the Import Exchange Rates form within the Exchange Rates input screen to upload the exchange rates. You can then use the trial balance report and your separate (sep.) financial statement reports to review the figures.
15. Running Consolidation	See the Consolidation Manager report for running the financial consolidation.
16. Review Consolidation	You can review the results through the automated journal entries displayed in the Consolidation Manager report. Additionally, you may use the Consolidated Financial Statement report, as well as the following reports: Consolidated Profit and Loss and Consolidated Financial Statement.

Updated August 28, 2024