Financial Statement Columns

Overview

Financial Statement Columns are a component of a Financial Statement Definition. The Financial Statement Columns section is where you will create the columns that will display on the report. The column definitions determine the period and the ledger to report on. Some examples of columns would be:

• Current
• Previous Period
• Calculation totaling or taking the difference of three columns.

Key fields on Financial Statement Columns include the following:

• Ledger - Search for and select a Ledger. This can be a Consolidations-Transactional, Transactional, Eliminations, Budget, or Consolidations-Budget Ledger.
  
  If you want to see more than one Ledger on a report, a Financial Statement Column can be cloned, with a different Ledger selected and different Header 1 Text added as in the sample screen below. Up to 12 columns can be included on a Financial Statement Definition/Report Name.
  
  
  Note: Click here to view the above image in full screen. 
  
  
• Type - These are the options to choose for type listed below with explanations.
  • Current Period - Select this for the column to report only on the period you selected. This will default to the “Current Period” that is in the Financial Cube for the GL Account and Accounting Period (and Ledger) the report will be run for.
  • Year to Date - Select this to report on all transactions for the year to date. This will default to the “Year to Date” that is in the Financial Cube for the GL Account and Accounting Period (and Ledger) the report will be run for.
  • Calculation Number- Use this to make a calculation using different columns with the result being a number. For example, if one column was showing transactions for January 2021 and the next was for February 2021, a third column could be created to give you the difference between the two. The formula would look like {c1}-{c2}. {c1} would represent column 1 and {c2} would represent column 2. If Calculation Number is selected as the Type, an entry is required in the Calculation field.
    
  • Calculation Percentage - Use this to make a calculation using different columns with the result being a percentage.  If Calculation Percentage is selected as the Type, an entry is required in the Calculation field. The decimal format of this is driven by the value that is entered in the Percentage Decimal Places field on the Financial Statement Definition.
• Position - If using multiple columns, this field will determine the order they appear in. When numbering columns for position it is easier to number them in increments of 10 as opposed to 1, 2, 3, 4, 5. This way if you need to create a new row or column you don't have to renumber everything, as you can just insert it where needed. This is an integer value that determines the location of the column in relation to other columns (left to right), with the lowest number being the left-most column.
• Offset - This field determines which period the column will report on. To report on the selected period enter a "0" (zero). If you would like to report on a previous period insert "-1" for the prior month or a "-n" for the amount of months you would like to report in arrears. To report on after periods, enter a positive number in this field. Use this field to display information from different periods for comparison.
• Calculation - Use this field to add, subtract, multiply or divide columns. This is used in conjunction with selecting “Calculation Number” in the Type field. For more information, refer to the Sample Calculation Syntax article. 
• Header 1 Value/Text - The options available are Text or Period Name. If Period Name is selected then the period being reported on will be listed at the top of the column. Select Text if you wish to enter your own text to display on the column header, such as a specific company name or Ledger name (Company A, Company B, Eliminations A, etc).
• Header 2 Value/Text - This works the same as Header 1, but will be below Header 1 on the report.
• Column Override Row - Select this checkbox to handle instances when a cell on a report has an intersection of both a column and a row with a calculation and you would like to display the column calculation rather than the row. If this checkbox is not selected, the row calculation will automatically be used.
• Suppress Print - Selecting this checkbox will hide the column on the Report Viewer, Excel spreadsheet and PDF version.

Define Financial Statement Columns

Below is an example for creating the following three report columns:

• Previous Accounting Period
• Zero Accounting Period
• Variance (%) between the first two columns.

1. Navigate to Accounting Home and click the Run Reports tab. Then, under the Custom menu, click Financial Statement Definitions.
2. Select a list view. Typically, you want to select a list view other than Recently Viewed. 
3. Select the Financial Statement Definition Name for which you want to define columns. 
4. Click the Definition Criteria tab. 
   
   
   Note: Click here to view the above image in full screen. 
   
   
5. From the Financial Statement Columns related list, click New.
   
   
   Note: Click here to view the above image in full screen. 
   
   
6. Enter values for the fields described below: 
   Note: Required fields display with a red asterisk (*). 
   
   • Ledger - Search for and select a Ledger. This can be a Consolidations-Transactional, Transactional, Eliminations, Budget, or Consolidations-Budget Ledger.
   • Header 1 Value - The options available are Text or Period Name. If Period Name is selected, the period being reported on will be listed at the top of the column. Select Text if you wish to enter your own text to appear on the column header. When you select Text, you enter the column header text in the Header 1 Text box below the Header 1 Value field. In this example, select Period Name and leave the Header 1 Text box blank.
   • Header 2 Value - This works the same as the Header 1. Leave it  defaulted to None. 
   • Type - Refer to the Type information above. In this example, Select Current Period.
   • Position - If you are creating a report with multiple columns, this field will determine the order in which the columns will display. Number them such as 10, 20, 30, 40. Using increments of 10 allows you to add a column at a later point that can be inserted between two existing columns if it’s position number is between the two. For this column, Enter 10.
   • Offset - When you run a report, you must select the "0 Accounting Period," (for example 2021-04). That period defines the zero point from which offsets are calculated. Offsets in either direction (-1, -2, +1, +2, for example) are calculated from the "0 Accounting Period." If the column is for the "0 Accounting Period," enter a "0" (zero). If it is for a period prior to the "0 Period," enter -1 as the offset (or "-n" for each prior period to be included as a column in the report). For this column, enter -1 (for the prior period to our "0 Accounting Period," which will be selected when running the report).
     
     
     
     
7. Click Save & New to add the second column.
8. Enter values as above except for Position and Offset. For Position, enter 20 and for Offset, enter 0 ("0 Accounting Period" selected when running the report).
9. Click Save & New to add the third column.
10. The third column is the variance column where we are calculating the variance between the current and prior Accounting Period. For this column, enter values as above except for the following:
    • Header 1 Value = Text
    • Header 1 Text = Variance (%)
    • Header 2 Value = Text
    • Header 2 Text = Current vs Prior Period
    • Type = Calculation Percentage
    • Position = 30
    • Offset = 0
    • Calculation = ({c20}-{c10})/{c10}
11. In the Formatting Information section, select the Column Override Row.
    Note: This should be checked anytime you are creating a column that has a percentage calculation. It ensures that the calculation result displays as a percentage.
12. Click Save.
    
    
    Note: Click here to view the above image in full screen. 
    
    If you want to see more than one Ledger on a report, a Financial Statement Column can be cloned, with a different Ledger selected and different Header 1 Text added as in the sample screen below. Up to 12 columns can be included on a Financial Statement Definition/Report Name.
    
    Note: Click here to view the above image in full screen. 

Return to top

____________________

Release Notes and Schedule

• With the Magnolia release, the Custom Financial Reports can be generated for Consolidations-Transactional and Eliminations Ledgers