\r \r

Estimate Builder\r

\r

Business Case\r

\r

Problem Statement\r

Estimate creation challenges:\r

• Complex cost structures\r
• Multiple cost categories\r
• Markup calculations\r
• Format requirements vary\r \r

Solution\r

Structured estimate builder that creates professional construction estimates with proper cost categorization, markups, and export capabilities.\r \r

Technical Implementation\r

\r

import pandas as pd\r
from typing import Dict, Any, List, Optional\r
from dataclasses import dataclass, field\r
from datetime import date\r
from enum import Enum\r
\r
\r
class CostCategory(Enum):\r
    LABOR = "labor"\r
    MATERIAL = "material"\r
    EQUIPMENT = "equipment"\r
    SUBCONTRACTOR = "subcontractor"\r
    OTHER = "other"\r
\r
\r
@dataclass\r
class EstimateLineItem:\r
    line_number: int\r
    wbs_code: str\r
    description: str\r
    quantity: float\r
    unit: str\r
    unit_cost: float\r
    category: CostCategory\r
    notes: str = ""\r
\r
    @property\r
    def total_cost(self) -> float:\r
        return round(self.quantity * self.unit_cost, 2)\r
\r
\r
@dataclass\r
class CostSummary:\r
    labor: float = 0\r
    material: float = 0\r
    equipment: float = 0\r
    subcontractor: float = 0\r
    other: float = 0\r
\r
    @property\r
    def direct_cost(self) -> float:\r
        return self.labor + self.material + self.equipment + self.subcontractor + self.other\r
\r
\r
@dataclass\r
class Markup:\r
    name: str\r
    rate: float  # As decimal (0.10 = 10%)\r
    base: str = "direct"  # "direct" or "subtotal"\r
\r
\r
class EstimateBuilder:\r
    """Build construction project estimates."""\r
\r
    def __init__(self, project_name: str, project_number: str = ""):\r
        self.project_name = project_name\r
        self.project_number = project_number\r
        self.estimate_date = date.today()\r
        self.items: List[EstimateLineItem] = []\r
        self.markups: List[Markup] = []\r
        self._next_line = 1\r
\r
    def add_item(self,\r
                 wbs_code: str,\r
                 description: str,\r
                 quantity: float,\r
                 unit: str,\r
                 unit_cost: float,\r
                 category: CostCategory = CostCategory.OTHER,\r
                 notes: str = "") -> EstimateLineItem:\r
        """Add line item to estimate."""\r
\r
        item = EstimateLineItem(\r
            line_number=self._next_line,\r
            wbs_code=wbs_code,\r
            description=description,\r
            quantity=quantity,\r
            unit=unit,\r
            unit_cost=unit_cost,\r
            category=category,\r
            notes=notes\r
        )\r
        self.items.append(item)\r
        self._next_line += 1\r
        return item\r
\r
    def add_markup(self, name: str, rate: float, base: str = "direct"):\r
        """Add markup (overhead, profit, contingency, etc.)."""\r
        self.markups.append(Markup(name=name, rate=rate, base=base))\r
\r
    def set_standard_markups(self,\r
                             overhead: float = 0.15,\r
                             profit: float = 0.10,\r
                             contingency: float = 0.05):\r
        """Set standard construction markups."""\r
\r
        self.markups = [\r
            Markup("General Conditions / Overhead", overhead, "direct"),\r
            Markup("Profit", profit, "subtotal"),\r
            Markup("Contingency", contingency, "subtotal")\r
        ]\r
\r
    def get_cost_summary(self) -> CostSummary:\r
        """Get cost summary by category."""\r
\r
        summary = CostSummary()\r
        for item in self.items:\r
            cost = item.total_cost\r
            if item.category == CostCategory.LABOR:\r
                summary.labor += cost\r
            elif item.category == CostCategory.MATERIAL:\r
                summary.material += cost\r
            elif item.category == CostCategory.EQUIPMENT:\r
                summary.equipment += cost\r
            elif item.category == CostCategory.SUBCONTRACTOR:\r
                summary.subcontractor += cost\r
            else:\r
                summary.other += cost\r
        return summary\r
\r
    def calculate_total(self) -> Dict[str, Any]:\r
        """Calculate total estimate with markups."""\r
\r
        summary = self.get_cost_summary()\r
        direct_cost = summary.direct_cost\r
\r
        markups_detail = []\r
        subtotal = direct_cost\r
\r
        for markup in self.markups:\r
            if markup.base == "direct":\r
                amount = direct_cost * markup.rate\r
            else:\r
                amount = subtotal * markup.rate\r
\r
            markups_detail.append({\r
                'name': markup.name,\r
                'rate': f"{markup.rate * 100:.1f}%",\r
                'amount': round(amount, 2)\r
            })\r
            subtotal += amount\r
\r
        return {\r
            'cost_summary': {\r
                'labor': round(summary.labor, 2),\r
                'material': round(summary.material, 2),\r
                'equipment': round(summary.equipment, 2),\r
                'subcontractor': round(summary.subcontractor, 2),\r
                'other': round(summary.other, 2),\r
                'direct_cost': round(direct_cost, 2)\r
            },\r
            'markups': markups_detail,\r
            'total_markups': round(subtotal - direct_cost, 2),\r
            'grand_total': round(subtotal, 2)\r
        }\r
\r
    def get_items_by_wbs(self) -> Dict[str, List[EstimateLineItem]]:\r
        """Group items by WBS code prefix."""\r
\r
        by_wbs = {}\r
        for item in self.items:\r
            prefix = item.wbs_code.split('.')[0] if '.' in item.wbs_code else item.wbs_code\r
            if prefix not in by_wbs:\r
                by_wbs[prefix] = []\r
            by_wbs[prefix].append(item)\r
        return by_wbs\r
\r
    def import_from_df(self, df: pd.DataFrame):\r
        """Import line items from DataFrame."""\r
\r
        for _, row in df.iterrows():\r
            self.add_item(\r
                wbs_code=str(row.get('wbs_code', '')),\r
                description=row['description'],\r
                quantity=float(row['quantity']),\r
                unit=row['unit'],\r
                unit_cost=float(row['unit_cost']),\r
                category=CostCategory(row.get('category', 'other').lower()),\r
                notes=row.get('notes', '')\r
            )\r
\r
    def export_to_df(self) -> pd.DataFrame:\r
        """Export estimate to DataFrame."""\r
\r
        data = []\r
        for item in self.items:\r
            data.append({\r
                'Line': item.line_number,\r
                'WBS': item.wbs_code,\r
                'Description': item.description,\r
                'Qty': item.quantity,\r
                'Unit': item.unit,\r
                'Unit Cost': item.unit_cost,\r
                'Total': item.total_cost,\r
                'Category': item.category.value,\r
                'Notes': item.notes\r
            })\r
        return pd.DataFrame(data)\r
\r
    def export_to_excel(self, output_path: str) -> str:\r
        """Export estimate to Excel."""\r
\r
        totals = self.calculate_total()\r
\r
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:\r
            # Cover sheet\r
            cover_df = pd.DataFrame([{\r
                'Project Name': self.project_name,\r
                'Project Number': self.project_number,\r
                'Estimate Date': self.estimate_date,\r
                'Total Items': len(self.items),\r
                'Direct Cost': totals['cost_summary']['direct_cost'],\r
                'Grand Total': totals['grand_total']\r
            }])\r
            cover_df.to_excel(writer, sheet_name='Summary', index=False)\r
\r
            # Line items\r
            items_df = self.export_to_df()\r
            items_df.to_excel(writer, sheet_name='Line Items', index=False)\r
\r
            # Cost breakdown\r
            breakdown_df = pd.DataFrame([totals['cost_summary']])\r
            breakdown_df.to_excel(writer, sheet_name='Cost Breakdown', index=False)\r
\r
            # Markups\r
            if totals['markups']:\r
                markups_df = pd.DataFrame(totals['markups'])\r
                markups_df.to_excel(writer, sheet_name='Markups', index=False)\r
\r
        return output_path\r
\r
    def validate(self) -> List[str]:\r
        """Validate estimate for common issues."""\r
\r
        issues = []\r
\r
        if not self.items:\r
            issues.append("Estimate has no line items")\r
\r
        for item in self.items:\r
            if item.quantity \x3C= 0:\r
                issues.append(f"Line {item.line_number}: Invalid quantity")\r
            if item.unit_cost \x3C 0:\r
                issues.append(f"Line {item.line_number}: Negative unit cost")\r
            if not item.description:\r
                issues.append(f"Line {item.line_number}: Missing description")\r
\r
        if not self.markups:\r
            issues.append("No markups defined (overhead, profit)")\r
\r
        return issues\r
```\r
\r
## Quick Start\r
\r
```python\r
# Create estimate\r
estimate = EstimateBuilder("Office Building A", "PRJ-2024-001")\r
\r
# Add line items\r
estimate.add_item("01.01", "Site Preparation", 5000, "SF", 2.50, CostCategory.OTHER)\r
estimate.add_item("03.01", "Concrete Foundation", 200, "CY", 350, CostCategory.MATERIAL)\r
estimate.add_item("03.02", "Foundation Formwork", 1500, "SF", 8.50, CostCategory.LABOR)\r
estimate.add_item("05.01", "Structural Steel", 50, "TON", 4500, CostCategory.SUBCONTRACTOR)\r
\r
# Set markups\r
estimate.set_standard_markups(overhead=0.15, profit=0.10, contingency=0.05)\r
\r
# Calculate total\r
result = estimate.calculate_total()\r
print(f"Direct Cost: ${result['cost_summary']['direct_cost']:,.2f}")\r
print(f"Grand Total: ${result['grand_total']:,.2f}")\r
```\r
\r
## Common Use Cases\r
\r
### 1. Cost by Category\r
```python\r
summary = estimate.get_cost_summary()\r
print(f"Labor: ${summary.labor:,.2f}")\r
print(f"Material: ${summary.material:,.2f}")\r
```\r
\r
### 2. Export to Excel\r
```python\r
estimate.export_to_excel("estimate_output.xlsx")\r
```\r
\r
### 3. Validate Estimate\r
```python\r
issues = estimate.validate()\r
for issue in issues:\r
    print(f"Warning: {issue}")\r
```\r
\r
## Resources\r
- **DDC Book**: Chapter 3.1 - Cost Calculations and Estimates\r
- **Website**: https://datadrivenconstruction.io\r