The Participant Ledger report can be used to calculate revenue for your organization, including both electronic and external transactions . Using the split line items report, you can summarize revenue by:
Registration
Add-On
GL Code
Date range (monthly, annual, seasonal, etc.)
In this article: Before You Start This process is best completed in Microsoft Excel or Google Sheets.
You should be comfortable with:
Copying and pasting columns
Creating new sheets
Entering formulas
Removing duplicates
The examples below use Microsoft Excel terminology, but the same process can also be completed in Google Sheets.
1. Generate the Participant Ledger Report This report will be necessary for all revenue reports below
Log in to your Provider Portal.
In the Menu, click Finances.
In the submenu, click Participant Ledger.
Click the Filter icon.
Enter the following:
Start Date : First financial transaction date to report
End Date : Last financial transaction date to report
Under Filter by Type , select:
PAYMENT REFUND ACH-RETURN CHARGEBACK
Optional selections (if used by your organization):
External Payments External Refunds Convenience Fees
Under Split Line Items , select Included. Note: Split Line Items must be included so each Registration, Add-On, or GL Code appears on its own row for reporting calculations.
Click SEARCH.
Click the Export icon to download the report as a CSV file.
Open the downloaded file in Excel or Google Sheets.
[ Return to Top ]
Calculate the Net Transaction Amount
00:00: This training will teach you how to efficiently insert columns and enter formulas in
00:04: Google Sheets.
00:05: You will learn essential spreadsheet manipulation skills to enhance your data
00:09: management capabilities.
00:10: Click here to open the column options menu in your Google Sheets
00:13: document.
00:15: Click "Insert 1 column left" to add a new column to the left of the selected column.
00:20: Click here to select the target cell where you will enter the formula.
00:25: Fill "=K2+L2" to calculate the sum of the values in cells K2 and L2.
00:29: K2 and L2.
00:30: Click here to confirm and apply the formula
00:32: you have entered in the cell.
00:35: Click here to finalize the formula application and update your spreadsheet
00:38: accordingly.
00:40: You have learned how to insert a new column and enter a basic formula in Google
00:44: Sheets.
00:44: This skill will help you manage and analyze your data more effectively in your daily
00:49: tasks.
The Net column calculates the final transaction amount after processing fees are included.
In the Participant Ledger:
Payments appear as negative values (credits) Refunds appear as positive values (debits) Because of this, revenue totals may appear as negative numbers in your spreadsheet.
Add the Net Column
Insert a new column after Processing Fee (Column L).
Label the new column Net.
In the first transaction row of the Net column (M2), enter:
=K2+L2
Press Enter.
Copy the formula down the entire column. (You can also double-click the lower right-hand corner of the M2 cell.)
The Net column now shows the transaction amount after processing fees for each row item.
This value can be summarized by:
Registration
Add-On
GL Code
[ Return to Top ]
Calculate Revenue by Registration
Use this section to calculate total net revenue by Registration.
Rename Sheet Rename the sheet by right-clicking and selecting Rename . line-items
Create a New Sheet
Insert a new sheet in your spreadsheet (+ icon).
Rename Sheet1 by right-clicking and selecting Rename .
Revenue by Registration
Copy Registration Data
In the Line Items sheet, copy Column T (Registration).
Paste the values into Column A of the Revenue by Registration sheet.
Remove Duplicate Registrations
Select Column A .
In Excel, click:
Each Registration should now appear only once.
Add the Revenue Formula Label Column B: Net Revenue
In cell B2, enter:
=SUMIF('line-items'!T:T,A2,'line-items'!M:M)
Formula Breakdown
Reference Formula Part Description
'line-items'!T:T Criteria Range
Registration column in the Line Items sheet
A2 Criteria
Registration name in the Revenue by Registration sheet
'line-items'!M:M Sum Range
Net column in the Line Items sheet
Press Enter.
Drag the formula down Column B to calculate totals for all Registrations.
The Net Revenue column now shows total revenue by Registration.
00:03: Click "Sheet1" to open the
00:04: first sheet in your Google Sheets
00:05: document.
00:05: Document
00:07: Click "Rename" to change the name of the selected sheet.
00:08: Fill "line-items" as the new name for the sheet to better reflect its content.
00:11: Click here to proceed with the next step in managing your sheets.
00:14: Click "Sheet4" to access another sheet within your Google Sheets document.
00:18: Document
00:19: Fill "Revenue by Registration" as the new sheet name to indicate its data focus.
00:24: Click "line-items" to return to the sheet containing your line item data.
00:28: Click here to continue with the current task in Google Sheets.
00:32: Click "Revenue by Registration" to access the sheet focused on revenue data.
00:37: Click here to proceed with the next spreadsheet operation.
00:40: Click here to advance to the next step in your spreadsheet tasks.
00:43: Click "Data" to access data-related options and tools in Google Sheets.
00:47: Click "Remove duplicates" to clean your data by eliminating repeated entries.
00:52: Click "Data has header row" to specify that your data includes a header row for
00:56: accurate processing.
00:57: Click "Remove duplicates" again to execute the duplicate removal process.
01:01: Click "OK" to finalize the removal of duplicate entries from your data.
01:02: Click here to proceed with the next step in managing your spreadsheet.
01:06: Fill "Net Revenue" to label the relevant column or field in your sheet.
01:09: Click here to continue with your spreadsheet operations.
01:12: Click here to advance to the next step in your spreadsheet management.
01:18: Click here to complete
01:19: the current task within your Google Sheets document.
01:23: You have successfully renamed sheets, managed data by removing duplicates, and
01:27: applied formulas to calculate revenue in Google Sheets.
01:30: These steps help organize your spreadsheet and ensure accurate data analysis for
01:35: your project.
[ Return to Top ]
Calculate Revenue by Add-On
Use this section to calculate total net revenue by Add-On.
Rename Sheet Rename the sheet by right-clicking and selecting Rename . line-items
Create a New Sheet
Insert a new sheet in your spreadsheet (+ icon).
Rename Sheet1 by right-clicking and selecting Rename .
Revenue by Add-On
Copy Add-On Data
In the Line Items sheet, copy Column S (Add-On).
Paste the values into Column A of the Registration by Revenue sheet.
Remove Duplicate Add-Ons
Select Column A.
In Excel, click:
Each Add-On should now appear only once.
Add the Revenue Formula Label Column B: Net Revenue
In cell B2, enter: =SUMIF('line-items'!S:S,A2,'line-items'!M:M)
Formula Breakdown
Reference Formula Part Description
'line-items'!S:S Criteria Range
Add-On column in the Line Items sheet
A2 Criteria
Current Add-On name
'line-items'!M:M Sum Range
Net column in the Line Items shee
Press Enter.
Drag the formula down Column B to calculate totals for all Add-Ons.
The Net Revenue column now shows total revenue by Add-On.
00:03: Click "Sheet1" to open the
00:04: first worksheet in your Google Sheets document.
00:06: Document.
00:06: Click "Rename" to change the name of the selected sheet.
00:09: Fill "line-items" as the new name for the sheet to organize your data clearly.
00:13: Click here to confirm the sheet renaming and apply the new name.
00:16: Click "Sheet5" to switch to another worksheet within the same Google
00:19: Sheets file.
00:20: Click "Rename" to modify the name of the current sheet for better identification.
00:24: Fill "Revenue by Add-On" as the new sheet name to categorize revenue data
00:27: effectively.
00:29: Click "line-items" to navigate back to the previously renamed worksheet.
00:32: Click here to open the context menu for additional options related to your
00:36: selection.
00:36: Click here to access the edit menu for data manipulation.
00:40: Click "Revenue by Add-On" to switch to the sheet
00:42: containing revenue details.
00:44: Click here to access special paste functions for your data.
00:47: Click "Data" to access data-related tools and options in Google Sheets.
00:51: Click "Remove duplicates" to clean your data by eliminating repeated entries.
00:57: Click "Data has header row" to ensure the first row is excluded from duplicate
01:01: removal.
01:01: Click "Remove duplicates" to proceed with deleting duplicate rows from your data.
01:05: Click
01:05: "OK" to confirm that duplicates
01:07: have been successfully removed.
01:08: Click here to select the data range for further processing.
01:14: Go here to move to the specific area where you want to apply changes or review data.
01:19: Click here to access editing options for your data.
01:22: Click here to complete the current operation and apply all changes.
01:27: You have successfully renamed sheets, removed duplicate entries, and applied
01:32: formulas to calculate revenue in Google Sheets.
01:34: These steps help organize your data and ensure accurate financial analysis for
01:39: your project.
[ Return to Top ]
Calculate Revenue by GL Code
Use this section to calculate total net revenue by GL Code.
Pro Tip : Custom GL Codes can make financial reporting easier by grouping related Registrations and Add-Ons together.
Rename Sheet Rename the sheet by right-clicking and selecting Rename . line-items
Create a New Sheet
Insert a new sheet in your spreadsheet (+ icon).
Rename Sheet1 by right-clicking and selecting Rename .
Revenue by GL Code
Copy GL Code Data
In the Line Items sheet, copy Column U (GL Code).
Paste the values into Column A of the Revenue by GL Code sheet. Remove Duplicate GL Codes
Select Column A.
In Excel, click:
Each GL Code should now appear only once.
Add the Revenue Formula Label Column B: Net Revenue
In cell B2, enter:
=SUMIF('line-items'!U:U,A2,'line-items'!M:M)
Formula Breakdown
Formula Part Formula Part Description
'line-items'!U:U Criteria Range
GL Code column in the Line Items sheet
A2 Criteria
GL Code in the Revenue by GL Code sheet
'line-items'!M:M Sum Range
Net column in the Line Items sheet
Press Enter.
Drag the formula down Column B to calculate totals for all GL Codes.
The Net Revenue column now shows total revenue by GL Code.
00:03: Click "Sheet1" to open the
00:04: first sheet in your Google Sheets
00:05: document.
00:06: Document
00:06: Click "Rename" to change the name of the selected sheet.
00:09: Fill "line-items" as the new name for the sheet to better reflect its content.
00:11: Click here to move to the next sheet in your document.
00:15: Click "Sheet7" to open the seventh sheet in your Google Sheets document.
00:18: Document
00:18: Click "Rename" to change the name of the selected sheet.
00:22: Fill "Revenue by GL Code" as the new name to clearly identify the sheet's data.
00:27: Click "line-items" to access the sheet containing detailed line item data.
00:30: Click here to access the copy command from the context menu.
00:34: Click "Revenue by GL Code" to switch to the sheet where data will be pasted.
00:38: Click here again to confirm
00:39: paste options are visible.
00:42: Click here to access more options related to the pasted data.
00:46: Click "Data" in the menu bar to access data management features.
00:49: Click "Remove duplicates" to clean your data by eliminating repeated entries.
00:54: Click "Data has header row" to ensure the first row is excluded from duplicate
00:58: removal.
00:58: Click "Remove duplicates" to proceed with cleaning the data.
01:01: Click "OK" to finalize the removal of duplicate entries from your data.
01:05: Click here to select the field where you will enter new data.
01:08: Fill "Net Revenue" to label the data column appropriately for financial tracking.
01:12: Click here to select the cell where you will input a formula.
01:14: Click here to select the cell for pasting or editing data.
01:19: Go here to access the area where data manipulation will occur.
01:23: Click here to open the context menu for additional data options.
01:27: Click here again to finalize your data management
01:29: actions.
01:30: You have successfully managed and renamed sheets in Google Sheets, copied and pasted
01:35: data, and removed duplicates to organize your spreadsheet
01:38: effectively. Next, you can apply formulas and further analyze your data for better
01:44: insights.
[ Return to Top ]
Optional: Use a Pivot Table Instead
Advanced Excel users may prefer using a Pivot Table to summarize:
Revenue by Registration
Revenue by Add-On
Revenue by GL Code
Pivot Tables can simplify reporting and reduce manual formula setup for recurring reports.
[ Return to Top ]