Getting Started with the Projected Balance Sheet Model

Return to

Projected Balance Sheet Model Overview

To get familiar with the Projected Balance Sheet model you can install it with a sample configuration which 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 how to configure the model with sample data in Setting Up the Projected Balance Sheet Model. The model can also be set up without sample data. Follow the steps below to configure the model and populate it with your company's data.

The steps below explain how to configure the model and populate it with your own data.

Note that the instructions below are based on using an Excel workbook to collect relevant data and figures into worksheets and uploading them into the Jedox database. Jedox offers you a variety of ways to achieve the same goal, e.g. editing elements manually in the Modeler, defining a job in the Integrator to connect to your ERP. In the context of this article, we'll stick to using the Excel workbook - feel free to use the most appropriate method for your needs.

The Projected Balance Sheet model comes with an Excel Workbook containing a sample configuration as well as sample data. You will find this in Jedox Web > Report Designer > Models > Projected Balance Sheet/Files/Sample Data/Projected Balance Sheet sample data (IFRS). Save a copy of this workbook (using Export from the context menu) locally, edit it to replace the sample configuration and data with your own.

You can configure dimensions, as well as cubes by uploading your modified Excel workbook into the model you're working with via the steps below:

  1. Open Modeler > [Database Name] > Dimensions or Cubes > [Dimension's or Cube's name] and choose Upload file from the context menu.
  2. The Dimension/Cube Upload Wizard opens which guides you through the steps.
  3. Select the modified copy of the workbook from your local file system. For details, see Uploading and Downloading Dimensions.

Use the essential steps below for configuring the model and modifying individual worksheets in the Excel workbook:

1. Set up Model

Install the model without sample data. For directions, see Setting Up the Projected Balance Sheet Model.

2. Set up Languages

  1. Remove unnecessary languages from the model by going to Modeler > [Database Name] > Internationalization and selecting the language(s) you would like to remove and then Delete (-). Once the languages are removed from the model, attributes in the workbook using that language can be ignored. Columns that have been localized will have the attribute name followed by an @ symbol with the country code for the language in the format xx_XX. For example, 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.
  2. To add a language go to Modeler > [Database Name] > Internationalization and then New (+) to select the language you would like to add from the menu. Once the new language has been added, go to the worksheet of the dimensions and cubes you would like to update with the translations for the new language. For example, to add a new column to the worksheet for the Version dimension of the Name attribute in Portuguese (Brazil), you'd add Name@pt_BR.

3. Setting Role-Rights

In order to run the Posting Journal reports, user rights must be set to "Read only" or higher for user info. In order to set this up, navigate to Administration → Roles in the navigation panel, select Assigned Rights , locate user info in the Object Name column, then select either "Full access, Writable, or Read only" from the dropdown list in the Object permission column:

4. Add Legal Entities

Legal Entity is a required dimension for the presentation of reports, it identifies a company or a group of companies within a report. You can edit it within the Legal Entity worksheet and upload it to the Legal Entity dimension with the upload wizard. If you only have one legal entity, you still have to use this dimension, populate it with the single entity that you have. Use the standard settings provided by the upload wizard to populate this dimension. Make sure that the Currency attribute is populated with local currencies.

5. Adjust 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 dimension.

6. Change Forecast Versions

  1. The elements in the Version dimension helps you to compare figures for reporting and planning. A minimum of two forecast elements are required.
  2. The Actual Months attribute of the forecast elements must have a value between 1 and 11.

7. Uploading Chart of Accounts

The BS Account dimension contains the hierarchy for ledger accounts or reporting positions. You can do calculations on these accounts within the model as well as connecting/integrating accounts between models. You can edit accounts within the BS Account worksheet and upload them to the BS Account dimension with the upload wizard. When uploading the BS Account worksheet, check Allow multiple parents within one hierarchy in order to allow individual accounts to roll up into several reporting positions.

8. Set Up Currency Conversion

  1. When a company or a group of companies stores accounting data, one currency like the Euro or Dollar, must be chosen. This currency which is called the Source Currency will be stated under the Currency attribute within the Legal Entity dimension. You have to update 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.
  2. You need currency conversion when a source currency has 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 have to update and upload the Currency and Target Currency worksheets to the Currency and Target Currency dimensions.
  3. You can import the actual exchange rates into the model by using the import exchange rates interfaces. All of the planning versions (Budget & forecast versions) can be set from the January average rate. You can load planning Exchange Rates via the Exchange Rates worksheet and upload them into the Exchange Rates cube.

9. Set Up Projection

Organizations use projections to populate planned balance sheets. You can update the mapping of the Profit and Loss (PnL) statements to the Balance Sheet (BS) statements in the Projection Configuration report. Click on the link under the Configuration Report column to go to the PnL Account Configuration report where you can see a detailed view of the mapping of the batch procedure you want to update.

10. Add Logo

Replace the Jedox logo within the web reports to your company logo. See Configuring Design Elements.

11. Change Cell Styles

Modify the default cell styles within the web reports to your company style. See Configuring Design Elements.

At this point basic configuration is complete! Follow the steps below to use the models:

12. Initial Data Import

To import actual figures (e.g. historical data up to the most recent month) and the existing plans, use Upload file on the Balance Sheet cube.

13. Import Actual Figures

To import actual figures on a monthly basis you can use Upload file on the Balance Sheet cube. This is a quick way to get started, but if you want, for example, to enable daily reporting, you can extend this functionality by using an Integrator job that connects directly to your finance system or ERP and loads in automatically.

14. Review the Actual Figures

If you've imported actuals, multiple currencies may be required within the system before reviewing the figures. Access the Import Exchange Rates form within the Exchange Rates input screen to import the exchange rates into the system. Once that is completed, use the Projected Balance Sheet Actual report to review current reporting positions in both local and target currencies.

15. Do Mid-Term Planning

If you need to do the mid-term planning, you'll have to prepare the system to receive data. Use the Exchange Rates input screen to update the currencies in your mid-term plan. Once that is completed, use the Projected Balance Sheet Mid-Term Planning input screen to create and adjust the plan across multiple years.

16. Create your Budget

If you need to prepare your budget, you'll have to prepare the system to receive data. Use the Exchange Rates input screen to update the currencies in your budget. You can then go to the Projection Configuration report to run the batch procedures. Once the updates are complete, use the Projected Balance Sheet Budget report to prepare your company budget.

17. Do your Forecast

If you need to create a forecast, you'll have to prepare the system to receive data. Use the Exchange Rates input screen to update the currencies in your forecast. You can then go to the Projection Configuration report to run the batch procedures. Then use the Balance Sheet Initialization form to populate the forecast version. Once you've populated the forecast version, use the Projected Balance Sheet Forecast report to make updates.

Updated August 1, 2024