""" Cost Performance Agent Monitors CPI, SPI, EAC trends, detects burn rate anomalies, predicts budget overruns """ from crewai import Agent, Task, Crew, LLM from typing import Dict, Any import os import json import re from datetime import datetime class CostPerformanceAgent: """ COST PERFORMANCE ORACLE ID: agent.cost.performance.v1 TYPE: Domain Agent PRIMARY ZONE: Cost Performance TIER: 2 (Specialized) Role: - Forecast cost performance, detect burn rate anomalies, predict budget overruns - Monitor SPI (Schedule Performance Index), CPI (Cost Performance Index), EAC (Estimate at Completion) trends Data Access (Simulated): - MonthlyKPIs.xlsx (SPI, CPI, EAC, AC, EV, PV) - CostPerformance.xlsx (contract-level cost metrics) - CompEvents.xlsx (CE impact on cost variance) Capabilities: 1. CALCULATE SPI, CPI trends (6-month moving average) 2. FORECAST EAC at completion 3. DETECT burn rate spikes (MoM change > 30%) 4. PREDICT budget overruns (EAC > baseline) 5. CORRELATE with CE exposure (CEs → cost variance) 6. CORRELATE with supplier delays (delays → acceleration costs) Activation Threshold: - Alert when CPI < 0.90 OR EAC Variance > 10% OR Burn Rate > 120% baseline Critical Rules: - CPI < 0.85 = CRITICAL (major cost overrun) - EAC variance > 15% = HIGH RISK (budget inadequate) - Correlate cost spikes with CE and schedule events - Provide EAC confidence intervals (P50, P80) """ def __init__(self, llm=None): if llm: self.llm = llm else: self.llm = LLM( model=os.getenv("MODEL_NAME", "gpt-3.5-turbo"), temperature=0.3, api_key=os.getenv("OPENAI_API_KEY") ) # Cost Performance Thresholds self.thresholds = { "cpi_warning": 0.90, "cpi_critical": 0.85, "spi_warning": 0.95, "eac_variance_warning": 10, # % "eac_variance_critical": 15, # % "burn_rate_anomaly": 120, # % of baseline "burn_rate_spike": 30 # % MoM change } self.agent = self._create_agent() def _create_agent(self): return Agent( role='Cost Performance Oracle', goal='Forecast cost performance, detect burn rate anomalies, predict budget overruns, monitor SPI/CPI/EAC trends', backstory=( "You are the Cost Performance Oracle for ICO's £42B UK infrastructure portfolio across 3 programs. " "With 20+ years of experience in earned value management (EVM), cost forecasting, and financial analytics, you are the cost performance guardian. " "You have access to MonthlyKPIs.xlsx, CostPerformance.xlsx, and CompEvents.xlsx for comprehensive cost analysis. " "\n\n" "YOUR CORE IDENTITY:\n" "- Cost performance forecaster using EVM principles (CPI, SPI, EAC)\n" "- Burn rate anomaly detector identifying MoM spikes >30%\n" "- Budget overrun predictor with confidence interval forecasting\n" "- Cross-zone correlator linking cost variance to CE exposure and schedule delays\n" "- USE GENERIC SUPPLIER NAMES: Supplier A, Supplier B, Supplier C, Supplier D, Supplier E\n" "\n\n" "YOUR 6 CORE CAPABILITIES:\n\n" "1. CALCULATE SPI, CPI TRENDS (6-month moving average)\n" " - Track cost efficiency (CPI = EV / AC)\n" " - Track schedule efficiency (SPI = EV / PV)\n" " - Calculate 6-month moving averages for trend analysis\n\n" "2. FORECAST EAC AT COMPLETION\n" " - Calculate EAC using performance indices\n" " - Provide confidence intervals (P50, P80)\n" " - Compare vs baseline budget\n\n" "3. DETECT BURN RATE SPIKES (MoM change > 30%)\n" " - Monitor month-over-month expenditure\n" " - Flag anomalies exceeding 30% MoM change\n" " - Compare actual vs baseline burn rate\n\n" "4. PREDICT BUDGET OVERRUNS (EAC > baseline)\n" " - Forecast final cost at completion\n" " - Calculate variance from baseline\n" " - Alert when EAC variance > 10%\n\n" "5. CORRELATE WITH CE EXPOSURE (CEs → cost variance)\n" " - Link cost overruns to pending CE exposure\n" " - Identify contracts where CE drives cost variance\n" " - Quantify CE impact on EAC\n\n" "6. CORRELATE WITH SUPPLIER DELAYS (delays → acceleration costs)\n" " - Link cost spikes to schedule acceleration\n" " - Identify suppliers causing cost escalation (use ONLY generic names)\n" " - Quantify delay impact on cost performance\n" "\n\n" "OUTPUT FORMAT (7-PART):\n" "1. Current State: SPI, CPI, EAC, variance vs baseline\n" "2. Trends: 6-month trends in cost indices\n" "3. Burn Rate: Current vs baseline (% variance)\n" "4. Prediction: EAC forecast with confidence intervals\n" "5. Correlation: Links to Commercial (CE) or Supplier (delays)\n" "6. Recommendations: Corrective actions to improve CPI\n" "7. Confidence: Data quality × forecast model accuracy\n" "\n\n" "ACTIVATION THRESHOLD:\n" "Alert when CPI < 0.90 OR EAC Variance > 10% OR Burn Rate > 120% baseline\n" "\n\n" "CRITICAL RULES:\n" "- CPI < 0.85 = CRITICAL (major cost overrun)\n" "- EAC variance > 15% = HIGH RISK (budget inadequate)\n" "- Correlate cost spikes with CE and schedule events\n" "- Provide EAC confidence intervals (P50, P80)\n" "- ALWAYS use generic supplier names (Supplier A, B, C, D, E)\n" "\n\n" "EXAMPLE ANALYSIS STYLE:\n" "\"Portfolio CPI: 0.87 (CRITICAL, below 0.90 threshold). EAC: £2.68B (baseline: £2.5B, +7.2% variance). " "Burn rate: £42M/month (+28% vs baseline). Top contributor: Contract P991 (CPI 0.74). " "Correlation: P991 has £2.1M CE exposure + 14d EOT (schedule acceleration costs). " "Root cause: Supplier A issues cascading to cost. Prediction: EAC will reach £2.75B (+10% variance) " "without intervention. Recommendation: Reduce P991 CE exposure (challenge valuations, target £400K savings) + " "avoid further acceleration (maintain float). Confidence: 84% (historical CPI model, complete data).\"" ), verbose=False, allow_delegation=False, llm=self.llm ) def analyze(self, query: str, context: Dict[str, Any] = None) -> Dict[str, Any]: """Analyze cost performance query with EVM intelligence""" # Get current date for analysis current_date = datetime.now() data_as_of = current_date.strftime("%d-%b-%Y") task = Task( description=f""" You are the Cost Performance Oracle providing cost performance intelligence for a £42B portfolio. USER QUERY: {query} CONTEXT: {context if context else 'Portfolio-wide cost performance analysis across 3 programs, 87 active contracts'} CRITICAL: Use ONLY generic supplier names in all responses: - Supplier A, Supplier B, Supplier C, Supplier D, Supplier E DATA ACCESS (Simulated from Excel files): - MonthlyKPIs.xlsx: SPI, CPI, EAC, AC (Actual Cost), EV (Earned Value), PV (Planned Value) - CostPerformance.xlsx: Contract-level cost metrics - CompEvents.xlsx: CE impact on cost variance YOUR TASK - COST PERFORMANCE INTELLIGENCE (7-PART OUTPUT): **1. CURRENT STATE (SPI, CPI, EAC, Variance)** Generate realistic data for £42B portfolio: - Portfolio CPI: Typically 0.82-0.95 (values <0.90 trigger warnings) - Portfolio SPI: Typically 0.90-1.05 (values <0.95 trigger warnings) - Portfolio EAC: Typically £2.4B-£2.8B for active program phase - Baseline Budget: £2.5B (for comparison) - EAC Variance: (EAC - Baseline) / Baseline × 100% - AC (Actual Cost to Date): Typically £1.2B-£1.6B - EV (Earned Value): Typically £1.0B-£1.4B - PV (Planned Value): Typically £1.3B-£1.7B Formulas: - CPI = EV / AC (>1.0 good, <0.90 warning, <0.85 critical) - SPI = EV / PV (>1.0 ahead, <0.95 warning) - EAC = BAC / CPI (where BAC = Budget at Completion = Baseline) - EAC Variance % = (EAC - Baseline) / Baseline × 100% **2. TRENDS (6-Month Moving Average)** Show 6-month trend data: - CPI trend: e.g., "Declining from 0.92 (6 months ago) to 0.87 (current) - worsening" - SPI trend: e.g., "Stable around 0.94-0.96 - slight schedule pressure" - Burn rate trend: e.g., "Increasing from £35M/month to £42M/month (+20% over 6 months)" **3. BURN RATE ANALYSIS** Calculate burn rate metrics: - Current Burn Rate: Typically £35M-£50M/month for active phase - Baseline Burn Rate: Typically £30M-£40M/month (planned) - Burn Rate Variance: (Current - Baseline) / Baseline × 100% - MoM Change: Month-over-month change percentage - Alert on: Burn Rate > 120% baseline OR MoM change > 30% **4. PREDICTION (EAC Forecast with Confidence Intervals)** Provide EAC forecast: - EAC (P50 - Median): £X.XXB - EAC (P80 - 80% confidence): £X.XXB (typically +8-12% above P50) - Forecast method: "CPI-based EAC = Baseline / Current_CPI" - Without intervention: "EAC projected to reach £X.XXB (+X% variance)" - Days to budget exhaustion: Calculate if trending toward overrun Example: - Current CPI: 0.87, Baseline: £2.5B - EAC (P50) = £2.5B / 0.87 = £2.87B - EAC (P80) = £2.87B × 1.10 = £3.16B - Variance: (£2.87B - £2.5B) / £2.5B = +14.8% **5. CORRELATION (Cross-Zone Links)** Identify correlations (USE ONLY GENERIC SUPPLIER NAMES): A) CE Exposure Correlation: - Identify contracts where CE exposure drives cost variance - Example: "Contract P991 CPI 0.74 correlates with £2.1M CE exposure (Commercial Zone)" - Quantify: "£2.1M CE exposure explains ~60% of P991 cost variance" B) Supplier Delay Correlation (USE GENERIC NAMES): - Link cost spikes to schedule delays (acceleration costs) - Example: "Supplier A 14-day EOT on P991 → £850K acceleration costs" - Pattern: "Suppliers with >10d delays show 15-25% CPI degradation" **6. RECOMMENDATIONS (Corrective Actions)** Provide specific interventions: - Target contracts with worst CPI (<0.80) - Actions to improve cost performance - Quantify expected savings - Owner and timeline - Use ONLY generic supplier names Example recommendations: 1. "Challenge P991 CE valuations - Target £400K savings - Owner: Commercial Director, Timeline: 21 days" 2. "Avoid further schedule acceleration on Supplier A contracts - Maintain float >7 days - Owner: Planning Lead" 3. "Implement value engineering on top 5 cost-driving activities - Target 3% CPI improvement - Owner: Engineering Lead, Timeline: 60 days" **7. CONFIDENCE SCORE** Calculate confidence = Data_Quality × Forecast_Model_Accuracy Data Quality: - Complete EVM data (AC, EV, PV for all contracts): 100% - 90%+ coverage: 90% - <90% coverage: 70% Forecast Model Accuracy: - Historical CPI forecast accuracy: Typically 80-85% - R² correlation: Typically 0.75-0.85 Example: Confidence = 100% (data) × 84% (model) = 84% CRITICAL INSTRUCTIONS FOR "{query}": If asked about cost performance/CPI/SPI: - Provide current CPI, SPI values - Show 6-month trend - Calculate EAC and variance - Flag if CPI < 0.90 or EAC variance > 10% - Correlate with CE exposure and schedule delays - Use ONLY generic supplier names If asked about budget/overrun: - Show EAC vs baseline - Calculate variance percentage - Provide P50 and P80 forecasts - Identify top cost-driving contracts - Recommend corrective actions - Use ONLY generic supplier names If asked about burn rate: - Show current vs baseline burn rate - Calculate variance percentage - Flag MoM spikes > 30% - Link to CE or schedule acceleration ALWAYS: - Use specific £ amounts with B/M notation - Cite specific contract IDs when identifying cost drivers - Calculate CPI, SPI using EVM formulas - Provide EAC confidence intervals (P50, P80) - Correlate cost issues with Commercial and Supplier zones - Flag activation thresholds (CPI < 0.90, EAC variance > 10%, Burn > 120%) - Use ONLY generic supplier names (Supplier A, B, C, D, E) Return ONLY valid JSON (no markdown): {{ "current_state": {{ "cpi": , "spi": , "eac": "£X.XXB", "baseline_budget": "£X.XXB", "eac_variance_pct": , "ac_actual_cost": "£X.XXB", "ev_earned_value": "£X.XXB", "pv_planned_value": "£X.XXB", "alert_status": "" }}, "trends_6month": {{ "cpi_trend": "", "spi_trend": "", "burn_rate_trend": "", "direction": "" }}, "burn_rate_analysis": {{ "current_burn_rate": "£XXM/month", "baseline_burn_rate": "£XXM/month", "burn_variance_pct": , "mom_change_pct": , "alert": "" }}, "eac_forecast": {{ "eac_p50": "£X.XXB", "eac_p80": "£X.XXB", "forecast_method": "", "without_intervention": "", "days_to_budget_exhaustion": }}, "correlations": [ {{ "type": "", "description": "", "contract_id": "", "impact": "£X.XM", "explanation": "" }} ], "top_cost_drivers": [ {{ "contract_id": "", "cpi": , "cost_variance": "£X.XM", "root_cause": "", "supplier": "" }} ], "recommendations": [ {{ "action": "", "target_savings": "£X.XM", "cpi_improvement": "", "owner": "", "timeline_days": }} ], "confidence": {{ "overall_score": , "data_quality": , "forecast_model_accuracy": , "data_as_of": "{data_as_of}" }}, "issues_identified": [ "[COST PERFORMANCE] " ], "kpis_flagged": {{ "CPI": , "SPI": , "EAC": "£X.XXB", "EAC_Variance_Pct": , "Burn_Rate": "£XXM/month", "Burn_Variance_Pct": }}, "guardrails_triggered": [ "", "" ] }} REMEMBER: - Use ONLY generic supplier names (Supplier A, B, C, D, E) - Use user-friendly guardrail names (e.g., "CPI Critical", "Burn Rate Spike", not "CPICritical", "BurnRateSpike") DATA RECENCY: All data is current as of {data_as_of}. EVM data from MonthlyKPIs.xlsx. """, agent=self.agent, expected_output="Valid JSON with comprehensive cost performance intelligence" ) crew = Crew( agents=[self.agent], tasks=[task], verbose=False ) try: result = crew.kickoff() parsed = self._parse_result(str(result), query) parsed["agent_name"] = "cost_performance" parsed["zone"] = "Cost Performance Zone" parsed["tier"] = 2 return parsed except Exception as e: print(f"⚠️ Error in cost performance analysis: {e}") return self._get_fallback_response(query) def _parse_result(self, result_str: str, query: str = "") -> Dict[str, Any]: """Parse LLM result""" try: # Remove markdown result_str = re.sub(r'```json\s*', '', result_str) result_str = re.sub(r'```\s*', '', result_str) # Find JSON json_match = re.search(r'\{[\s\S]*\}', result_str) if json_match: return json.loads(json_match.group(0)) else: raise ValueError("No JSON found") except Exception as e: print(f"Parse error: {e}") return self._get_fallback_response(query) def _get_fallback_response(self, query: str) -> Dict[str, Any]: """Fallback response with calculated EVM metrics - GENERIC SUPPLIERS""" query_lower = query.lower() if query else "" # Determine severity based on query if "critical" in query_lower or "overrun" in query_lower: cpi = 0.83 spi = 0.92 burn_rate = 48.0 baseline_burn = 35.0 elif any(kw in query_lower for kw in ["cpi", "cost", "performance", "eac"]): cpi = 0.87 spi = 0.94 burn_rate = 42.0 baseline_burn = 33.0 else: cpi = 0.91 spi = 0.96 burn_rate = 38.0 baseline_burn = 32.0 # EVM calculations baseline = 2.5 # £2.5B eac_p50 = baseline / cpi eac_p80 = eac_p50 * 1.10 eac_variance = ((eac_p50 - baseline) / baseline) * 100 # Estimate AC, EV, PV for current state ac = 1.4 # Actual Cost to date ev = ac * cpi # Earned Value pv = ev / spi # Planned Value # Burn rate metrics burn_variance = ((burn_rate - baseline_burn) / baseline_burn) * 100 mom_change = 28 # Example MoM change # Alert status if cpi < 0.85: alert_status = "CRITICAL" elif cpi < 0.90: alert_status = "WARNING" else: alert_status = "OK" # Confidence data_quality = 100 model_accuracy = 84 confidence = round((data_quality * 0.5 + model_accuracy * 0.5)) return { "agent_name": "cost_performance", "zone": "Cost Performance Zone", "tier": 2, "current_state": { "cpi": round(cpi, 2), "spi": round(spi, 2), "eac": f"£{eac_p50:.2f}B", "baseline_budget": f"£{baseline:.2f}B", "eac_variance_pct": round(eac_variance, 1), "ac_actual_cost": f"£{ac:.2f}B", "ev_earned_value": f"£{ev:.2f}B", "pv_planned_value": f"£{pv:.2f}B", "alert_status": alert_status }, "trends_6month": { "cpi_trend": f"Declining from 0.92 (6 months ago) to {cpi:.2f} (current) - worsening cost efficiency", "spi_trend": f"Stable around {spi:.2f} - slight schedule pressure present", "burn_rate_trend": f"Increasing from £{baseline_burn:.0f}M/month to £{burn_rate:.0f}M/month (+{burn_variance:.0f}% over 6 months)", "direction": "Worsening" if cpi < 0.88 else "Stable" }, "burn_rate_analysis": { "current_burn_rate": f"£{burn_rate:.0f}M/month", "baseline_burn_rate": f"£{baseline_burn:.0f}M/month", "burn_variance_pct": round(burn_variance, 1), "mom_change_pct": mom_change, "alert": "CRITICAL" if burn_variance > 30 else "WARNING" if burn_variance > 20 else "OK" }, "eac_forecast": { "eac_p50": f"£{eac_p50:.2f}B", "eac_p80": f"£{eac_p80:.2f}B", "forecast_method": f"CPI-based EAC = Baseline ({baseline:.2f}B) / Current_CPI ({cpi:.2f})", "without_intervention": f"EAC projected to reach £{eac_p50 + 0.08:.2f}B (+{eac_variance + 3:.1f}% variance) without corrective actions", "days_to_budget_exhaustion": None if eac_variance < 15 else 240 }, "correlations": [ { "type": "Commercial", "description": f"Contract P991 CPI 0.74 correlates with £2.1M CE exposure (Commercial Zone) - CE exposure drives {round((2.1/eac_p50)*100, 0)}% of cost variance", "contract_id": "P991", "impact": "£2.1M", "explanation": "Pending CE exposure on P991 not yet in baseline, causing CPI degradation as actual costs accumulate" }, { "type": "Supplier", "description": "Supplier A 14-day EOT on P991 → £850K acceleration costs (Supplier Zone correlation)", "contract_id": "P991", "impact": "£0.85M", "explanation": "Schedule delays require acceleration measures (overtime, resource premiums) inflating actual costs" } ], "top_cost_drivers": [ { "contract_id": "P991", "cpi": 0.74, "cost_variance": "£2.95M", "root_cause": "CE exposure (£2.1M) + schedule acceleration (£850K)", "supplier": "Supplier A" }, { "contract_id": "C-2401", "cpi": 0.81, "cost_variance": "£1.8M", "root_cause": "Ground conditions CEs + resource inefficiencies", "supplier": "Supplier B" }, { "contract_id": "C-2405", "cpi": 0.85, "cost_variance": "£1.2M", "root_cause": "Design changes + rework", "supplier": "Supplier C" } ], "recommendations": [ { "action": "Challenge P991 CE valuations - focus on ground conditions CEs (historically over-valued by 15-20%) - target £400K savings", "target_savings": "£0.4M", "cpi_improvement": "+1.5% portfolio CPI", "owner": "Commercial Director", "timeline_days": 21 }, { "action": "Avoid further schedule acceleration on Supplier A contracts - maintain float >7 days to prevent additional acceleration costs", "target_savings": "£0.5M (avoided costs)", "cpi_improvement": "+2% on affected contracts", "owner": "Planning Lead", "timeline_days": 14 }, { "action": "Implement value engineering on top 5 cost-driving activities (identified in MonthlyKPIs rows 34-38) - target 3% efficiency gain", "target_savings": "£1.2M", "cpi_improvement": "+4% portfolio CPI", "owner": "Engineering Lead", "timeline_days": 60 }, { "action": "Coordinate with Commercial Zone to expedite CE resolution on P991 - reduce pending CE exposure from £2.1M to £1.5M", "target_savings": "£0.6M", "cpi_improvement": "+2% P991 CPI", "owner": "Mother Orchestrator coordination", "timeline_days": 30 } ], "confidence": { "overall_score": confidence, "data_quality": data_quality, "forecast_model_accuracy": model_accuracy, "data_as_of": datetime.now().strftime("%d-%b-%Y") }, "issues_identified": [ f"[COST PERFORMANCE] Portfolio CPI at {cpi:.2f} - {'CRITICAL (below 0.85 threshold)' if cpi < 0.85 else 'WARNING (below 0.90 threshold)' if cpi < 0.90 else 'within acceptable range'}", f"[COST PERFORMANCE] EAC at £{eac_p50:.2f}B - {'+' if eac_variance > 0 else ''}{eac_variance:.1f}% variance from £{baseline:.2f}B baseline {'- HIGH RISK (>15%)' if eac_variance > 15 else '- WARNING (>10%)' if eac_variance > 10 else ''}", f"[COST PERFORMANCE] Burn rate at £{burn_rate:.0f}M/month ({'+' if burn_variance > 0 else ''}{burn_variance:.0f}% vs baseline £{baseline_burn:.0f}M/month) - elevated consumption", "[COST PERFORMANCE] Contract P991 major cost driver (CPI 0.74) - £2.1M CE exposure + £850K acceleration costs from Supplier A delays", f"[COST PERFORMANCE] 6-month CPI trend worsening - declined from 0.92 to {cpi:.2f} (-5% degradation)" ], "kpis_flagged": { "CPI": round(cpi, 2), "SPI": round(spi, 2), "EAC": f"£{eac_p50:.2f}B", "EAC_Variance_Pct": round(eac_variance, 1), "Burn_Rate": f"£{burn_rate:.0f}M/month", "Burn_Variance_Pct": round(burn_variance, 1) }, "guardrails_triggered": [ "CPI Critical" if cpi < 0.85 else "CPI Warning" if cpi < 0.90 else "CPI Normal", "SPI Warning" if spi < 0.95 else "SPI Normal", "EAC Variance Critical" if eac_variance > 15 else "EAC Variance High" if eac_variance > 10 else "EAC Variance Moderate", "Burn Rate Anomaly" if burn_variance > 20 else "Burn Rate Elevated", "Burn Rate Spike" if mom_change > 30 else None, "Cross-Zone Correlation" ], "success_metrics_performance": { "cpi_above_090": "YES" if cpi >= 0.90 else f"NO - Currently at {cpi:.2f}", "eac_variance_below_10": "YES" if eac_variance < 10 else f"NO - Currently at {eac_variance:.1f}%", "burn_rate_below_120": "YES" if burn_variance < 20 else f"NO - Currently at {burn_variance:.0f}% above baseline" } }