Prompt Chain: Merit Cycle Planning from HRIS to Leadership Brief
For Compensation and Benefits Analysts ·
For Compensation and Benefits Analysts
Tools: ChatGPT Plus | Time to build: 2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using AI for Excel work — see Level 2 guide: "Use Excel's Copilot to Build Merit Cycle Formulas"
What This Builds
A repeatable 5-step prompt chain that transforms your annual HRIS data export into a complete merit cycle package: merit modeling outputs, manager communication templates, an executive summary, and a leadership presentation brief. What currently takes 3–4 weeks of back-and-forth and 60+ hours gets compressed into a structured workflow where AI handles the writing and formatting work at each step.
Prerequisites
- ChatGPT Plus subscription (for larger context windows and file uploads, {{tool:ChatGPT.price}}/month)
- Your HRIS merit cycle data export in Excel format (employee, grade, salary, compa-ratio, performance rating, manager)
- Your merit matrix finalized (performance rating x compa-ratio bands = increase %)
- Budget guidance from Finance (total merit budget as % of payroll)
- 2 hours for first-time setup; 30–60 minutes per annual cycle thereafter
The Concept
A prompt chain is like an assembly line for your merit cycle: each prompt takes the output of the previous step and adds the next layer of work. Instead of rebuilding from scratch each year, you follow the same chain, updating inputs at each step. The AI handles the writing; you handle the judgment calls.
Build It Step by Step
Part 1: Prepare Your Input Data
Export your HRIS merit cycle file to Excel. Required columns:
- Employee ID, Name, Department, Manager
- Job Grade, Current Annual Salary, Market Midpoint, Compa-Ratio
- Performance Rating (1–5 scale or equivalent)
- Hire Date, FTE Status (for proration)
- Current Year Base Pay Increase % (if applicable)
Create your merit matrix in a separate Excel tab:
- Rows: performance ratings
- Columns: compa-ratio bands (e.g., <80%, 80–95%, 95–115%, >115%)
- Cells: recommended increase % for each combination
Confirm your total merit budget: e.g., "2.5% of total base payroll"
What you should see: A clean Excel file with employee data and a separate matrix tab. No formulas yet, just data.
Part 2: Chain Step 1 — Build the Merit Model Formulas
Open a new conversation in ChatGPT Plus. Upload your Excel file and send:
I have a merit planning spreadsheet with employee data. I need Excel formulas for the merit cycle.
My data is in Sheet 1. Columns: A=EmpID, B=Name, C=Department, D=Manager, E=Grade, F=Current Salary, G=Market Midpoint, H=Compa-Ratio, I=Performance Rating, J=Hire Date, K=FTE.
My merit matrix is in Sheet 2: performance ratings in column A (rows 2–6 for ratings 1–5), compa-ratio bands in row 1 (columns B–E for <80%, 80–95%, 95–115%, >115%).
Please write formulas for these columns in Sheet 1:
- Column L: Merit Increase % (lookup from matrix using performance rating and compa-ratio)
- Column M: Prorated Increase % (reduce by 50% if hire date is after June 30 of this year)
- Column N: Dollar Increase Amount (Salary x Prorated Increase %)
- Column O: New Salary (Current Salary + Dollar Increase)
- Column P: New Compa-Ratio (New Salary / Market Midpoint)
- Column Q: Budget Flag — if individual increase exceeds 8%, flag "REVIEW"
What you should see: A set of ready-to-paste Excel formulas. Paste them into your spreadsheet, spot-check 3 rows manually.
Part 3: Chain Step 2 — Run the Budget Check
After pasting formulas and calculating your model, share the aggregate results with ChatGPT:
My merit model is complete. Here are the aggregate results:
- Total employees: [N]
- Total current payroll: $[X]
- Total merit budget at 2.5%: $[Y]
- Model total merit spend: $[Z]
- Model as % of payroll: [X]%
- Employees with REVIEW flags: [N]
- Average increase by performance rating: [paste table]
- Average increase by department: [paste table]
Identify any issues with this model and suggest adjustments. Flag if: (1) total spend is over budget, (2) any department or performance group has a disproportionate average increase, (3) the increase distribution is not meaningfully differentiated by performance.
What you should see: A diagnostic of your model: budget variance analysis, distribution check, and specific recommendations if something looks off.
Part 4: Chain Step 3 — Generate Manager Communication Templates
My merit cycle model is finalized. Now generate manager communication templates for 3 scenarios:
SCENARIO A — Employee receiving a merit increase (standard):
"This is to notify you that [Employee Name]'s annual base salary will increase from $[current] to $[new] effective [date], reflecting a [X]% merit increase based on [performance rating] performance. This increase will be reflected in the [pay period] paycheck."
SCENARIO B — Employee below range minimum (structural increase + merit):
Indicate both the structural and merit components; explain the range minimum concept.
SCENARIO C — Employee above range maximum (no increase):
Explain range maximum, acknowledge the situation fairly, include the pay equity and performance recognition alternatives available.
For each template: use placeholders in [brackets], keep to 3 sentences maximum, use professional but direct tone, no HR jargon.
What you should see: Three ready-to-use email templates for your managers. Customize the [Company Name] and [Date] fields.
Part 5: Chain Step 4 — Write the Executive Summary
The merit cycle is complete. Write a 400-word executive summary for the CHRO and CFO summarizing:
Results:
- Total merit spend: $[amount] ([X]% of payroll, budget was [Y]%)
- Employees receiving increases: [N] of [total] eligible ([X]%)
- Average increase: [X]% overall; [breakdown by performance if relevant]
- Pay equity impact: average compa-ratio moved from [X] to [Y]
- Outliers reviewed: [N] REVIEW-flagged cases resolved
Key decisions made during the cycle:
- [Any notable above-range approvals, structural adjustments, exceptions]
Recommendations for next cycle:
- [2-3 bullets based on what you observed]
Tone: factual, financial, board-ready. No narrative fluff.
What you should see: A concise executive summary you can drop directly into your merit cycle close-out memo.
Part 6: Chain Step 5 — Build the Manager FAQ
Based on the merit cycle described above, generate a 10-question FAQ for managers receiving their merit recommendations. Include:
- How the merit increase percentage was determined
- Why some employees received 0% (cap)
- What "compa-ratio" means and why it affects the increase
- How to handle an employee who asks "why did [coworker] get more than me?"
- When increases become effective and when they appear in paychecks
- What to do if they believe the increase is wrong
Keep each answer to 3 sentences. Plain language, no HR jargon.
Real Example: Full Cycle Run
Setup: Finance approved 2.5% merit budget. Your HRIS export has 680 employees. Merit matrix finalized.
Input (Step 1): Upload Excel file → Get formulas → Paste → Spot-check 5 rows Step 2: Share aggregate totals → Model shows 2.6% spend → Adjust one high-increase department down → Recheck Step 3: Generate 3 manager templates → Route to HR Director for tone review Step 4: Paste final totals → Get executive summary → Route to CHRO before leadership meeting Step 5: Generate manager FAQ → Post to SharePoint with manager communications
Time saved: Steps 2–5 previously took 2 days of writing/formatting. Now: 90 minutes.
What to Do When It Breaks
- Formula errors in Excel → Paste the formula back into ChatGPT: "This formula returns a #REF error — fix it." It will diagnose.
- Executive summary sounds generic → Add specific numbers and named departments to the prompt. Generic inputs produce generic outputs.
- Manager FAQ is too long → Add "Limit each answer to 2 sentences" to the prompt.
- Budget calculation is off → Double-check your payroll total input. The AI will calculate correctly if the inputs are correct.
Variations
- Simpler version: Use only Steps 1 and 4 (formula building and executive summary). Skip the manager templates if you have standard language.
- Extended version: Add a Step 6 where ChatGPT generates a department-by-department merit cycle summary for each HR Business Partner to share with their leaders.
What to Do Next
- This week: Run Steps 1–2 as a dry run with last year's data (lower stakes, you know the answer).
- This month: Run the full chain during your actual merit cycle.
- Advanced: Build a SharePoint page with your merit cycle templates, the FAQ output, and a manager communication timeline, updated automatically each year by running the chain.
Advanced guide for compensation and benefits analysts. ChatGPT Plus required for file upload. Never upload documents with full employee names and SSNs. Use anonymized data or aggregate summaries for AI analysis.