HR Dashboard in Excel: Turnover, Headcount, and Absenteeism
ESCOPO
This prompt turns raw HR data into a complete plan for building a corporate dashboard in Microsoft Excel. It guides the creation of the sheet architecture, data standardization, indicator calculations, benchmark comparisons, and analysis by area, period, location, and employee profile.
Ideal for analysts, business partners, coordinators, and HR managers who need to deliver a reliable executive view of turnover, headcount, and absenteeism. The result provides instructions directly applicable in Excel, including formulas in Portuguese, Power Query, a pivot table model, charts, slicers, quality rules, and optional VBA.
The prompt also requests a management narrative and a slide structure for PowerPoint, making it possible to turn the dashboard into a concise presentation for leadership and people committees.
Conteudo
Prompt principal
Act as a Senior Specialist in People Analytics, HR Controlling, and corporate Microsoft Excel. Your mission is to design a robust, audit-ready HR dashboard ready for implementation in Excel, focused on turnover, headcount, and absenteeism, using exclusively the data and business rules I provide. Before developing the solution, analyze the information below. If any critical field is missing, do not invent assumptions: first present a section called "Gaps and validation questions", prioritizing what prevents the correct calculation of the indicators. Then provide a functional proposal with assumptions explicitly identified as provisional. DATA AND CONTEXT TO BE PASTED BY THE USER: 1. Dashboard objective, target audience (operational HR, leadership, board, etc.), update frequency, and analysis period. 2. Targets, benchmarks, or expected ranges for turnover, absenteeism, and headcount. 3. Internal definitions: desired turnover (total, voluntary, involuntary, regrettable, monthly or cumulative), headcount rule (month-end, monthly average, FTE), and absenteeism rule (hours/days lost, absences included or excluded). 4. Structure and sample of the available data sources, including exact column names and 10 to 30 anonymized rows when possible. Example fields: employee ID, hire date, termination date, termination type/reason, area, directorate, location, job title, manager, sex, age range, reference date, status, scheduled hours, absent hours, absence type, and business days. 5. Excel version, formula language (pt-BR or en-US), permitted use of Power Query, Power Pivot/Data Model and VBA, plus confidentiality restrictions. Follow this working method exactly: STEP 1 — Diagnosis and data model design. Identify the necessary fact tables and dimensions, the granularity of each one, and the recommended relationship key. Suggest a naming convention for structured tables, such as tb_Colaboradores, tb_Movimentacoes, tb_Ausencias, tb_Calendario, and tb_Metas. Explain how to avoid duplicate employees, overlapping periods, and retroactive termination errors. STEP 2 — Excel file architecture. Propose the exact sheet structure, with the purpose and contents of each sheet. Include, at minimum: 00_ReadMe, 01_Parameters, 02_Employee_Base, 03_Transactions_Base, 04_Absences_Base, 05_Calendar, 06_Calculations, 07_Pivot_Tables, 08_Dashboard, and 09_Quality. For each sheet, specify tables, fields, data validations, helper columns, and what should be protected from editing. STEP 3 — Ready-to-use calculations. Provide complete formulas, using structured references and column names consistent with the provided data. Include formulas for: initial, final, and average headcount; hires; total, voluntary, and involuntary terminations; total and by-type turnover; scheduled hours; lost hours/days; absenteeism rate; comparison against target; monthly variance; year-to-date cumulative; area ranking; and trend flagging. Indicate the destination cell/column, the formula, and a brief explanation. If the solution requires DAX, also provide the DAX measures and clearly state the necessary relationships. STEP 4 — Executive dashboard. Define a block-based layout, indicating approximate position, KPI, data source, formula/measure, chart type, and expected management message. Include cards for current headcount, variance versus previous month, monthly and YTD turnover, monthly and YTD absenteeism, hires, terminations, and target attainment. Recommend suitable charts, such as a line chart for monthly trend, bar charts for areas, a waterfall chart for headcount movement, and a heat map for absenteeism. Specify recommended slicers: period, directorate, area, location, job title, manager, and termination type, depending on availability. STEP 5 — Automation and refresh. Create a step-by-step Power Query process to import, type, clean, standardize, and consolidate files. If VBA is allowed, provide a complete, commented, and safe macro to refresh queries, pivot tables, the refresh date, and export the dashboard as PDF. Include instructions on where to insert the code, how to run it, and what permissions are required. Do not use VBA if Power Query solves the problem better; explain the decision. STEP 6 — Quality, audit, and narrative. Create reconciliation tests such as initial headcount + hires - terminations = final headcount, absence not exceeding scheduled hours, and identification of duplicate IDs. List visual alerts, acceptable thresholds, and handling of missing data. Finally, deliver a 5-slide PowerPoint structure with title, key message, recommended visual, and management questions to be answered. MANDATORY RESPONSE FORMAT: use numbered headings; tables in Markdown for architecture, formulas, KPIs, and tests; separate code blocks for VBA, Power Query M, and DAX; and a final section called "Implementation checklist" in execution order. Use objective, technical, and actionable language. Do not deliver generic recommendations, incomplete formulas, invented numbers, or charts without a decision-making purpose. Prioritize traceability, maintenance by non-technical users, and compatibility with corporate Excel.
