FSG Reports
Application:
General Ledger
Process:
Create Basic FSG Reports
Objectives:
1. To create create the the basic basic component componentss for a Balance Sheet Sheet report. report.
Prerequisites:
1. Defi Define ne Locat Locatio ion n Hiera Hierarc rchy hy
2. Define Account Hierarchy Hierarchy
Table of Contents TABLE OF CONTENTS .................................... ......................................................................... ......................................... ....1
1
FSG Reports
Overview Financial Statement Generator (FSG) (FSG) is a tool used to build custom financial reports without programming to meet the individual individual business reporting reporting needs that are dependent on such factors as as business type, type, business size, organizational organizational structure, and management preferences. In summary, the steps to create FSG report are: Optional, draft the report on paper Define row sets Define column sets Define column set heading Define financial reports and report sets Request financial reports and report sets • • • • • •
Before creating FSG report, it is recommended recommended that you first draft it on paper. Sketching the the financial report in advance will help you plan the format and content and therefore saves you time.
Define Row Sets Row sets are defined to specify the format and content of the report rows. Define Define Row sets include assigning accounts, defining calculations and formatting. There are three types of row sets: Heading rows Detail rows (Account Range) Calculation rows (Subtotals and Totals) • • •
Example: R# 10 12 14 16 18
Income: Freight Acct.#310000 = All its Child values Sales & Services Acct.#320000 = All its Child values Othe Otherr Inc Incom omee Acct Acct.# .#33 3300 0000 00 = All All its its Chil Child d val value uess ------------------------------------Total In Income Sum of of Ro Rows of 10 th through 16 16
The above example illustrates the following: Rows 10 is the heading row Rows 12-16 is the detail rows Row 18 is the calculation row • • •
For the purposes of row set training, we will define the following rows to exemplify the three types of row sets defining: 2
FSG Reports
Row 10 Row 12 Row 18
Heading Row Account Assignment Calculations
Navigate to:
Reports>Define>Rowset Reports>Define>Rowset
Define Row Sets: Name: Description:
Enter a name for the Row Sets ( TCIL Profit & Loss Account ). Enter a description fo for the Ro Row Sets
Click on Define Rows button.
3
FSG Reports
Define Row 10:
Enter the Selection Criteria:
Line:
Line Item:
Enter the line number (10). Line increments of 10 are good practice, as this enables you you to insert insert new lines lines later in the the required order. Note: These rows will will be printed in ascending order and therefore control the order in which rows appear in the report. Enter a line item description (Income:) for row to appear as the row label.
Note: Optionally, enter the Format Options, Advanced Options, Display Options and Balance Control options for each row.
Format Option: Indent:
Enter the number of spaces to be indented from the left margin of the report. Line ines to to Ski Skip: p: Ente En terr the the num number ber of of blan blank k lin lines es that that FSG FSG wil willl pla place ce on the the re report port Befo Before re (0) and After (0) the row. Note: This applies applies to the the entire row. row. Underline Character: Character: Specify an underline character (----(----- single-ruled single-ruled for subtotal
Or = = = = double-ruled double-ruled for totals) to print print on the report Before (left 4
FSG Reports
Page Break:
Display Options: Format Mask:
•
format is used for blank ) and After (left blank ) the row. Usually, this format the calculation row. Check the Before (left blank) and After (left blank ) checkboxes to indicate whether FSG should insert a page break before and /or after printing the row. row.
Enter the format mask (left blank ) to define how FSG prints the numerical values on the reports. reports. With a format mask, mask, you can control the following: Number of digits digits displayed displayed on both sides of the decimal point (The number indicator is always the the numeric digit “9”. For each 9 that appears in the format mask field, FSG will will print one number of values.) Symbol used to separate (For example, the format mask 99,999 uses a comma while format mask 99*999 uses an asterisk as the thousands separator.) Currency symbol (For example, to display USD, you can specify a $ and Japanese yen, a ¥.) Symbol used as the decimal indicator (For example, the format mask 99.9 uses a period, while format mask 99,9 uses a comma as the decimal point.) Additional information you may want to print before or after the amounts (For example, you could have each number print with the phrase “translated” immediately following following it.) •
•
•
•
Note: Enter a format mask for displaying displaying row values only if you want to override the column level format mask. For example, number 4500 would be displayed using different format mask: Format Mask 9 999 999,999 $999,999 ¥999,999.99 99.999.999,99 DM 99,999,99 (Translated)
The Factor: •
Displayed As… 4500 4,500 $4,500 ¥4,500.00 4.500,00 DM 4,500 (Translated)
Enter the display precision factors (left blank ) to display numeric values: to two decimal places Units –Full precision; rounds to 1,000; rounds to three decimal Thousands –Divides balances by 1,000; places •
5
FSG Reports
1,000,000; round to three decimal Millions –Divides balances by 1,000,000; places 1,000,000,000; round to three Billions –Divides balances by 1,000,000,000; • decimal •
Note: The row set factor overrides the column level factor.
Level of Detail: il:
Assign the level of detail (left blank ). ). There are three options that control the level of detail FSG prints on the report: Financial Analyst (includes all levels of detail) (Includes only rows and columns defined for Supervisor (Includes Supervisor or controller level of detail) (Includes only rows and columns defined for the Controller (Includes Controller level of detail) • •
•
Note: The default level of detail is Controller.
Display Row/Column: Check the Display Row box to display rows on the report. Note: For rows that are defined but not displayed, FSG will will hide the rows and repositions all other rows. Disp Displa lay y Zer Zero: o:
Check heck the the Dis Displ play ay Zero Zero box box to to pri print nt the row row when hen it it has has a zero zero bala balanc nce. e. Note: If this box is not checked, the row is suppressed on reports when it has a zero balance.
Change Sign:
Change the sign (left unchecked ) to display negative balances. Credits are displayed as negative amounts and debits as positive amounts. This option is typically applies to rows. Note: You should change the sign for Revenue, Liability, and other credit balance accounts.
Change Sign on Variance:
Change the sign on variance (left blank ) to display negative variance balances. This option typically typically applies to rows. rows. Note: You should change the sign for Revenue, Liability, and other credit balance accounts.
Note: Because defining Row 10 is only applicable to the point of Display Options, the Account Assignments, Assignments, Advanced Options, Calculations, and Balance Control will be discussed discussed further in the training document as Row 12 and Row 18 are defined.
Define Row 12:
6
FSG Reports
Enter the Selection Criteria: Line: Line Item: Indent: Lines to to Sk Skip: Underline Underline Characte Character: r: Page Break:
Enter the line number (12). Enter a line item description (Freight ). Enter the number of spaces to be indented (8) Enter th the nu number of of Lines to to Sk Skip Be Before (0) and After (0). Enter Enter the Underline Underline Characte Characters rs Before Before (left blank ) and After (left ). blank ). Enter the Pagebreaks Before (left blank ) and After (left blank )
Click on Account Assignments button to assign accounts for Row 12.
7
FSG Reports
Account Assignment Selection Criteria: Sign: Select a mathematical sign such as +, –, etc (+) to add or subtract the totals for the account range. Note: To use this feature, each segment in the range must be defined with a display type of T (Total) Accounts: Enter the Low (310000) and High (310000 ) for the range of account combinations. In the value set for account segment, segment, 310000 is the parent account for the posting level accounts falling in its hierarchy. Display: Select (T) the display type for each account segment E (Expand the range and display all segment values) T (Total the range and display only the total for the segment values) B (Both expand and total the range and display all segment values and a total for the segment values) Note: Select “T” for each segment of the range if multiple account ranges are assigned to a row and you want to total them and if you later want to define the content sets to create more specific reports. • • •
8
FSG Reports
Summary: Activity:
The summary option (left unchecked ) allows the report to have only summary balances for the accounts accoun ts in the specified range. Select an activity type such as Dr, Cr, or Net (Net) to specify the types of balances to use for the accounts in each account range.
Note: If you assign accounts to a row, you cannot define a calculation for that same row. You can do one or the other, but not both.
Display Options: Format Mask: Enter the format mask (left blank ). ). The Factor: Enter the display precision factors (left blank ). ). Level of Detail: il: Assign the level of detail (left blank ). ). Display Row/Column: Row/Column: Check the Display Row box. Display Zero: Check the Display Zero box. Change Sign: Change the sign (left unchecked ). Change Sign on Variance: Change sign on variance (left unchecked ). Advanced Options: Row Name: Percent cent of Row: ow:
Enter a name (Freight ) to reference the row when defining calculations. Note: this name does not appear on any reports. Ente nter a val value ue for for a pe percent centag agee ro row (left blank ) to use as the percentage denominator.
9
FSG Reports
Over verride ide Col Colum umn: n:
Overr verrid idee col colum umn n (left unchecked ) if you want the row calculation to take precedence over any conflicting column calculations
Save your work
Define Row 18:
Enter the Selection Criteria: Line: Enter the line number (18). Line Item: Enter a line item description (Total ) Format Option: Indent: Lines to to Sk Skip: Underline Underline Characte Character: r: Page Break:
Enter the number of spaces to be indented (8). Enter th the nu number of of Lines to to Sk Skip Be Before (0) and After (0). Enter Enter the Underline Underline Characte Characters rs Before Before (-) and After (-). Enter the Pagebreaks Before (left blank ) and After (left ).
Display Options: Format Mask:
Enter the format mask (left blank ). ). 10
FSG Reports
The Factor: Enter the display precision factors (left blank ). ). Level of Detail: il: Assign the level of detail (left blank ). ). Display Row/Column: Row/Column: Check the Display Row box. Display Zero: Check the Display Zero box. Change Sign: Change the sign (left unchecked ). Change Sign on Variance: Change sign on variance (left unchecked ). Advanced Options: Row Name: Percent cent of Row: ow: Override Column
Enter a na name to to re reference nce th the row when de definin ning calcul culations. Note: this name does not appear on any reports. (left blank ) Ente nter a val value ue for for a pe percent centag agee ro row (left blank ) to use as the percentage denominator. Override column (left unchecked ) if you want the row calculation to take precedence over any conflicting column calculations.
Click on Calculations button to define the formula to calculate row amounts.
Calculations Selection Criteria: 11
FSG Reports
Sequence:
Operator:
Constant:
Sequence Low and High:
Row Name:
Enter the sequence number (1) for each step of the calculation. This controls the order FSG follow when performing the mathematical operations required completing the calculation. Line increments of 10 are good practice, as this enables you to insert new lines later in the required order. Enter the mathematical operator (+) such such as + for addition, addition, – for subtraction, * for multiplication, / for division, Enter for enter value, etc) for each step of the calculation. Optionally, enter a number to use as a constant in the formula. For example, if you want to see a 10% decrease in administrative expenses when creating a budget for next year, multiply the present administrative expenses by the constant value. Enter the sequence Low (12) and High (16) that correspond to the range of rows in the calculation. fields, and select the Note: Click on the poplist in the Low and High fields, specific Low (12) and High (16) detail rows to be included in the range. Optionally, enter the name (left blank ) of a specific row instead of the sequence low and high to use in the calculation.
Save your work. Note: Though Balance Control Options may also be defined for rows, they are usually defined for columns. As a result, they will will be discussed as the columns are defined in the training document.
Define Column Sets Column sets define define the format and content of the report columns. Defining Column Column sets include specifying account balance types, creating headings, defining calculations, and specifying specifying format. The two types of columns that will be defined are: Detail columns (Actual/Current and Budget) Calculation columns (Variance, Percentages, Subtotals and Totals) • •
Example: C# 10 20 30
Current Previous Year Variance nce (=Current – Previo vious)
The above example illustrate the following: following: Columns 10-20 are the detail columns Column 30 is the calculation column • •
12
FSG Reports
For the purposes of column set training, we will define the following columns to illustrate the two types of column sets defining: Column 10 10 Current Colu Column mn 30 30 Vari Varian ance ce (= (=Curr Curren entt – Pr Previo evious us)) • •
Navigate to Reports Reports > Define > Column set
Define Column Sets: Name Description: Over verride ide Segment ent:
Enter a name for the Column Sets Sets Enter a description for the Column Se Sets Optio ptiona nal. l. (leave blank) The override segment is used to create “breakdown” reports. For example, if Accounting Unit is is selected as the Override Segment, Segment, this feature allows you to breakdown data in a report to specific segment values for Accounting Units.
Click on Define Columns button.
13
FSG Reports
Define Column 10:
Enter the Selection Criteria: Position:
Enter the starting position (60) for each column. The position position determines the number of characters from the left margin of the page that marks where each column starts. Note: Consider the following factors when determining the starting position: Total report width Number of columns columns in the column set Width of each column Starting position and width of previous columns Currency profile options Margins Overall appearance (balance and uniformity) • • • • • • •
Sequence:
Format Ma Mask: The Factor: Level of Detail: il:
Enter a sequence number (10) for each column. Note: Unlike the row set, the sequence number does not control the order of the columns on a report. Instead, the column order order is determined by its its position. Enter the format ma mask Enter the display precision factors (left blank ). ). Assign the level of detail (left blank ). ).
14
FSG Reports
Note: Optionally, enter the Balance Control options, Advanced Options, and Display Options for each column.
Balance Control Options: Amount Type:
Currency: Control Value: Offset:
Advanced Options: Column Na Name: Description: Perce Percent nt of of Colu Column: mn: Over Overri ride de Val Value ue Override Row Calculations:
Define the type of values (YTD-Actual ) to include include in a column. column. Note: If you assign an amount type to a column, you must also assign an offset. offset. Note: If you enter a budget, encumbrance, or variance amount type, you should enter a Control Value to assign budgets and encumbrance type to the report definition Enter the currency (INR ) which is used to report translated account balances for a specific currency. currency. Enter the control value (left blank ) which is used to include budgets, encumbrance types, and currencies in report. Enter the relative offset (0) if you want to report on a period or effective date before or after your runtime period or effect date. If you specified specified Amount type refers to a period, such as PTD-Actual, then the Offset will be in number of periods. periods. However, However, if your specified specified Amount Type refers to days then the Offset will be in number number of days. Note: You must must specify offsets offsets at the same level (row or column) at which you specified amount types and control value numbers. Enter a co column na name (Current ) to reference the column when defining calculations. Note: this name does not appear on any reports. Enter th the column de description (Current ). Enter Enter a value value for a perce percentag ntagee row row (left blank ) to use as the percentage denominator. If you you ass assig igne ned d an an ove overr rrid idee seg segme ment nt to your your colu column mn set set,, you you ente enterr the the segment value here here (left blank ). ). Enter an ov override column (left unchecked ) if you want the column calculation to take precedence over any conflicting row calculations.
Display Options: Level of Detail: il: Assign the level of detail (left blank ). ). Display Row/Column: Row/Column: Check the Display Row box. Display Zero: Check the Display Zero box. Change Sign: Change the sign (left unchecked ). Change Sign on Variance: Change the sign on variance (left unchecked ). Note: Because defining Column 10 is only applicable to the point of Display Options, the Calculations, Account Assignments Assignments and Exceptions buttons buttons will be further in the training document document when Column 30 is defined. Save your work.
15
FSG Reports
Define Column 30:
Enter the Selection Criteria: Position: Sequence: Format Mask: The Factor:
Enter the starting position (100). Enter the sequence number (30). Enter the format mask (9,999,999,999). Enter the display precision factors (left blank ). ).
Balance Control: Amount Type: Currency: Control Value: Offset:
Define the type of values (left blank ). ). Enter the currency (left blank ). ). Enter the control value (left blank ). ). Enter the relative offset (left blank ). ).
Advanced Options: Column Na Name: Description: Perce Percent nt of Column Column:: Over Overri ride de Val Value ue
Enter a co column na name (Variance) to reference the column when defining calculations. Note: this name does not appear on any reports Enter a description (Variance ). Enter Enter a valu valuee for for a perce percentag ntagee of colu column mn (left blank ) to use as the percentage denominator. If you you ass assig igne ned d and and over overri ride de segm segmen entt to to you yourr col colum umn n set set,, you you ente enterr the the segment value here (left blank ). ).
Override Row 16
FSG Reports
Calculations:
Override row (left unchecked ) if you want the column calculation to take precedence over any conflicting row calculations.
. Display Options: Level of Detail: il:
Assign the level of detail (left blank ) as you would when defining the rowset. Display Row/Column: Row/Column: Check the Display Column. Display Zero: Check the Display Zero. Change Sign: Change the sign (left unchecked ). Change Sign on Variance: Change the sign on variance (left unchecked ).
Click on Calculations button.
Calculations Selection Criteria: Note: The calculations for Column 30 (Variance = Current Year – Previous Year)
Sequence: Operator:
Constant: Sequence Low and High:
Enter the sequence number (10) and (20) for each step of the calculation. Enter the mathematical operator (+ for line 10 and – for line 20) such as + for addition, – for subtraction, * for multiplication, / for division, Enter for enter value, etc) for each step of the calculation. Optional, enter a number to use as a constant (left blank ) in the formula. Optional, enter or select from the field poplist the sequence Low (left ran ge of columns in blank ) and High (left blank ) that correspond to the range the calculation.
17
FSG Reports
Row Na Name:
Instead of of ent enteering the se sequence Lo Low and and High, you ca can ent enteer or or se select form the field poplist the name Current Year for line 10 and Previous Year for line 20 to use in the calculation.
Save your work.
Optionally, click on Account Assignments button to assign accounts to columns as you would to rows. assign accounts to the same column. column. In Note: If you define a calculation for the column, you cannot assign other words, you can do one or the other, but not both. Optionally, click on Exceptions button to create an exception report.
Build Column Set
Optionally, click on Build Column Set button in the Column Set window to define a column set or modify existing column sets by laying it out graphically. Note: Because Column Set Builder does not have all the options as the Column Define such as account assignments, calculations, or exceptions, it is recommended that you use this tool to modify existing column sets.
Navigate to:
Reports>Define>Column Reports>Define>Column Set>Build Column Set
18
FSG Reports
Enter the Selection Criteria: Note: Sequence, Name, Amount Type, Offset, and Format Mask are discussed in the Column Define and therefore need not be covered covered in this section section again. Enter the Sequence number, number, Name, Amount Type, Offset, Offset, and Format Mask for each column. Left Margin: Width: Heading:
Specify the left margin (60) of the report where the columns are set. Specify the width of the columns. Consider the size of the numbers, factor, and format mask when you specify the width. Enter the heading for ea e ach column.
Optionally, click on Create Default Heading button to have FSG generate a heading based on the amount type and period offset for each column. You can later modify the the default heading.
The ampersand (&) in the heading identifies the following data element, such as POI, as a relative heading. POI represents represents period of interest with 0 offset. offset. Optionally, click on More Columns Options button to enter additional column format and display options, such as Currency, Control Value, Override Value, Factor, and Level of Detail. Note: Use the layout icons to add, move, or delete columns, or to change column width. 19
FSG Reports
Click on Apply button to save your work and continue working working with Column Set Builder. Builder. Click on OK to save your work and close the Column Set Builder. Optionally, In the Column Set window, click on Create Heading button to define column headings for a report.
Enter the heading for each column in the Scrolling region, using using the ruler as a guide. Click onApply button to save the heading and continue working working in the the Column Set Heading. Click onOK button to save your work. Note: Optionally, click on Create Default Heading button to have FSG generate a heading based on the amount type and period offset offset for each column. You can later modify the the default heading later.
20
FSG Reports
Define Financial Reports and Report Sets Define a financial report by combining the row set and the column set. Navigate to:
Reports>Define>Report Reports>Define>Report
Enter the Selection Criteria: Name: Title: Description: Required Components: Row Set: Column Set: Optional Components: Cont ontent ent Se Set:
Enter a name for the financial report. Enter a title for the financial report. Enter a de description fo for the financial re report. Select the row set to use for the financial report TCIL Profit & Loss Account ). Select the column set to use for the financ nanciial repor port ( TCI P&L)
Optio ptiona nall lly y ent enter er or sele elect from the popl popliist a pr predefi define ned d Con Conttent ent Se Set to to generate multiple, similar similar reports in one run. The content set controls controls how the multiple multiple reports differ from from each other. For example the same expense report can be generated for every department. 21
FSG Reports
Row Or Order:
Opti ptionall nally y en enter or or se select fr from the the popli plist a pr predefined Ro Row Or Order to to control how detail rows appear in the report. For example, you can display account descriptions in addition to or instead of segment values, sort detail rows by amounts displayed in a column, account segment values or segment value descriptions. Display play Set: et: Optio ptiona nall lly y ent enter er or sele elect from the popl popliist a pr predefi define ned d Di Display play Set to to produce report variations which omit sensitive sensitive information. information. To do this you simply tell FSG which row or columns should or should not be displayed.
Other Options: Segme Segment nt Overri Override: de: Currency:
Roun Roundi ding ng Opti Option on:: •
•
Level of De Detail tail::
• •
•
Output put Opt Optiion: • •
•
Optiona Optionally lly,, enter enter a segme segment nt overri override de value value for the the account account segm segment entss you want to override. Optionally, enter a default cu currency for the report. FSG uses this currency only for those rows and columns to which you did not assign a currency when you defined row and column sets. Opti Optiona onall lly, y, sel selec ectt the the rou round nding ing meth method odss (Calculate and Then Round ) to use for calculations in the report: Calculate and Then Round – FSG performs calculations before any rounding. Round and Then Calculate – FSG rounds values before they are used in calculations. Optio ptiona nall lly, y, ass assign ign the the level of de detail ail (left blank ) for individual rows and columns as well well as for a report. FSG prints prints only those those rows and columns whose level level of detail matches that specified specified for the report. report. There are three options that control the level of detail FSG prints on the report: Financial Analyst (includes all levels of detail) Supervisor (Includes only rows and columns defined for Supervisor or controller level of detail) Controller (Includes only rows and columns defined for the Controller level of detail) Note: If you do not enter a level of detail for a row or column, the default level of detail is Controller Opti ptionall nally y, se select th the ou output put op options (Text) for the report: Text (Produces the report in standard text form) Tab Delimited (Produces the report whose columns are delimited by tabs, making it easier to import the report into a spreadsheet) Spreadsheet (Produces the report designed specifically for downloading to GL Desktop Integrator)
Optionally, click on Control Values button to assign budgets, encumbrance types, and currencies if the row or column set has control values assigned. Save your work.
22
FSG Reports
Click on Define Report Set button to combine predefined FSG reports so that you may run them together, OR Navigate to:
Reports>Define>Report Reports>Define>Report Set
Enter the Selection Criteria: Name: Description:
Reports: Sequence:
Name:
Enter a name ( Financial Financial Statements) for the report set. Enter a description (Balance Sheet and Profit and Loss Account ) for the report set.
Enter the sequence number (10) and (20) for each step of the calculation. Line increments of 10 are good practice, as this enables you to insert new lines later in the required order. Select name of each report you want want to include in the report set. set. In this example, Profit & Loss Account– (for line 10) and Balance Sheet As At– (for line 20) were selected.
Save your work. 23
FSG Reports
Request Financial Reports and Report Sets
Navigate to:
Report>Request>Financial Report>Request>Financial
Note: You can also click on Run Report button from the Define Financial Report window to navigate to the Run Financial Reports window.
To Run Individual Report: Reports – Request – Financials
24
FSG Reports
Select tab for Individual Reports . Select the report name (Profit & Loss Account ). Select the Ledger Name for which to run the report. Select the accounting period for which to run the report Accept functional currency default (INR ) for Set of Books, or select the currency to be used for rows and columns. Click on Submit button to process the request. • • • • •
•
25
FSG Reports
26
FSG Reports
To Run Report Sets: Select tab for Single Report Set . Select the report set name ( Financial Statements ) Optionally, change the Report Parameters (Period and Currency ) and click on the Apply button to save the changes. Run all or part of the report set: Check the box next to the report (s) you want to run. Optionally, check the Select All Reports checkbox to run all the reports. Click on Submit to process the request. • • •
• • • •
Optionally, you can run multiple report sets: Select tab for Multiple Report Sets . Select the Report Set names ( Financial Financial Statements, Statements, etc ) Select the accounting period for which to run the report Accept functional currency default for Set of Books, or select the currency to be used for rows and columns. Click on Submit button to process the request. • • • •
•
27
FSG Reports
Run FSG Reports form Standard Request Submission :
Navigate to:
Other>Request
Click on Submit a New Request button. Select Single Request and click on OK .
28
FSG Reports
Select Program – Run Financial Statement Generator for the Request Name. Tab to the parameters.
29
FSG Reports
Select the report and optionally change the parameter defaults. defaults. The Currency, Rounding Rounding Option, and other parameters default from from the report. Click OK to close the Parameters window.
Click on the Schedule button to schedule the request to run at time other than As Soon as Possible. Click on the Options button to select the print parameter. Select the style, printer, and number of copies to print the report. report. Click OK to close the Upon Completion…window. Click on the Submit Request button to process the request.
30