Project Health Reporting Agent

An AI-powered agent that reads S2P (Source-to-Pay) project plans, determines RAG (Red/Amber/Green) health status, and generates executive-ready reports and presentations.

Built for the Zycus AI Engineer Intern technical assignment.

Architecture

┌─────────────────────────────────────────────────┐
│                   agent.py                       │
│            (orchestration layer)                 │
├──────────┬──────────┬──────────┬────────────────┤
│ parser.py│rag_engine│ Gemini   │presentation.py │
│          │   .py    │ 2.5 Flash│                │
│ Excel    │Weighted  │Plain-    │python-pptx     │
│ parsing  │scoring   │English   │slide gen       │
│ + adapt  │framework │reasoning │                │
└──────────┴──────────┴──────────┴────────────────┘
     ↑                                    ↓
  data/*.xlsx                    outputs/weekly/*.md
                                 outputs/monthly/*.pptx

The agent uses a two-layer approach:

1. Deterministic scoring (rag_engine.py) — Computes RAG status from five weighted signals (schedule variance, completion rate, milestone health, blocker density, scope risk). This gives consistent, reproducible results.

2. LLM reasoning (agent.py via Gemini) — Takes the structured scores plus raw task data and generates plain-English analysis. The LLM interprets patterns, reads stakeholder comments, and produces actionable recommendations that a VP can understand without procurement expertise.

This separation matters because deterministic rules catch the quantitative signals reliably, while the LLM handles the qualitative interpretation that makes reports useful. Neither layer alone would be sufficient — rules without reasoning produce dashboards nobody reads, and LLMs without grounding hallucinate metrics.

Design Decisions

Why Gemini 2.5 Flash? It offers strong reasoning capabilities with fast inference, which matters for a weekly reporting tool that might run across many projects. The structured context I pass to Gemini (pre-computed scores, key risk tasks, comments) keeps the prompt focused and reduces the chance of the model inventing data.

Why deterministic scoring before LLM? I considered letting the LLM do all the analysis directly from raw data, but that creates two problems: (a) inconsistency between runs — the same data might get different RAG scores, and (b) no auditability — you can't explain why a project turned Red without re-running the model. The scoring framework acts as a contract: these specific signals, with these thresholds, produce this status. The LLM then explains why that status matters.

Handling messy data. Real project plans have missing variance fields, unparseable dates, and inconsistent column layouts. The parser uses header-name matching (not positional indexing) to adapt to different Excel formats. When data is missing, the RAG engine redistributes signal weights and flags gaps in the report rather than assuming defaults. For example, when UniSan's project plan had only one variance data point, the engine falls back to the summary sheet's schedule health indicator instead of computing a misleading average.

Override rules. The scoring framework includes hard overrides — if a critical milestone is Red with >20 days variance, the project is forced to Red regardless of composite score. This prevents the weighted average from masking a single catastrophic issue behind four green signals.

Setup

# Clone and install dependencies
git clone https://github.com/Sivakumarraj/project-health-agent.git
cd project-health-agent
pip install -r requirements.txt

# Configure API key
cp .env.example .env
# Edit .env and add your Gemini API key

Get a Gemini API key from Google AI Studio.

Usage

Generate weekly reports only

python agent.py weekly

Generate weekly reports + monthly synthesis + executive presentation

python agent.py monthly

Add new project plans

Drop .xlsx files into the data/ directory. The parser auto-detects column layouts.

Project Structure

project-health-agent/
├── agent.py              # Main entry point and LLM orchestration
├── parser.py             # Excel parser — adapts to column variations
├── rag_engine.py         # Deterministic RAG scoring (5 weighted signals)
├── presentation.py       # PowerPoint generator for monthly synthesis
├── rag_methodology.md    # Phase 1: One-page RAG framework definition
├── requirements.txt
├── .env.example
├── data/                 # Input project plan Excel files
│   ├── S2P_Project.xlsx
│   └── Project_Plan_B.xlsx
└── outputs/
    ├── weekly/           # Per-project weekly health reports (Markdown)
    └── monthly/          # Cross-project synthesis JSON + PPTX

Sample Output

Running against the two provided project plans:

Project	RAG Status	Score	Key Issue
Titan S2P (Outokumpu)	RED	2.2/3.0	Configuration & Build phase 32 days behind; Phase 2 P2P has -81d variance
UniSan S2P	RED	1.55/3.0	15% completion gap; 20 overdue not-started tasks; Training & Hypercare milestones Red

Weekly reports are in outputs/weekly/. The monthly executive presentation is in outputs/monthly/.

Bonus: Weekly Scheduling

To run the agent on a weekly cron schedule:

# Linux/Mac — every Monday at 9 AM
0 9 * * 1 cd /path/to/project-health-agent && python agent.py monthly

# Windows Task Scheduler
schtasks /create /tn "ProjectHealthAgent" /tr "python C:\path\to\agent.py monthly" /sc weekly /d MON /st 09:00

Author

Padmaraju Siva Kumar Raju

• GitHub: github.com/Sivakumarraj
• LinkedIn: linkedin.com/in/siva-kumar-raju-359157229