估价构建器

商业案例

问题陈述

估价编制面临的挑战：

• 成本结构复杂
• 成本类别繁多
• 需进行加价（markup）计算
• 格式要求因项目而异

解决方案

一款结构化的估价构建工具，可生成专业的建筑工程估价，支持合理的成本分类、加价计算及多种导出能力。

技术实现

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  # 以小数表示（例如 0.10 表示 10%）
    base: str = "direct"  # "direct" 或 "subtotal"


class EstimateBuilder:
    """构建建筑工程估价。"""

    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:
        """向估价中添加明细项。"""

        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"):
        """添加加价项（如管理费、利润、不可预见费等）。"""
        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):
        """设置标准建筑工程加价率。"""

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

    def get_cost_summary(self) -> CostSummary:
        """按成本类别获取汇总信息。"""

        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]:
        """计算含加价项的估价总额。"""

        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]]:
        """按 WBS 编码前缀对明细项进行分组。"""

        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):
        """从 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:
        """将估价导出为 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:
        """将估价导出为 Excel 文件。"""

        totals = self.calculate_total()

        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # 封面页
            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)

            # 明细项表
            items_df = self.export_to_df()
            items_df.to_excel(writer, sheet_name='Line Items', index=False)

            # 成本分解表
            breakdown_df = pd.DataFrame([totals['cost_summary']])
            breakdown_df.to_excel(writer, sheet_name='Cost Breakdown', index=False)

            # 加价项表
            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]:
        """校验估价中常见的潜在问题。"""

        issues = []

        if not self.items:
            issues.append("估价中无明细项")

        for item in self.items:
            if item.quantity <= 0:
                issues.append(f"第 {item.line_number} 行：数量无效")
            if item.unit_cost < 0:
                issues.append(f"第 {item.line_number} 行：单价为负值")
            if not item.description:
                issues.append(f"第 {item.line_number} 行：缺少描述")

        if not self.markups:
            issues.append("未定义加价项（如管理费、利润等）")

        return issues

快速上手

# 创建估价实例
estimate = EstimateBuilder("Office Building A", "PRJ-2024-001")

# 添加明细项
estimate.add_item("01.01", "Site Preparation", 5000, "SF", 2.50, CostCategory.OTHER)
estimate.add_item("03.01", "Concrete Foundation", 200, "CY", 350, CostCategory.MATERIAL)
estimate.add_item("03.02", "Foundation Formwork", 1500, "SF", 8.50, CostCategory.LABOR)
estimate.add_item("05.01", "Structural Steel", 50, "TON", 4500, CostCategory.SUBCONTRACTOR)

# 设置加价项
estimate.set_standard_markups(overhead=0.15, profit=0.10, contingency=0.05)

# 计算总金额
result = estimate.calculate_total()
print(f"Direct Cost: ${result['cost_summary']['direct_cost']:,.2f}")
print(f"Grand Total: ${result['grand_total']:,.2f}")

常见使用场景

1. 按成本类别统计

summary = estimate.get_cost_summary()
print(f"Labor: ${summary.labor:,.2f}")
print(f"Material: ${summary.material:,.2f}")

2. 导出为 Excel

estimate.export_to_excel("estimate_output.xlsx")

3. 估价校验

issues = estimate.validate()
for issue in issues:
    print(f"Warning: {issue}")

参考资料

• DDC Book：第 3.1 章 — 成本计算与估价
• 网站：https://datadrivenconstruction.io