Revised:  09/01/2017

How to Create and Modify Financial Statements

Creating and modifying Financial Statements can be complicated.  These instructions will focus on a simple statement; however, there are more options available that are not discussed here.

There are 3 parts to creating a statement:

• Template Master.  Think of this as setting up your rows.  Rows can be setup for headers, to print general ledger accounts, for subtotals, and totals, etc.

• Column Specifications  This program is used to setup what data is in each column.  A column may contain budget information, activity for selected periods, year to date activity or calculations such as budget verses actual.

• Financials Statement Master  This is where the Statement Code and Name are setup and a Template and a Column Specification are assigned to complete the Financial Statement.

• • Note:  You can create multiple reports by mixing and matching different Templates and Column Specifications, such as this example for several income statements.

Template "A" contains the general ledger account information for Department A.

Template "B" contains the general ledger account information for Department B.

Column Specification "CUR" has only 1 column for the Current Period Activity.

Column Specification "BUD" has column 1 - YTD Activity, column 2 = YTD Budget, column 3 - Variance.

We can use the templates and specifications to create four different Financial Statement Codes such as:

"ACUR" using Template "A" and Column Specification "CUR"

"BCUR" using Template "B" and Column Specification "CUR"

"ABUD" using Template "A" and Column Specification "BUD"

"BBUD" using Template "B" and Column Specification "BUD"

So to create a set of Income Statements for multiple departments, you will need a Template for each department (Yes, there is a copy feature, see:  How to Copy a Financial Statement Template) and 1 Column Specification.  Then create a Financial Statement Code for each department and assign the appropriate Template and the Column Specification.

Getting Started:

Template Master

Go to Maintain>>Template Master

• Enter a new unique Template Code and Description, such as "INC1000" as the code and "Income Statement 1000" as the description.

• Select a Statement Type as either "Balance Sheet" or "Profit Loss"

These instructions will focus on building a Profit-Loss statement.  The main difference to keep in mind is that a profit and loss statement will calculate year to date actual information as the activity that happens from the beginning of the fiscal year to the end date selected when running the report.  Balance sheet statements will display account balances on the end date selected when running the report.

• Select one of the following for Account Mode:

• • Standard - general ledger account numbers will be displayed for selecting as normal alpha-numeric data

  • FS Grouping 1

  • FS Grouping 2

  • FS Grouping 3

  • FS Grouping 4

  • FS Grouping 5

  • FDS Reporting

  • Any additional User Defined Ordering that has been setup in Settings>>General Ledger Settings>>User Defined Ordering.

Normally Standard will be selected as the Account Mode; however, one of the other options may create better reporting in a complicated GL Chart of Accounts.

• Checkmark the Print Account box to have general ledger account numbers print on each line.

• • Enter a number for the Column Width.  The number equals the space required for 17 X characters to print, i.e account number 1000-3110-00 is 12 characters (including the 2 dashes) 17 X 12 = 204.  You may enter a Column Width of 204 or you may wish to add another space between the Account Number and the Description to make a Column Width of 221 (204 + 17).

• Checkmark the Print Description box to have the description for the GL account numbers print on the statement.

• • Enter a Column Width for descriptions of 510 to allow 30 characters to print.  You may decrease this number to save space (17 spaces per character).  Longer descriptions will continue printing on the next line.

Note:  Width amounts are based on default fonts.  Font formatting will change the requirements and you may need to increase or decrease widths to get better spacing on your report.

• Checkmark one or more of the displayed Segments to Summarize boxes, to summarize information, beginning with the bottom box and working upward.  Do not leave an unchecked box between 2 checked boxes.  For each checked segment, all accounts will be combined with 1 total into the next segment.  You may leave all of the boxes unchecked if you do not want to summarize any of the segment information.

• Checkmark one or more of the displayed Account Segments to Print boxes that you want to print.  Best practice is to leave unchecked any box that is checked under Segments to Summarize.

For Example:

In the above example, PROJECT account number amounts such as 1000-3110-00-10 for $50.00 and 1000-3110-00-20 for $100.00 will be summarized and print as 1 total in the SUB ACCOUNT general ledger number of 1000-3110-00 as $150.00.

Open the Detail Information tab:

This grid is for setting up lines with headers, formatting information and selecting general ledger accounts or ranges.

• Click on the column header or press F4 to open the list of available function commands.  Every line must have a function command.  Later on in the "how to", the most commonly used functions will be described.

• The Comp column is generally left blank as it is used to define information from a different company.

• A general ledger Starting Account number may be entered to begin inclusion in this section.  Use only when the Function command is looking for general ledger account activity.

• A general ledger Ending Account number may be entered to end inclusion in this section.  Use only when the Function command is looking for general ledger account activity.

• A text Description may be entered to print on the statement when the Function is for Headers or total lines.

• Enter a number or leave at the default of 1 for the OS (offset) column.

• • This column can be split into multiple column offsets.  I.E.  The activity for each account is in OS 1, the totals are in OS 2 and print offset to the right.  (The Column Specification Master must also be setup with the maximum OS number used in the template entered in the Segment field and be of sufficient width to accommodate all of the data.)

• Enter the number of LF (line feeds) or leave at the default of 1.  A value of 0 will not print the line, a higher value will cause more space between lines.

Below is a simple income statement template:

• Line 1, 3 and 8 are Header functions and the text entered in the Description columns will print on the statement.  There are 3 Header functions:

• • HEADER - Prints the entered description left justified.

  • HEADERC - Prints the entered description centered.

  • HEADERR - Prints the entered description right justified.

• Lines 2 and 7 are LF (Line Feeds), showing spacing as single spaced (1)

• Lines 4 and 9 are ARP (Account Range Print) functions.  Although this is shown as one line on the template, the statement may print many lines of data on the report.  For every general ledger account included in the entered Starting and Ending Account range and matching any filters in the template or in the Column Specification, a line will print.

• • Template Account Filters - In the Description column, of any account range function, a ? and numerals may be entered as filters.  For example:  ????????????55 will print any account in the range that ends with 55, no other account numbers will print.  The filter must contain the same number of characters as your general ledger accounts (do not include punctuation -./, etc).  ? (question marks) will include all values in the general ledger account.  When a specific number is entered as one of the filter places, only general ledger accounts with that number in that location will print on the statement.

  • Multiple ARP lines may be entered, with different account ranges or using different filters, on a template.

• Lines 5, 12 and 14 (DSB1, CSB1 and DSB2) are subtotal lines.  There are 2 types of subtotal functions, DSB# and CSB#.  The text information entered in the Description column will be printed on the statement for the subtotal.  The OS value of 2 for these lines will cause the subtotal to print offset to the right of the detail records above it.  The LF column value of 2 will cause extra spacing below this record.  

1. • Each of these functions ends with a number DSB1 to DSB9 or CSB1 to CSB9.  When one of these functions is used it will start adding all values above it, moving upward until it reaches a DSB#, CSB# or CLR# of the same # or higher.  

   • • Line 5's DSB1 will add all records above it as there are no functions telling it to stop.  

     • Line 12's CSB1 will add all records above it until reaching line 5 (does not add line 5 to total) as this function's DSB1 has the same numeric ending.

     • Line 14's DSB2 ending is greater than the subtotal values of 1 used above so it will continue to add all of the expense and revenues.

   • DSB# functions will print credit values as positive and debit values as negative.  Use this subtotal function with Revenue, Liability and Equity accounts.

   • CSB# functions will print debit values as positive and credit values as negative.  Use with Asset and Expense Accounts.

Note:  Revenue and expense values all appear as positive on the report; however, when adding revenue with expenses, the debit expense balances subtract from the credit revenue balances giving a Net Gain or Loss.

• Line 10 is an ARA (Account Range Accumulate).  The ARA function will accumulate the information for the accounts within the Starting and Ending Account range specified for a total amount, but does not print the individual general ledger accounts like ARP.  A CPAT or DPAT function is used to print the accumulated totals.

• Line 11 is a CPAT (Credit values as a negative Print Accumulated Total).  A DPAT will print the accumulated debit totals as a negative.  The CPAT and DPAT functions will print the totals accumulated in the ARA specified range in the lines above it and will  print the text Description entered (Extraordinary Expenses) in lieu of an Account # and Description.

• Lines 6 and 13 are UL (Underline) functions.  An underline will be drawn under the previous record.

• To make calculations on financial statement lines:

• • You may add up to nine line item variables and perform calculations based on the variables (Var1 - 9 selected under the Function>>column)

  • • For example to calculate a Current Ratio you would create Var1 for Current Assets, VAR2 for Current Liabilities. The calculation function would be DVAR = (VAR1/VAR2).  The program will handle debits and credits the same as for any other Financial Statement function.

    • Please contact your Horizon Support Representative for assistance.

This completes a basic Income Statement template.  There are more Function commands available and the use of other Account Mode options can add flexibility and options to design statements in a more complicated GL structure.  Please call the help desk if further assistance is needed to set up a Financial Statement.

Column Specification Master

Go to Maintain>>Column Specifications

• Enter a new unique Code and Desc such as "BUD" as the code and "BUDGET" as the description.

• Select a Type of "Balance Sheet", "Cash Flow Statement" or "Profit-Loss Statement"

These instructions will focus on building a Profit-Loss Statement.  The main difference to keep in mind is that a profit and loss statement will calculate year to date actual information as the activity that happens from the beginning of the fiscal year to the end date selected when running the report.  Balance sheet statements will display account balances on the end date selected when running the report.

• The Column Number will auto fill for each record with the net available column number.  Use the browse option to select an existing column to view or edit.

• Enter a name for the column Header of your choosing.

• Select a Column Type option.  This option will determine the type of data that appears in the column on the report.

• Enter a value for Width.  

• • Note:  Too small of a value will cause the column data to wrap to the next line.  You can hide a column by using a width of 0.  It can be useful to hide columns that are needed for a calculation but not needed to print.  Suggested:  Start with 300 with a Segment of 1 and 600 when you are entering 2 Segments.

• Enter a value for Segments.  This determines the Offset (OS) Segments.  Set to 1 to have all data line up in a single column.  Set to 2 or more to allow the Template OS to move totals to separate sub-columns.

• Enter the number of Decimals you wish to report.  Note:  When set to 0, this option alone will not cause rounding.  The Rounding option in the Financial Statement Master must also be set to "Rounding On".

• Enter a calculation formula in Main Expression when the Column Type is set to "Column Calculation"; otherwise, leave blank.  Normal mathematical symbols are used.  For example:  C1 + C2 will add the values of Column 1 and Column 2.  C2 * C3/C4 will multiply column 2 times column 3 and divide by column 4.

• • Warning:  Do not include the column # of this calculation in the formula as it may cause the formula to provide unexpected results.

  • The formula column numbers will not update if the Column #s are changed by inserting or deleting columns, the formulas must be checked and adjusted.

• Use the Alternate Paren Control (D) Expression field to enter an alternate formula for dealing with different account types.  When completed, GL accounts with Debit Paren Controls will use this calculation.  It is generally used for a difference column.  Example:  C1 is Actual, C2 is Budget.  The Main Expression formula is C2 - C1. If an Expense account causes this formula to return a positive "good" result when the budget is more than actual expenses or when a Revenue account causes the formula to return a positive result when a negative (under expected income) is wanted.  Reverse the formula in Alternate Paren Control (D) Expression as C1 - C2 to achieve the desired results of good = positive, bad = negative on all account types.

• • Warning:  When entering in the Main Expression and the Alternate Paren Control D Expression, the boxes will fill up with blank spaces.  Delete spaces to make room for more text if you get the error "Text too large for database field.  (Maximum Length = 255, Current Length....."

• Use Advanced Filtering to select filtering options for this column by segment.  You may use Account Filtering in the template or you may filter in each of the columns.  To create a multi-departmental statement, the template account ranges should include all ranges.  Then each column will have a filter for the department/project/etc.

• Use the Renumber Columns feature if your column numbers are missing numbers due to deleting columns to bring the columns back into sequence. I.E. if your columns are numbered 1, 2,4,7 pressing the Renumber Columns button will renumber the columns to 1, 2, 3 and 4.

• Use the Insert Column(s) feature to add columns and renumber existing ones.  Enter the position(s) to add in the Starting Column and Ending Column fields and press the Insert Column(s) button.  I.E. to add a new column in position 1, enter 1 in both fields and press the Insert Column button.  To add 2 columns between the existing columns 3 and 4 enter a 4 as the Starting Column and a 5 as the Ending Column.  The column that was previously number 4 will be column 6 after the Insert Column button is pressed.

Refer to the Income Statement at the end of these instructions while reviewing these instructions for setting up a Column Specification code.

Sample Profit and Loss Budget Column Specification:

• Enter a new unique Code and Desc. such as "Bud" and "Budget"

• Select a Type of "Profit-Loss Statement"

Enter the following information for the column numbers:

Customize these options to get the report you want.  You can build as many columns as needed.  Segment value of 2 was used in Column 1 and 3.  Column 2 uses a segment value of 1.  Notice the difference in the final report below.

Financial Statement Master

Go to Maintain>>Financials Statement Master

This is the final step to creating a Financial Statement.  Here you may mix and match templates and column specifications to make a report.

• Enter a new unique Financial Statement Code and Description such as "1000INC" & "Income Statement 1000"  The description will print as the report header.

• Select a Template Number to use.  The Description of the selected Template will be displayed.

• Select a Specification Number to  use.  The Description of the selected Column Specification template will be displayed.

• Enter the default Number of Copies to print.

• Leave Macro Form Number blank as this is reserved for special uses.

• Checkmark Print Accounts Without Activity to have all of the GL accounts print.  This box may be left blank if you only want accounts with activity in the fiscal year to print.  The current program will not print accounts that have a budget amount if it does not have activity in the fiscal year.  Future Versions will include account with budget or activity.

• Select a Paper Type of "Legal" or "Letter".

• Select an Orientation of "Portrait" or "Landscape".

• Select a Rounding option of "No Rounding" or "Rounding On"

• • With No Rounding, if a column specification has 0 decimal numbers, the decimals will not be displayed but the report will not have rounded information.  500.01 to 500.99 will print as 500.

  • With Rounding On, if a column specification has 0 decimal numbers, the report will be rounded to the nearest whole number.  500.01 to 500.49 will print as 500 and 500.50 to 500.99 will print as 501

The following Income Statement is produced by following the steps in the instructions above.

Sample Final Report: