9.2 HCA Benefit Data Balancing

Purpose:  Use this document as a reference for reconciling and balancing Healthcare Authority (HCA) Benefits Data in ctcLink.  

Audience:  Benefits Administrator.

You must have at least one of these local college managed security roles:

  • ZD HCA Reconciliation

If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.

HCA Benefits Data Balancing

  1. Open and Save a copy of the reconciliation spreadsheet.

NOTE: This is the same reconciliation spreadsheet that is included in the Balance W-2 Worksheet. (Many colleges used this spreadsheet while balancing in Legacy.)

If you do not have the worksheet template, you can select the link here: HCA Reconciliation Worksheet_Sample.xlsx.

Example spreadsheet used in Legacy for reconciling and balancing activities.
  1. In ctcLink, follow this navigation:

Navigation:  Benefits > CTC Custom > HCA Benefits Data

  1. The HCA Benefits Data search page displays.

The search page has three (3) required fields:

  1. Enter or select the Company (school code) that you are interested in viewing.
  2. Enter or select the relevant Coverage Year.
  3. Enter or select the relevant Coverage Month.
  4. Optional: At this time you may provide an Empl ID, if a specific employee’s data is desired for that Year/Month combination.
  5. Select the Search button.
HCA Benefits Data Find an Existing Value tab to enter Company, Coverage YUear and Coverage Month and optionally, Employee ID.

The data displayed will be on three tabs:

  • Billing Details
  • Billing Summary
  • Discrepancies
  1. Select the Billing Summary tab.
HCA Benefits Data - Billing Summary tab highlighting Balance Forward and Total Amount Due this Period rows.
  1. Enter the HCA Billing Information Balance Forward amount in the corresponding HCA Balance Forward cell on the spreadsheet.
  2. Enter the Total Amount Due This Period in the corresponding month Invoicing cell on the spreadsheet.
Example balancing spreadsheet highlighting the cells for the amount of Balance Forward and June Invoicing amounts.
  1. Run the QHC_BA_HCA_PAYMENTS query to Excel.
  2. When balancing for the month of June, for example, in the From Pay Period End Date use the 05B end date.
  3. In the To Pay Period End Date use the 06A end date.
  4. The payment amount will be input as a negative in the appropriate payroll EFT cell.


HCA Vendor Payments query parameter page highlighting Company, From Pay Period End Date 05B date, To Pay Period End Date 06A date.
Example query results with Row Labels, 31-May column, 15-Jun column and Grand Total column.
  1. Enter the amount paid to the Healthcare Authority in the EFT cells.   (Pink highlighted cells)
Example balancing spreadsheet highlighting the cells for the amount of 05B EFT and 06A EFT amounts.
  1. Select the Discrepancies tab and download the page to Excel.
HCA Benefits Data - Discrepancies tab highlighting Download to Excel icon.
  1. Open the Excel spreadsheet and highlight the two Difference columns.
HCA Benefit Data spreadsheet highlightinh the two Difference columns.
  1. Add details to the HCA Balancing Spreadsheet.
  2. Enter the employees from the Discrepancies spreadsheet created in Step 18.
Example balancing spreadsheet including adding the amounts in the Differences columns from the Discrepancies tab.
  1. Compare the amount carried forward from the previous month and see if any of the discrepancies clear.  
    If it does, remove it from the carried forward list.
    If it does not, add the discrepancy to the carried forward list.  
  2. To get the new carry forward amount, take the amount carried forward from the previous month and add the discrepancies for the current month.

May carry forward (60,612.23)

June discrepancies 349.00

New carry forward (60,263.23)

Example of the spreadsheet showing Previous Month Balance Forwad and Next Month Balance Forward.

Important Reminders!

  • The final billing from HCA is received around the 16th of each month, however, the discrepancy page will not be accurate until after the A payroll is confirmed.
  • The Discrepancies page is comparing HCA billing to what was actually paid to the Health Care Authority each payroll. It does not capture employee deductions taken and not sent to accounts payable (payment voucher).

To find employees who had a deduction that was not sent to accounts payable run query QHC_BA_HCA_PREMIUMS - HCA Ded/Cont by EE + Prepay.

  • Pay close attention to the Period Covered column when adding employee data.

The procedure for Healthcare Balancing is complete.


Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.