Predictive HR Analytics, Text Mining Organizational Network Analysis with Excel

1. 1Rewrite each descriptive demographic question as a predictive one before you touch data. The source pairs 'How long has John been with the company' with 'How long will John stay', and 'What is John's marital status, salary market-ratio, performance rating, traveling time to office?' with 'What variables affect John's tenure?'. Write the pair for your own population, e.g. 'Which Sales employee is likely to leave next quarter?'.
2. 2Pull the descriptive fields from the HRIS: age, tenure, marital status, gender, commute (traveling time to office), plus salary market-ratio and performance rating. The HRIS reports who your employees are, but it cannot find patterns or correlations in them. Pattern-finding is your job in Excel.
3. 3Fix the output variable first. A numeric outcome such as tenure calls for multiple regression. A categorical outcome such as leaves/stays calls for logistic regression, which the source says predicts the value of a categorical output.
4. 4Prepare each input by type. Age, tenure and commute time are numeric. Gender or marital status is a categorical variable with 2 or 3 categories. The source has separate regression examples for a 1-categorical input with 2 values and with 3 values. Code the categories as the examples do, and do not feed text labels to Solver or the regression tool.
5. 5Run a correlation matrix first. Correlation tells you whether a relationship exists and whether it is positive, negative or zero. Then run regression, which tells you which variables have the most impact on the dependent variable. Do not rank demographic drivers from the correlation matrix alone.
6. 6Add a qualitative layer for context. The source says focus-group quotes and similar descriptive data contextualize quantitative results. Use them to explain why a commute or tenure band matters rather than just that it does.
7. 7Check legal and stakeholder exposure before you publish. Age, gender and marital status are protected-type attributes in many settings. The source devotes a section to court rulings on analytics cases (Griggs v. Duke Power Co., 1971; Johnson v. Transportation Agency, 1987), plus stakeholder analysis of who is affected. Run both before any model touches decisions about people.
8. 8Tell the story in three acts: setup (why should I care), confrontation (how does this improve my life), resolution (what action do I take). Fit it to the audience. Top management wants it concise.

The source's own descriptive-to-predictive pair is the template. Descriptive: 'What is John's marital status, salary market-ratio, performance rating, traveling time to office?' Predictive: 'What variables affect John's tenure with the company?' To operationalize it, build one row per employee with tenure as the outcome and marital status, market-ratio, performance rating and commute time as inputs. The source supplies no dataset or results, so none are invented here. Marital status is a categorical input, so it is coded as in the source's 2-category regression example. Commute time and market-ratio enter as numeric inputs. Run the correlation matrix to see which inputs move with tenure, then multiple regression to see which carries the most impact. For a stay/leave version, such as 'Which Sales employee is likely to leave next quarter?', swap in logistic regression. The result is a variable ranking you can present as a story, not just a list of John's attributes.

• If the question is 'what is' (counts, averages, current marital status or age mix), stay descriptive and use the HRIS. If it is 'what is likely', build a model.
• If the outcome is a number such as tenure, use multiple regression. If it is a yes/no such as leave next quarter, use logistic regression.
• If a categorical demographic has 2 or 3 levels, use the matching categorical-input regression pattern. Do not treat the codes as a numeric scale.
• If correlation shows a relationship, then run regression to learn which variable matters most. Never claim a driver from correlation alone.
• If a demographic variable (age, gender, marital status) would feed a decision about individuals, then complete the stakeholder and case-law review first.

• Stopping at descriptive reporting. The tell: dashboards show age, tenure and gender splits but nothing says who is likely to leave or why.
• Entering a categorical variable as a plain number. The tell: a gender or marital-status coefficient that is meaningless or changes with arbitrary code values.
• Reading correlation as impact. The tell: the report ranks drivers from a correlation matrix without any regression.
• Showing data without narrative. The tell: different executives read the same demographic chart differently, because data carries no context on its own.
• Ignoring legal and stakeholder exposure on protected characteristics. The tell: no stakeholder list or legal review exists, yet the model output is used on named employees.

• ▸Descriptive question: write the current 'what is' question (e.g. How long has John been with the company?).
• ▸Predictive question: rewrite it as 'what is likely' (e.g. Which Sales employee is likely to leave next quarter?).
• ▸Output variable and type: name it and mark numeric (multiple regression) or categorical (logistic regression).
• ▸Numeric inputs: list age, tenure, commute/traveling time, salary market-ratio, performance rating, as available.
• ▸Categorical inputs: list gender, marital status etc., with the number of categories (2 or 3) and the coding used.
• ▸Correlation result: note which inputs show positive, negative or zero correlation with the output.
• ▸Regression result: name the inputs with the most impact on the output.
• ▸Qualitative context: record the focus-group or comment evidence that explains the finding.
• ▸Legal/stakeholder check: list who is affected and note review against the case-law section before use.

1. 1Define the outcome first. Pick one outcome (for example, staying at least six months, or a performance rating) before you pick any trait. Xerox's outcome was 'a call-center worker who won't quit before the company recovers its investment in training.'
2. 2Write a testable hypothesis in the passage's format: 'If {X}, {Y} will happen.' Example: 'If applicants score high on creativity, then they stay at least six months.' Share it with your sponsor and get agreement before you collect data.
3. 3Treat other companies' findings as hypotheses, not facts. The passages state that what works elsewhere may not work in your company. Test Xerox's finding (creative stays, inquisitive leaves) on your own population.
4. 4Select the traits to measure. Use the passage's named traits (conscientiousness, extraversion, agreeableness, creativity, grit) plus any that fit your role, such as inquisitiveness or empathy. Decide whether to use an existing assessment or collect new data, and set up the capture process if the data is not already recorded.
5. 5Collect trait scores through a structured test. Xerox used a 30-minute assessment with forced-choice statements, such as 'I ask more questions than most people do' versus 'People tend to trust what I say,' combined with job scenarios.
6. 6Add the non-personality factors the passages tie to attrition: commute time, reliable transportation, social network use, tenure, performance rating, and salary market-ratio. Without them you cannot tell whether personality or something else drives the result.
7. 7Test the relationship with the tools the source names. Use correlation to check each trait against the outcome. Use multiple regression for a performance rating (traits plus Social Network and Skillsets). Use logistic regression for stay/leave. Read the coefficients to see which traits matter and in which direction.
8. 8Convert the model into a simple score band, as Xerox did: red (low potential), yellow (medium), green (high). Decide in advance how you treat yellows (Xerox accepts some if it thinks it can train them). Run a trial before full rollout. Xerox's half-year trial cut attrition by 20 percent.

Xerox staffed call centers by hiring applicants with relevant call-center experience. An analytics algorithm showed that experience does not matter for top performers. Personality did: creative people usually stay at least six months, while inquisitive people usually do not. The ideal worker also lives near the job, has reliable transportation, and uses one or more social networks but not more than four. That worker tends not to be overly inquisitive or empathetic, but is creative. Applicants now take a 30-minute test that screens personality and runs job scenarios, then receive a red, yellow, or green score. Xerox mostly hires greens and accepts some yellows it can train. After a half-year trial that cut attrition by 20 percent, Xerox handed all call-center hiring to the software. The lesson: the trait that predicts retention (creativity) and the one that hurts it (inquisitiveness) are not what intuition suggests, so you measure them rather than assume.

• If the outcome is retention within a payback window (such as training cost recovery), then measure traits against stay/leave over that window and use logistic regression; if the outcome is a performance rating, then use multiple regression.
• If a trait looks like a virtue (inquisitiveness, empathy) but your data shows it predicts early exit in a given role, then follow your data; Xerox found inquisitive people usually do not stay six months.
• If a candidate lands in a middle band (yellow), then hire only when you believe training can close the gap, as Xerox does; default to the top band otherwise.
• If you borrow a finding from another company, then retest it on your own data before acting, because results vary by business.
• If personality alone gives a weak result, then add commute, transportation, tenure, and market-ratio to the model, since attrition is influenced by many factors beyond personality.

• Copying Xerox's profile into a different role or company without testing. Tell: the score bands do not separate stayers from leavers in your own data.
• Using personality as the only predictor. Tell: unexplained attrition remains high because commute, pay market-ratio, manager quality, or triggering events were never included.
• Assuming that more of a socially desirable trait is better. Tell: high-inquisitive or high-empathy hires leave early while you expected them to stay.
• Skipping a trial and going straight to full automation. Tell: no before/after attrition comparison exists, so you cannot show the 20 percent-style reduction.
• Vague hypotheses and undefined outcomes. Tell: you cannot state 'If X, then Y' in one sentence, and 'success' means different things to different stakeholders.

• ▸Outcome (Y): state the single measurable result, e.g. 'stays at least 6 months' or 'performance rating.'
• ▸Hypothesis: write it as 'If {trait/score profile}, then {Y} will happen.'
• ▸Source of hypothesis: note the study or company it came from and how your context might differ.
• ▸Traits measured: list each (e.g. conscientiousness, creativity, grit, inquisitiveness) and the assessment used.
• ▸Control factors: list commute time, transportation, tenure, market-ratio, and any others in the model.
• ▸Method: correlation, multiple regression, or logistic regression, and why.
• ▸Result per trait: record direction (+/-) and size of each coefficient or correlation.
• ▸Score bands: define red / yellow / green cut-offs and the rule for yellows.
• ▸Trial plan and result: record trial length, baseline attrition, trial attrition, and the change.

1. 1Collect the text first: pull Glassdoor reviews, engagement survey comments, and social posts into one Excel column, one comment per cell. Hundreds of comments are too tedious to read by hand, which is why you score them.
2. 2Install the free Microsoft add-in: Insert tab > Get Add-ins > search 'Azure Machine' > Add > open 'Text Sentiment Analysis'.
3. 3Click 'View Schema' and rename your column header (e.g. 'Glassdoor review') to the schema word 'tweet_text'. It is case sensitive. Close the schema, select the comment range (the passage's example uses A1:A9), and run it.
4. 4Read each output as a probability of how positive or negative the comment is. The add-in uses a dictionary of 5,097 negative and 2,533 positive words, each marked strong or weak. Map scores to polarity labels: positive, negative, neutral.
5. 5Know the rules-based logic underneath: count positive and negative dictionary words. More positive than negative returns positive, more negative returns negative, a tie returns neutral. Sample scores from the source: 'great colleagues' = positive 0.82; 'nasty bosses, long hours' = negative 0.42.
6. 6Pair sentiment with word frequency: count the most-mentioned words (VBA macro in Word, or a free online tool) and build a Word Cloud (Pro Word Cloud add-in) so you see what the polarity is about.
7. 7Read a sample of comments against their scores by hand. Look for context reversals, sarcasm, and comparisons, where the dictionary approach misfires.
8. 8Link sentiment to a business outcome: correlate Glassdoor company ratings with company attrition rate, then run multiple regression with rating alongside other drivers (unemployment, GDP growth, engagement score).

The source's regression example predicts a company's average employee attrition rate from sentiment and context. Inputs: unemployment rate 3%, GDP growth 2%, Glassdoor public sentiment rating 5, engagement score 7. Predicted output: average attrition rate 8%. To build it, score each company's Glassdoor reviews and take the rating. Add the macro variables and engagement score to a sheet with actual attrition per company. Run correlation first (Glassdoor rating vs attrition), then multiple regression in Analysis ToolPak. Plug in the four input values to get the 8% prediction. The sentiment variable is only one input, so the model shows its contribution beside the economic controls instead of claiming it alone drives attrition.

• If you have more than a handful of comments, then score them with the Azure add-in instead of reading them all; then spot-check a sample.
• If a comment's meaning depends on the question asked (e.g. 'Nothing!' to 'What did you dislike?'), then separate scoring by question or hand-review it, because the question flips polarity.
• If a comment is short, ironic, or agreeable-sounding ('Yeah, sure.'), then treat its score as unreliable and review it manually.
• If the comment is a comparison ('better than old tools'), then check what it is compared to before calling it positive; it may be neutral.
• If you want to claim sentiment predicts attrition, then run correlation first and include economic controls (unemployment, GDP growth) and engagement in the regression rather than sentiment alone.
• If your dataset is large or needs more sophistication, then move beyond Excel to R or Python.

• The add-in returns errors or empty output because the header is not exactly 'tweet_text' (case sensitive). The tell: no scores appear after running.
• Scoring all comments without context: survey answers to a 'dislike' question get misclassified. The tell: an obviously negative theme scores positive.
• Sarcasm and irony counted at face value. The tell: 'Yeah, sure.' lands positive or neutral while the writer is clearly unhappy.
• Treating comparisons as positive by default. The tell: 'better than old tools' is flagged positive although the old tools were useless.
• Reporting sentiment-to-attrition results without the story or controls. The tell: executives ask why attrition moves and you present only a score, with no narrative or accompanying variables.

• ▸Text source: Glassdoor, survey, or social post, plus date range collected.
• ▸Question context: the exact survey question the comment answers (flags polarity flips).
• ▸Comment text (column header set to 'tweet_text'): one comment per cell.
• ▸Azure sentiment score: probability output for each comment.
• ▸Polarity label: positive, negative, or neutral from the score.
• ▸Manual check flag: mark sarcasm, comparison, or question-reversal cases and your corrected label.
• ▸Top frequent words: top words from the word count / Word Cloud, with sentiment direction.
• ▸Outcome link: Glassdoor rating, attrition rate, and controls (unemployment, GDP growth, engagement score) per company.
• ▸Result and narrative: correlation/regression output and one-sentence story for executives.

1. 1Write the question as a hypothesis before touching data (ARHAT steps 1-3: ask, review literature, hypothesize). Example: 'High performers paid below market and sitting at the network periphery resign more.' The definition names five input families: pay relative to market, performance, demographics, network position, behavioral signs. State which direction you expect for each.
2. 2Define the outcome as a voluntary resignation flag (1 = resigned, 0 = stayed) over a fixed window. Keep involuntary exits out of the data. The model estimates the probability of voluntary resignation, so mixed exits contaminate it.
3. 3Build the pay variables from Compensation & Benefits analytics: market-ratio (pay vs. market) and compa-ratio (pay vs. the internal range). Use the book's Flight Risk Formula as a reference point. The passages do not reproduce it, so treat any formula you build as your own hypothesis to test.
4. 4Add network position if you can collect it. Classify each person as central node, knowledge broker or peripheral, using an active ONA survey (point-in-time) or passive sources such as email (continuous). Peripherals are described as flight risks, and a talented peripheral is a risk to the organization. Georgetown research cited in the references says internal social networks can predict turnover. Passive data needs clear communication on what data, what purpose, what benefit, governance and opt-in/out.
5. 5Test each input against resignation with correlation first (Excel Data Analysis ToolPak), as in the book's 'Correlation: Predict Employee Flight Risk'. Keep the candidates that show a relationship in the expected direction.
6. 6Fit a logistic regression, since the output is categorical (resigned or not). Use Excel Solver and work through the Solver error fixes the book covers. Alternatively, start with a probability tree to see how the conditions combine. Each employee's output is a probability of resignation.
7. 7Compare your drivers with documented criteria. The book lists cases: AIHR turnover predictors, FlightNetwork 'At-Risk' criteria, Nielsen retention analytics, iNostix flight risk scoring, and IBM Kenexa Talent Insights. Use them to sanity-check which variables you include.
8. 8Tell the story for the audience (ARHAT step 5): who is at risk, which drivers, what intervention. Lead with the finding for executives and keep the statistics in the appendix.

The passages give no resignation data or coefficients, so this walk-through is illustrative. It reuses their ONA categories and their rule that talented peripherals are flight risks. Take three employees in one team. A is a central node, rated high performing, paid at market. B is a peripheral, rated high performing, paid below market. C is a peripheral, rated average, paid at market. After your logistic regression runs, B should receive the highest resignation probability because three hypothesized drivers stack: low pay vs. market, high performance, and peripheral position. The source says the combination makes a talented peripheral a risk to the organization. A's centrality should lower the score even with high performance. C is peripheral but not talented, so the organizational loss is smaller. Rank by probability, then check whether B's pay gap and isolation are fixable. Possible actions are a market adjustment (market-ratio) and connecting B to groups (ONA intervention). A Nedbank talent analyst reports running a first Predictive Employee Flight Risk model within a week using this method, so a first pass in Excel is feasible.

• If a person is peripheral in the network and rated a high performer, then escalate them for a retention conversation first. The source treats talented peripherals as an organizational risk.
• If market-ratio or compa-ratio shows pay below market or range, then treat pay as a candidate driver. Check it in correlation before adding it to the logistic regression.
• If the outcome is resigned or stayed, then use logistic regression, not multiple (linear) regression. Use Solver and fix its error messages rather than falling back to linear.
• If your data is only an ONA survey, then treat the network position as a snapshot that can go stale. Re-survey, or move to passive sources with opt-in governance.
• If a correlation or regression result contradicts your hypothesis direction, then re-check the data and variable definitions before building a story around it.

• Mixing involuntary exits into the 'resigned' flag. Tell: the model's top drivers look like restructuring or dismissal patterns, not pay, performance or network.
• Scoring on pay alone because it is easy to get. Tell: the flagged list is just the lowest-paid people, with no performance or network differentiation, and talented peripherals are missed.
• Using point-in-time survey ONA as if it were current. Tell: scores for people who changed teams or projects since the survey no longer match reality.
• Collecting passive data such as email without communication, purpose or opt-in/out. Tell: employees learn about monitoring after the fact, and trust and project legitimacy collapse.
• Reporting probabilities without a narrative or action. Tell: executives ask 'so what?' The book stresses a structured story: audience, narrative, visuals.

• ▸Employee ID: use an anonymized key, not a name.
• ▸Voluntary resignation in window (1/0): exclude involuntary exits from the dataset.
• ▸Market-ratio: pay divided by market pay for the role, from Compensation & Benefits analytics.
• ▸Compa-ratio: pay relative to the internal range midpoint.
• ▸Performance rating: use the same scale for everyone.
• ▸Network role (central / knowledge broker / peripheral): from active or passive ONA.
• ▸Demographic variables used: list only those approved by your governance and tested as hypotheses.
• ▸Behavioral signs: note any observable signs you can legitimately record, with source.
• ▸Model probability of resignation: output from your logistic regression.
• ▸Recommended action and owner: pay review, network connection, or retention conversation, with a date.

1. 1Define both outcomes with the book's formulas before modeling. Absenteeism Rate = Sick Days / Permanent FTE (work days missed due to illness). 1st Year Resignation Rate = Permanent Resignations within 0-1 Year of Service / Permanent Headcount within 0-1 Year of Service. Use permanent headcount in every denominator so the figures are comparable across periods.
2. 2Split attrition by tenure and by department. The book treats a higher first-year resignation rate than the rate for longer-serving staff as a signal of a larger issue: cultural misfit, job-description mismatch, overselling by the recruiter, or poor onboarding. A single company-wide number hides this.
3. 3Read Vacancy Rate (Permanent Vacant Positions / Permanent Headcount) next to attrition. The book says a high share of open positions can indicate high attrition, so check whether vacancies track resignations before you blame sourcing.
4. 4Build the candidate predictor list from the book's own linkages: Overtime per Individual Contributor Headcount (Overtime Hours / Permanent Individual Contributor Headcount), Labour Cost per FTE (too low may cause turnover), Benefits as Percentage of Labour Costs (low versus benchmark may bring higher absenteeism and sick leave), and engagement and Glassdoor sentiment scores. Add external context such as unemployment rate and GDP growth where you predict company-level attrition.
5. 5Run correlation first (the book's 'Predict Employee Flight Risk' step) to screen predictors against the outcome. Keep only those with a visible relationship, and discard the rest before regressing.
6. 6Choose the model by the outcome type. Use logistic regression for an individual who resigns or stays (a categorical yes/no). Use multiple regression for a company or group attrition rate (a continuous percentage). Use a probability tree for a simple resignation-likelihood read. The book covers all three. If Solver throws errors in Excel, the book has a dedicated 'Fixing Solver Error Messages' section for logistic regression.
7. 7Treat rising absenteeism as an early-warning input. The book calls it a possible leading indicator of future turnover and of a disengaged workforce, so feed it into the flight-risk model as a lagged predictor, not just a reporting line.
8. 8Score each employee as a flight-risk output, then validate. A reader testimonial reports running a first predictive Employee Flight Risk model within one week using the book. Prove your own against the resignations that actually occurred in the next period before you act on it.

The book's own attrition example predicts a company's average employee attrition rate from four inputs: unemployment rate 3%, GDP growth 2%, Glassdoor public sentiment rating 5, and engagement score 7. The multiple-regression model returns a predicted average attrition rate of 8%. To reproduce the logic, build a table with one row per company (or business unit and period). The outcome column is attrition rate. The four predictor columns are unemployment, GDP growth, Glassdoor sentiment rating, and engagement score. Run Excel's regression tool, then read each coefficient to see which input moves attrition most. Plug the four values for a given company into the fitted equation to get its predicted rate; the book's inputs yield 8%. Compare the prediction to the actual rate. The passages do not give the coefficients, so use your own regression output. A large gap between predicted and actual flags a company or unit that the sentiment and engagement scores do not explain.

• If the first-year resignation rate is higher than the rate for longer-tenured staff, then investigate hiring and onboarding first (misfit, overselling, job mismatch) rather than compensation.
• If absenteeism (Sick Days / Permanent FTE) rises, then treat it as a possible leading indicator of disengagement and turnover, and check overtime and engagement scores before attrition shows up.
• If Labour Cost per FTE or benefits percentage sits low against benchmark, then test pay and benefits as drivers of turnover and sick leave before launching engagement programs.
• If the outcome is an individual leaving or staying, then use logistic regression. If it is a group attrition rate, then use multiple regression.
• If overtime per individual contributor is consistently high, then weigh hiring additional employees. The book notes excessive overtime can raise both absence and turnover, and that it is easily fixed by hiring.

• Using total headcount or temporary staff in denominators. The book's formulas use permanent FTE or permanent headcount; the tell is rates that move when contractors are added or removed.
• Reporting one blended attrition rate. The tell is a stable company-wide number while first-year or departmental resignations spike unnoticed.
• Treating absenteeism as a lagging HR statistic. The tell is that you only review it after resignations have occurred, so it never feeds the prediction.
• Feeding a continuous attrition rate into a logistic model, or a yes/no resignation into a plain regression. The tell is nonsensical predictions, such as rates outside 0-100%, or Solver errors.
• Skipping validation against actual leavers. The tell is a flight-risk list nobody checks against who resigned, so the model's accuracy is never known.

• ▸Outcome definition: state whether you predict individual resignation (yes/no) or group attrition rate (%), which fixes your model type.
• ▸Attrition formula: write the exact numerator and denominator, using permanent headcount (and tenure band, e.g. 0-1 year).
• ▸Absenteeism formula: Sick Days / Permanent FTE for the same period and population.
• ▸Segment cuts: list the departments and tenure bands you will report separately.
• ▸Predictor list: note each input (overtime per individual contributor, Labour Cost per FTE, benefits %, engagement score, Glassdoor sentiment, unemployment, GDP growth) and its data source.
• ▸Correlation screen result: record each predictor's correlation with the outcome and keep or drop it.
• ▸Model chosen: probability tree, multiple regression, or logistic regression, with the reason.
• ▸Predicted vs actual: record the predicted value and the actual outcome for each unit or person next period.
• ▸Action trigger: write the score or rate threshold that prompts a retention action and who owns it.

1. 1Pick one outcome column and define it before touching drivers. The source treats performance as a measurable output in one of three forms: a performance rating (e.g., the "9" in the HiPo example), an employee's sales revenue, or customer service quality. Do not blend them into one composite.
2. 2Run the ARHAT sequence on it: Ask the question (e.g., what predicts an employee's sales?), Review literature (the book's cases are Deloitte on high-performing financial-services salespeople and HBR on what makes great salespeople), then Formulate hypotheses. Write each hypothesis as driver → outcome before you pull data.
3. 3Choose drivers from the families the source actually models: organizational network (size, score, diversity index), skillsets/competency score, personality traits, training, and engagement. Aim for a short list of drivers, each tied to a written hypothesis.
4. 4Convert soft constructs into numbers. An employee's organizational network becomes a Social Network Score (via an ONA survey and Excel NodeXL or the Excel network tool). Diversity of that network becomes a Social Network Diversity Index. Competencies become a Competency Score. Build one row per employee with the outcome and all drivers side by side.
5. 5Run correlation first (Excel Data Analysis ToolPak) between each driver and the outcome. This is the book's own sequence: a correlation example precedes each multiple-regression example (e.g., 20.3 before 20.4). Drop drivers that show no relationship with the outcome.
6. 6Run multiple regression in the ToolPak with the outcome as Y and the surviving drivers as X. Do this once for the rating and once for sales if you track both. These are the same designs as the book's examples: sales ~ organizational network, and rating ~ social network + skillsets + personality traits.
7. 7Turn the fitted equation into a prediction by entering a candidate profile, the way the book does: Network Score 16, Diversity Index 3, Competency Score 8 → predicted rating 9 (HiPo). Use the same method to score new hires or current staff.
8. 8Tell the story (ARHAT step 5): know the audience, build a narrative, build the visuals. Express the finding in the executive's unit, as Best Buy does: a 0.1% rise in engagement → $100,000 in annual store income.

The source's HiPo case predicts a performance rating from three drivers. The Excel model is built from per-employee rows: Social Network Score, Social Network Diversity Index, and Competency Score, with the performance rating as the outcome. For an employee with a Network Score of 16, a Diversity Index of 3, and a Competency Score of 8, the regression predicts a rating of 9, which flags them as a high-potential. The passages do not give the coefficients, so you derive them from your own data. A parallel result concerns sales: VoloMetrix found that a salesperson's network size inside the company was a more important leading indicator of sales than time spent with customers. That is why 17.10 regresses sales on network metrics instead of on activity time alone. For the executive story, translate the output into money, as Best Buy does with engagement: 0.1% more engagement equals $100,000 more annual store income. Sales, rating, and customer service each get their own model, so a result is never reported as a vague "performance" effect.

• If the outcome is a rating, a revenue figure, or a service score, then build a separate regression for each; do not average them into one performance index.
• If a driver is qualitative (network, diversity, skills), then convert it to a score or index first, and only then enter it in the regression.
• If a driver shows no correlation with the outcome, then drop it before the multiple regression rather than adding it for completeness.
• If an activity metric (time with customers) competes with a structural one (network size), then include both and let the model show which matters, since VoloMetrix found the network metric led.
• If you present to executives, then state the effect in business units (income, sales, rating points) and give the narrative before the statistics.

• Vague outcome: "performance" is never defined as rating vs. sales vs. service. The tell: no one can say which column is Y.
• Driver soup: throwing every available field into the regression with no stated hypothesis. The tell: you cannot explain why a driver is in the model.
• Skipping the correlation step and going straight to regression. The tell: surprising or unstable coefficients you can't trace back to a simple relationship.
• Using unconverted network or diversity data. The tell: text or raw ONA survey output sitting in the regression range instead of a score or index.
• Reporting the equation without the story. The tell: executives ask "so what?" because results are not tied to income, sales, or a decision such as identifying HiPos.

• ▸Outcome (Y): choose one: performance rating, employee sales revenue, or customer service score, and name the exact column.
• ▸Business question: one sentence stating what decision the prediction supports (e.g., identify HiPos, forecast sales).
• ▸Hypotheses: one line per driver written as "driver → outcome, direction".
• ▸Drivers (X) and their source: list each (Social Network Score, Diversity Index, Competency Score, personality, training, engagement) and where the number comes from.
• ▸Conversion method: for each soft driver, state how it becomes a number (ONA survey → NodeXL score, diversity index formula).
• ▸Correlation result: driver-by-driver correlation with Y; mark keep or drop.
• ▸Regression setup: Y range, X range, and sample size (employees) used in the Excel ToolPak.
• ▸Prediction test profile: enter sample driver values (e.g., 16 / 3 / 8) and record the predicted outcome.
• ▸Executive translation: restate the finding in business units and the one action it should trigger.

1. 1Name the one business outcome you are predicting and define it with Finance before touching data. The passages list EBIT, market share, revenue, absenteeism, and customer service as outcomes. Pick one and align the definition, including how Finance treats manpower cost.
2. 2Start with the ARHAT sequence: Ask the question, Review literature, Formulate a hypothesis, Analyze data, Tell the story. Write the hypothesis as a directional claim, for example 'higher senior-team diversity is associated with higher EBIT'.
3. 3Mine published research first to set expectations. The passages cite McKinsey's 366-company study (EBIT up 0.8% per 10% rise in racial/ethnic diversity in the US senior team; 3.5% per 10% rise in gender diversity in the UK) and Deloitte's absenteeism finding. Use these as benchmarks for your own result.
4. 4Convert the HR state into a number. For diversity, build an index (the book has a 'Convert Diversity into an Index' step). For training, link it to the outcome via EPS or customer service. Place HR predictor and business outcome in one Excel table with one row per unit (team, site, or period).
5. 5Run correlation first, then multiple regression, using Excel's Analysis ToolPak. Put the business outcome (EBIT, customer service score) as the dependent variable and the HR measures as independent variables. Check the direction and size of each coefficient against the benchmark research.
6. 6Take preliminary results to domain experts and Finance before concluding. A spike in customer turnover may be deliberate, for instance because products were withdrawn. Ask what context explains unusual months.
7. 7Tell the story: know the audience, build a narrative, and chart it (clustered column, combination, scatter). For non-technical leaders, lead with the business outcome and the recommended action, not the statistics.
8. 8Check legal and privacy exposure before acting. HR data are sensitive, and employees should know what is collected and how it is used. Any selection criterion you adopt must be shown to predict job success.

The source passages give the McKinsey 'Diversity Matters' study as the model case for linking an HR state to EBIT. It covered 366 public companies across Canada, Latin America, the UK, and the US. In the US, each 10% increase in racial and ethnic diversity on the senior-executive team corresponded to a 0.8% rise in EBIT. In the UK, each 10% increase in gender diversity corresponded to a 3.5% rise. To replicate the approach in your own firm, convert ethnic and gender diversity into index values for each business unit. Then regress unit EBIT on both indices using Excel's Analysis ToolPak, which the book's chapter 18.2 does as 'Predict Ethnic & Gender Diversity's Impact On EBIT'. Read each coefficient as the EBIT change per one-unit index change, then compare it with the 0.8% and 3.5% benchmarks. Share the draft with Finance to confirm EBIT definitions, and with domain experts to check for non-diversity explanations. Present the result as association, since the passages describe correlation and prediction, not proven cause.

• If a published benchmark exists for your outcome (such as McKinsey's EBIT figures), then run your own regression and compare your coefficient with it before presenting.
• If a movement in a business outcome (such as high customer turnover in certain months) looks odd, then ask domain experts what happened before attributing it to an HR factor.
• If your definition of cost or earnings differs from Finance's, then align with Finance first, since this protects your credibility.
• If the outcome is a continuous financial measure (EBIT, revenue, customer service score), then use multiple regression. If it is yes/no (resign or not, accident or not), then use logistic regression.
• If your analysis screens or selects people, then get Legal's view and confirm the criteria are proven to predict job success.

• Claiming cause from correlation. The tell: the deck says 'diversity raises EBIT' while the data are one cross-section with no controls. The sources frame results as relationships and predictions.
• Using an HR metric Finance does not recognise. The tell: Finance disputes the cost or earnings figures and the discussion shifts away from your finding.
• Skipping context from domain experts. The tell: a spike in the outcome (such as customer turnover) is read as an HR effect when it was a deliberate product removal.
• Presenting accurate statistics without a story. The tell: leaders ask 'so what?' and no action follows. The source states that accurate analytics fail without an engaging story.
• Ignoring privacy and discrimination risk. The tell: employees do not know their data feed the model, or screening variables (such as distance to work) could filter out older or minority applicants without proven job relevance.

• ▸Business outcome: Name one measure (EBIT, revenue, market share, absenteeism, customer service score).
• ▸Finance-agreed definition: Write the exact formula and period, and who in Finance confirmed it.
• ▸HR state or practice: Name the single HR variable (diversity index, training hours, inclusion score).
• ▸Directional hypothesis: One sentence stating the expected direction (e.g. higher X is associated with higher Y).
• ▸Published benchmark: Cite the study and figure you compare against (e.g. McKinsey: +0.8% EBIT per 10% racial/ethnic diversity in US).
• ▸Data unit and sample size: State what each row is (team, site, quarter) and how many rows.
• ▸Method: Correlation, multiple regression, or logistic regression, and why.
• ▸Domain-expert check: Name who reviewed the preliminary result and any context they supplied.
• ▸Legal/privacy sign-off: Record that data use and selection criteria were cleared with Legal.
• ▸Story headline and audience: One-sentence takeaway and the chart type for the audience.

1. 1Define the outcome before touching data. Use 'recordable case frequency' (the Shell metric, which the source calls a key industry safety standard) as the continuous outcome for correlation and multiple regression. Add a binary column (accident = 1, none = 0) per employee for logistic regression. Keep the two definitions separate.
2. 2Build one row per employee, or per site-period, with the predictors the construct names: engagement score, demographics (age group), tenure band (years of service), and earnings pressure. The passages give no measure for earnings pressure, so choose one you can defend and say so in your notes. Add supervisor communication score, because the IBM Kenexa example cuts incidents by it.
3. 3Start with descriptive cuts before any model. Tabulate incidents by age group and by tenure band, as AIHR did: 52% of road traffic accidents involved drivers/riders with 5 years of service and below, and many involved ages 31 to 35. Then cross age group with supervisor communication score, as the IBM Kenexa diagrams do. These cuts show where the signal sits.
4. 4Run a correlation of each predictor against incident frequency (Excel Data Analysis > Correlation). Keep predictors with a clear relationship to the outcome. Drop any pair of predictors that move together almost identically, so the regression coefficients stay readable.
5. 5Run multiple regression (Excel Regression tool) with incident frequency as Y. Read the Summary Output coefficients as 'for each unit increase in X, incidents change by b'. Write the prediction as intercept + (b1 × X1) + (b2 × X2) + …, the same form as the source's attrition formula (13.708 + 1.631 × Unemployment + …). Note the sign of each coefficient, since a negative one lowers incidents.
6. 6For the yes/no question 'will this person have an accident?', run logistic regression. Compute L from the coefficients, then P(X) = e^L / (1 + e^L). Closer to 1 means the event is probable, closer to 0 means it is not. The source's resignation example gives 0.985 for a staff compa-ratio of 0.7, age 30, and travel time 1, which is read as 'probably Resign'. Use the same reading for 'probably Incident'.
7. 7Translate the result into money or risk terms for the stakeholder. Cite the engagement benchmarks: MolsonCoors found engaged employees five times less likely to have a safety incident, and Shell found a 1% engagement rise gave a 4% drop in recordable case frequency. Add the compliance angle: companies that miss safety benchmarks may be barred from government contract bids.
8. 8Pre-brief the function head that results may be inconclusive or may only confirm what was already suspected. Confirmation is still evidence. Ask a domain expert (safety or operations) to explain unusual periods before you present them.

A safety team has one year of incident records and wants to know where to act. First they tabulate by tenure and age, following the AIHR analysis, which found 52% of road traffic accidents involved drivers/riders with 5 years of service and below and a concentration in the 31–35 age band. That points to short-tenure drivers as the first group to examine. Next they cross incidents by age group and supervisor communication score, the same view IBM Kenexa Talent Insights offers, to see whether weak supervisor communication sits behind the age pattern. They then run multiple regression with recordable case frequency as Y and engagement, tenure and communication score as Xs, and write the prediction as intercept plus coefficient-times-variable terms. To size the engagement lever for leadership, they quote Shell: a 1% engagement increase produced a 4% drop in recordable case frequency, and MolsonCoors found engaged employees five times less likely to have an incident. They do not apply Shell's 4% to their own site until their own regression produces a comparable coefficient. Finally, a logistic model scores each employee's probability of an incident, where values near 1 mean 'probably incident'.

• If incidents cluster in a tenure band (e.g., 5 years of service and below) or age band (e.g., 31–35), then target onboarding and supervision for that band first, and test it in the regression before committing resources.
• If two predictors correlate almost perfectly with each other, then keep only one in the regression, so each coefficient can be read alone.
• If the outcome is a count or rate (recordable case frequency), then use multiple regression; if it is yes/no per person, then use logistic regression and read P(X) near 1 as 'probably incident'.
• If you quote an outside engagement benchmark (Shell's 4% drop per 1% engagement, MolsonCoors' five times), then label it as external and replace it with your own coefficient once you have one.
• If results are inconclusive or only confirm what was suspected, then report that plainly and treat it as evidence, not a failed project.

• Treating Shell's 1%→4% result as your own site's effect. The tell: the business case quotes a drop with no regression output from your data behind it.
• Using only descriptive cuts and stopping. The tell: the report says '52% of accidents involve short-tenure staff' without the share of all staff in that band, so you cannot tell whether the group is over-represented.
• Defining 'incident' inconsistently across sites or periods (recordable case versus any injury). The tell: incident counts jump when a new site or reporting form joins the dataset.
• Putting correlated predictors (age and tenure) into the same model and reading each coefficient as independent. The tell: coefficient signs flip or look implausible when one variable is added.
• Skipping the domain expert. The tell: an incident spike is presented without anyone from safety or operations explaining what happened in that period.

• ▸Outcome definition: state exactly what counts (recordable case frequency, or binary accident yes/no) and the period covered.
• ▸Data sources and owners: list where incident records, engagement scores, tenure and age data come from, and who grants access.
• ▸Predictor list: engagement score, age group, tenure band, supervisor communication score, earnings pressure measure (and how you measure it).
• ▸Descriptive cut results: incidents by age group, by tenure band, and by age × supervisor communication score.
• ▸Correlation results: coefficient of each predictor against incidents, with the ones you keep or drop and why.
• ▸Regression formula: intercept and coefficient per predictor, written as intercept + (b1 × X1) + …
• ▸Logistic check: L value and P(X) = e^L/(1+e^L) for a sample employee profile, with the plain-language reading.
• ▸External benchmark used (e.g., Shell 1%→4%, MolsonCoors 5x), labeled as external, plus your own coefficient when available.
• ▸Domain expert comments and stakeholder expectations: notes on unusual periods and how results will be presented.

1. 1Start from the business problem, not the chart. The book's flow for any HR analytic runs from defining the business problem to telling the story. Write the problem in one sentence before touching the data (for example, high staff turnover, absenteeism, performance issues). That sentence becomes your villain in Act 1.
2. 2Finish the analysis before you build the story. Step 5 (Tell the Story) comes after the analysis. Use correlation to establish whether a relationship exists, and regression to establish which variables have the most impact on the dependent variable. Your story carries only those findings.
3. 3Add qualitative evidence to the numbers. Qualitative analysis (opinions, attitudes, life experiences) contextualizes quantitative results. Pull focus-group quotes or comments that bring the statistical finding to life, because a quote adds color and depth that a coefficient does not.
4. 4Profile the audience before you draft. Classify them as Neutral, Critic, Uninformed, Subject-Matter-Expert or Top Management. Decide what you want them to know or do, and set the depth and length from that.
5. 5Assemble the three elements deliberately: data, visuals, narrative. Narrative plus data explains context, source, relevance, what you did and why it matters. Visuals plus data reveal trends and outliers hidden in table rows and columns. Narrative plus visuals engage. All three together drive change.
6. 6Structure the narrative in three acts. Act 1, The Setup (Why should I care?): give background and context, and name the villain (the problem) and the hero (the solution, such as an incentive or training programme). Act 2, The Confrontation (How will your idea make my life better?). Act 3, The Resolution (What action do I need to take?).
7. 7Build each visual in Excel to show one insight that the table hides. Place it in the act where it supports the question that act answers.
8. 8Close with a specific recommendation and ask. The point of the story is to communicate insights and recommendations to stakeholders so they act, so end Act 3 on the action you need them to take.

The passages name the story elements but give no full case, so this uses their own villain and hero examples, with figures left as placeholders. Problem: high staff turnover (the villain). Analysis: correlation shows which factors relate to leaving, and regression ranks which factors have the most impact. Audience: Top Management, who "don't have patience", so the story stays concise. Act 1 (Why should I care?): state the turnover problem and its context in two or three lines. Act 2 (How will your idea make my life better?): introduce the hero, an incentive or training programme, show one Excel chart of the top-ranked driver, and add a focus-group quote to give the number a human voice. Act 3 (What action do I need to take?): ask for approval of the programme, with the ask stated in the final slide's headline. The sales-vacancy formula in the source, (Annual Sales Quota / 365 days) x 47 average time-to-fill, shows how to convert a people problem into a business-impact figure that executives respond to.

• If the audience is Top Management, then keep it concise: lead with the problem and the ask, and move detail to an appendix.
• If the audience is Critics, then identify why they disagree with your proposal and open from a common ground before presenting the data.
• If the audience is Uninformed, then decide how much they already know and give only the background they need for Act 1.
• If the audience is Subject-Matter Experts, then skip what they know and give them something new they are uninformed about.
• If a chart or table adds no insight beyond the narrative, then cut it. If a finding has no narrative context (source, relevance, why it matters), then add that context before you present it, because different people interpret the same data differently.

• Showing the data without a story. The tell: stakeholders read the same numbers differently and ask what to do with them. The fix is a narrative that supplies context and a recommendation.
• Having accurate analytics but no engaging story. The tell: a technically correct model that nobody acts on. Data is less memorable than stories, so build the three-act arc.
• Using one deck for every audience. The tell: executives lose patience while experts hear nothing new. Profile the audience first.
• Hiding the insight in tables. The tell: trends and outliers exist in the spreadsheet but never reach the room. Add a visual so the audience sees them.
• Dropping the setup and starting at the solution. The tell: the audience asks why they should care. Open Act 1 with background, the villain and the hook.

• ▸Business problem (villain): state the problem in one sentence, e.g. turnover or absenteeism.
• ▸Key finding: state the correlation or regression result in plain language, with its source.
• ▸Audience type: Neutral, Critic, Uninformed, Subject-Matter Expert or Top Management, and how that changes your depth and length.
• ▸Desired know/do: what you want the audience to know or do afterwards.
• ▸Act 1 – Setup (Why should I care?): context, hook, and the hero (the proposed solution).
• ▸Act 2 – Confrontation (How will it make my life better?): the one visual and one qualitative quote that support the finding.
• ▸Act 3 – Resolution (What action?): the specific decision, owner and next step you are asking for.
• ▸Visual check: which insight does each chart reveal that the data table hides?
• ▸Objection and common ground: the likeliest objection and the shared ground you open from.

1. 1Write the engagement hypothesis as a business outcome before touching data. The passages name the outcomes: sales, customer satisfaction, workplace accidents, attrition. Example: 'Higher store engagement scores predict higher store annual income.' Take the hypothesis from function heads, since the source says to get their beliefs about causes.
2. 2Secure a sponsor and data access first. The source states that analytics projects lacking a strong sponsor are likely to fail, and that a well-defined hypothesis still fails if you cannot get the data. Name the data owners for the survey results and for the business metrics, and agree timelines before you analyze.
3. 3Collect two kinds of engagement signal. The numeric signal is the engagement survey score and the Employee Net Promoter Score. The text signal is open-ended survey comments, plus pulse surveys, focus groups and Glassdoor reviews as additional sentiment sources.
4. 4Convert the text to numbers. Count frequently mentioned words (VBA in Word, or an online counter), visualize them as a Word Cloud (Pro Word Cloud add-in), then run sentiment analysis in Excel (Azure Machine Learning) to get a sentiment score per unit or company.
5. 5Merge engagement data with business data in one Excel table. One row per unit (store, team, company), with engagement score and outcome side by side. This is the step the iNostix case title describes: 'merging engagement survey outcomes with business data'.
6. 6Run a correlation first, then a multiple regression. Correlation tells you whether engagement and the outcome move together. Regression adds the other drivers so engagement's effect is not overstated. The source's attrition model includes unemployment rate, GDP growth, Glassdoor sentiment and engagement score.
7. 7Express the result as a business-unit statement, the way the Best Buy finding is framed: 'a 0.1% increase in employee engagement results in $100,000 more annual income per store'. Executives fund engagement work on that kind of statement.
8. 8Report it as a story, not a model printout. The source says the key gap in organizations is a solid narrative for executives, so structure the communication: hypothesis, data, finding, implication, ask.

Best Buy is the source's headline case: it can predict that a 0.1% increase in employee engagement results in a $100,000 increase in a store's annual income. To reproduce the logic in Excel, build one row per store with engagement score and annual income, then run a correlation and a regression of income on engagement. The regression coefficient is your 'dollars per engagement point' figure, the same form as Best Buy's statement. The source's second model shows how to add controls. A company's average employee attrition rate is predicted to be 8% when unemployment is 3%, GDP growth is 2%, Glassdoor public sentiment rating is 5, and engagement score is 7. Here engagement is one input among external and sentiment variables, so its coefficient is not credited with effects that belong to the economy. Present the result to executives as a one-line dollar claim with the hypothesis and data sources behind it. If you extrapolate linearly (say 0.3 points → $300,000), label it an assumption, because the source gives only the 0.1% figure.

• If you can show a correlation between engagement and an outcome, then run a multiple regression with other drivers before presenting any dollar claim; the source's own attrition model includes unemployment, GDP and Glassdoor sentiment alongside engagement.
• If scores are high but you have no evidence of effort or results, then do not call it success; the source warns that making employees happy doesn't mean they'll work hard for you. Test engagement against a business outcome.
• If survey scores and comments disagree, then trust neither alone; run word counts and sentiment analysis on the comments and compare them with the numeric score.
• If you cannot get the business data or a sponsor, then stop and fix access before modeling; the source says projects fail without either, even with a good hypothesis.
• If the audience is executives, then lead with the outcome statement (engagement change → income change) and the narrative, not the regression mechanics.

• Treating engagement as satisfaction. The tell: the only metric is how happy people say they are, with no link to performance, sales, accidents or attrition.
• Reporting a survey score with no business data attached. The tell: a deck of engagement averages and no outcome column in the spreadsheet.
• Claiming causation from a simple correlation. The tell: a single-variable chart is used to justify a dollar figure, with no controls such as unemployment or GDP.
• Ignoring open-text comments. The tell: free-text responses sit unread while only the numeric score is reported; there are no word counts, Word Cloud or sentiment score.
• Starting analysis before securing sponsor and data access. The tell: a clean hypothesis stalls because the data owner never releases the business metrics, and no senior leader is removing roadblocks.

• ▸Hypothesis: one sentence linking engagement to a named business outcome (sales, customer satisfaction, accidents, attrition).
• ▸Sponsor: the senior leader who will rally support and unblock data access.
• ▸Engagement metrics: survey score, Employee Net Promoter Score, pulse or other measures, with the period covered.
• ▸Text sources: open-ended survey comments, focus groups, Glassdoor reviews; note which you will word-count and sentiment-score.
• ▸Outcome data and owner: the business metric, who holds it, and the agreed delivery date.
• ▸Control variables: other drivers to include in the regression (e.g., unemployment rate, GDP growth, Glassdoor sentiment).
• ▸Unit of analysis: store, team or company; one row each in the merged Excel table.
• ▸Result statement: 'A X-point change in engagement is associated with a $Y change in outcome', plus correlation and regression notes.
• ▸Executive narrative: hypothesis, finding, implication and the decision you are asking for.

1. 1Fix definitions in writing before pulling data. Market-ratio compares an employee's pay to the external market. Compa-ratio compares it to the internal salary range. Pay-spread describes how pay is distributed. The source names these metrics (sections 21.1 and 21.2) but does not print their formulas, so record your formula and the reference point (for example midpoint) in the analysis file.
2. 2Build one row per permanent employee: ID, job, current pay, market reference for the job, internal range reference, market-ratio, compa-ratio, performance rating, tenure, resigned yes/no. Use permanent headcount, matching the source's HR-metric convention.
3. 3Pick outcome metrics from the source's retention and recruitment lists. Voluntary Turnover Rate = permanent resignations / permanent headcount. Top Quartile Performer Resignation Rate = resignations in the top performance quartile / permanent headcount. Depletion Ratio = top talents lost in a year / employees. Vacancy Rate = permanent vacant positions / permanent headcount. 1st Year Resignation Rate = resignations at 0–1 year of service / headcount at 0–1 year of service.
4. 4Read the aggregate signals first. The source says rising voluntary turnover can point to a lack of salary competitiveness, and a high vacancy rate means you should review the competitiveness of the compensation package. Treat these as prompts to investigate, not as proof, because the same passages list leadership credibility, retention practices and the job market as other causes.
5. 5Test the link inside your own data. Run a correlation in Excel between compa-ratio (and market-ratio) and the resignation flag. Then run a multiple regression, or the logistic regression the source uses for categorical outputs, with pay ratios plus performance and tenure as inputs. Check whether pay position still predicts resignation after those controls.
6. 6Segment before concluding. Cut the ratios for top-quartile performers, for first-year hires, and for the jobs where vacancies persist. Competitiveness problems usually sit in one segment, not across the whole company.
7. 7Feed the results into a flight-risk view (the source lists a Flight Risk Formula in 21.3, without printing it). Flag employees whose pay ratios are low and whose profile matches past leavers, and rank them by performance.
8. 8Tell the story in the source's order: know the audience, build the narrative, then add the visuals. Scatter plots of pay ratio against resignation, and clustered columns by segment, fit this; both appear in the source's Excel visualization section.

The source supplies the outcome side of this analysis, not a full pay dataset, so the numbers below come from its depletion-ratio example and the rest is a method sketch. A company with 1,000 employees loses 100 top talents in the prior year. Depletion Ratio = 100/1000 = 0.10. The source says a higher ratio predicts worse profitability, because the company loses its best leaders and innovators. To test whether pay position contributes, list the 100 leavers and the retained top talents, then attach each person's compa-ratio and market-ratio. Correlate each ratio with the leave/stay flag, then add tenure and performance in a regression. If leavers cluster at low pay ratios, the source's reading applies: lack of salary competitiveness behind rising voluntary turnover. If they do not, look at the other causes the source names (leadership credibility, retention practices, a better job market) before touching pay budgets.

• If voluntary turnover or vacancy rate rises, then check market-ratio and compa-ratio by job before attributing it to pay; the source lists pay as only one possible cause.
• If resignations concentrate in the top performance quartile, then prioritise pay position for that group; the Top Quartile Performer Resignation Rate and Depletion Ratio show where losses cost most.
• If a high first-year resignation rate appears, then look first at fit, job description, oversell and onboarding, not pay; the source assigns those causes to that metric.
• If the correlation or regression shows no link between pay ratios and resignations, then do not raise pay on that basis; investigate the other drivers.
• If benefits as a percentage of labour costs is low against benchmark, then check absenteeism and sick leave alongside pay ratios, since the source ties low benefits to higher absenteeism.

• Defining market-ratio or compa-ratio inconsistently across jobs or years. The tell: the same person gets different ratios in different reports.
• Treating rising turnover as proof of low pay. The tell: no analysis separates leavers' pay position from stayers', and leadership, retention practices and job market are never examined.
• Averaging across the whole company. The tell: a healthy overall ratio while top-quartile resignations or specific vacancies stay high.
• Skipping controls for performance and tenure. The tell: pay appears to predict leaving only because it tracks tenure or the first-year effect.
• Reporting ratios without a narrative for the audience. The tell: executives see a table of compa-ratios and ask what to do.

• ▸Metric definitions: write your exact market-ratio, compa-ratio and pay-spread formulas and the reference point used.
• ▸Population and period: state which permanent headcount and which year are covered.
• ▸Outcome metrics: record voluntary turnover rate, top-quartile resignation rate, depletion ratio, vacancy rate and 1st-year resignation rate.
• ▸Segment table: list job, performance quartile and tenure band, with average market-ratio and compa-ratio per segment.
• ▸Correlation result: enter the correlation between each pay ratio and the resignation flag.
• ▸Regression result: enter the inputs used (pay ratios, performance, tenure) and whether pay ratios stay significant.
• ▸Competing explanations checked: tick leadership credibility, retention practices, job market, onboarding/job fit.
• ▸Flight-risk list: name high-performing employees with low pay ratios and the proposed action.
• ▸Narrative and visual: write a one-sentence finding for the audience and name the chart that shows it.

1. 1Separate the construct into three measured components: provision (what you spend and deliver), perceived quality (training satisfaction), and effectiveness (knowledge gained and on-the-job application). Decide which of these you can measure today. Most HRIS systems only give you provision.
2. 2Compute the three Learning & Development metrics from the HR Metrics Standards for provision. L&D Investment per FTE = L&D Cost / Permanent FTE. L&D Cost Payroll Percentage = L&D Cost / Labour Cost. L&D Hours per FTE = L&D Hours / Permanent FTE. Run them by team, not only company-wide, because the later regression needs one row per group or person.
3. 3Treat these metrics as descriptive analytics only. They answer 'how many hours did Sales clock last year'. They do not tell you whether training worked. Write down the predictive version of the question: 'What is the impact of the training programme on Sales?'
4. 4Add the effectiveness layers from the source's evaluation chain: the four Kirkpatrick levels, then Jack Phillips' fifth level, ROI. Collect satisfaction, knowledge gained and on-the-job application per participant, so you can test which layer links to a business outcome.
5. 5Choose one business outcome that training is meant to move, such as sales revenue or customer service scores. The source names both as outputs of Excel regression: 'predict training's impact on sales revenue' and 'Predict training's Impact on Customer Service'. Set up one row per employee or team: training inputs in columns, the outcome in the last column.
6. 6Run correlation first, then multiple regression in Excel, with training variables alongside other drivers. Read the coefficient on training to estimate the outcome change per unit of training. Do not read training hours alone as the effect.
7. 7Convert the result to money and, where relevant, to earnings per share. The source's outline has a method for training's impact on EPS and a Phillips ROI level. Then tell the story to the audience in a short narrative with one visual, following the ARHAT framework's 'Tell the Story' step.
8. 8Compare against your own history and, where available, an external benchmark. The HR Metrics Service publishes the standard definitions, so you can keep formulas consistent across periods.

The source frames the shift this way: the descriptive question 'How many hours of training did our Sales employees clock last year?' becomes the predictive question 'What is the impact of the training programme on Sales?'. Start with the descriptive layer using the L&D metrics. The numbers below are illustrative, not from the source. Suppose Sales has 50 permanent FTE and logged 2,000 training hours, so L&D Hours per FTE = 2,000 / 50 = 40. Suppose its L&D cost is $100,000, so L&D Investment per FTE = $2,000. That describes provision but says nothing about effect. For the predictive layer, build a table with one row per salesperson: training hours, a training satisfaction score, a knowledge-gain score, an application rating, and sales revenue. Run a multiple regression in Excel with revenue as the output and the training variables as inputs. The source describes this method for training's impact on sales revenue and on customer service. If the coefficient for training hours is meaningful and the application variable carries more weight than satisfaction, you have evidence that the on-the-job application component, not the liking of the course, drives results. You then put a dollar value on the revenue difference and take it to the ROI and EPS step.

• If you only have hours and cost per FTE, report them as provision metrics and make no effectiveness claim. Descriptive metrics cannot show impact.
• If satisfaction scores are high but the regression shows no link to the business outcome, treat the program as well liked but unproven. Redirect measurement to knowledge gained and on-the-job application.
• If the training coefficient is positive and meaningful, move to Phillips ROI. Convert the outcome change to money, compare it with L&D cost, and report EPS impact only when the financial link is explicit.
• If L&D Cost Payroll Percentage or Investment per FTE differs sharply across teams, test the outcome regression by team before drawing a company-wide conclusion.
• If the audience is executives, lead with the business outcome and its dollar value. Put hours and satisfaction in the supporting detail.

• Equating hours trained with effectiveness. The tell: the report's headline is L&D Hours per FTE and no outcome variable appears anywhere.
• Relying on satisfaction alone. The tell: strong post-course ratings, but no data on knowledge gained or on-the-job application.
• Running regression with training as the only input. The tell: the training coefficient looks large but is not tested alongside other drivers, so it may reflect something else.
• Inconsistent metric definitions across periods or teams. The tell: Investment per FTE moves because the FTE or cost basis changed, not because spending changed. Fix by using the standard formulas every time.
• Stopping at the analysis without a narrative. The tell: a regression output is shared with no audience-specific story, no visual, and no ROI or financial translation.

• ▸Group / period: name the team and the time window so rows are comparable.
• ▸L&D Cost: total learning and development spend for the group.
• ▸Permanent FTE and Labour Cost: needed as denominators for the three formulas.
• ▸L&D Investment per FTE: L&D Cost / Permanent FTE.
• ▸L&D Cost Payroll Percentage: L&D Cost / Labour Cost.
• ▸L&D Hours per FTE: L&D Hours / Permanent FTE.
• ▸Satisfaction, knowledge gained, application: record the average score for each component per group or participant.
• ▸Target business outcome and its measured change: e.g. sales revenue or customer service score, with the regression coefficient for training.
• ▸Monetised benefit and ROI note: convert the outcome change to dollars, compare it with L&D Cost, and note any EPS effect.

1. 1Separate the two halves before you measure anything. Diversity is the who: who is in the department, who is recruited, who is promoted, who you are monitoring. Inclusion is the how: the behaviors that welcome and embrace diversity. Give each half its own metrics, because a headcount mix says nothing about whether people have equal access to opportunities.
2. 2Choose the traits to track from the source's list: ethnicity, age, gender, education, socioeconomic background, sexual orientation, religious beliefs. Measure each trait for each unit (department, team, hiring pool) so you can see where the mix differs.
3. 3Measure the pipeline as well as the stock. Track Diversity Hire Ratio (new hires who are Aborigine, Disabled or Minority / new hires) and Female Hire Ratio (new hires who are female / new hires). Add promotion and retention cuts by the same groups, since diversity covers who is recruited, promoted and monitored.
4. 4Convert each trait's composition into a single Simpson's Diversity Index value (section 18.1 of the source) so units can be compared on one number. Calculate it identically for every unit and period, and record the group definitions you used.
5. 5Measure inclusion through the network. ONA can evaluate how diverse a person's network is. Build a Social Network Diversity Index and a Social Network Size per employee from the ONA survey, so you see who actually connects across groups.
6. 6Test impact with Excel multiple regression, reading coefficients the way the source reads its sales example. Run EBIT against the ethnic and gender diversity indices. Run performance rating against Social Network Diversity Index, Social Network Size and Skillsets. A coefficient is the change in the outcome per one-unit increase in that predictor, holding the others constant. You can also plug values into the equation to forecast.
7. 7Check the 1st Year Resignation Rate (permanent resignations in 0-1 year of service / permanent headcount in 0-1 year of service) by group. The source reads a high first-year rate as cultural misfit, a job mismatch, overselling or poor onboarding. A gap between groups points at inclusion, not recruiting.
8. 8Screen every action against the legal record before you act on the numbers. Griggs v. Duke Power Co. (1971) allows professionally developed ability tests only if they are not designed, intended or used to discriminate. Johnson v. Transportation Agency (1987) allowed sex as one factor in a case-by-case, gradual approach. Document your reasoning accordingly.

The source's real case is Johnson v. Transportation Agency (1987). In 1978 the Santa Clara County Transportation Agency adopted a plan for hiring and promoting minorities and women. Its aim was measurable annual improvement in recruitment and promotion where they were underrepresented, and in the long term a workforce that reflects the proportion of women and minorities in the area's labor force. When a road dispatcher position opened, no dispatcher position was held by a woman. The Agency promoted Diane Joyce over a male employee, and both were rated well qualified. The male employee sued. The Court held that the Agency had considered her sex as one factor, in a moderate, flexible, case-by-case approach to gradual improvement in representation. Applied as an analytics routine: measure representation by position group, flag groups with zero or very low representation (the dispatcher group is the pattern), set a measurable annual target, and treat the trait as one factor among rated qualifications. The case supports that design. It is not a licence for quotas or for overriding qualifications.

• If a department's Diversity Index is low but its Diversity Hire Ratio and Female Hire Ratio are healthy, then look at promotion and retention (including 1st Year Resignation Rate by group), because the problem is inclusion rather than recruiting.
• If the diversity-to-EBIT regression shows a positive coefficient, then report it as an association within your sample and fit, and don't claim it as proof of cause. Present it alongside the strategy narrative, as Schneider Electric treats diversity: a core strategy, not an HR program.
• If someone's Social Network Diversity Index is low while their Social Network Size is high, then they are well connected but homogeneous. Target cross-group connections rather than more connections.
• If a selection test or criterion produces group differences, then confirm it is professionally developed and not designed, intended or used to discriminate (Griggs) before you keep using it.
• If you plan to weigh a protected trait in a promotion decision, then limit it to one factor among qualified candidates, tie it to a measurable underrepresentation target, and decide case by case (Johnson).

• Treating diversity and inclusion as one thing. The tell: the dashboard shows only headcount mix, with no inclusion or behavior measure such as network diversity or first-year resignation by group.
• Reporting only a company-wide index. The tell: the overall number looks fine while individual departments or senior positions have no representation, as with the dispatcher group that had no women.
• Computing the index inconsistently, for example with different group definitions or traits from period to period. The tell: movements in the index that line up with a change in category definitions rather than a change in people.
• Reading a regression coefficient as a cause or a forecast guarantee. The tell: a slide that says 'raising diversity raises EBIT by X' with no stated variables, sample or controls.
• Using selection criteria or tests without a legal check. The tell: no documentation that tests are professionally developed and not used to discriminate, and no record of the case-by-case reasoning behind preferential decisions.

• ▸Unit and period: name the department or team and the reporting period, so indices are comparable.
• ▸Traits tracked: list which of ethnicity, age, gender, education, socioeconomic background, sexual orientation and religious belief you measure, and the group definitions used.
• ▸Simpson's Diversity Index per trait: enter the value from the section 18.1 conversion, one per trait.
• ▸Diversity Hire Ratio: new hires who are Aborigine, Disabled or Minority / total new hires.
• ▸Female Hire Ratio: new hires who are female / total new hires.
• ▸Promotion and 1st Year Resignation Rate by group: record promotions and permanent first-year resignations / first-year headcount, split by group.
• ▸Social Network Diversity Index and Social Network Size (average): take both from the ONA survey for the unit.
• ▸Regression outputs: record the coefficients for EBIT ~ ethnic and gender diversity indices, and for performance rating ~ network diversity, network size and skillsets.
• ▸Legal check and action: note the Griggs/Johnson review, the one-factor, case-by-case rationale, and the chosen intervention with its owner.

1. 1Start with a business problem, not a graph. The source's Step 1 rule applies: if attrition is not a real problem for the business unit's KPIs, do not investigate it. Write one sentence naming the outcome (employee churn, an employee's sales, or a performance rating) and the KPI it threatens. Get the project sponsor to agree on scope, timeframe, budget and data access before you build anything.
2. 2Do a short literature review (the source's Step 2). Look for how other companies framed network position against your outcome and which techniques they used. Write down your hypothesis for each metric, for example 'lower reach predicts higher churn', so you test a direction rather than fish.
3. 3Build an ONA survey with a survey tool. The source lists 'Tools to build an ONA Survey' in section 17.4, and names SurveyGizmo and SurveyMonkey as free survey tools elsewhere. Each respondent names the colleagues they interact with. The result is an edge list: one row per who-connects-to-whom.
4. 4Load the edge list into NodeXL (Excel add-in) and visualize it (section 17.6). Check the picture first: isolated people, tight clusters, and individuals who bridge groups. This is a sanity check before any statistics.
5. 5Compute graph metrics per employee in NodeXL: degree, betweenness, closeness and eigenvector centrality. These give the structural position. Record network size (reach) and, where you have attributes such as department or skillset, a Social Network Diversity Index. The source (section 18.1) says to convert diversity into an index.
6. 6Join the metrics to your HR outcome table in Excel, one row per employee. Use a consistent employee ID and keep the survey window and the outcome window separate, so position is measured before the outcome.
7. 7Test the relationship in the order the source uses. First, correlation between each graph metric and the outcome (17.8). Then logistic regression for a yes/no outcome like churn (17.9). Then multiple regression for a numeric outcome like sales (17.10), or performance rating with Social Network Diversity Index, Social Network Size and Skillsets as predictors (18.3).
8. 8Report in story form. Open with people, then data: say 'one-third of our new hires in Sales leave within 6 months' rather than 'attrition is high'. Show the network visual instead of a raw correlation or regression table, and name the intervention (the source lists 'ONA Interventions' in 17.7).

The source's own ONA design gives the template; the excerpts contain no numeric results, so none are invented here. Business problem: new Sales hires are leaving (the source's phrasing is 'one-third of our new hires in Sales leave within 6 months, making it tough for us to achieve our sales targets'). Step one is to confirm with the sponsor that this blocks a KPI. Run an ONA survey of the Sales group, load the edges into NodeXL, and export degree, betweenness, closeness and eigenvector values per person. In Excel, add a 0/1 'left within 6 months' column and run correlations (the book's 17.8 'Predict employee churn with ONA graph metrics'), then a logistic regression on the same metrics (17.9). For a sales-output question, regress each employee's sales on the same network variables (17.10). For a performance question, regress the rating on Social Network Diversity Index, Social Network Size and Skillsets (18.3). Present the NodeXL graph with leavers highlighted, and recommend an intervention from the findings.

• If the outcome is not a real KPI problem for the business unit, stop. Do not run the network analysis, as the source warns against investigating attrition that is not a real problem.
• If the outcome is binary (stayed/left), use logistic regression after the correlations. If it is numeric (sales, rating), use multiple regression.
• If you want to claim diversity of connections matters, compute a Social Network Diversity Index and include Social Network Size and Skillsets as controls in the same regression. Do not read diversity from the picture.
• If the audience is executives, show the NodeXL visual and a one-line people-first finding, not correlation or regression tables.
• If a metric's correlation with the outcome contradicts your hypothesis direction, report it and revisit the hypothesis. Do not quietly swap the story.

• Starting with the tool and not the problem. The tell: a polished network graph and no named KPI or sponsor who asked for it.
• Reading one metric in isolation. The tell: the conclusion cites only degree (or only betweenness), with no comparison to the other centrality metrics, size or diversity.
• Mismatched IDs or timing when joining survey data to HR outcomes. The tell: employees with metrics but no outcome row, or leavers who left before the survey.
• Showing raw regression output to leaders. The tell: the audience asks 'so what?'. The source says to share insights, not raw analysis.
• Treating position as the cause and ignoring other predictors. The tell: no controls such as skillset or network size in the model, even though the source's performance model includes them.

• ▸Business problem and KPI: one sentence naming the outcome and the KPI it blocks.
• ▸Sponsor and agreed scope/timeframe/budget: who approved it and what was agreed.
• ▸Hypothesis per metric: expected direction for degree, betweenness, closeness, eigenvector, size and diversity index.
• ▸ONA survey source: tool used and the question asked (who do you work or interact with).
• ▸Employee ID and edge list location: confirm one consistent ID across survey, NodeXL and HR data.
• ▸Metrics table: per-employee degree, betweenness, closeness, eigenvector, network size, Social Network Diversity Index.
• ▸Outcome column and window: churn (0/1), sales, or performance rating, with the dates measured.
• ▸Test results: correlation first, then logistic or multiple regression, with the controls used.
• ▸Story line and intervention: a people-first finding in one sentence, the visual to show, and the proposed ONA intervention.