Also see: Financial Reporting Overview for more information on tabs and menu items at the bottom of the screen.

How do I set up a Spreadsheet?

Spreadsheet reporting is a great management tool that highlights key operating numbers and statistics, enabling users to spot problems. The one-line format per cost center, per well, is easy to review and act upon. Spreadsheet reporting can be generated at a company level, a group level, or both, providing timely and complete information for desired properties.

Financial information in Pak Accounting can be summarized into a spreadsheet format and printed through basic financial reporting procedures. To start you need to set up report definitions to define the rows and columns of the spreadsheet.

NOTES:

• Two reports will need to be defined: A Report for Rows and a Report for Columns.
• When defining a spreadsheet report, both the row and the column definitions must be considered in the account ranges.

For example, management wants to create an Internal Property Operating Statement for Texas properties. The report will need to show the properties as rows. The columns will consist of quantities, revenue, and expenses to arrive at Net Revenue. Management would like the report to resemble the one below

To accomplish this, two reports will be defined and used—a report for rows and a report for columns. When defining a spreadsheet report, both the row and the column definitions must be considered in the account ranges.

The first step is to create a report outline defining the needed rows and columns. Rows going down the page can be grouped and subtotaled by whatever makes sense to you. Do you want to view the report by Lease Number? By State and County? Grouped by Operator? The list is endless…

Columns can be added for whatever you determine is necessary. Do you want to see Oil and Gas Volume, Revenue, Expenses, and Net? What are the key essential elements necessary to provide the information management wants?

Row Based Report

The Row Report is the simplest to define since the bulk of the detail will be set up in the Column Report or defined on the Report screen. Below is the most common setup. The report can be as complex as you need it to be; however, with the power of Financial Groups (which we will learn about in the next section), there is little need to make this complex.

Set up a Row Report as an S record to print a line for every Sub-Account in the defined account range. Make sure the range of accounts includes any accounts that will be reported in columns. This is an internal report in our example, so we will only include the P&L range of 4000 – 5999. If you need a report for the 100% numbers, you will set the range for 9000 – 9999. Adding multiple ranges for the S record will run the risk of seeing the Sub-Account twice on the report.

Since the Spreadsheet reports are run in pairs, it’s good practice to name both reports the same but differentiate them by beginning them with SS Row or SS Column.

Column-Based Report

When defining the Row-Based Report Definitions, we used various Record Types (H, A, P, T, etc.) to describe how we wanted the report to look going down the page. To define each column, we also use different record types to define the different elements and/or data sources. To accomplish this, we use the following Column-Based Record types:

U - Defines the title that will print for the column.

E – Defines one or more account ranges to be accumulated for the column.

N – Defines one or more math/formulas for a column.

For each column, you will have a U record for the column to print. Then, depending on the contents of the column, you could have one or more E or N records (either/or not mixed).

NOTES:

• When doing advanced math functions, there are times when you may want the system to look at an account but not print it on the report. In that case, you would set up your E or N record but not associate it with a U record.
• There is a Wildcard feature included on the screen that allows for the restriction of Sub-Accounts. For example, *TX can be entered into the Wildcard field. All Sub-Accounts containing TX will be included. However, this feature will work only in a report where the column represents one or more different Sub-Accounts and the accounts are set to be listed as rows. See Report Definitions > Record Type E for more information.

NOTE: When you set up the report definitions for spreadsheets, the column widths will auto-adjust to fit your information unless you use the following widths: 10 for the description, 5 for the current Period/YTD amount columns, and 3 for the Pct columns.