CWICR Change Order Processor

Business Case

Problem Statement

Change orders require:

• Quick cost impact analysis
• Comparison to original scope
• Fair pricing for added work
• Documentation for disputes

Solution

Systematic change order processing using CWICR data to calculate fair costs, document changes, and analyze impact on project budget.

Business Value

• Fair pricing - Based on validated norms
• Quick turnaround - Rapid cost analysis
• Documentation - Clear change records
• Budget tracking - Cumulative impact

Technical Implementation

import pandas as pd
from typing import Dict, Any, List, Optional
from dataclasses import dataclass, field
from datetime import datetime, date
from enum import Enum


class ChangeType(Enum):
    """Types of changes."""
    ADDITION = "addition"        # New work added
    DELETION = "deletion"        # Work removed
    MODIFICATION = "modification"  # Changed scope
    SUBSTITUTION = "substitution"  # Material/method change


class ChangeStatus(Enum):
    """Change order status."""
    DRAFT = "draft"
    SUBMITTED = "submitted"
    APPROVED = "approved"
    REJECTED = "rejected"
    PENDING = "pending"


@dataclass
class ChangeItem:
    """Single change item."""
    item_number: int
    work_item_code: str
    description: str
    change_type: ChangeType
    original_qty: float
    revised_qty: float
    unit: str
    unit_cost: float
    original_cost: float
    revised_cost: float
    cost_impact: float


@dataclass
class ChangeOrder:
    """Complete change order."""
    co_number: str
    project_name: str
    date_created: date
    status: ChangeStatus
    description: str
    items: List[ChangeItem]
    direct_cost_impact: float
    overhead_markup: float
    profit_markup: float
    total_impact: float
    schedule_impact_days: int
    justification: str


class CWICRChangeOrder:
    """Process change orders using CWICR data."""

    def __init__(self,
                 cwicr_data: pd.DataFrame,
                 overhead_rate: float = 0.12,
                 profit_rate: float = 0.08):
        self.cost_data = cwicr_data
        self.overhead_rate = overhead_rate
        self.profit_rate = profit_rate
        self._index_data()
        self._change_orders: Dict[str, ChangeOrder] = {}

    def _index_data(self):
        """Index cost data."""
        if 'work_item_code' in self.cost_data.columns:
            self._code_index = self.cost_data.set_index('work_item_code')
        else:
            self._code_index = None

    def get_unit_cost(self, code: str) -> Tuple[float, str]:
        """Get unit cost from CWICR."""
        if self._code_index is None or code not in self._code_index.index:
            return (0, 'unit')

        item = self._code_index.loc[code]
        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)
        unit = str(item.get('unit', 'unit'))

        return (labor + material + equipment, unit)

    def create_change_order(self,
                            co_number: str,
                            project_name: str,
                            description: str,
                            justification: str = "") -> str:
        """Create new change order."""

        co = ChangeOrder(
            co_number=co_number,
            project_name=project_name,
            date_created=date.today(),
            status=ChangeStatus.DRAFT,
            description=description,
            items=[],
            direct_cost_impact=0,
            overhead_markup=0,
            profit_markup=0,
            total_impact=0,
            schedule_impact_days=0,
            justification=justification
        )

        self._change_orders[co_number] = co
        return co_number

    def add_change_item(self,
                        co_number: str,
                        work_item_code: str,
                        change_type: ChangeType,
                        original_qty: float,
                        revised_qty: float,
                        description: str = None) -> ChangeItem:
        """Add item to change order."""

        co = self._change_orders.get(co_number)
        if co is None:
            raise ValueError(f"Change order {co_number} not found")

        unit_cost, unit = self.get_unit_cost(work_item_code)

        if description is None:
            if self._code_index is not None and work_item_code in self._code_index.index:
                description = str(self._code_index.loc[work_item_code].get('description', work_item_code))
            else:
                description = work_item_code

        original_cost = original_qty * unit_cost
        revised_cost = revised_qty * unit_cost
        cost_impact = revised_cost - original_cost

        item = ChangeItem(
            item_number=len(co.items) + 1,
            work_item_code=work_item_code,
            description=description,
            change_type=change_type,
            original_qty=original_qty,
            revised_qty=revised_qty,
            unit=unit,
            unit_cost=unit_cost,
            original_cost=round(original_cost, 2),
            revised_cost=round(revised_cost, 2),
            cost_impact=round(cost_impact, 2)
        )

        co.items.append(item)
        self._recalculate_totals(co_number)

        return item

    def _recalculate_totals(self, co_number: str):
        """Recalculate change order totals."""
        co = self._change_orders.get(co_number)
        if co is None:
            return

        direct_impact = sum(item.cost_impact for item in co.items)
        overhead = direct_impact * self.overhead_rate
        profit = (direct_impact + overhead) * self.profit_rate

        co.direct_cost_impact = round(direct_impact, 2)
        co.overhead_markup = round(overhead, 2)
        co.profit_markup = round(profit, 2)
        co.total_impact = round(direct_impact + overhead + profit, 2)

    def set_schedule_impact(self, co_number: str, days: int):
        """Set schedule impact for change order."""
        co = self._change_orders.get(co_number)
        if co:
            co.schedule_impact_days = days

    def update_status(self, co_number: str, status: ChangeStatus):
        """Update change order status."""
        co = self._change_orders.get(co_number)
        if co:
            co.status = status

    def get_change_order(self, co_number: str) -> Optional[ChangeOrder]:
        """Get change order by number."""
        return self._change_orders.get(co_number)

    def calculate_quick_impact(self,
                                changes: List[Dict[str, Any]]) -> Dict[str, Any]:
        """Quick impact calculation without creating CO."""

        additions = 0
        deletions = 0
        modifications = 0

        for change in changes:
            code = change.get('work_item_code', change.get('code'))
            change_type = change.get('change_type', 'modification')
            original = change.get('original_qty', 0)
            revised = change.get('revised_qty', 0)

            unit_cost, _ = self.get_unit_cost(code)

            if change_type == 'addition':
                additions += revised * unit_cost
            elif change_type == 'deletion':
                deletions += original * unit_cost
            else:
                modifications += (revised - original) * unit_cost

        net_direct = additions - deletions + modifications
        overhead = net_direct * self.overhead_rate
        profit = (net_direct + overhead) * self.profit_rate

        return {
            'additions': round(additions, 2),
            'deletions': round(deletions, 2),
            'modifications': round(modifications, 2),
            'net_direct_impact': round(net_direct, 2),
            'overhead': round(overhead, 2),
            'profit': round(profit, 2),
            'total_impact': round(net_direct + overhead + profit, 2)
        }

    def compare_to_budget(self,
                          co_number: str,
                          original_budget: float,
                          approved_changes: float = 0) -> Dict[str, Any]:
        """Compare change order to project budget."""

        co = self._change_orders.get(co_number)
        if co is None:
            return {}

        current_budget = original_budget + approved_changes
        new_budget = current_budget + co.total_impact

        return {
            'original_budget': original_budget,
            'previously_approved_changes': approved_changes,
            'current_budget': current_budget,
            'this_change_order': co.total_impact,
            'new_budget': round(new_budget, 2),
            'change_from_original': round(new_budget - original_budget, 2),
            'change_percent': round((new_budget - original_budget) / original_budget * 100, 1)
        }

    def get_project_changes_summary(self,
                                     project_name: str) -> Dict[str, Any]:
        """Get summary of all changes for a project."""

        project_cos = [
            co for co in self._change_orders.values()
            if co.project_name == project_name
        ]

        total_additions = 0
        total_deletions = 0
        total_impact = 0
        total_schedule_impact = 0

        for co in project_cos:
            for item in co.items:
                if item.change_type == ChangeType.ADDITION:
                    total_additions += item.cost_impact
                elif item.change_type == ChangeType.DELETION:
                    total_deletions += abs(item.cost_impact)

            total_impact += co.total_impact
            total_schedule_impact += co.schedule_impact_days

        return {
            'project': project_name,
            'total_change_orders': len(project_cos),
            'approved': len([co for co in project_cos if co.status == ChangeStatus.APPROVED]),
            'pending': len([co for co in project_cos if co.status == ChangeStatus.PENDING]),
            'total_additions': round(total_additions, 2),
            'total_deletions': round(total_deletions, 2),
            'net_cost_impact': round(total_impact, 2),
            'total_schedule_days': total_schedule_impact
        }

    def export_change_order(self,
                             co_number: str,
                             output_path: str) -> str:
        """Export change order to Excel."""

        co = self._change_orders.get(co_number)
        if co is None:
            raise ValueError(f"Change order {co_number} not found")

        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # Header
            header_df = pd.DataFrame([{
                'Change Order': co.co_number,
                'Project': co.project_name,
                'Date': co.date_created,
                'Status': co.status.value,
                'Description': co.description,
                'Schedule Impact (days)': co.schedule_impact_days
            }])
            header_df.to_excel(writer, sheet_name='Summary', index=False)

            # Items
            items_df = pd.DataFrame([
                {
                    '#': item.item_number,
                    'Code': item.work_item_code,
                    'Description': item.description,
                    'Type': item.change_type.value,
                    'Original Qty': item.original_qty,
                    'Revised Qty': item.revised_qty,
                    'Unit': item.unit,
                    'Unit Cost': item.unit_cost,
                    'Original Cost': item.original_cost,
                    'Revised Cost': item.revised_cost,
                    'Impact': item.cost_impact
                }
                for item in co.items
            ])
            items_df.to_excel(writer, sheet_name='Items', index=False)

            # Totals
            totals_df = pd.DataFrame([{
                'Direct Cost Impact': co.direct_cost_impact,
                f'Overhead ({self.overhead_rate:.0%})': co.overhead_markup,
                f'Profit ({self.profit_rate:.0%})': co.profit_markup,
                'Total Impact': co.total_impact
            }])
            totals_df.to_excel(writer, sheet_name='Totals', index=False)

        return output_path

Quick Start

# Load CWICR data
cwicr = pd.read_parquet("ddc_cwicr_en.parquet")

# Initialize change order processor
co_processor = CWICRChangeOrder(cwicr, overhead_rate=0.12, profit_rate=0.08)

# Create change order
co_processor.create_change_order(
    co_number="CO-001",
    project_name="Building A",
    description="Additional foundation work due to soil conditions"
)

# Add items
co_processor.add_change_item(
    co_number="CO-001",
    work_item_code="EXCV-002",
    change_type=ChangeType.ADDITION,
    original_qty=0,
    revised_qty=150
)

# Get change order
co = co_processor.get_change_order("CO-001")
print(f"Direct Impact: ${co.direct_cost_impact:,.2f}")
print(f"Total Impact: ${co.total_impact:,.2f}")

Common Use Cases

1. Quick Impact Analysis

changes = [
    {'code': 'CONC-001', 'change_type': 'addition', 'original_qty': 0, 'revised_qty': 50},
    {'code': 'REBAR-002', 'change_type': 'modification', 'original_qty': 1000, 'revised_qty': 1500}
]

impact = co_processor.calculate_quick_impact(changes)
print(f"Net Impact: ${impact['total_impact']:,.2f}")

2. Compare to Budget

comparison = co_processor.compare_to_budget(
    co_number="CO-001",
    original_budget=5000000,
    approved_changes=150000
)
print(f"Budget Change: {comparison['change_percent']}%")

3. Export Documentation

co_processor.export_change_order("CO-001", "change_order_001.xlsx")

Resources

• GitHub: OpenConstructionEstimate-DDC-CWICR
• DDC Book: Chapter 3.2 - Change Order Management