Data Validation and Audit Reconciliation:
Data Validation and Audit Reconciliation: Minimizing your AAP Risks and Preparing for an Audit Rosemary Cox, SPHR, SHRM-CHP, SR-CAAP Principal Business Consultant
Data is the new science. Big Data holds the answers.
Are you asking the right questions? – Pat Gelsinger, EMC
Data are just summaries of thousands of stories – tell a few of those stories to help make the data meaningful. PeopleFluent Proprietary and Confidential
It’s All About Telling Your Story
Agenda • Reviewing your data BEFORE you receive an audit notice • Data files • Data inconsistencies • Preparing for the audit submission • Best practices
PeopleFluent Proprietary and Confidential
It starts with your AAP at plan time BEFORE you receive the audit notice
PeopleFluent Proprietary and Confidential
When errors are not caught until an audit notice is received, what happens? Communication Requirements: • • •
Communicate plan results to Executives Communicate placement goals, benchmarks & utilization goals to management Train managers annually on EEO/AA responsibilities
If your original plans are not accurate and you find yourself re-doing the plan at audit time: Goals may change entirely - Managers would have been working on original goals. If interviewed by auditor and goals are now different, violations may occur. Adverse Impact changes and/or occurs with changes to data - If asked when adverse impact (AI) was found and what was the outcome of the research, you will have to answer honestly and say at audit time, which may cause a violation. PeopleFluent Proprietary and Confidential
Affirmative Action
PeopleFluent Proprietary and Confidential
What data is needed? • Employee File • Static snapshot as of your AA plan date • Hire File • Information on anyone hired during preceding year • Promotion File • Information on anyone promoted during the preceding plan year – need promotion TO and FROM information • Transfer File • Information on anyone who transferred during the preceding plan year – need transfer TO and FROM information • Termination File • Information on anyone who terminated during the preceding plan year • Applicant File • All qualified applicant pools associated with each hire in the Hire File for the preceding year. PeopleFluent Proprietary and Confidential
Employee File
PeopleFluent Proprietary and Confidential
Employee file feeds the following reports: Work Force
Incumbency
Job Group
Employee File
Vets/Disabled Utilization
Census
Compensation
PeopleFluent Proprietary and Confidential
Employee File Data Validation
Missing race/gender Job Groups - are job titles in the correct job groups Mixed EEO/Job Groups Same Job Titles different Job Groups Disabled Status - Yes (Y) No (N) Unknown (U) Veteran Status - Yes (Y) No (N) Unknown (U) AAP Location - check for missing/incorrect AAP Annual Compensation - check for missing, is everything annualized (part time to full time equivalent) Missing departments Census Codes - any missing, are they accurate Does every employee have a manager ID & AAP on the file PeopleFluent Proprietary and Confidential
Employee Data Inconsistencies Same Titles Different Spelling
Mixed Compensation
Same Title Different EEO/Job Groups
Missing Pieces
PeopleFluent Proprietary and Confidential
History Files
PeopleFluent Proprietary and Confidential
History Files Feed: Goal Attainment
Data Collection
Scorecards
History File
Count Reports
Impact Ratio Analysis
PeopleFluent Proprietary and Confidential
History File Data Validation Hires, Promotions, Transfers & Terminations
Note: for all promotions and transfers there must be a “TO” and “FROM” side of the transaction. Validate same information as Employee file plus: Demotions – check for movement downward in EEO category for transactions coded as PR / TR. Promotion Confirmation – does the transaction meet the company definition of a promotion? Are the TO and FROM side the same? If yes, is that accurate? Duplicate Transactions – check for multiples of the same transaction on the same date. Termination Reason – check for Voluntary (v) Involuntary (i), validate against decode table. PeopleFluent Proprietary and Confidential
History Data Inconsistencies Same To and From Job Title
Missing Termination Reasons
Two Termination Rows, No Rehire Row
Promoted after Termination, no rehire record
PeopleFluent Proprietary and Confidential
Applicant File PeopleFluent Proprietary and Confidential
Applicant File Feeds: Hire Impact Ratio Analysis
Hire Counts
Data Collection
Monitoring Scorecards
PeopleFluent Proprietary and Confidential
Applicant File Data Validation
Disabled Status – check for Yes (Y) No (N) Unknown (U) Veteran Status - check for Yes (Y) No (N) Unknown (U) EEO Code – validate Job Group – validate against decode table or last run AAP Applicant ID – check for missing Disposition – check for missing, validate against definition of applicant Requisition – did requisition result in a hire, did requisition result in multiple hires Requisition with no hires should be removed Requisitions with only internal hires should be removed Basic match of Applicant Name into Applicant Log – where match, and disposition = hired, the information from the hire record trumps the applicant record (Race, Gender, AAP, Job Group) PeopleFluent Proprietary and Confidential
Applicant Data Inconsistencies Hires with Unknown Race/Gender
Non-Applicant Disposition Reasons
One Requisition Multiple Job Groups
One Requisition – Multiple AAPs
PeopleFluent Proprietary and Confidential
AUDIT PREPARATION
PeopleFluent Proprietary and Confidential
The following reports are due in the event of an audit: • 3 Narratives • Work Force Analysis • Work Force Analysis Include/Exclude • Job Group Analysis • Job Group Analysis Include/Exclude • Incumbency v Availability • Placement Goals • Compensation Item 19
• • • • •
Goal Attainment Hire, Promotion and Termination Counts Veteran Monitoring Data Collection Utilization Analysis - Disabled Reports reflect current plan year and end-of-prior year unless audit notice comes more than 6 months into your current plan year, then midyear reports would also be required
All of these reports must be accurate and reconciled with prior year. PeopleFluent Proprietary and Confidential
Preparing data for an Audit • • • • •
Are correct people in the AAP? Are includes/excludes appropriate? Are manager/supervisors notated? Is compensation data complete? Do you have adverse impact in any area? » Can you produce documentation? » Are disposition codes correct? » Have you performed a step analysis?
• Are transactions correct (hires, promotions, transfers, terminations)? • Do the prior year totals – plus and minus the transactions – accurately reflect the current year totals?
PeopleFluent Proprietary and Confidential
Reconciling/Audit Prep • • • • •
Are the Hires accounted for in the employee file? Have terms been removed from the employee file? Is every hire in the applicant file? Are there hires in the applicant file that are not in the hire file? Are there instances of duplicate transactions with no corresponding action– 2 termination rows but no rehire row, for example?
•
•
•
Are there requisitions with hires that cross AAP’s – has the pool been duplicated? Are there people in prior employee file in 1 plan but in another plan for current employee file? Can you account for demotions, laterals and other movement?
PeopleFluent Proprietary and Confidential
Advanced Data Inconsistencies In Prior Employee file – Not in current – No term Row
In plan 10 prior year – in plan 20 current – no history record
In previous and current employee file – term row in history with no rehire row
In plan 10 prior and current employee file – termed from plan 12 and no rehire row
PeopleFluent Proprietary and Confidential
Compare prior year to current year, correct errors or ensure you can explain discrepancies
Comparing 2015 to 2014 • • • • • • • • • •
1A +1 1B +2 1D – 3 2B +2 2E +4 5A +2 5B – 2 6A +4 7B +3 8A +23
Should find history transactions to account for each difference. PeopleFluent Proprietary and Confidential
Here is the 1A hire to account for difference
Here are 2 hires in 1B to account for difference
But…in 5B there are 3 terms, not 2 – how do we explain the difference?
PeopleFluent Proprietary and Confidential
Check Include/Excludes to make sure they are correct
PeopleFluent Proprietary and Confidential
Other Items to check
PeopleFluent Proprietary and Confidential
Item #19 Compensation This is high level, we will not be deep-diving into compensation concerns during this course. We do offer compensation courses at PeopleFluent.
Make sure Comp data contains same groups as your AAP Job Group Analysis Check for mismatched job titles Check for Mixed columns Check for anomalies – high merit, low merit, high bonus, low bonus, etc. Add data that helps explain variances – location, dept, shift, time in position, etc. Sort by title, then salary, look for potential disparities If unsure of accuracy or you see issues in your comp data, consider get an expert involved. PeopleFluent Proprietary and Confidential
Best Practices • • • • • • • • • •
Review data at time of AAP, do not wait until audit notice is received – Reconcile prior year with current and review transactions Communicate placement goals, Utilization goals and hiring benchmark to anyone who makes a decision about another person Monitor goal progress throughout the year Research all adverse impact Analyze compensation annually Review internal processes and ensure they are followed Document all employment decisions (applicant dispositions, hires, promotions, terminations, compensation, etc.) Log all outreach and recruiting efforts List all jobs with the local or state workforce agency (3 exceptions) Review bulletin boards and tour facilities PeopleFluent Proprietary and Confidential
Questions
PeopleFluent Proprietary and Confidential
Thank you! Questions/Comments/Need Help? [email protected]
Data is the new science. Big Data holds the answers.
Are you asking the right questions? – Pat Gelsinger, EMC
Data are just summaries of thousands of stories – tell a few of those stories to help make the data meaningful. PeopleFluent Proprietary and Confidential
It’s All About Telling Your Story
Agenda • Reviewing your data BEFORE you receive an audit notice • Data files • Data inconsistencies • Preparing for the audit submission • Best practices
PeopleFluent Proprietary and Confidential
It starts with your AAP at plan time BEFORE you receive the audit notice
PeopleFluent Proprietary and Confidential
When errors are not caught until an audit notice is received, what happens? Communication Requirements: • • •
Communicate plan results to Executives Communicate placement goals, benchmarks & utilization goals to management Train managers annually on EEO/AA responsibilities
If your original plans are not accurate and you find yourself re-doing the plan at audit time: Goals may change entirely - Managers would have been working on original goals. If interviewed by auditor and goals are now different, violations may occur. Adverse Impact changes and/or occurs with changes to data - If asked when adverse impact (AI) was found and what was the outcome of the research, you will have to answer honestly and say at audit time, which may cause a violation. PeopleFluent Proprietary and Confidential
Affirmative Action
PeopleFluent Proprietary and Confidential
What data is needed? • Employee File • Static snapshot as of your AA plan date • Hire File • Information on anyone hired during preceding year • Promotion File • Information on anyone promoted during the preceding plan year – need promotion TO and FROM information • Transfer File • Information on anyone who transferred during the preceding plan year – need transfer TO and FROM information • Termination File • Information on anyone who terminated during the preceding plan year • Applicant File • All qualified applicant pools associated with each hire in the Hire File for the preceding year. PeopleFluent Proprietary and Confidential
Employee File
PeopleFluent Proprietary and Confidential
Employee file feeds the following reports: Work Force
Incumbency
Job Group
Employee File
Vets/Disabled Utilization
Census
Compensation
PeopleFluent Proprietary and Confidential
Employee File Data Validation
Missing race/gender Job Groups - are job titles in the correct job groups Mixed EEO/Job Groups Same Job Titles different Job Groups Disabled Status - Yes (Y) No (N) Unknown (U) Veteran Status - Yes (Y) No (N) Unknown (U) AAP Location - check for missing/incorrect AAP Annual Compensation - check for missing, is everything annualized (part time to full time equivalent) Missing departments Census Codes - any missing, are they accurate Does every employee have a manager ID & AAP on the file PeopleFluent Proprietary and Confidential
Employee Data Inconsistencies Same Titles Different Spelling
Mixed Compensation
Same Title Different EEO/Job Groups
Missing Pieces
PeopleFluent Proprietary and Confidential
History Files
PeopleFluent Proprietary and Confidential
History Files Feed: Goal Attainment
Data Collection
Scorecards
History File
Count Reports
Impact Ratio Analysis
PeopleFluent Proprietary and Confidential
History File Data Validation Hires, Promotions, Transfers & Terminations
Note: for all promotions and transfers there must be a “TO” and “FROM” side of the transaction. Validate same information as Employee file plus: Demotions – check for movement downward in EEO category for transactions coded as PR / TR. Promotion Confirmation – does the transaction meet the company definition of a promotion? Are the TO and FROM side the same? If yes, is that accurate? Duplicate Transactions – check for multiples of the same transaction on the same date. Termination Reason – check for Voluntary (v) Involuntary (i), validate against decode table. PeopleFluent Proprietary and Confidential
History Data Inconsistencies Same To and From Job Title
Missing Termination Reasons
Two Termination Rows, No Rehire Row
Promoted after Termination, no rehire record
PeopleFluent Proprietary and Confidential
Applicant File PeopleFluent Proprietary and Confidential
Applicant File Feeds: Hire Impact Ratio Analysis
Hire Counts
Data Collection
Monitoring Scorecards
PeopleFluent Proprietary and Confidential
Applicant File Data Validation
Disabled Status – check for Yes (Y) No (N) Unknown (U) Veteran Status - check for Yes (Y) No (N) Unknown (U) EEO Code – validate Job Group – validate against decode table or last run AAP Applicant ID – check for missing Disposition – check for missing, validate against definition of applicant Requisition – did requisition result in a hire, did requisition result in multiple hires Requisition with no hires should be removed Requisitions with only internal hires should be removed Basic match of Applicant Name into Applicant Log – where match, and disposition = hired, the information from the hire record trumps the applicant record (Race, Gender, AAP, Job Group) PeopleFluent Proprietary and Confidential
Applicant Data Inconsistencies Hires with Unknown Race/Gender
Non-Applicant Disposition Reasons
One Requisition Multiple Job Groups
One Requisition – Multiple AAPs
PeopleFluent Proprietary and Confidential
AUDIT PREPARATION
PeopleFluent Proprietary and Confidential
The following reports are due in the event of an audit: • 3 Narratives • Work Force Analysis • Work Force Analysis Include/Exclude • Job Group Analysis • Job Group Analysis Include/Exclude • Incumbency v Availability • Placement Goals • Compensation Item 19
• • • • •
Goal Attainment Hire, Promotion and Termination Counts Veteran Monitoring Data Collection Utilization Analysis - Disabled Reports reflect current plan year and end-of-prior year unless audit notice comes more than 6 months into your current plan year, then midyear reports would also be required
All of these reports must be accurate and reconciled with prior year. PeopleFluent Proprietary and Confidential
Preparing data for an Audit • • • • •
Are correct people in the AAP? Are includes/excludes appropriate? Are manager/supervisors notated? Is compensation data complete? Do you have adverse impact in any area? » Can you produce documentation? » Are disposition codes correct? » Have you performed a step analysis?
• Are transactions correct (hires, promotions, transfers, terminations)? • Do the prior year totals – plus and minus the transactions – accurately reflect the current year totals?
PeopleFluent Proprietary and Confidential
Reconciling/Audit Prep • • • • •
Are the Hires accounted for in the employee file? Have terms been removed from the employee file? Is every hire in the applicant file? Are there hires in the applicant file that are not in the hire file? Are there instances of duplicate transactions with no corresponding action– 2 termination rows but no rehire row, for example?
•
•
•
Are there requisitions with hires that cross AAP’s – has the pool been duplicated? Are there people in prior employee file in 1 plan but in another plan for current employee file? Can you account for demotions, laterals and other movement?
PeopleFluent Proprietary and Confidential
Advanced Data Inconsistencies In Prior Employee file – Not in current – No term Row
In plan 10 prior year – in plan 20 current – no history record
In previous and current employee file – term row in history with no rehire row
In plan 10 prior and current employee file – termed from plan 12 and no rehire row
PeopleFluent Proprietary and Confidential
Compare prior year to current year, correct errors or ensure you can explain discrepancies
Comparing 2015 to 2014 • • • • • • • • • •
1A +1 1B +2 1D – 3 2B +2 2E +4 5A +2 5B – 2 6A +4 7B +3 8A +23
Should find history transactions to account for each difference. PeopleFluent Proprietary and Confidential
Here is the 1A hire to account for difference
Here are 2 hires in 1B to account for difference
But…in 5B there are 3 terms, not 2 – how do we explain the difference?
PeopleFluent Proprietary and Confidential
Check Include/Excludes to make sure they are correct
PeopleFluent Proprietary and Confidential
Other Items to check
PeopleFluent Proprietary and Confidential
Item #19 Compensation This is high level, we will not be deep-diving into compensation concerns during this course. We do offer compensation courses at PeopleFluent.
Make sure Comp data contains same groups as your AAP Job Group Analysis Check for mismatched job titles Check for Mixed columns Check for anomalies – high merit, low merit, high bonus, low bonus, etc. Add data that helps explain variances – location, dept, shift, time in position, etc. Sort by title, then salary, look for potential disparities If unsure of accuracy or you see issues in your comp data, consider get an expert involved. PeopleFluent Proprietary and Confidential
Best Practices • • • • • • • • • •
Review data at time of AAP, do not wait until audit notice is received – Reconcile prior year with current and review transactions Communicate placement goals, Utilization goals and hiring benchmark to anyone who makes a decision about another person Monitor goal progress throughout the year Research all adverse impact Analyze compensation annually Review internal processes and ensure they are followed Document all employment decisions (applicant dispositions, hires, promotions, terminations, compensation, etc.) Log all outreach and recruiting efforts List all jobs with the local or state workforce agency (3 exceptions) Review bulletin boards and tour facilities PeopleFluent Proprietary and Confidential
Questions
PeopleFluent Proprietary and Confidential
Thank you! Questions/Comments/Need Help? [email protected]
