Comprehensive HR Management Excel System

I need you to build a complete HR Control Excel System for my company (workforce supply business). The file must be professional, protected, scalable, and ready for daily real use. 1) Main Goals The Excel system must cover: Employee master database (local + foreign) Daily attendance (by employee) Working hours (daily) Project-level productivity (actual vs expected hours) Compliance tracking for foreign employees (Iqama + Work Permit expiry) Management dashboard + alerts Daily PDF attendance report (end of day) No complex macros unless absolutely necessary. Prefer formula-based solution compatible with Excel 365. 2) Required Sheets (Mandatory) A) Employees_Master (Database) Primary key: Employee_ID (unique) Required columns: Employee_ID Employee_Name Nationality (or Employee_Type: Local/Foreign) Project (dropdown list) Job_Title Supervisor Status (Active / On Hold / Left) Foreign compliance fields (mandatory for foreign employees): Iqama_Expiry_Date Work_Permit_Expiry_Date Iqama_Days_Remaining (calculated) Work_Permit_Days_Remaining (calculated) Renewal_Priority (calculated) Optional workflow: Renewal_Owner Renewal_Status (Not Started / In Progress / Submitted / Completed) Renewal_Last_Updated Validation: Project/Status dropdowns Date validation for expiry dates Highlight foreign Active employees with missing expiry dates Protection: This sheet must be protected (admin-only editing). B) Daily_Attendance (Supervisor Entry) This is the daily input sheet. Required columns: Date (fixed, not dynamic) Employee_ID (validated) Employee_Name (auto-filled) Project (auto-filled) Attendance_Status (Present / Absent) Absence_Reason (optional) Working_Hours (numeric) Rules: Working_Hours must be numeric between 0 and 12 If Absent → Working_Hours must be 0 (enforced or flagged) Data entry allowed for TODAY only (no past dates) Past data must not be editable Protect sheet so only entry cells are editable End-of-day process: Supervisor exports a PDF attendance report (with date in header) Naming format: [login to view URL] C) Settings (Configuration) Must include: Project list (dropdown source) Headcount per project Absence threshold per project Standard daily hours (default = 8) Thresholds for renewal alerts No hard-coded values inside formulas — everything referenced from Settings. Projects: Fadhili Project (Headcount: 31) DAL ARABIA (Headcount: 5) ENPPI (Headcount: 4) D) Dashboard (Management View) This is the decision-making dashboard. Section 1: Daily KPI Cards Current date Total Active headcount Total Present today Total Absent today Total working hours today (all projects) Section 2: Project Comparison Table Per project: Headcount Present Absent Missing records = Headcount - (Present + Absent) Total working hours (actual) Expected hours = Headcount × Standard Hours Productivity % = Actual / Expected Conditional formatting: Green/Yellow/Red based on thresholds Section 3: Compliance (Iqama + Work Permit) KPI cards: Total foreign active employees Expiring within 30 days (Iqama) Expiring within 30 days (Work Permit) Expired (Iqama or Work Permit) Next 7 days urgent list Table: Employee_ID, Name, Project, Supervisor Iqama expiry + days remaining Work permit expiry + days remaining Renewal Priority Renewal Status / Owner (if included) Sorted by most urgent (Expired first, then lowest days remaining) Renewal Thresholds: OK (Green): 91+ days Monitor (Blue/Gray): 61–90 days Warning (Yellow): 31–60 days Critical (Red): 1–30 days Expired (Dark Red): 0 or negative Renewal Priority logic: If either doc expired → EXPIRED Else if either ≤ 30 → CRITICAL Else if 31–60 → WARNING Else if 61–90 → MONITOR Else OK Section 4: Alerts Box Automated alerts such as: High absence above threshold per project Low productivity today Missing attendance entries today Foreign employee missing expiry dates Expired/critical renewals list 3) Protection & User Roles Admin-only sheets: Employees_Master, Settings (protected) Supervisor sheet: Daily_Attendance (protected except entry fields) Dashboard: view-only (protected) 4) Deliverables A complete working Excel file with all sheets above Clean formatting + professional layout All dropdowns, validations, calculations and conditional formatting working A short “How to Use” note inside the file (simple instructions) 5) Acceptance Criteria (How I will test) TODAY-based metrics update automatically Attendance entry is restricted to today only Project productivity calculations match inputs Renewal days remaining and priority are correct Alerts show correctly based on thresholds PDF export includes date header and correct print area Please confirm: Estimated cost and delivery time Whether you will use formulas only or any macros (preferred: formulas only) Thanks,

Projektin tunnus (ID): 40189811