Estimate Builder

Business Case

Problem Statement

Estimate creation challenges:

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

Solution

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

Technical Implementation

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


class CostCategory(Enum):
    LABOR = "labor"
    MATERIAL = "material"
    EQUIPMENT = "equipment"
    SUBCONTRACTOR = "subcontractor"
    OTHER = "other"


@dataclass
class EstimateLineItem:
    line_number: int
    wbs_code: str
    description: str
    quantity: float
    unit: str
    unit_cost: float
    category: CostCategory
    notes: str = ""

    @property
    def total_cost(self) -> float:
        return round(self.quantity * self.unit_cost, 2)


@dataclass
class CostSummary:
    labor: float = 0
    material: float = 0
    equipment: float = 0
    subcontractor: float = 0
    other: float = 0

    @property
    def direct_cost(self) -> float:
        return self.labor + self.material + self.equipment + self.subcontractor + self.other


@dataclass
class Markup:
    name: str
    rate: float  # As decimal (0.10 = 10%)
    base: str = "direct"  # "direct" or "subtotal"


class EstimateBuilder:
    """Build construction project estimates."""

    def __init__(self, project_name: str, project_number: str = ""):
        self.project_name = project_name
        self.project_number = project_number
        self.estimate_date = date.today()
        self.items: List[EstimateLineItem] = []
        self.markups: List[Markup] = []
        self._next_line = 1

    def add_item(self,
                 wbs_code: str,
                 description: str,
                 quantity: float,
                 unit: str,
                 unit_cost: float,
                 category: CostCategory = CostCategory.OTHER,
                 notes: str = "") -> EstimateLineItem:
        """Add line item to estimate."""

        item = EstimateLineItem(
            line_number=self._next_line,
            wbs_code=wbs_code,
            description=description,
            quantity=quantity,
            unit=unit,
            unit_cost=unit_cost,
            category=category,
            notes=notes
        )
        self.items.append(item)
        self._next_line += 1
        return item

    def add_markup(self, name: str, rate: float, base: str = "direct"):
        """Add markup (overhead, profit, contingency, etc.)."""
        self.markups.append(Markup(name=name, rate=rate, base=base))

    def set_standard_markups(self,
                             overhead: float = 0.15,
                             profit: float = 0.10,
                             contingency: float = 0.05):
        """Set standard construction markups."""

        self.markups = [
            Markup("General Conditions / Overhead", overhead, "direct"),
            Markup("Profit", profit, "subtotal"),
            Markup("Contingency", contingency, "subtotal")
        ]

    def get_cost_summary(self) -> CostSummary:
        """Get cost summary by category."""

        summary = CostSummary()
        for item in self.items:
            cost = item.total_cost
            if item.category == CostCategory.LABOR:
                summary.labor += cost
            elif item.category == CostCategory.MATERIAL:
                summary.material += cost
            elif item.category == CostCategory.EQUIPMENT:
                summary.equipment += cost
            elif item.category == CostCategory.SUBCONTRACTOR:
                summary.subcontractor += cost
            else:
                summary.other += cost
        return summary

    def calculate_total(self) -> Dict[str, Any]:
        """Calculate total estimate with markups."""

        summary = self.get_cost_summary()
        direct_cost = summary.direct_cost

        markups_detail = []
        subtotal = direct_cost

        for markup in self.markups:
            if markup.base == "direct":
                amount = direct_cost * markup.rate
            else:
                amount = subtotal * markup.rate

            markups_detail.append({
                'name': markup.name,
                'rate': f"{markup.rate * 100:.1f}%",
                'amount': round(amount, 2)
            })
            subtotal += amount

        return {
            'cost_summary': {
                'labor': round(summary.labor, 2),
                'material': round(summary.material, 2),
                'equipment': round(summary.equipment, 2),
                'subcontractor': round(summary.subcontractor, 2),
                'other': round(summary.other, 2),
                'direct_cost': round(direct_cost, 2)
            },
            'markups': markups_detail,
            'total_markups': round(subtotal - direct_cost, 2),
            'grand_total': round(subtotal, 2)
        }

    def get_items_by_wbs(self) -> Dict[str, List[EstimateLineItem]]:
        """Group items by WBS code prefix."""

        by_wbs = {}
        for item in self.items:
            prefix = item.wbs_code.split('.')[0] if '.' in item.wbs_code else item.wbs_code
            if prefix not in by_wbs:
                by_wbs[prefix] = []
            by_wbs[prefix].append(item)
        return by_wbs

    def import_from_df(self, df: pd.DataFrame):
        """Import line items from DataFrame."""

        for _, row in df.iterrows():
            self.add_item(
                wbs_code=str(row.get('wbs_code', '')),
                description=row['description'],
                quantity=float(row['quantity']),
                unit=row['unit'],
                unit_cost=float(row['unit_cost']),
                category=CostCategory(row.get('category', 'other').lower()),
                notes=row.get('notes', '')
            )

    def export_to_df(self) -> pd.DataFrame:
        """Export estimate to DataFrame."""

        data = []
        for item in self.items:
            data.append({
                'Line': item.line_number,
                'WBS': item.wbs_code,
                'Description': item.description,
                'Qty': item.quantity,
                'Unit': item.unit,
                'Unit Cost': item.unit_cost,
                'Total': item.total_cost,
                'Category': item.category.value,
                'Notes': item.notes
            })
        return pd.DataFrame(data)

    def export_to_excel(self, output_path: str) -> str:
        """Export estimate to Excel."""

        totals = self.calculate_total()

        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # Cover sheet
            cover_df = pd.DataFrame([{
                'Project Name': self.project_name,
                'Project Number': self.project_number,
                'Estimate Date': self.estimate_date,
                'Total Items': len(self.items),
                'Direct Cost': totals['cost_summary']['direct_cost'],
                'Grand Total': totals['grand_total']
            }])
            cover_df.to_excel(writer, sheet_name='Summary', index=False)

            # Line items
            items_df = self.export_to_df()
            items_df.to_excel(writer, sheet_name='Line Items', index=False)

            # Cost breakdown
            breakdown_df = pd.DataFrame([totals['cost_summary']])
            breakdown_df.to_excel(writer, sheet_name='Cost Breakdown', index=False)

            # Markups
            if totals['markups']:
                markups_df = pd.DataFrame(totals['markups'])
                markups_df.to_excel(writer, sheet_name='Markups', index=False)

        return output_path

    def validate(self) -> List[str]:
        """Validate estimate for common issues."""

        issues = []

        if not self.items:
            issues.append("Estimate has no line items")

        for item in self.items:
            if item.quantity <= 0:
                issues.append(f"Line {item.line_number}: Invalid quantity")
            if item.unit_cost < 0:
                issues.append(f"Line {item.line_number}: Negative unit cost")
            if not item.description:
                issues.append(f"Line {item.line_number}: Missing description")

        if not self.markups:
            issues.append("No markups defined (overhead, profit)")

        return issues