Custom Financial Report - Training Guide

The IFS Custom Report uses Account Groups you have created in your accounting software (Excalibur GL120 or BOLO GL1225), and formats your report based on patterns in the Account Group IDs. 

To use the Custom Report, you must create Account Groups in BOLO or Excalibur, and assign the Account Group IDs according to specific patterns. These patterns allow you to choose the order of your line items, number formatting (e.g., including dollar formats), and spacing (e.g., inserting a space between sections of data in the report). 
Your Account Group IDs have three parts:

• Prefix: A set of characters that help you identify which Account Groups to select when running the report.

  • Recommendation to use 1-2 in Excalibur because of character limits.

• Line Number: 2-3 numbers that determine the sorting order of the Line Items in your report.

• Suffix: Determines the format and value of the line item.

  • For example, "HH" marks the row as a Header; "VV" marks the row as a Volume value, and "TV" marks the row as a Total Volume.

Figure 1 Account Group-based Formatting

The Custom Report worksheet is a spreadsheet that helps you plan your report and create Account Groups in your accounting software.

Figure 2 Custom Financial Report Planner

• Column E: Select a Row Type from the dropdown list.

• Column F: Type in a Prefix for your Account Groups.

  • It is not required to use the same prefix for each of your Account Groups; however, using the same prefix makes it easier to select the Account Groups when you run the report.

  • Both the Prefix and the Row Number are used to sort the Account Groups in the report. For example, LOS20 would come before ZZZ01.

• Column G: Type in a Description for your Account Group. The Description is what will appear in the header cell of the Line Item for this Account Group.

• Columns H to J: You do not need to enter values into these columns. They will automatically generate the appropriate Suffix and Row Number for your Line Item, and create an Account Group ID.

  • The Account Group ID determines the sorting order of Account Groups in the report, but the ID itself does not display on the report.

• Column L: You can use this column to list out the Accounts you want to include in your Account Groups. This is particularly helpful for creating Account Groups that are Total Rows, Spacers, and Headers.

  • Total Rows: Total Rows include all the Accounts used in each of the Account Groups that are included in your Total Row.

    • For example, in Figure 2, report section Revenue Deductions contains two line items – Account Groups Lease Burdens (accounts 560.010 and 560.070) and Production Taxes (accounts 550.010 and 550.110). The Total row – Total Revenue Deducts – should contain all four of these accounts. Column L lists these 4 accounts for the Total Revenue Deductions line item.

  • Header Rows: You will create an Account Group for your header rows so that they appear in the reports.

Figure 3 Header Rows

• Header Account Groups should include all Accounts contained in all the Account Groups for the report section.

• For example, in Figure 3, the report section Revenue Deductions contains the Account Groups Lease Burdens, Production Taxes, and Total Revenue Deductions. The Account Group for the Header row contains the 4 Accounts that are in Account Groups Lease Burdens and Production Taxes, as shown in Column L.

• The reason for this is so that the Header row will print even if there is no data for one of the Account Groups. I.e., if there is no Lease Burdens but there is Production Tax, the Header row will still print.

  • Spacer Rows: Account Groups should also be created for each of your spacer rows.

    • For example, if you want a spacer row between report sections Oil Sales, Gas Sales and NGL Sales, you will need two spacer Account Groups, as in Figure xx.

    • Spacer Account Groups should be mapped to all Accounts contained in the Account Groups for the report section, as described for Header Account Groups in the section above.

    • For example, in Figure xx, there is a spacer row after the Revenue Deducts section of the report and before the Total Revenue Deducts line item. The Account Group Revenue Deducts Spacer contains all the accounts included in the Lease Burdens and Production Taxes Account Groups – 4 in total.

    • The purpose of assigning all the accounts to the spacer Account Group is so that the spacer appears in the report even if there is only data for some of the Accounts in that section of the report.

Figure 4 Spacer Rows

After filling out IFS Custom Report worksheet, use this information to create your Account Groups in Bolo or Excalibur. After you have entered the Account Groups into your accounting software, the next time your data is refreshed these new Account Groups will become available in IFS Reporting.

1. In IFS Reporting, navigate to the Custom Report at: public/P2/Midcap/Financial/Reports/02. Accounting/R 02.25 Custom Financial Report.

2. Right click to copy the report.

Figure 5 Copy the Custom Report

3. Navigate to your user content folder.

4. Click paste to copy the report to your folder.

Figure 6 Paste the Custom Report in your User Folder

5. To rename the report, Right-click and select Properties.

6. To save a set of report filter values:

Figure 7 Selecting your Custom Account Groups

• In the Input Control pop-up window, select value for each of your filters.

• For Select Account Groups, select all of the Account Groups you created for your custom report. For example, in the screenshot above, the Account Groups with prefix "DS-" are selected.

• Click Save, and type in a name for your report options.

Figure 8 Saving Report Filter Values

• The next time you run the report, you can select the saved values at the top of the Input Control window.