Using 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 > Data Integration > 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.

How to Configure 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 > Administration > Configuration Tasks.
2. Select the DLR Data CalculationsConfiguration.
3. Select to apply the calculation exceptions to all scenarios or selected scenarios.
4. Click the All Scenarios or Selected Scenariosradio button for the Apply For option. 
   1. 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.
   2. If Selected Scenarios is selected, click Add to select the scenarios you want to apply data calculations to. Then, select MTD or YTD for Flow and Balance Accounts.
5. Click Save.

How to Specify Data Calculation Exceptions?

Once the configuration is complete, specify accounts to apply data calculation exceptions. 

To apply data calculation exceptions to companies and accounts, follow the steps below:

1. Access the DLR Data Calculation Exception Setup page by navigating to Maintenance > Data Integration > 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.
2. In the right pane, click the Add button. The Add - Company with Data Calculation Exceptions dialog box appears. 
3. 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.
   
4. Click Add.
5. Set MTD or YTD for Set Zero Default Value from the drop-down.
   
6. 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.
7. Click Save.
   
   
   Note:

   Click Output to export the Company dimensions that Data Calculations applied along the Account hierarchy code.
   

How to Mass Upload DLR Data Calculation Exceptions?

To mass upload the DLR Data Calculation Exceptions, follow the steps below:

1. Access the DLR Data Calculation Exception Setup page by navigating to Maintenance > Data Integration > Data Calculation Exceptions. 
2. Use the Output All feature to export data load calculation exceptions by account code to an Excel file.
3. 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.
4. Click the Upload file icon. This action uploads the file with the calculation exception adjustments that you just saved to your local machine.
5. Click the Choose File button and select the saved file to load the data.
6. 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. And also a message will appear after the task is completed.
7. Reload the page to view the uploaded data.