CWICR Bid Analyzer
Business Case
Problem Statement
Evaluating contractor bids requires:
- Comparing against market benchmarks
- Identifying unusual pricing
- Understanding cost composition
- Documenting evaluation rationale
Solution
Analyze contractor bids against CWICR-based benchmarks to identify anomalies, compare components, and support objective bid evaluation.
Business Value
- Objective evaluation - Data-driven bid analysis
- Risk identification - Spot unrealistic pricing
- Fair comparison - Normalized bid analysis
- Documentation - Audit trail for decisions
Technical Implementation
import pandas as pd
import numpy as np
from typing import Dict, Any, List, Optional, Tuple
from dataclasses import dataclass, field
from datetime import datetime
from enum import Enum
from collections import defaultdict
class BidStatus(Enum):
"""Bid evaluation status."""
COMPLIANT = "compliant"
NON_COMPLIANT = "non_compliant"
UNDER_REVIEW = "under_review"
RECOMMENDED = "recommended"
NOT_RECOMMENDED = "not_recommended"
class PriceFlag(Enum):
"""Price anomaly flags."""
NORMAL = "normal"
LOW = "low" # >20% below benchmark
HIGH = "high" # >20% above benchmark
VERY_LOW = "very_low" # >40% below - potential front-loading
VERY_HIGH = "very_high" # >40% above - potential profiteering
@dataclass
class BidLineItem:
"""Single line item from bid."""
item_code: str
description: str
quantity: float
unit: str
unit_rate: float
total_price: float
benchmark_rate: float
benchmark_total: float
variance_pct: float
price_flag: PriceFlag
@dataclass
class BidAnalysis:
"""Complete bid analysis."""
bidder_name: str
bid_total: float
benchmark_total: float
variance_pct: float
line_items: List[BidLineItem]
flagged_items: List[BidLineItem]
status: BidStatus
summary: Dict[str, Any]
@dataclass
class BidComparison:
"""Comparison of multiple bids."""
project_name: str
benchmark_total: float
bids: List[BidAnalysis]
ranking: List[Tuple[str, float]]
recommended_bidder: Optional[str]
class CWICRBidAnalyzer:
"""Analyze bids against CWICR benchmarks."""
# Thresholds for price flags
LOW_THRESHOLD = -0.20
HIGH_THRESHOLD = 0.20
VERY_LOW_THRESHOLD = -0.40
VERY_HIGH_THRESHOLD = 0.40
def __init__(self, cwicr_data: pd.DataFrame):
self.benchmark_data = cwicr_data
self._index_data()
def _index_data(self):
"""Index benchmark data."""
if 'work_item_code' in self.benchmark_data.columns:
self._code_index = self.benchmark_data.set_index('work_item_code')
else:
self._code_index = None
def _get_price_flag(self, variance_pct: float) -> PriceFlag:
"""Determine price flag from variance."""
if variance_pct <= self.VERY_LOW_THRESHOLD * 100:
return PriceFlag.VERY_LOW
elif variance_pct <= self.LOW_THRESHOLD * 100:
return PriceFlag.LOW
elif variance_pct >= self.VERY_HIGH_THRESHOLD * 100:
return PriceFlag.VERY_HIGH
elif variance_pct >= self.HIGH_THRESHOLD * 100:
return PriceFlag.HIGH
else:
return PriceFlag.NORMAL
def get_benchmark_rate(self, work_item_code: str) -> Optional[float]:
"""Get benchmark rate for work item."""
if self._code_index is None:
return None
if work_item_code in self._code_index.index:
item = self._code_index.loc[work_item_code]
# Total unit rate
labor = float(item.get('labor_cost', 0) or 0)
material = float(item.get('material_cost', 0) or 0)
equipment = float(item.get('equipment_cost', 0) or 0)
return labor + material + equipment
return None
def analyze_bid(self,
bid_data: pd.DataFrame,
bidder_name: str,
code_column: str = 'item_code',
quantity_column: str = 'quantity',
rate_column: str = 'unit_rate',
total_column: str = 'total_price') -> BidAnalysis:
"""Analyze single bid against benchmarks."""
line_items = []
for _, row in bid_data.iterrows():
code = row[code_column]
qty = float(row[quantity_column])
bid_rate = float(row[rate_column])
bid_total = float(row.get(total_column, bid_rate * qty))
benchmark_rate = self.get_benchmark_rate(code)
if benchmark_rate is None:
benchmark_rate = bid_rate # No comparison possible
benchmark_total = benchmark_rate * qty
variance_pct = ((bid_rate - benchmark_rate) / benchmark_rate * 100) if benchmark_rate > 0 else 0
line_items.append(BidLineItem(
item_code=code,
description=str(row.get('description', '')),
quantity=qty,
unit=str(row.get('unit', '')),
unit_rate=bid_rate,
total_price=bid_total,
benchmark_rate=benchmark_rate,
benchmark_total=benchmark_total,
variance_pct=round(variance_pct, 1),
price_flag=self._get_price_flag(variance_pct)
))
# Totals
bid_total = sum(item.total_price for item in line_items)
benchmark_total = sum(item.benchmark_total for item in line_items)
total_variance = ((bid_total - benchmark_total) / benchmark_total * 100) if benchmark_total > 0 else 0
# Flagged items
flagged = [item for item in line_items if item.price_flag != PriceFlag.NORMAL]
# Determine status
if len([f for f in flagged if f.price_flag in [PriceFlag.VERY_LOW, PriceFlag.VERY_HIGH]]) > len(line_items) * 0.1:
status = BidStatus.UNDER_REVIEW
elif total_variance < -30 or total_variance > 30:
status = BidStatus.UNDER_REVIEW
else:
status = BidStatus.COMPLIANT
# Summary statistics
summary = {
'total_items': len(line_items),
'flagged_items': len(flagged),
'items_below_benchmark': len([i for i in line_items if i.variance_pct < 0]),
'items_above_benchmark': len([i for i in line_items if i.variance_pct > 0]),
'average_variance': np.mean([i.variance_pct for i in line_items]),
'max_overpriced': max([i.variance_pct for i in line_items]) if line_items else 0,
'max_underpriced': min([i.variance_pct for i in line_items]) if line_items else 0
}
return BidAnalysis(
bidder_name=bidder_name,
bid_total=round(bid_total, 2),
benchmark_total=round(benchmark_total, 2),
variance_pct=round(total_variance, 1),
line_items=line_items,
flagged_items=flagged,
status=status,
summary=summary
)
def compare_bids(self,
bids: List[Tuple[str, pd.DataFrame]],
project_name: str = "Project") -> BidComparison:
"""Compare multiple bids."""
analyses = []
for bidder_name, bid_data in bids:
analysis = self.analyze_bid(bid_data, bidder_name)
analyses.append(analysis)
# Get benchmark from first bid's items (they should be same scope)
benchmark_total = analyses[0].benchmark_total if analyses else 0
# Rank by total price
ranking = sorted(
[(a.bidder_name, a.bid_total) for a in analyses],
key=lambda x: x[1]
)
# Recommend lowest compliant bidder
recommended = None
for bidder, total in ranking:
bid_analysis = next(a for a in analyses if a.bidder_name == bidder)
if bid_analysis.status == Bi