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.

  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:

  • Enter or select the Company (school code) that you are interested in viewing.
  • Enter or select the relevant Coverage Year.
  • Enter or select the relevant Coverage Month.
  • Optional: At this time you may provide an Empl ID, if a specific employee’s data is desired for that Year/Month combination.
  1. Select the Search button.

The data displayed will be on three tabs:

  • Billing Details
  • Billing Summary
  • Discrepancies
  1. Select the Billing Summary tab.
  2. Enter the HCA Billing Information Balance Forward amount in the corresponding HCA Balance Forward cell on the spreadsheet.
  3. Enter the Total Amount Due This Period in the corresponding month Invoicing cell on the spreadsheet.
  4. Run the QHC_BA_HCA_PAYMENTS query to Excel.
  5. When balancing for the month of June, for example, in the From Pay Period End Date use the 05B end date.
  6. In the To Pay Period End Date use the 06A end date.
  7. The payment amount will be input as a negative in the appropriate payroll EFT cell.
  8. Enter the amount paid to the Healthcare Authority in the EFT cells (pink highlighted cells).
  9. Select the Discrepancies tab and download the page to Excel.
  10. Open the Excel spreadsheet and highlight the two Differences columns.
  11. Add details to the HCA Balancing Spreadsheet.
  12. Enter the employees from the Discrepancies spreadsheet .
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. Example below:

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.