WELCOME TO GMS ACCOUNTING ONLINE HELP
The help you need at your fingertips.
You are here: General Ledger > G/L Supplements > #412 R & E Report Designer
#412 R & E Report Designer
Function
You must display the Revenue and Expenditure Report by Element, Project and Agencywide Revenue/Expense Report or select Post All Financials from Monthly Processing/Financial Reports prior to preparing a report using this supplement.
The R & E Report Designer allows you to design the report format(s) and content from a variety of different options. You can prepare reports using all Projects and Elements, selected Projects & Elements, or an aggregate total of selected Projects and Elements. You can select to show line item detail, or cost categories (subtotals) with or without the detail behind the categories. You can generate reports using the actual Fringe, Common, and M & G rates, or request a provisional rate even if your organization’s rates are defined as actual in the Cost Allocation selection of Tools.
Things You Should Know
When saving a format with allocation detail, it will also save which pools you selected to the detail.
In order to utilize the show combining Projects or Elements and Cost Category features, you need to create these and save the formats. This is done through the second and third tabs appearing on this supplement’s screen. Following are the instructions:
Set-up Cost Categories:
When you click on the Cost Categories Tab, a form will appear with the two options, Setup Categories and Assign Line Items.
To set up Categories:
When that option is selected, a form will appear with two fields, Code and Description.
Edit - Click on this to enter a new category.
Code – At the bottom of the grid, assign a numeric value to the Category you are creating. All revenue categories need to be between 1000 and 1999. Category 1999 is the default for Other Revenues. All expenditure codes need to be between 2000 and 9999. Category 9999 is the default for Other Expenses.
Description – Enter the description of the Category you are creating. An example of these would be, Personnel Costs, Supplies, etc.
Save Edit – This will permanently record the current Category.
Delete - This will delete the entire format currently displayed on the screen. Should you wish to delete only one GL code setup, highlight the area and use your delete key to remove the characters.
Print – This will display the current categories with the option to print or export.
To Assign Line Items:
When this option is selected, a form will appear for you to use in assigning Revenue and Expenditure codes to the existing categories. You have the option of creating numerous formats. This gives you the ability to define which transaction codes are assigned to a specific category using multiple combinations, depending on different reporting needs.
When the Assign Line Item option is selected, the following items appear:
Edit - Click on this to begin assigning line items.
Format # – Assign a number to the Format you are creating.
Description – Enter the description of the Format you are creating.
GL Code – All of your revenue and expenditure codes will populate this column.
Cost Category Code – Select the existing Cost Category to which you wish to assign the transaction code. As stated in the code section above under Set up Categories, all Revenue codes will be restricted to existing categories between 1000 and 1999, and all Expenditure codes will be restricted to existing Categories between 2000 and 9999. If you have transaction codes with budgets or YTD activity that are not assigned to a category, when the report is generated all unassigned Revenue Codes will appear under category 1999 – Other Revenues, and all unassigned Expenditure codes will appear under category 9999 – Other Expenses.
Save Edit – When all transaction code assignments are complete for this format, select save edit to permanently record this information.
Set up Combine Project/Element Formats
When you select the Combine Formats tab, a form will appear that allows you to create numerous formats in which you can choose combinations of Projects or Elements to be included in the report. This is useful for agencies that wish to produce department reports that would include Projects and Elements specific only to one department, such as Children’s Services, or Weatherization, etc.
Edit - Click on this to enter a new format.
Type – Select either Element or Project to define whether you will be producing reports at the Element or Project level for this format.
Format # – Assign a number to the format you are creating.
Title – Enter the title for your new format.
Status – Click this box if you wish this to be an active Format.
Grid – Select the Elements or Projects you wish to include on the report.
Save Edit – When all data on the form is complete, select Save Edit to permanently record this information.
Delete - This will delete the entire format currently displayed on the screen. Should you wish to remove only certain Projects or Elements from an existing format, do so by clicking on the desired Project or Element number in the grid to remove the checkmark.
Print – This will display the current format’s information with the option to print or export.
Steps to follow to print cost allocation detail reports for a previous period:
Operating Instructions
R/E Report Designer tab
Data to be Included:
Regular Month End – This selection will generate the reports with all costs that are allocated through the pools remaining in their control accounts. For example, if your organization uses the Fringe Benefit Pool, all fringe costs will remain in the control account, 505000. As this selection is made, the following forms will appear:
Fringe Actual/Provisional Rate– If you wish to use the actual Fringe Benefit rate as calculated, leave this blank. If you wish to use a Provisional Fringe Benefit Rate, enter the desired rate at this time. For a 25-½ % Fringe Benefit Rate, please enter it in the format of 25.5.
Indirect Actual/Provisional Rate - If you wish to use the actual Indirect Cost rate as calculated, leave this blank. If you wish to use a Provisional Indirect Cost Rate, enter the desired rate at this time. For an 18-½ % Indirect Cost Rate, please enter it in the format of 18.5.
If Supplement #403 Dual Indirect Cost Pool is used, you will be asked:
Common Cost Actual/Provisional Rate – If you wish to use the actual Common Cost rate as calculated, leave this blank. If you wish to use a Provisional Common Cost Rate, enter the desired rate at this time. For an 18-½ % Common Cost Rate, please enter it in the format of 18.5.
M&G Actual/Provisional Rate– If you wish to use the actual M&G rate as calculated, leave this blank. If you wish to use a Provisional M&G Rate, enter the desired rate at this time. For a 9-¾ % M&G Rate, please enter it in the format of 9.75.
Allocation Detail – If you use Supplement #399 Consolidated Cost Allocation Detail and have prepared the detail for the selected month, this option will provide you with the line item detail of all cost allocation pools that you select to include the detail for. If you only select one pool to show the detail for and then decide you want to include the detail for additional pools, you need to close the supplement and re-open and make your pool selections. Refer to Supplement #399 Consolidated Cost Allocation Detail in the Help section for further information on how that supplement works.
Period – Displays the date the Agencywide R&E was last prepared. Report Designer reports will reflect information as of the date displayed. The R&E by Element and Project must be displayed to get accurate information on these reports.
Report By:
Project– Use this option if your reports will be prepared at the Project level.
Element – Use this option if your reports will be prepared at the Element level. If an element report is chosen, you will be given the option for page breaks between elements.
Aggregate – If this option is selected, the data for all Projects or Elements chosen will be combined for one consolidated report. You will be prompted to enter a Title, which will be printed as the title on the report.
Use Agencywide Budget – If you are printing an aggregate report and including all projects/elements that are reflected on your Agencywide Budget, you may choose to have budget amounts come from your agencywide budget rather than adding all project/element budgets together. If this option is selected, the columns appearing on the report will be the same that appear on the Agencywide Revenue/Expense Report in Monthly Processing. (No Prior Year or Project Total will be included.)
Use Combine Format? – To use this option you need to have set up and saved Combined Element or Project Formats through the Combine Formats tab on this supplement form. As this option is selected, you will be prompted to enter P for Project Format or E for Element Format. Then identify the Format number you wish to use.
Title - You can enter a title to any report format chosen.
Report Format:
Normal Line Items – Select this option to include all line items in your report.
Cost Category Detail – To use this option you need to have set up and saved the Cost Category Report Format(s) through the Cost Categories tab on this supplement form. This selection will provide you with the Cost Category totals as defined as well as the line item detail for each category.
Cost Category Summary – To use this option you need to have set up and saved the Cost Category Report Format(s) through the Cost Categories tab on this supplement form. This selection will provide you with the Cost Category totals as defined without the line item detail.
Format # – If you select Cost Category Detail or Cost Category Summary, you must identify the report format you wish to use.
Include All Projects – Check this box to include all Projects in your report. If this box is not checked, a grid will appear allowing you to select which Projects you wish to include.
Include All Elements – Check this box to include all Elements in your report. If this box is not checked, a grid will appear allowing you to select which Elements you wish to include.
Include Indirect Detail? – You will be given this option if you select Regular Month End data, Report by Aggregate or use Agencywide Budget and include all projects and all Elements. If this is selected, you will not see the YTD amount for the indirect transfer code, but it will show each line item in the indirect pool added to the direct charges.
With Drill Down Detail? – Check this option if you wish to get the drill down detail for selected GL codes.
Drill downs are designed to be used to get the detail of all direct charges for the current month and year-to-date.
If you selected Normal Line Items or Cost Cat Detail on the Report Format section, a checkbox will appear to select the drill down option.
Include all GL Codes – Removing the checkmark will display a listing of all revenue and expenditure codes to select from.
Create Expenditure Excel File – Rather than printing a report, your selections will be exported to an Excel spreadsheet. You may select which columns (Budget, Prior Year, Current, YTD, Project Total, Under/Over and % of Budget) you wish to be included on your spreadsheet along with Expense Only, Revenue Only, or Both.
Retrieve- This will allow you to retrieve a previously saved Report Designer format. Should you wish to delete a format, single click on the format name appearing on the Retrieve Format form, and select delete. Should you wish to edit an existing format, retrieve the format to be edited, make the desired changes, and save it using the same format name.
Print – This will send the report to the screen with the option to print or export the file. Any new GL codes that have been entered will be assigned to the default categories.
You must display the Revenue and Expenditure Report by Element, Project and Agencywide Revenue/Expense Report or select Post All Financials from Monthly Processing/Financial Reports prior to preparing a report using this supplement.
The R & E Report Designer allows you to design the report format(s) and content from a variety of different options. You can prepare reports using all Projects and Elements, selected Projects & Elements, or an aggregate total of selected Projects and Elements. You can select to show line item detail, or cost categories (subtotals) with or without the detail behind the categories. You can generate reports using the actual Fringe, Common, and M & G rates, or request a provisional rate even if your organization’s rates are defined as actual in the Cost Allocation selection of Tools.
Things You Should Know
When saving a format with allocation detail, it will also save which pools you selected to the detail.
In order to utilize the show combining Projects or Elements and Cost Category features, you need to create these and save the formats. This is done through the second and third tabs appearing on this supplement’s screen. Following are the instructions:
Set-up Cost Categories:
When you click on the Cost Categories Tab, a form will appear with the two options, Setup Categories and Assign Line Items.
To set up Categories:
When that option is selected, a form will appear with two fields, Code and Description.
Edit - Click on this to enter a new category.
Code – At the bottom of the grid, assign a numeric value to the Category you are creating. All revenue categories need to be between 1000 and 1999. Category 1999 is the default for Other Revenues. All expenditure codes need to be between 2000 and 9999. Category 9999 is the default for Other Expenses.
Description – Enter the description of the Category you are creating. An example of these would be, Personnel Costs, Supplies, etc.
Save Edit – This will permanently record the current Category.
Delete - This will delete the entire format currently displayed on the screen. Should you wish to delete only one GL code setup, highlight the area and use your delete key to remove the characters.
Print – This will display the current categories with the option to print or export.
To Assign Line Items:
When this option is selected, a form will appear for you to use in assigning Revenue and Expenditure codes to the existing categories. You have the option of creating numerous formats. This gives you the ability to define which transaction codes are assigned to a specific category using multiple combinations, depending on different reporting needs.
When the Assign Line Item option is selected, the following items appear:
Edit - Click on this to begin assigning line items.
Format # – Assign a number to the Format you are creating.
Description – Enter the description of the Format you are creating.
GL Code – All of your revenue and expenditure codes will populate this column.
Cost Category Code – Select the existing Cost Category to which you wish to assign the transaction code. As stated in the code section above under Set up Categories, all Revenue codes will be restricted to existing categories between 1000 and 1999, and all Expenditure codes will be restricted to existing Categories between 2000 and 9999. If you have transaction codes with budgets or YTD activity that are not assigned to a category, when the report is generated all unassigned Revenue Codes will appear under category 1999 – Other Revenues, and all unassigned Expenditure codes will appear under category 9999 – Other Expenses.
Save Edit – When all transaction code assignments are complete for this format, select save edit to permanently record this information.
Set up Combine Project/Element Formats
When you select the Combine Formats tab, a form will appear that allows you to create numerous formats in which you can choose combinations of Projects or Elements to be included in the report. This is useful for agencies that wish to produce department reports that would include Projects and Elements specific only to one department, such as Children’s Services, or Weatherization, etc.
Edit - Click on this to enter a new format.
Type – Select either Element or Project to define whether you will be producing reports at the Element or Project level for this format.
Format # – Assign a number to the format you are creating.
Title – Enter the title for your new format.
Status – Click this box if you wish this to be an active Format.
Grid – Select the Elements or Projects you wish to include on the report.
Save Edit – When all data on the form is complete, select Save Edit to permanently record this information.
Delete - This will delete the entire format currently displayed on the screen. Should you wish to remove only certain Projects or Elements from an existing format, do so by clicking on the desired Project or Element number in the grid to remove the checkmark.
Print – This will display the current format’s information with the option to print or export.
Steps to follow to print cost allocation detail reports for a previous period:
- Go to the Financial Reporting menu. Select the "to" date for any month prior to your current period that you wish to re-print the report for.
- Display R&E by Element, Project and Agencywide.
- Go to GL Supplements, Consolidated Cost Allocation Detail.
- Verify that the correct ending date is listed. Select the desired report you wish to reprint.
Operating Instructions
R/E Report Designer tab
Data to be Included:
Regular Month End – This selection will generate the reports with all costs that are allocated through the pools remaining in their control accounts. For example, if your organization uses the Fringe Benefit Pool, all fringe costs will remain in the control account, 505000. As this selection is made, the following forms will appear:
Fringe Actual/Provisional Rate– If you wish to use the actual Fringe Benefit rate as calculated, leave this blank. If you wish to use a Provisional Fringe Benefit Rate, enter the desired rate at this time. For a 25-½ % Fringe Benefit Rate, please enter it in the format of 25.5.
Indirect Actual/Provisional Rate - If you wish to use the actual Indirect Cost rate as calculated, leave this blank. If you wish to use a Provisional Indirect Cost Rate, enter the desired rate at this time. For an 18-½ % Indirect Cost Rate, please enter it in the format of 18.5.
If Supplement #403 Dual Indirect Cost Pool is used, you will be asked:
Common Cost Actual/Provisional Rate – If you wish to use the actual Common Cost rate as calculated, leave this blank. If you wish to use a Provisional Common Cost Rate, enter the desired rate at this time. For an 18-½ % Common Cost Rate, please enter it in the format of 18.5.
M&G Actual/Provisional Rate– If you wish to use the actual M&G rate as calculated, leave this blank. If you wish to use a Provisional M&G Rate, enter the desired rate at this time. For a 9-¾ % M&G Rate, please enter it in the format of 9.75.
Allocation Detail – If you use Supplement #399 Consolidated Cost Allocation Detail and have prepared the detail for the selected month, this option will provide you with the line item detail of all cost allocation pools that you select to include the detail for. If you only select one pool to show the detail for and then decide you want to include the detail for additional pools, you need to close the supplement and re-open and make your pool selections. Refer to Supplement #399 Consolidated Cost Allocation Detail in the Help section for further information on how that supplement works.
Period – Displays the date the Agencywide R&E was last prepared. Report Designer reports will reflect information as of the date displayed. The R&E by Element and Project must be displayed to get accurate information on these reports.
Report By:
Project– Use this option if your reports will be prepared at the Project level.
Element – Use this option if your reports will be prepared at the Element level. If an element report is chosen, you will be given the option for page breaks between elements.
Aggregate – If this option is selected, the data for all Projects or Elements chosen will be combined for one consolidated report. You will be prompted to enter a Title, which will be printed as the title on the report.
Use Agencywide Budget – If you are printing an aggregate report and including all projects/elements that are reflected on your Agencywide Budget, you may choose to have budget amounts come from your agencywide budget rather than adding all project/element budgets together. If this option is selected, the columns appearing on the report will be the same that appear on the Agencywide Revenue/Expense Report in Monthly Processing. (No Prior Year or Project Total will be included.)
Use Combine Format? – To use this option you need to have set up and saved Combined Element or Project Formats through the Combine Formats tab on this supplement form. As this option is selected, you will be prompted to enter P for Project Format or E for Element Format. Then identify the Format number you wish to use.
Title - You can enter a title to any report format chosen.
Report Format:
Normal Line Items – Select this option to include all line items in your report.
Cost Category Detail – To use this option you need to have set up and saved the Cost Category Report Format(s) through the Cost Categories tab on this supplement form. This selection will provide you with the Cost Category totals as defined as well as the line item detail for each category.
Cost Category Summary – To use this option you need to have set up and saved the Cost Category Report Format(s) through the Cost Categories tab on this supplement form. This selection will provide you with the Cost Category totals as defined without the line item detail.
Format # – If you select Cost Category Detail or Cost Category Summary, you must identify the report format you wish to use.
Include All Projects – Check this box to include all Projects in your report. If this box is not checked, a grid will appear allowing you to select which Projects you wish to include.
Include All Elements – Check this box to include all Elements in your report. If this box is not checked, a grid will appear allowing you to select which Elements you wish to include.
Include Indirect Detail? – You will be given this option if you select Regular Month End data, Report by Aggregate or use Agencywide Budget and include all projects and all Elements. If this is selected, you will not see the YTD amount for the indirect transfer code, but it will show each line item in the indirect pool added to the direct charges.
With Drill Down Detail? – Check this option if you wish to get the drill down detail for selected GL codes.
Drill downs are designed to be used to get the detail of all direct charges for the current month and year-to-date.
If you selected Normal Line Items or Cost Cat Detail on the Report Format section, a checkbox will appear to select the drill down option.
- Put a check in the checkbox next to "Include drill down detail". Since the report takes longer to load when you select drill down detail, we don't have it automatically included.
- Since the report is displayed, you can double click on any column for a GMS code and it will display the current and year-to-date detail. It will display a new report reflecting the posting period, type of book of entry, batch #, document #, description, current and YTD amounts of each item coded to this GL code.
- To close out the detail form, click on the Main Report tab.
- Because of the way we designed the table in the database that stores the timesheets and the allocation of leave and fringe benefit costs, you can click on any of the GL codes that have been set up in the Class Setup for salary, leave and fringe benefits and it will give you a list of the employees with a total of their direct timesheet charges and allocations, if you have been given access to Personnel Drill Downs under Tools, Security Menu. For the GL Code assigned for salaries, you will get a list of employees with their timesheet charges. If leave is also set up to be reflected in the same GL code as salaries, it will give you a total of the timesheet charges plus leave allocated for that employee.
- For 59700 and 59900, which are the two reserved GL codes for indirect and common costs, you will not get any detail if you double click on these codes as there are no transactions directly charged to them. This is also true for the transfer codes set up in Supplement #367 Service Unit Allocations and Supplement #381 Special Allocations/Internal Base.
Include all GL Codes – Removing the checkmark will display a listing of all revenue and expenditure codes to select from.
Create Expenditure Excel File – Rather than printing a report, your selections will be exported to an Excel spreadsheet. You may select which columns (Budget, Prior Year, Current, YTD, Project Total, Under/Over and % of Budget) you wish to be included on your spreadsheet along with Expense Only, Revenue Only, or Both.
Retrieve- This will allow you to retrieve a previously saved Report Designer format. Should you wish to delete a format, single click on the format name appearing on the Retrieve Format form, and select delete. Should you wish to edit an existing format, retrieve the format to be edited, make the desired changes, and save it using the same format name.
Print – This will send the report to the screen with the option to print or export the file. Any new GL codes that have been entered will be assigned to the default categories.
