Z Roofing Pricing Formula Checker

If referenced, show source as filename+cell

Roof Measurements

= squares (Dashboard C19)

(Dashboard C23)

(Dashboard B15)

Roofing System

⚠️ Source: Materials worksheet SUM(H:H) - Enter actual value from Excel

⚠️ Source: C32 = SUM(Labor!H12,H14,H15) - Enter actual value from Excel

Additional Items (✓ Verified in Excel)

Business Rules (✓ Verified)

C26 → C39

⚠️ Below 20% (D26/E26 warning)

C38: =0.12*SUM

C40: =0.03*SUM

All *1.1 formulas

Total Estimate (C42)

Per Square

Total Squares

Roof Type

Waste Adj

Cost Breakdown (Dashboard Column C)

C30: Materials =SUM(Materials!H:H)
C31: Sales Tax =0.07*C30
C32: Labor =SUM(Labor!H12,H14,H15)
C33: Heavy Equipment =IF(D15="Boom Req",2000,0)
$2,000.00
C34: Tile Debris =IF(Tile,150,0)
$150.00
C35: Bird Stop =IF(Tile,(sqft/100)*27,0)
C37: Dump Fees =0.25*(C19)+0.26*(C23)
SUM(C30:C37)
C38: Overhead =*SUM(C30:C37)
SUM(C30:C38)
C39: Pre-Comm PM =C26*SUM(C30:C38)
C40: Commission =*SUM(C30:C39)
C41: Actual PM =C39-C40
C42: TOTAL =SUM(C30:C38)+C40+C41

Formula Verification

Source File

ZRW pricing engine with Macros.xlsm

Analysis: PRICING_ENGINE_ANALYSIS.md, extracted_formulas.txt

✅ Verified Formulas (Dashboard Worksheet)

Cell
Formula
Description
C30
=SUM(Materials!H:H)
Materials total
C31
=0.07*C30
7% sales tax
C32
=SUM(Labor!H12,H14,H15)
Labor costs
C33
=IF(D15="Boom Req",2000,0)
$2,000 boom lift
C34
=IF(OR(B19="Concrete Tile"...),150,0)
$150 tile debris
C35
=IF(Tile,(sqft/100)*27,0)
Bird stop $0.27/sqft
C37
=0.25*(C19)
Dump fees 25¢/sqft
C54
=0.26*(C23)
Flat dump 26¢/sqft
C38
=0.12*SUM(C30:C37)
12% overhead
C39
=C26*SUM(C30:C38)
Profit margin
C40
=0.03*SUM(C30:C39)
3% commission
C41
=C39-C40
Net profit
C42
=SUM(C30:C38)+C40+C41
TOTAL
C43
=C42/SUM(C19,E19,G19)
Price per sq ft
ALL
*1.1
10% waste factor

⚠️ NOT in Excel - Requires Manual Entry

Materials Cost
→ Enter from Materials!SUM(H:H)
Labor Cost
→ Enter from Labor!SUM(H12,H14,H15)
C36: Crew Per Diem
VLOOKUP - lookup table not extracted

❌ NOT Found in Excel (Excluded)

• Building Permit - No formula found

• Product unit prices - Uses INDIRECT lookups to Parts List

• Labor rates per square - Complex productivity formulas

• Dumpster pricing - Not in Dashboard

Calculation Trace

C30 Materials:
C31 Tax (C30×0.07):
C32 Labor:
C33 Boom:
C34 Tile Debris:
C35 Bird Stop:
C37 Dump:
SUM(C30:C37):
C38 Overhead (×):
SUM(C30:C38):
C39 PM (×):
C40 Comm ((C30:C39)×):
C41 Net PM (C39-C40):
C42 TOTAL:
Formula check: SUM(C30:C38) + C40 + C41 =