PSI Bill Reconciliation - Revised for CSC

CSC - Reconciliation of PSI – Revised for CSC

C:\Users\wboheim\OneDrive - ASWB\EServ Mgmt - General\PSI Billing\2024

• Open Candidate Activity Detail spreadsheet from folder

• Create and Save a copy to reonciliation folder and add WORKING COPY to the file name

Open

File Name = ASWB_Standard Client Financials - 9 - Candidate Activity - Detail - Working Copy

Delete all columns except: CustomerName, Date (this is the exam date), Status, CandidateGrade, VendorFees, Registration Source, ClientAuthorizationID

• Select and Copy All data from 1^st Tab (Standard Client Financials – 9) to Tab 2

• On Tab 2 - Highlight all No Shows and cut and paste into Tab 3. Name Tab 3 “No Shows”.

  • reference Tab names at the bottom of page 3 (circled in red)

• Sort Sheet 2 (CopyAll) by Vendor Fees and then by name

• Open Access and Run Monthly Exam Rec Query

• Set ExamDate range for prior month

• Sort PassFail column Largest to Smallest and look for any Bad Records (4) or Invalids (5) – make note if there are any –

• Copy the ACTNo column and paste in the (CopyAll Tab) next to column named ClientAthorizationID

Open

• Run a conditional comparison against the Financials reported by PV and check the database to determine why one may appear in Pearson Vue Financials report and not in Access.

• Under Conditional formatting, select the first one “Highlight Cells Rules”, then the last one “Duplicate Values”

  • Reference on page 6

• Go to Management / Reports and Lookup / Revenue Reports / Accounting Reports Select NoShow Report – set Date Range – check send to excel

• Some may appear from last day of prior month that were not captured on previous invoice.

• Copy and paste the Auth No and Name to the No Show Tab next to the “ClientAuthorizationID” from Pearson Vue

• Select Excused Report and Run with same settings and check send to excel

• Some may appear from last day of prior month that were not captured on previous invoice.

• Copy and paste the Auth No and Name to the No Show Tab under the No Shows

• After copying and pasting both the NoShow and Excused Reports - run a conditional comparison against the NoShows reported by PV and check the database to determine why one may appear in one of the NoShow reports and not the other.

• Under Conditional formatting, select the first one “Highlight Cells Rules”, then the last one “Duplicate Values”.

• From Tab 2 (CopyAll) you will copy and paste this data to the last tab and Name it Sums.

  • (All data except No Shows)

• Highlight and sort all data by Vendor Fees, except the ones that have Parenthesis around the vendor fees.

• Insert a row after each like amt. such as; 106.30, 111.67, etc

• Autosum each like amt.

• Add each subtotal and put Total at the bottom.

• Then check numbers at each fee and compare to PV incoice details QUANTITY. Change sort to Largest to Smallest

Now go back and check all differences on the CopyAll Tab and the No Show Tab. After determining why the Authorizatio Number appeared in one list and not the other – make note if credit or charge is needed from PV (ex: in this case it was a Bad Record that was not updated in our system)

***Example: This candidate appears on the Pearson VUE details, but not the Access list. After looking that Auth No up in the Registration Database, I saw it was coded a Bad record and should be given a free exam. This would need to go on the Shared Pearson Vue Requested credits document and look for a credit next month.

• We note all differences that need adjustment on the spreadsheet to be made on the next bill.

• This spreadsheet is a shared file on OneDrive (2022 PV Bill Requested Credits)

After everything is done, go to the Sums Tab. Highlight a few rows from each like amount and print. 2-3 pages.

Add those pages to the Invoices that you printed and staple them all together. File away in Felicia’s office.

*Make sure you have slected Print Gridlines