Data Calculation Exceptions

This functionality instructs the system, during the data load process, to take default values for missing data for periods where no entries are posted. Access Data Calculations by navigating to Maintenance > DLR > Data Calculation Exceptions.

Behavior When Data Calculation Exceptions are Not Defined

By default, if data is not provided for a particular period, Planful makes certain assumptions during the data load process when loading data from your source system to Planful.

In the example below, values are posted for January, February, and April, but there are no posted values for March or May in the source system. When this is the case, Planful assumes that the account has no balance, which results in a zero year-to-date balance. The system also reverses the balance showing each balance as activity for the period for the month-to-date value. This is why, in the example below, March is -20 and May is -10.

Behavior When Data Calculation Exceptions are Defined

Lets say you don't want the system to behave as it does by default. You want the system to assume account values based on account type and the type of data you load from your source system. In order to make this happen, you must define data calculation exceptions.

For example, you have an income statement account and a sales return is posted to the account for January. But, in February, nothing is posted to the account. The account is not zero; there was just no activity for the month of February and you want the account to show the balance from the previous month (January). To make this happen, define a data calculation exception.

In the Income Statement example below, there is no activity to the account for May through December, but a data calculation exception is defined so that the balance is carried forward from May onward.

In the Balance Sheet account example below, you want the bank account to post balances for the account each month even when there is a zero balance for a period.

Configuring Data Calculations

The Data Calculations configuration setting instructs the system to take default values for missing data for periods where no entries are posted. Complete the Data Calculation configuration by following the steps below.

1. Navigate to Maintenance > Admin > Configuration Tasks.

2. Select the DLR Data Calculations configuration.

3. Select to apply the calculation exceptions to all scenarios or selected scenarios.

4. If All Scenarios is selected, select MTD or YTD for Flow Accounts and Balance Accounts. This setting is used to calculate and post values by Data Load Rules for periods where no data is posted.

5. If All Scenarios is not selected, click Add to select scenarios you want to apply data calculations to. Then, select MTD or YTD for Flow and Balance Accounts.

Specifying Calculation Exceptions

Once configuration is complete, specify accounts to apply data calculation exceptions to. Access the Data Calculation Exceptions page by navigating to Maintenance > DLR > Data Calculation Exceptions.

The application page is divided into 2 panes; left and right. All accounts from your account hierarchy are listed on the left pane. All company dimensions from your company hierarchy are available on the right pane.

Applying Data Calculation Exceptions to Companies and Accounts

1. In the right pane, click the Add button to enable the Add - Company with Data Calculation Exceptions page. On this page, select company dimension members you want data exceptions applied to based on the configuration. To select several members at once, hold down the shift key.

2. Set MTD or YTD for Set Zero Default Value.

3. Click Copy To to set the value of the account for the dimension. When the Copy To - Accounts page opens, select the accounts you want to apply the exception to. Click Copy.

4. Click Save.

5. Click Output. 

How to Mass Upload DLR Data Calculation Exceptions

1. Use the Output All feature to export data load calculation exceptions by account code to an Excel file.

2. Once the file is exported, make the necessary adjustments and save the newly exported file to your local machine. You have two options:

   1. Remove the header row and upload the file.

   2. Leave the header row. This option will result in an error upon loading. The error occurs due to the header row, but the data will be loaded. For example, you load a file with 1 header row and 10 data rows. Only the header row fails because it has invalid. The other 10 data rows are uploaded.

3. Instead of selecting each line clicking the Copy To option and selecting the accounts to copy the exception to, click Upload file . This action uploads the file with the calculation exception adjustments that you just saved to your local machine.

4. Select the saved file to load, by clicking the Choose File button.

5. Click Submit to perform the upload. A message will appear indicating the upload is processing and you can check the status in the Detail Log. A message will appear as follows: