CWICR Overhead & Markup Calculator
Business Case
Problem Statement
Direct costs need additional markups:
-
General overhead (office, insurance)
-
Project overhead (site costs)
-
Profit margins
-
Bonds and insurance
Solution
Systematic markup application to CWICR direct costs with configurable rates for overhead, profit, bonds, and other indirect costs.
Business Value
-
Complete pricing - From cost to selling price
-
Configurable rates - By project/client type
-
Transparency - Clear markup breakdown
-
Consistency - Standard markup application
Technical Implementation
import pandas as pd from typing import Dict, Any, List, Optional from dataclasses import dataclass, field from enum import Enum
class MarkupType(Enum): """Types of markup.""" OVERHEAD = "overhead" PROFIT = "profit" BOND = "bond" INSURANCE = "insurance" CONTINGENCY = "contingency" TAX = "tax" ESCALATION = "escalation" CUSTOM = "custom"
class MarkupMethod(Enum): """Markup calculation methods.""" ON_COST = "on_cost" # Markup on direct cost ON_COST_PLUS = "on_cost_plus" # Markup on cost + previous markups FIXED = "fixed" # Fixed amount
@dataclass class MarkupItem: """Single markup item.""" name: str markup_type: MarkupType rate: float method: MarkupMethod base_amount: float markup_amount: float
@dataclass class MarkupSchedule: """Complete markup schedule.""" name: str markups: List[MarkupItem]
def get_total_rate(self) -> float:
"""Get combined markup rate."""
return sum(m.rate for m in self.markups)
@dataclass class PricingResult: """Complete pricing with all markups.""" direct_cost: float labor_cost: float material_cost: float equipment_cost: float subcontractor_cost: float markups: List[MarkupItem] total_markup: float total_price: float markup_percentage: float
Standard markup templates
MARKUP_TEMPLATES = { 'residential': { 'overhead': 0.10, 'profit': 0.10, 'contingency': 0.05 }, 'commercial': { 'overhead': 0.12, 'profit': 0.08, 'bond': 0.015, 'insurance': 0.02, 'contingency': 0.05 }, 'industrial': { 'overhead': 0.15, 'profit': 0.08, 'bond': 0.02, 'insurance': 0.025, 'contingency': 0.08 }, 'government': { 'overhead': 0.12, 'profit': 0.06, 'bond': 0.025, 'contingency': 0.05 }, 'subcontractor': { 'overhead': 0.08, 'profit': 0.10 } }
class CWICROverheadMarkup: """Apply overhead and markup to CWICR estimates."""
def __init__(self, cwicr_data: pd.DataFrame = None):
self.cost_data = cwicr_data
if cwicr_data is not None:
self._index_data()
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_template(self, template_name: str) -> Dict[str, float]:
"""Get markup template."""
return MARKUP_TEMPLATES.get(template_name, MARKUP_TEMPLATES['commercial'])
def create_markup_schedule(self,
name: str,
markups: Dict[str, float],
method: MarkupMethod = MarkupMethod.ON_COST) -> MarkupSchedule:
"""Create markup schedule from rates."""
items = []
for markup_name, rate in markups.items():
markup_type = MarkupType.CUSTOM
for mt in MarkupType:
if mt.value in markup_name.lower():
markup_type = mt
break
items.append(MarkupItem(
name=markup_name,
markup_type=markup_type,
rate=rate,
method=method,
base_amount=0,
markup_amount=0
))
return MarkupSchedule(name=name, markups=items)
def apply_markups(self,
direct_cost: float,
schedule: MarkupSchedule,
cost_breakdown: Dict[str, float] = None) -> PricingResult:
"""Apply markup schedule to direct cost."""
if cost_breakdown is None:
cost_breakdown = {
'labor': direct_cost * 0.40,
'material': direct_cost * 0.45,
'equipment': direct_cost * 0.10,
'subcontractor': direct_cost * 0.05
}
markup_items = []
running_total = direct_cost
for markup in schedule.markups:
if markup.method == MarkupMethod.ON_COST:
base = direct_cost
elif markup.method == MarkupMethod.ON_COST_PLUS:
base = running_total
else: # FIXED
base = 1
amount = base * markup.rate
markup_items.append(MarkupItem(
name=markup.name,
markup_type=markup.markup_type,
rate=markup.rate,
method=markup.method,
base_amount=round(base, 2),
markup_amount=round(amount, 2)
))
running_total += amount
total_markup = running_total - direct_cost
markup_pct = (total_markup / direct_cost * 100) if direct_cost > 0 else 0
return PricingResult(
direct_cost=round(direct_cost, 2),
labor_cost=round(cost_breakdown.get('labor', 0), 2),
material_cost=round(cost_breakdown.get('material', 0), 2),
equipment_cost=round(cost_breakdown.get('equipment', 0), 2),
subcontractor_cost=round(cost_breakdown.get('subcontractor', 0), 2),
markups=markup_items,
total_markup=round(total_markup, 2),
total_price=round(running_total, 2),
markup_percentage=round(markup_pct, 1)
)
def price_estimate(self,
items: List[Dict[str, Any]],
template: str = 'commercial') -> PricingResult:
"""Price complete estimate with markups."""
# Calculate direct costs
labor = 0
material = 0
equipment = 0
subcontractor = 0
for item in items:
code = item.get('work_item_code', item.get('code'))
qty = item.get('quantity', 0)
if self._code_index is not None and code in self._code_index.index:
wi = self._code_index.loc[code]
labor += float(wi.get('labor_cost', 0) or 0) * qty
material += float(wi.get('material_cost', 0) or 0) * qty
equipment += float(wi.get('equipment_cost', 0) or 0) * qty
subcontractor += item.get('subcontractor_cost', 0)
direct_cost = labor + material + equipment + subcontractor
cost_breakdown = {
'labor': labor,
'material': material,
'equipment': equipment,
'subcontractor': subcontractor
}
# Get template and create schedule
rates = self.get_template(template)
schedule = self.create_markup_schedule(template, rates)
return self.apply_markups(direct_cost, schedule, cost_breakdown)
def calculate_bid_price(self,
direct_cost: float,
overhead_rate: float = 0.12,
profit_rate: float = 0.08,
bond_rate: float = 0.015,
contingency_rate: float = 0.05) -> Dict[str, Any]:
"""Calculate bid price with standard markups."""
overhead = direct_cost * overhead_rate
subtotal1 = direct_cost + overhead
profit = subtotal1 * profit_rate
subtotal2 = subtotal1 + profit
bond = subtotal2 * bond_rate
subtotal3 = subtotal2 + bond
contingency = direct_cost * contingency_rate
total = subtotal3 + contingency
return {
'direct_cost': round(direct_cost, 2),
'overhead': round(overhead, 2),
'overhead_rate': f"{overhead_rate:.1%}",
'profit': round(profit, 2),
'profit_rate': f"{profit_rate:.1%}",
'bond': round(bond, 2),
'bond_rate': f"{bond_rate:.1%}",
'contingency': round(contingency, 2),
'contingency_rate': f"{contingency_rate:.1%}",
'bid_price': round(total, 2),
'total_markup': round(total - direct_cost, 2),
'total_markup_pct': round((total - direct_cost) / direct_cost * 100, 1)
}
def compare_markup_scenarios(self,
direct_cost: float,
scenarios: Dict[str, Dict[str, float]]) -> pd.DataFrame:
"""Compare different markup scenarios."""
results = []
for name, rates in scenarios.items():
schedule = self.create_markup_schedule(name, rates)
pricing = self.apply_markups(direct_cost, schedule)
results.append({
'Scenario': name,
'Direct Cost': pricing.direct_cost,
'Total Markup': pricing.total_markup,
'Markup %': pricing.markup_percentage,
'Total Price': pricing.total_price
})
return pd.DataFrame(results)
def export_pricing(self,
result: PricingResult,
output_path: str) -> str:
"""Export pricing breakdown to Excel."""
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
# Summary
summary_df = pd.DataFrame([{
'Direct Cost': result.direct_cost,
'Labor': result.labor_cost,
'Material': result.material_cost,
'Equipment': result.equipment_cost,
'Subcontractor': result.subcontractor_cost,
'Total Markup': result.total_markup,
'Markup %': result.markup_percentage,
'Total Price': result.total_price
}])
summary_df.to_excel(writer, sheet_name='Summary', index=False)
# Markup Details
markup_df = pd.DataFrame([
{
'Markup': m.name,
'Type': m.markup_type.value,
'Rate': f"{m.rate:.1%}",
'Base': m.base_amount,
'Amount': m.markup_amount
}
for m in result.markups
])
markup_df.to_excel(writer, sheet_name='Markups', index=False)
return output_path
Quick Start
Initialize markup calculator
markup = CWICROverheadMarkup()
Calculate bid price
bid = markup.calculate_bid_price( direct_cost=1000000, overhead_rate=0.12, profit_rate=0.08 )
print(f"Direct Cost: ${bid['direct_cost']:,.2f}") print(f"Bid Price: ${bid['bid_price']:,.2f}") print(f"Total Markup: {bid['total_markup_pct']}%")
Common Use Cases
- Template-Based Pricing
cwicr = pd.read_parquet("ddc_cwicr_en.parquet") markup = CWICROverheadMarkup(cwicr)
items = [ {'work_item_code': 'CONC-001', 'quantity': 150}, {'work_item_code': 'STRL-002', 'quantity': 25} ]
pricing = markup.price_estimate(items, template='commercial') print(f"Total Price: ${pricing.total_price:,.2f}")
- Compare Scenarios
scenarios = { 'Aggressive': {'overhead': 0.08, 'profit': 0.05}, 'Standard': {'overhead': 0.12, 'profit': 0.08}, 'Premium': {'overhead': 0.15, 'profit': 0.12} }
comparison = markup.compare_markup_scenarios(1000000, scenarios) print(comparison)
- Custom Markup Schedule
schedule = markup.create_markup_schedule('Custom', { 'overhead': 0.10, 'profit': 0.08, 'bond': 0.02, 'insurance': 0.015 })
pricing = markup.apply_markups(500000, schedule)
Resources
-
GitHub: OpenConstructionEstimate-DDC-CWICR
-
DDC Book: Chapter 3.1 - Cost Markup Methods