Configuring Row Formats

Configuring Row Formats

Once you create your basic Row Format, you can add, edit, or delete rows, or change the data or format of any rows.

The following procedure takes you through the process of creating a simple Row Format from scratch.

NOTE: Refer to Design Statement Samples for more detailed information about creating specific kinds of statements.

1. In the General Ledger module, click the Design Statements super tab, and then click the Row Format tab.

2. Do one of the following:

• If the Row Format already exists, click the arrow in the Format Name drop-down box on the Row Format tab and select the Row Format record you want to configure.
• If the Row Format does not exist, click the Create/Copy Format button to create a new Row Format. Refer to Creating Row Formats for more information.

Example:  New Row Format

3. The default number in the first Row field is 10. Click in this field and change the number, if desired.

NOTE: The Row number determines the sequence in which the rows will be printed (unless you enter an N in the Print Control field to prevent the row from printing).

4. In the Description field, enter a text description of the row (for example, if you are creating a row that displays a beginning account balance, you could enter Beginning Balance).

TIP: If you want to indent your descriptions, insert spaces in front of the text you enter in this field.

NOTE: Descriptions for Row Formats used in Columnar Financial Statements will not be printed.

5. Click the arrow in the Format drop-down box and select one of the following Format codes to determine the type of row you are creating:

• AR%: Asset Rebalancing Target percentage % from the GLYear record for the asset chosen.
• ARF: Asset Rebalancing Fixed Amount from the GLYear record for the asset chosen.
• BB: Prints a beginning balance for an account (or range of accounts) instead of the year-to-date or current value indicated by the Column Format. If you use this code, you must enter the appropriate account (or range) into the Formula field. The balance of the specified account(s) will be calculated as of the beginning of the period range defined for each column in the Column Format record. For example, if a column is set to Current with an absolute Period range of 4 to 6, the beginning balance of the specified accounts(s) will be as of the end of Period 3. A YTD column always returns the beginning balance for the fiscal year.
• Desc: Prints text in the Description field on the current line, flush left. This is useful for section headers.
• Dund: Underlines columns with a double row of dashes.
• FBB: Prints a beginning Fund balance for the period range indicated in the Period field (on the Column Format record). Use this code when you are creating statements of activity, to place an initial quarterly Fund Balance above a column of Current activity. You can also use this code for YTD columns, if you want to print the beginning Fund Balance for the fiscal year.
• FBE: Prints an ending Fund balance for the period range indicated in the Period field (on the Column Format record). You can specify a single Fund balance, or a range of Fund Balances in the From and Through fields.
• Fld: Field.  Used to indicate that a particular field will be chosen for display from the Fund or FieldMaker Fund record.  Fields are specified in the Formula column of the Row Format. 
• GL: Prints values from the General Ledger account (or range of accounts) indicated in the Formula field. The values may be the sum of account balances for a number of selected Funds, or from a single Fund, as determined by the segments specified in the Formula field (or the segment values that you specify at run time).NOTE: Leaving the Format field blank is equivalent to selecting GL. 
• NRow: When the system encounters this value it prints the subsequent rows on a new line. Think of it as a carriage return. 
• Page: Forces the report to start a new page and print the description contained in the Desc field centered in the report header. This is necessary if you are printing your Fund Statements directly from FIMS, so that each Fund statement will begin on a new page. It is an option for Consolidated Fund Statements and can be used if you need an extra line in the statement header. 
• Tot: Totals prior rows (or ranges of rows) as indicated by the row number formula in the Formula field (for example, 10+20, 10+40-70, or 10 to 100). 
• Und: Underlines columns with a single row of dashes.

6. In the Print Control field, do one of the following: 

• If you want to skip a line (or multiple lines) before printing the row, enter the number of rows you want to skip.
• If you do not want to print the row (for example, if you are adding the values from a group of rows together, but only want to print the total), enter an N. 
• If you want to print the row without skipping any lines, leave this field blank.

7. In the Print Columns field, enter the letters associated with the column(s) that you want the row to print in. Leave this field blank if you want the row to print in all columns.

NOTE: If you specify column letters, the associated Column Format must include the specified columns.

8. In the Normal Balance field, enter dr if you want to print the normal balance value kept in General Ledger. Enter cr to print the opposite of the normal balance value kept in General Ledger. For example, Debit balance accounts, such as Assets, print as positive amounts and credit balance accounts, such as Grants Payable, print as negative amounts. To reverse the sign of the row, enter a cr code (for example, on a P & L where you want to present Beginning Fund Balance, Revenues and Expenses with signs that are the opposite of their normal G/L balances).

9. In the Formula field, enter a formula that identifies the account (or account range) from which the value is calculated for the statement (for GL rows) or indicates which previous rows to combine to calculate the current row value (for Tot rows).

NOTE: Click the Paste Formula button on the right-hand side of this field to open a window that will help you to enter the correct formula syntax.

NOTE: This field is only relevant for GL and Tot rows.

10. In the From/Through fields, enter the range of account types that you want to print (for G/L rows) or the Fund Balance number (or range of numbers) that you want to print (for FBB and FBE rows).

NOTE: These fields are only relevant for GL, FBB, or FBE rows.

TIP: Account Types can be used alone (for example, R2 through R2 prints all revenue to Fund Balance 2). An Account Type range can also be used in combination with a General Ledger account (or G/L account range) in the Formula field.

11. Click the Save button on the toolbar to save the Row Format.

12. Click the New button on the toolbar to create a new row in the Row Format.

13. Repeat this process to create any additional rows.

Example 1:  Row Format with 9 Rows

Example 2 figure below is a sample row with the AR% and ARF Formats chosen. One would indicate single asset accounts for each. Typically you would include rows for each of the assets used by the funds. If you had 14000, 14100, and 14200 as pools involved in asset rebalancing, then for each of these three assets you might include a line for a total of 6. If you don’t use the fixed amount feature, then you would only need 3. See Example 3 below for an illustration of this setup.

Example 2:  Sample row with AR% and ARF Rows.

Example 3:  AR% without ARF rows.

Example 4: Fld rows

The FLD Format enables you to use various fields in the Fund and associated Fieldmaker ( *) tab. At this time, only the Cash requirement and Establishing Gift values are available from the Fund 1 tab. Various FieldMaker values are available and are their availability is based on their data type. To access the fields, add a row with the FLD format and then click the fx (function) button in the Formula column. A drop down similar to this will appear and you can select either Fund or FM Fund from the Table drop-down that will avail the fields available (for the selected table) for adding in the Field drop-down. 

Using the NRow Value

A new value” Nrow” was added with FIMS 14.1 that can be used with columnar statements.

This value is added to the row definition and has a label that will be transferred to the columnar statement, but there are no values added on the line in the row formatter. When the system encounters this value, it prints the subsequent rows on a new line. Think of it as a carriage return.

Since this is used in columnar statements only, this allows you to build multiple lines for each fund. You may have as many Nrow values as you need.

When using this tool, you should not be using the columnar financial statement maintenance process after adding the first group of values. If you wish to use this process to create the initial top line, feel free, but further adjustments to the row formatter should be made manually. 

Here is an example of a simple row definition that places the asset balances on the top line and the new asset strategy percentages on the next line:

And the output is:

Tip: Row Numbering

• Number the rows in increments of 10 or 20 so it will be easier to add more rows later.
• You can change row numbers to reorder your statement. Click the Refresh button to re-sort rows after changing row numbers. When you are finished, inspect any Tot rows, which use numbers in formulas.
• You can create any number of rows with the same row number. Use this feature to combine two or more rows into one print row (for example, if a formula is too large to fit into one Formula field). You can also use this feature to combine two or more balances from noncontiguous General Ledger accounts or account ranges into a single row.
• Row records that share the same row number can either be G/L or Calculated rows (rows with Format codes of BB, FBB, Fbal, FBE, or Tot). The rows may print to all columns, or a specific column as determined by the Print Columns feature and will total by column. For example, if four rows have the same row number, you can total the first two in column A, and the second two in column B.

Tip: Total Rows

• You can specify the column, along with the row (for example, 10:A+20:B). This allows you to add not only rows, but specific cells. Use this when different values are printed in different columns of a single row, which can be accomplished via the Print Column feature.
• The formula for a Total row may include only one row code. This is useful when you need to display the negative value of a previous row (for example, in an interfund transfer elimination column). You can either precede the row code with a negative sign, or you can switch the row’s Normal Balance field. This method is an alternative to repeating a GL row and changing the normal balance.

Tip: Recent Features

• Users needing to indicate the current target percentages chosen by the donors can now place these values directly in the statements in paragraph or table formats. 
• Phrases that include the establishing date and gift can be added as appropriate with the addition of the establishing gift amount (that works similarly to the established date. 
• With the cash requirement field available, you can inform the donor of the amount of their current year spending policy without relying on booked journal entries or budget values.  Remember this will be the amount on the Fund 1 tab and thus it might or might not be associated with the year desired depending upon your own individual spending policy process and timing.