# Forecaster Workforce Planning - HR Integration

**Date:** 2026-01-25  
**Status:** ✅ Complete  
**Feature:** Workforce Planning & Salary Impact Analysis integrated with HR data

---

## Overview

The Forecaster module now includes **Workforce Planning** and **Salary Impact Analysis** features that pull real employee data from the HR system and project it forward based on growth assumptions.

**Key Decision:** Implemented **Option A - Pull from Actual HR Data** instead of manual entry or hybrid approach.

---

## Features Implemented

### 1. Workforce Planning Summary
**URL:** `/forecaster/workforce-summary`  
**Permission:** `forecasting.view`

**Functionality:**
- Displays current workforce snapshot from HR system (active employees)
- Projects headcount and labor costs across forecast periods
- Supports compound annual growth rates for headcount and salaries
- Shows scenario comparison and period-by-period breakdown

**Inputs:**
- Forecast selection (dropdown)
- Headcount growth rate % (annual, can be negative)
- Salary growth rate % (annual, can be negative)

**Displays:**
1. **Current Workforce Snapshot**
   - Total employees (from `employees` table)
   - Total annual labor cost
   - Average annual salary
   - Employees with salary data

2. **Scenario Comparison Table**
   - All scenarios for selected forecast
   - Average headcount across periods
   - Total projected labor cost
   - Number of periods

3. **Period-by-Period Breakdown**
   - Projected headcount per period
   - Projected labor cost per period
   - Revenue from forecast
   - Labor cost as % of revenue

### 2. Salary Impact Analysis
**URL:** `/forecaster/salary-impact`  
**Permission:** `forecasting.view`

**Functionality:**
- Analyzes impact of salary increases/decreases on financial scenarios
- Compares baseline (no change) vs. projected (with salary change)
- Shows effect on net income and profit margins

**Inputs:**
- Forecast selection (dropdown)
- Salary increase % (can be negative for cuts)

**Displays:**
1. **Current Workforce Baseline** (same as Workforce Summary)

2. **Impact Summary Cards**
   - Total baseline labor cost
   - Total projected labor cost
   - Total impact amount
   - Number of scenarios analyzed

3. **Scenario Comparison Table**
   - Baseline vs. projected labor costs per scenario
   - Impact on net income
   - % change in net income

4. **Period-by-Period Impact**
   - Baseline labor cost (no increase)
   - Projected labor cost (with increase)
   - Impact amount per period
   - Effect on profit margins (revenue - labor - COGS - opex)

---

## Technical Implementation

### Database Integration

**No new tables required!** The system queries existing tables:

**Primary Data Source:**
```sql
SELECT 
    COUNT(*) as total_headcount,
    SUM(CASE 
        WHEN salary_type = 'hourly' THEN salary * 2080 
        WHEN salary_type = 'monthly' THEN salary * 12
        WHEN salary_type = 'yearly' THEN salary
        ELSE 0
    END) as total_annual_cost
FROM employees
WHERE status = 'active' AND deleted_at IS NULL
```

**Salary Conversion Logic:**
- Hourly → Annual: `salary * 2080 hours` (40 hrs/week × 52 weeks)
- Monthly → Annual: `salary * 12`
- Yearly → Annual: `salary` (no conversion)

### Controller Methods

**File:** `controllers/ForecasterController.php`

#### New Methods:

**1. `getCurrentWorkforceData()` (private)**
- Queries `employees` table for active employees
- Calculates total headcount, annual labor cost, average salary
- Gets department breakdown
- Returns array with current workforce metrics

**2. `projectWorkforceByPeriods($periods, $currentWorkforce, $headcountGrowthRate, $salaryGrowthRate)` (private)**
- Projects workforce data across forecast periods
- Applies compound growth: `base * (1 + rate/100)^years`
- Pro-rates labor cost by period length (monthly/quarterly)
- Returns array of periods with `headcount` and `labor_cost` fields

#### Updated Methods:

**3. `workforceSummary()` (public)**
- Gets current workforce from HR system
- Projects across forecast periods with growth rates
- Builds workforce data for each scenario
- Passes data to view

**4. `salaryImpact()` (public)**
- Gets current workforce from HR system
- Projects baseline (0% growth) and projected (with salary increase)
- Calculates period-by-period comparison
- Shows impact on net income per scenario

---

## Projection Algorithm

### Compound Growth Formula

**Headcount Projection:**
```
projected_headcount = current_headcount × (1 + headcount_growth_rate/100)^years_from_start
```

**Labor Cost Projection:**
```
projected_annual_cost = current_annual_cost × (1 + salary_growth_rate/100)^years_from_start
period_labor_cost = (projected_annual_cost / 12) × period_length_months
```

### Example Calculation

**Current State:**
- 7 employees
- $76.50 average annual salary
- Total annual cost: $535.50

**Assumptions:**
- 10% annual headcount growth
- 5% annual salary growth
- Quarterly forecast (3-month periods)

**Period 1 (Q1 - months 0-3):**
- Years from start: 0
- Headcount: 7 × (1.10)^0 = 7.0
- Annual cost: $535.50 × (1.05)^0 = $535.50
- Period cost: ($535.50 / 12) × 3 = $133.88

**Period 2 (Q2 - months 3-6):**
- Years from start: 0.25
- Headcount: 7 × (1.10)^0.25 = 7.17
- Annual cost: $535.50 × (1.05)^0.25 = $542.00
- Period cost: ($542.00 / 12) × 3 = $135.50

**Period 5 (Q1 Year 2 - months 12-15):**
- Years from start: 1.0
- Headcount: 7 × (1.10)^1.0 = 7.7
- Annual cost: $535.50 × (1.05)^1.0 = $562.28
- Period cost: ($562.28 / 12) × 3 = $140.57

---

## Files Modified/Created

### Modified Files:
1. **`controllers/ForecasterController.php`**
   - Added `getCurrentWorkforceData()` method (lines 1480-1540)
   - Added `projectWorkforceByPeriods()` method (lines 1542-1586)
   - Updated `workforceSummary()` method (lines 1306-1379)
   - Updated `salaryImpact()` method (lines 1381-1477)

2. **`views/forecaster/workforce_summary.php`**
   - Added growth rate inputs (headcount & salary)
   - Added current workforce snapshot card
   - Updated to display projected data from HR integration

3. **`views/forecaster/salary_impact.php`**
   - Added current workforce baseline card
   - Updated period comparison to use baseline vs. projected
   - Changed labels from "Current" to "Baseline"

### Routes (already existed):
- `GET /forecaster/workforce-summary` → `ForecasterController@workforceSummary`
- `GET /forecaster/salary-impact` → `ForecasterController@salaryImpact`

### Menu Items (already existed):
- ID 462: "Workforce Planning" (`forecaster/workforce-summary`)
- ID 463: "Salary Impact Analysis" (`forecaster/salary-impact`)

---

## Use Cases

### 1. Annual Budget Planning
**Scenario:** CFO needs to forecast labor costs for next 3 years

**Steps:**
1. Navigate to Forecaster → Workforce Planning
2. Select "2026-2028 Budget" forecast
3. Enter 5% headcount growth, 3% salary growth
4. View projected quarterly labor costs
5. Compare against revenue projections

### 2. Salary Negotiation Analysis
**Scenario:** CEO considering 7% across-the-board raise

**Steps:**
1. Navigate to Forecaster → Salary Impact Analysis
2. Select current year forecast
3. Enter 7% salary increase
4. Review impact on net income by scenario
5. See effect on profit margins by quarter

### 3. Hiring Plan Modeling
**Scenario:** Planning to grow team by 15% next year

**Steps:**
1. Navigate to Forecaster → Workforce Planning
2. Select next year forecast
3. Enter 15% headcount growth, 4% salary growth
4. View cash flow impact by period
5. Ensure revenue supports increased labor costs

### 4. Cost Reduction Planning
**Scenario:** Need to reduce expenses by 10%

**Steps:**
1. Navigate to Forecaster → Workforce Planning
2. Select current forecast
3. Enter -10% headcount growth (reduction)
4. View savings by period
5. Model impact on operations

---

## Data Flow

```
┌─────────────────────┐
│  employees table    │
│  (HR System)        │
│  - status='active'  │
│  - salary           │
│  - salary_type      │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────────────────┐
│  getCurrentWorkforceData()      │
│  - Query active employees       │
│  - Convert salaries to annual   │
│  - Calculate totals & averages  │
└──────────┬──────────────────────┘
           │
           ▼
┌─────────────────────────────────┐
│  projectWorkforceByPeriods()    │
│  - Apply compound growth        │
│  - Calculate per-period costs   │
│  - Pro-rate by period length    │
└──────────┬──────────────────────┘
           │
           ▼
┌─────────────────────────────────┐
│  View (workforce_summary.php)   │
│  - Display current snapshot     │
│  - Show scenario comparison     │
│  - Period-by-period breakdown   │
└─────────────────────────────────┘
```

---

## Future Enhancements

### Potential Improvements:
1. **Department-Level Projections**
   - Allow different growth rates per department
   - Show department breakdown in projections

2. **Position-Level Analysis**
   - Project by position/role
   - Model specific hiring plans (e.g., "Add 3 engineers in Q2")

3. **Benefits & Taxes**
   - Include employer taxes (FICA, unemployment)
   - Add benefits costs (health insurance, 401k match)
   - Total compensation vs. base salary

4. **Historical Trending**
   - Show actual historical headcount growth
   - Suggest growth rates based on past trends

5. **Hiring Timeline**
   - Model specific hire dates
   - Ramp-up periods for new employees

6. **Attrition Modeling**
   - Factor in expected turnover rate
   - Replacement hiring costs

7. **Export to Excel**
   - Download projections as spreadsheet
   - Pivot tables for analysis

---

## Testing Notes

**Test Data:**
- 7 active employees in system
- 2 employees with salary data ($30 and $123)
- Average salary: $76.50

**Test Scenarios:**
1. ✅ Workforce Summary loads without errors
2. ✅ Current workforce data displays correctly
3. ✅ Growth rate inputs accept positive/negative values
4. ✅ Projections calculate with compound growth
5. ✅ Salary Impact shows baseline vs. projected
6. ✅ Period-by-period breakdown displays correctly

**Known Limitations:**
- Only 2 of 7 employees have salary data (others show $0)
- Recommend populating salary data for all employees for accurate projections

---

## Maintenance

**Dependencies:**
- `employees` table must have `status`, `salary`, `salary_type` fields
- `departments` table for department breakdown
- `forecaster_periods` table for forecast periods
- `forecaster_scenarios` table for scenario data

**Performance:**
- Queries are lightweight (simple aggregations)
- No N+1 query issues
- Suitable for up to 1000+ employees

**Security:**
- Requires `forecasting.view` permission
- Only CEO, CFO, CTO roles have access
- No salary data exposed to unauthorized users

---

## Related Documentation

- `/forecaster/README.md` - Forecaster module overview
- `/forecaster/IMPLEMENTATION_STATUS.md` - Implementation status
- `/docs/Forecaster Setup/FORECASTER_COMPLETION.md` - Setup guide
- `/HR_TIME_TRACKING_IMPLEMENTATION.md` - HR system documentation

---

**Implementation completed:** 2026-01-25  
**Implemented by:** Augment Agent  
**Approved by:** Rick M (CTO)