Financial Statements - Column Calculation dialog

The Column Calculation dialog appears when you are creating or editing a column in a Do-It-Yourself table. This dialog helps define the column properties depending on your requirements.

Tip: To navigate columns, simply click to go the next column and to go to the previous column.

Type Tab

The Select column type: drop down menu located under the Type tab, enables you to specify the calculation type to be used in the column cells. Depending on the calculation type selected, the remaining dialog options will change. The following calculation types are provided:

Column type Description
Input

Populate the cells in the column with input cells, into which users can type values directly. The input type can be useful where data is not derived from the trial balance.

Note: this selection overrides the row's line source setting.
Subtotal Populate the cells in the column a subtotal of other columns in the table.
Link mapping/grouping calculation Populate the cells in the column using the Working Papers Trial Balance values.
Custom Calculation Populate cells in this column with a customized calculation. Use of this calculation type requires an understanding of CaseView formulas. For more information, see the CaseView help topic About Cell Calculations.
*MMR only*Annotation Populate the cells in the column with Add Annotation ( ) buttons.
*MMR only* Description

Populate the cells in the column with the mapping and grouping description.

*MMR only* Icon Sets Populate the cells in the column with icon sets based on a set condition.
*MMR only* Map Browse Populate the cells in the column with a map browse drop-down.
*MMR only* Blank Populate the cells in the column to be blank.
*MMR only* Variance Populate the cells in the column with the difference between column 1 and column 2.

Input

Field Description
Column type The format of the values that will be typed into the column. This can be either Numeric or Alphanumeric.

Subtotal

Field Description
Columns This field lists the columns that can be included in the subtotal. Use Ctrl+click to select the columns to include.

Link Mapping/Grouping Calculation

The following fields are available from the Type tab:

Field Description

Entity

The source of data from the consolidation structure. Select one of the following from the drop-down:

  • Parent - Use the parent data as defined in the Entity Information tab in the financial statements.

  • Consolidated - Use the consolidated data as defined in the Entity Information tab in the financial statements.

  • Other - Use a specific entity identifier or entity selector cell as the data source.

    Select the Entity Identifier radio button to use an entity identifier as defined in the Consolidation dialog in Working Papers

    Select Entity Selector to let Financials automatically set the entity selector cell to let end users choose a specific entity. A new entity selector cell appears in the header of the table.

    Note: The entity selector cell is only available when the Entity field is set to Other.
Foreign Exchange

Specify whether to multiply the account balance by the foreign exchange rate or display the rate for a particular grouping number only. Select one of the following from the drop-down:

  • Default - Apply foreign exchange as defined in the Global Setup tab in the financial statements.

  • Always apply - Always apply the foreign exchange rate to the balance.

  • Never apply - Never apply the foreign exchange rate to the balance.

  • Rate only - Display the foreign exchange rate.

  • Other - Supply the foreign exchange rate to apply. If you specify this option, a Rate field appears, in which you type the exchange rate to use.
Rounding

The rounding value applied to a balance. Select one of the following from the drop-down:

  • Default - Does not apply rounding.

  • Round - Round the final balance to the nearest whole number. If rounding a group of accounts, sum all the accounts and round the sum of the group.

  • Round Entity - Enforce rounding by entity. Balances related to the entity are summed and then rounded.

  • Round Total - Enforce rounding on the total of a map() or grp() function. The balances assigned to the map or group are totaled first and then rounded.

  • Round Account - Round each account individually. If rounding a group of accounts, each account is rounded and then the grouping is totaled.
Unreconciled Difference Column Apply only the unreconciled difference formatting (red text) to the column. This column can then be set up to behave like the unreconciled difference row.

Custom Calculation

Field Description
Unreconciled Difference Column Applies only the unreconciled difference formatting (red text) to the column. This column can then be set up to behave like the unreconciled difference row.
Custom Calculation

Type the calculation that you want to use. A valid calculation is highlighted in green, and an invalid calculation is highlighted in red.

If you hover over this field, a tooltip appears that displays the result of this calculation.

Use of this custom calculation requires an understanding of CaseView formulas. For more information, see the CaseView help topic About Cell Calculations.
Apply custom calculation on subtotal rows Applies the custom calculation defined in the Custom calculation area to the subtotal rows.

*MMR only* Map Browse

*MMR only* Variance

Field Description
Include variance %

Select to add a variance percentage column based on the difference between column 1 and column 2.

Column 1 Select the column that is to be subtracted.
Column 2 Select the column that column 1 is subtracted from.

*MMR only* Icon Sets Tab

Field Description
Icon Select from a variety of icons to display based on the condition.
When Enter a valid CaseView calculation and whether the calculation needs to be less than, equal to, or greater than a specified value for the icon to appear. For more information on CaseView calculations, please refer to the CaseView Help.

Variables Tab

The Variables tab is provided if the column is a link mapping/grouping calculation type.

Field Description
Year The year to use as reflected in the trial balance. Year 0 is the current year, Year 1 is the previous year, and so on. If Balance Type is set to Forecast Balance, all years except Year 0 represent future years, not prior years.
Period Date Sequence The reporting period (bucket) to use. The Default option uses the current period date sequence and current active period set in Working Papers.
Period Number

How the period number is to be obtained. The following options are available:

  • Define period - Type the period number in the field provided. For example, entering 2 for a quarterly date sequence denotes the second quarter. If no period is specified, the active period in Working Papers is used. Typing 0 uses the opening balance column of the Working Trial Balance in Working Papers. If the period number typed is greater than the period date sequence, the final period for the sequence is used.

  • Define calculation - Specify a calculation for the period number. For example, type a cell number where users can type or select the period number or type a condition based on the value of a cell. The input box is outlined in green for valid calculations and red for invalid calculations.

  • I/S comparative period (Quarterly period date sequence only) - Based on the active period set in Working Papers, use either the Preceding Period or Equivalent Period in the Prior Year as defined in the Income Statement tab in the financial statements.

  • B/S comparative period (Quarterly period date sequence only) - Based on the active period set in Working Papers, use the Preceding Period, Equivalent Period or Prior Year End as defined in the Balance Sheet (or Statement of Financial Position) tab in the financial statements.
Balance Type

The balance type to use. If this is set to None, the default CaseView balance type is used, which is Balance Consolidated (normal + reclassifying + eliminating). If this is not set to None, you can apply custom adjustments to your balances. For more information, see adding an adjusting entry type in Working Papers.

Selecting Specify calculation allows you to manually enter the value. The entry must be entered with quotations. The input box is outlined in green for valid calculations and red for invalid calculations.
Apply other basis adjustments to balance Select this check box to apply other basis adjustments to the balance. This check box cannot be checked if Balance Type is set to None.
Balance Values

Select how to further customize the grouping values to be included in the calculation for a column:

  • Period to date - Include all periods up to and including the specified period. An example of this is Statement of Financial Position type accounts.

  • Selected period only - Use the values for the selected period only. An example of this is Income Statement accounts.
Sign

How a sign is to be treated during calculation. Select one of the following radio buttons:

  • Use account sign - Use the account's Sign property in the Working Trial Balance in Working Papers to determine how balances evaluate and display. For example, an account with a debit Sign and a credit balance will have a negative value and display with brackets.

    Note: When dealing with a group of accounts, the Sign of the first account in account number order is used and the rest ignored.

  • Use map/group sign - Use the map/group number's Sign property in Working Papers to determine how balances evaluate and display. The Signs of the accounts are ignored. For example, a map number with a debit Sign and a credit total balance will have a negative value and display with brackets.

  • Raw balance - Use the account or map/group balance and ignore the Sign property to determine how balances evaluate and display.
Sample Calculation Display the compiled calculation of all of the selections made in this dialog. To update this field to include any changes you have made, click Update.

Visibility Tab

The Visibility tab is provided for all columns, regardless of calculation type:

Field Description
Visibility Tab

Indicate when the column will print and display. The following visibility options are available:

  • Always show - Always print this column.

  • Don't print when - Do not print the column if the condition specified in the calculation field is true (0 = false, 1 = true).

  • Hide when - Do not display or print the column if the condition specified in the calculation field is true (0 = false, 1 = true).

  • Hide when the active reporting period is - Do not display or print the column if the reporting period matches the criteria specified in the drop-downs provided. Select a comparison operator, period date sequence and period number. For example, if the drop-downs are set to >, Quarter, and 3, hide the column when the reporting period is after the third quarter.

  • Hide when reporting is - Do not display or print the column when reporting on a period less than the number of years specified in the drop-down.