cd /vercel/sandbox && python skills/xlsx/scripts/workbook_sample.py menlo_creek_uea.xlsx --out samples 2>&1 | head -50; ls samples 2>/dev/null | head -50

cd /vercel/sandbox && ls -la *.xlsx

cd /vercel/sandbox && ls -la menlo_creek_uea.xlsx && python skills/xlsx/scripts/workbook_sample.py menlo_creek_uea.xlsx --out samples 2>&1 | tail -20

cd /vercel/sandbox && cat samples/sample_manifest.json | python3 -c "
import json,sys
d=json.load(sys.stdin)
for s in d['sheets']:
    print(s['sheet_name'], s.get('sample_files'))
" 2>&1 | head -40

cd /vercel/sandbox && python3 -c "
import json
d=json.load(open('samples/sample_manifest.json'))
print(list(d.keys()))
"
ls samples | head -80

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
targets = ["Rent Roll Sept16","River Run T12 Sept16","Sept 17 T12 - RedIQ","Sept 17 T12 - RedIQ RAW","RR Analysis","RedIQ - Floor Plan Summary","RedIQ - Rent Roll","DD Tracker","DD CapEx Schedule","DD Changes","OpEx Comps"]
for name in targets:
    ws = wb[name]
    print("=====", name, ws.dimensions)
    for r in range(1, min(6, ws.max_row+1)):
        row_vals = []
        for c in range(1, min(10, ws.max_column+1)):
            cell = ws.cell(row=r, column=c)
            row_vals.append(cell.value)
        print(r, row_vals)
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Debt"]
print(ws.dimensions)
for r in range(1, 40):
    row=[]
    for c in range(1,15):
        cell=ws.cell(row=r,column=c)
        if cell.value is not None:
            row.append((cell.coordinate, cell.value))
    if row:
        print(r, row)
EOF

import openpyxl, json

SRC = "menlo_creek_uea.xlsx"
OUT = "Menlo_Underwriting_Template_clean.xlsx"

wb = openpyxl.load_workbook(SRC, data_only=False, keep_vba=False)

cleared_log = []

def clear_cell(ws, coord):
    cell = ws[coord]
    if cell.data_type == 'f' or (isinstance(cell.value, str) and cell.value.startswith('=')):
        return  # never touch formulas
    if cell.value is not None:
        cleared_log.append((ws.title, coord))
        cell.value = None

def clear_body(ws, start_row, end_row=None, start_col=1, end_col=None):
    end_row = end_row or ws.max_row
    end_col = end_col or ws.max_column
    for r in range(start_row, end_row + 1):
        for c in range(start_col, end_col + 1):
            cell = ws.cell(row=r, column=c)
            if cell.data_type == 'f' or (isinstance(cell.value, str) and cell.value.startswith('=')):
                continue
            if cell.value is not None:
                cleared_log.append((ws.title, cell.coordinate))
                cell.value = None

# --- Summary Info: property identity + purchase economics scalars ---
ws = wb["Summary Info"]
for coord in ["B1","K1","B2","K2","D4","D5","D6","D7","D8","D9","E9","D10","E10","D11","D14",
              "D18","D19","D20"]:
    clear_cell(ws, coord)

# --- UEA: property identity header + hardcoded 6-yr assumption schedule (non-formula literals) ---
ws = wb["UEA"]
clear_cell(ws, "B1")
clear_cell(ws, "B2")
clear_body(ws, start_row=4, end_row=40, start_col=5, end_col=17)  # occupancy/growth literal rows

# --- Raw rent roll / T12 / DD import sheets: clear data body, keep header/label rows ---
raw_sheets = {
    "Rent Roll Sept16": 1,       # clear rows 1-4 identity + data rows from 6
    "River Run T12 Sept16": 1,   # identity rows 1-4 + data from row 6
    "Sept 17 T12 - RedIQ": 1,
    "Sept 17 T12 - RedIQ RAW": 1,
    "RR Analysis": 1,
    "RedIQ - Floor Plan Summary": 1,
    "RedIQ - Rent Roll": 1,
    "DD Tracker": 4,             # keep header rows 1-3, clear log rows from 4
    "DD CapEx Schedule": 6,      # keep definitions header, clear body
    "DD Changes": 5,             # keep header rows 1-4, clear log rows from 5
    "OpEx Comps": 5,             # keep header row 4 (property names ARE the header row - see note), clear data rows 5+
}

# Identity/title rows (rows 1-4) in raw import sheets: clear text identifying THIS deal/property
identity_clear = {
    "Rent Roll Sept16": [(2,1)],           # "River Run at Naperville..."
    "River Run T12 Sept16": [(1,1)],
    "Sept 17 T12 - RedIQ": [(1,2)],
    "RedIQ - Floor Plan Summary": [(1,1)],
    "RedIQ - Rent Roll": [(2,3)],
}
for sheet, cells in identity_clear.items():
    ws = wb[sheet]
    for (r, c) in cells:
        cell = ws.cell(row=r, column=c)
        if not (cell.data_type == 'f' or (isinstance(cell.value, str) and cell.value.startswith('='))):
            cleared_log.append((sheet, cell.coordinate))
            cell.value = None

# OpEx Comps: header row 4 holds the actual comp property names for THIS deal - clear those too (cols D onward; keep B4='Property', C4='Subject')
ws = wb["OpEx Comps"]
for c in range(5, ws.max_column + 1):
    cell = ws.cell(row=4, column=c)
    if cell.value is not None and cell.data_type != 'f':
        cleared_log.append((ws.title, cell.coordinate))
        cell.value = None

for sheet, start_row in raw_sheets.items():
    ws = wb[sheet]
    clear_body(ws, start_row=start_row)

wb.save(OUT)
print(json.dumps({"status": "success", "output": OUT, "cells_cleared": len(cleared_log)}))

cd /vercel/sandbox && python3 clean_template.py

cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py Menlo_Underwriting_Template_clean.xlsx 2>&1 | tail -40

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
for name in ["Property Returns","Op. Statement","CF & Returns","JV Metrics","Venture","Rollover"]:
    ws = wb[name]
    print("=====",name, ws.dimensions)
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Property Returns"]
for r in range(1,12):
    for c in range(1,16):
        cell = ws.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Op. Statement"]
for r in range(1,4):
    for c in range(1,15):
        cell = ws.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Op. Statement"]
count=0
for r in range(2,86):
    for col in ['F','G','H']:
        cell = ws[f"{col}{r}"]
        if cell.value is not None:
            count+=1
print("non-empty F:H cells", count)
# check any formulas among them
f=0
for r in range(2,86):
    for col in ['F','G','H']:
        cell = ws[f"{col}{r}"]
        if isinstance(cell.value,str) and cell.value.startswith('='):
            f+=1
print("formulas among them", f)
EOF

cd /vercel/sandbox && python3 clean_template.py && python skills/xlsx/scripts/recalc.py Menlo_Underwriting_Template_clean.xlsx 2>&1 | tail -30

cd /vercel/sandbox && timeout 300 python skills/xlsx/scripts/workbook_integrity_scan.py Menlo_Underwriting_Template_clean.xlsx --json integrity.json 2>&1 | tail -5; echo "EXIT:$?"; python3 -c "
import json
d=json.load(open('integrity.json'))
print(d.get('summary'))
highs=[f for f in d.get('findings',[]) if f.get('severity')=='high']
print('HIGH count', len(highs))
for h in highs[:20]:
    print(h.get('sheet'), h.get('cell'), h.get('type'), h.get('message',h.get('detail','')))
"

cd /vercel/sandbox && timeout 300 python skills/xlsx/scripts/workbook_integrity_scan.py menlo_creek_uea.xlsx --json integrity_orig.json 2>&1 | tail -3
python3 -c "
import json
d=json.load(open('integrity_orig.json'))
highs=[f for f in d.get('findings',[]) if f.get('severity')=='high']
print('ORIGINAL HIGH count', len(highs))
from collections import Counter
print(Counter(f.get('type') for f in highs))
"

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
for name, dn in wb.defined_names.items():
    print(name, dn.attr_text)
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
for key in ["Name","Units","SF_Unit"]:
    if key in wb.defined_names:
        print(key, wb.defined_names[key].attr_text)
    else:
        print(key, "NOT FOUND")
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Summary Info"]
for r in range(1,20):
    for c in range(1,12):
        cell = ws.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Summary Info"]
for r in range(18,35):
    for c in range(1,12):
        cell = ws.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["UEA"]
for r in range(1,20):
    for c in range(1,20):
        cell = ws.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
    print('---')
EOF

cd /vercel/sandbox && python3 clean_template.py && python skills/xlsx/scripts/recalc.py Menlo_Underwriting_Template_clean.xlsx 2>&1 | tail -20

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('Menlo_Underwriting_Template_clean.xlsx', data_only=False)
needles = ["Menlo Creek","Duluth","Satellite","Simpson","Walker & Dunlop","River Run","Naperville","Pat Jones","Arbors of Brookdale","Reserve at Evanston","Maple Knoll","59000000","38950000"]
hits=[]
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            v = cell.value
            if isinstance(v, str):
                for n in needles:
                    if n.lower() in v.lower() and not v.startswith('='):
                        hits.append((ws.title, cell.coordinate, v))
            elif isinstance(v,(int,float)):
                if v in (59000000,38950000):
                    hits.append((ws.title, cell.coordinate, v))
print(len(hits))
for h in hits[:60]:
    print(h)
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Property Returns"]
for coord in ["N13","O13","P13","Q13","Z13","AA13","AB13"]:
    print(coord, repr(ws[coord].value))
print("---Charts context---")
ws2 = wb["Charts"]
for r in range(70,80):
    for c in range(1,35):
        cell = ws2.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
EOF

import openpyxl, json, re

F = "Menlo_Underwriting_Template_clean.xlsx"
wb = openpyxl.load_workbook(F, data_only=False)

# exact numeric literals identified as deal-specific (clear only when NOT a formula)
NUMERIC_HITS = {
    ("Property Returns", "P13"): 59000000,
    ("Property Returns", "AA13"): 59000000,
    ("Charts", "AC77"): 59000000,
}

TEXT_TOKENS = [
    "River Run at Naperville", "River Run", "Naperville",
    "Menlo Creek", "Duluth, GA 30096", "Duluth, GA", "Duluth / Gwinnet",
    "4370 Satellite Blvd", "Simpson / Simpson",
    "Walker & Dunlop - Pat Jones", "Walker & Dunlop", "Pat Jones",
    "Arbors of Brookdale", "Reserve at Evanston", "Maple Knoll",
]

changed = []

for (sheet, coord), expected in NUMERIC_HITS.items():
    ws = wb[sheet]
    cell = ws[coord]
    if cell.data_type != 'f' and cell.value == expected:
        changed.append((sheet, coord, "cleared numeric", cell.value))
        cell.value = None

for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            v = cell.value
            if not isinstance(v, str) or v.startswith('='):
                continue
            new_v = v
            for tok in TEXT_TOKENS:
                if tok.lower() in new_v.lower():
                    pattern = re.compile(re.escape(tok), re.IGNORECASE)
                    new_v = pattern.sub("[Property]", new_v)
            if new_v != v:
                # collapse leftover artifacts like "[Property] - [Property]" or trailing separators
                new_v = re.sub(r"\[Property\]\s*[-/]\s*\[Property\]", "[Property]", new_v)
                new_v = new_v.strip()
                if new_v in ("", "-", "/"):
                    new_v = None
                changed.append((ws.title, cell.coordinate, "text scrub", f"{v!r} -> {new_v!r}"))
                cell.value = new_v

wb.save(F)
print(json.dumps({"status": "success", "changed_count": len(changed)}))
with open("scrub_log.json", "w") as f:
    json.dump(changed, f, indent=2, default=str)

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('Menlo_Underwriting_Template_clean.xlsx', data_only=False)
needles = ["Menlo Creek","Duluth","Satellite","Simpson","Walker & Dunlop","River Run","Naperville","Pat Jones","Arbors of Brookdale","Reserve at Evanston","Maple Knoll"]
hits=[]
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            v = cell.value
            if isinstance(v, str) and not v.startswith('='):
                for n in needles:
                    if n.lower() in v.lower():
                        hits.append((ws.title, cell.coordinate, v))
print(len(hits))
for h in hits: print(h)
EOF
python skills/xlsx/scripts/recalc.py Menlo_Underwriting_Template_clean.xlsx 2>&1 | tail -15

cd /vercel/sandbox && cp Menlo_Underwriting_Template_clean.xlsx "Menlo Underwriting Template.xlsx" && ls -la "Menlo Underwriting Template.xlsx"

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
for key in ["Price"]:
    if key in wb.defined_names:
        print(key, wb.defined_names[key].attr_text)
EOF

cd /vercel/sandbox && python3 << 'EOF'
import openpyxl
wb = openpyxl.load_workbook('menlo_creek_uea.xlsx', data_only=False)
ws = wb["Venture"]
for r in range(1,25):
    for c in range(1,10):
        cell = ws.cell(row=r,column=c)
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
EOF

# Menlo Underwriting Template — Population Agent

## 1. Role and Model Summary

This agent populates the **Menlo Underwriting Template** (a Pollack Shores/HVP-style multifamily acquisition and joint-venture underwriting model) for a specific deal. The template prices the acquisition of a stabilized or light value-add apartment community, projects a 6-to-11-year hold on an annual assumption grid that drives a monthly leveraged cash flow, and reports unlevered (F&C) and levered returns alongside a JV capital structure and promote.

The dependency chain runs: **property facts and purchase terms** (Summary Info, Property Returns) set the acquisition basis → **rent roll and T12 imports** (Rent Roll, T12, RedIQ tabs) establish the in-place starting point → the **UEA assumption grid** (occupancy ramp, rent growth, expense growth, recurring capital) projects those historicals forward → **Rollover** rolls the unit-level rent roll forward month by month → **Debt** amortizes the acquisition loan → **CF & Returns / Property Returns** assemble the monthly leveraged cash flow and back out unlevered and levered IRR, equity multiple, and NCF yields → **Venture / JV Metrics** allocate that cash flow across the fund's capital stack → **Sensitivity Analysis** stress-tests price and exit cap rate around the base case. **OpEx Comps** and **RR Analysis** are supporting benchmarks (expense comps and unit-level loss-to-lease) that feed judgment calls in the UEA grid rather than flowing through formulas automatically.

The template also carries a live due-diligence log (DD Tracker, DD Changes, DD CapEx Schedule) that is not part of initial deal population — it is a running record the user updates as diligence findings move the underwriting.

## 2. Field Guide

### 2.1 Deal identity and property facts — `Summary Info`

| Field | Coordinate | Intent | Source class | Required |
|---|---|---|---|---|
| Property Name | `Summary Info!B2` | Feeds the `Name` defined range, referenced by every sheet header | user_required | Yes |
| City, State | `Summary Info!B3` | Header line under property name | datamart_preferred_user_fallback | Yes |
| Total Units | `Summary Info!D6` | Feeds the `Units` defined range — denominator for every per-unit metric in the model | datamart | Yes |
| Total Net Rentable SF | `Summary Info!C7` | Aggregate building SF | datamart | Yes |
| Average Unit Size | `Summary Info!D7` | Feeds `SF_Unit`; average SF per unit | datamart | Yes |
| Year Constructed | `Summary Info!D8` | Vintage, used in comps framing only (not formula-driven elsewhere) | datamart | Yes |
| Street Address | `Summary Info!D11` | Physical address | datamart_preferred_user_fallback | Yes |
| City, State Zip | `Summary Info!D12` | Full mailing address line | datamart_preferred_user_fallback | Yes |
| Owner / Manager | `Summary Info!D13` | Seller/manager of record, informational | user_optional | No |
| Broker | `Summary Info!D14` | Listing broker, informational | user_optional | No |
| Property Class / Location | `Summary Info!D15` | e.g. "B+ / A-" quality/location grade shorthand | ai_estimate (confirm with user) | No |

Do not write `Summary Info!D9`, `D10`, `D16`, `D18`, `D19`, `D20` — these are formulas (parking ratio, units/acre, revision date, and purchase-summary roll-ups that pull from Property Returns). Writing over them breaks the sheet.

### 2.2 Purchase terms and deal economics — `Property Returns`

| Field | Coordinate | Intent | Source class | Required |
|---|---|---|---|---|
| Purchase Price | `Property Returns!D7` | Acquisition basis; drives `Price` named range and every per-unit/per-SF metric | user_required | Yes |
| Loan Amount | `Property Returns!I10` | 1st-lien proceeds; Debt sheet pulls this as `F8` | user_required | Yes |
| Acquisition Fee ($) | `Property Returns!D11` | Sponsor acquisition fee, dollar amount (cross-checked against 0.6779% convention seen in the source deal — confirm the fee % with the user rather than assuming it) | user_required | Yes |
| Year of Sale (hold period, yrs) | `Property Returns!M7` | Exit year; the UEA grid and CF & Returns size off this | user_required | Yes |

`Property Returns!M9` (Exit Cap Rate) is a formula pulling from `Sensitivity Analysis!C32`. **Open question:** the exact base-case exit-cap input cell on the Sensitivity Analysis sheet was not resolved with full confidence during introspection — confirm its address before writing to it; do not guess.

Two literal price points were found embedded directly in the Property Returns and Charts sensitivity grids (`Property Returns!P13`, `AA13`, `Charts!AC77`) duplicating the purchase price as a hardcoded sensitivity-axis value rather than deriving it from `D7`. This is a pre-existing template quirk, not something this agent should silently "fix" — flag it to the user once, then leave it alone.

### 2.3 Six-year cash flow assumptions — `UEA`

| Field | Coordinate(s) | Intent | Source class | Required |
|---|---|---|---|---|
| Physical Occupancy (T12 base + Yr 1–2 ramp) | `UEA!I5`, `H8`, `I8`, `L8` | Occupancy trajectory from trailing actual to stabilized | forecasting skill pre-fill, user confirms | Yes |
| Other Income growth % | `UEA!G12` | Annual growth applied to other income line | template_default (editable) | No |
| Operating Expense growth % | `UEA!G13` | Annual expense growth | forecasting skill pre-fill, user confirms | Yes |
| Recurring Capital $/unit (Yr 1–5) | `UEA!F15:K15` | Reserve/capex per unit by year, ramps toward a stabilized ~$300–400/unit | user_required (renovation/reserve budget) | Yes |

Market rent growth (`UEA!G10`) and in-place rent growth (`UEA!G11`) are formulas that pull from `Sensitivity Analysis!B61` and the Rollover-derived rent roll, respectively — do not write to them directly; they move when the Rollover/Sensitivity inputs move.

### 2.4 Debt assumptions — `Debt`

| Field | Coordinate | Intent | Source class | Required |
|---|---|---|---|---|
| Index Rate | `Debt!C4` | Base index (e.g. SOFR/LIBOR equivalent) for a floating loan | datamart (mortgage_rates) preferred | Yes |
| Spread | `Debt!D4` | Lender spread over index | user_required (loan-quote specific) | Yes |
| Debt Type toggle | `Debt!F7` | One of `FIXED` / `FLOATING` / `SWAP` — rewires the amortization schedule's rate logic | user_required (mode) | Yes |
| Term (years) | `Debt!F11` | Loan term | user_required | Yes |
| Amortization (years) | `Debt!F12` | Amortization period | user_required | Yes |
| Interest-Only period (months) | `Debt!F13` | I/O period before amortization begins | user_required | Yes |

`Debt!F8` (Debt Amount) and `Debt!F9` (Interest Rate) are formulas pulling from Property Returns and the index/spread cells above — never write to them directly.

### 2.5 Historical financials — paste-in source tables (table-level mapping)

These sheets are structured as direct exports from a property-management system or RedIQ, not scalar input cells. Map at the table level; preserve the header row exactly as the export produces it so downstream formulas (which reference these tables by column) keep resolving.

| Sheet | Header row | Data starts | Content | Source class |
|---|---|---|---|---|
| `Rent Roll Sept16` | Row 5 | Row 6 | Unit-level rent roll (unit, type, resident, market rent, charges) | user-uploaded document / datamart rent-roll fallback |
| `River Run T12 Sept16` | Row 5 | Row 6 | Monthly trailing-12 P&L, property-management export format | user-uploaded T12 document |
| `Sept 17 T12 - RedIQ` / `Sept 17 T12 - RedIQ RAW` | Rows 3–5 | Row 6 | RedIQ-normalized T12, annual + monthly cuts | user-uploaded document (RedIQ export) |
| `RedIQ - Rent Roll` | Table `RentRoll` (`B9:AK381`) | Row 10 | RedIQ-normalized rent roll | user-uploaded document |
| `RedIQ - Floor Plan Summary` | Row 5 | Row 6 | Unit-mix / floor-plan summary with occupancy status | user-uploaded document or derived from rent roll |

Never attempt to re-map these column-by-column against a different deal's export — a property-management export's column order can vary by system. If the user's uploaded rent roll or T12 doesn't match this layout, use the document-reconciliation protocol and the rental-comps/T12 parsing judgment to align columns before pasting, rather than forcing a rigid coordinate map.

### 2.6 Unit-level rent analysis — `RR Analysis`

| Field | Coordinate(s) | Intent | Source class |
|---|---|---|---|
| Unit #, SF, Months-to-expiration, Market Rent, In-Place Rent, Concessions | `RR Analysis!A4:H(n)`, header row 3 | Unit-by-unit loss-to-lease build; `Eff Rent` column is a formula (`=Market − In-Place`) | Market Rent from rental-comps skill; In-Place from the rent roll import above |

### 2.7 Operating expense comps — `OpEx Comps`

| Field | Coordinate | Intent | Source class |
|---|---|---|---|
| Comp property names (header) | `OpEx Comps!D4:K4` | Names of 4–6 comparable properties for expense benchmarking | sales-comps / rental-comps skill output |
| Comp expense line items | `OpEx Comps!D5:K77` | Per-line expense figures for each comp | comps skill output, user-confirmed |

### 2.8 JV capital structure — `Venture` / `JV Metrics`

| Field | Coordinate | Intent | Source class | Required |
|---|---|---|---|---|
| Fund Equity capacity (e.g. "HVP Equity") | `Venture!B6` | Fund-level committed equity pool, not this deal's specific equity ask | template_default (confirm whether reusable across deals or unique to this raise) | No |
| Co-Investor Equity capacity | `Venture!B7` | Co-investment equity pool | template_default | No |
| Target Leverage | `Venture!C18` | Target LTV for the venture capital stack | user_required | Yes |

Everything else on `Venture`, `JV Metrics`, `CF & Returns`, `Property Returns` (below the inputs listed above), `Rollover`, `Sensitivity Analysis`, `Charts`, `Portfolio Return Comparison`, and `Hist-Proj` is formula-driven output. Read results from these; do not write to them.

### 2.9 Diligence log (not part of initial population)

`DD Tracker`, `DD CapEx Schedule`, `DD Changes` hold a dated log of underwriting changes discovered during diligence. `DD CapEx Schedule` also carries a live **external workbook link** (`'[136]Table 1'!...`) to a third-party capital-needs assessment — per user instruction, investigate but never attempt to resolve or repoint this link. Do not populate these tabs during initial intake; offer to log an entry only when the user reports a specific DD finding.

## 3. Intake Design

One combined opening form:
- Property/entity picker (resolves Summary Info identity + facts against the datamart)
- Rent roll upload (optional — falls back to datamart rent-by-unit-type if not provided)
- T12 / trailing financials upload (optional — falls back to datamart operating-history topic)
- Purchase price, target loan amount, acquisition fee, hold period (years)
- Debt terms: index, spread, debt type (FIXED/FLOATING/SWAP), term, amortization, I/O period
- Target leverage (venture LTV)
- Renovation/capex budget per unit and ramp

Then targeted gap-fill only for `user_required` fields the form left blank (loan terms if no lender quote yet; exit cap rate confirmation; recurring capital schedule).

## 4. Retrieval Plan

| Field(s) | Datamart topic / skill | Entity grain | Fallback order |
|---|---|---|---|
| Units, avg unit size, year built, address | `property_mfr` identity + physical facts | property | datamart → user |
| In-place rents by unit type, occupancy | `property_mfr` rent/occupancy topics | property | uploaded rent roll → datamart → user |
| T12 operating lines | `property_mfr` financials topics | property | uploaded T12 → datamart → user |
| Market rent for loss-to-lease (RR Analysis) | rental-comps skill | property (comp set) | comps → user override |
| Expense comps (OpEx Comps) | sales-comps / rental-comps skill comp financials | property (comp set) | comps → user |
| Occupancy ramp, rent growth, expense growth trend | forecasting skill | market/submarket | forecast engine pre-fill → user confirms |
| Index rate | `mortgage_rates` | national/loan program | datamart → user |

Phase discipline: resolve property identity and parse any uploaded rent roll/T12 first; confirm purchase terms and loan structure next; run comps and forecasting retrieval only after the deal is confirmed, since they are the most retrieval-heavy calls.

## 5. Assumption Confirmation

Present a defaults table for everything resolved silently (property facts, comps, forecast trend pre-fills), each row tagged `property data` / `forecast engine` / `web research` / `default`. Follow with a short confirmation form for only the high-materiality fields: purchase price, loan amount, debt type/terms, hold period, exit cap rate, and the occupancy/rent-growth trajectory. Do not re-ask for anything already user-provided in the intake form.

## 6. Write Rules

1. Read this field guide before writing anything.
2. Never write to a cell marked as a formula above (Section 2 flags every one explicitly).
3. Clear a table's declared data-body range before writing new rows (Section 2.5), even if no prior data is present.
4. Write scalars to the exact coordinates in Sections 2.1–2.4 and 2.6–2.8; never pattern-extrapolate a coordinate for a similar-looking row.
5. Recalculate after every write batch.
6. Validate (Section 7) before delivering. On any violation (formula overwritten, table write skipped, unmapped output cell erroring) stop and report the specific violation; do not deliver a broken file.

## 7. Validation

**Blockers (stop delivery):**
- Any new `#REF!`, `#DIV/0!`, `#VALUE!`, or `#NAME?` error introduced by the write.
- NOI (Op. Statement / Property Returns) ≤ 0.
- Total operating expenses ≥ gross income.
- Physical occupancy outside 0–100%.
- Loan-to-value outside 0–100%.

**Advisory (flag, don't block):**
- Going-in cap rate outside roughly 4.0%–7.5% for a stabilized multifamily deal in a comparable market — confirm rather than reject.
- Exit cap rate more than ~100 bps below the going-in cap rate (aggressive cap-rate compression assumption).
- Recurring capital per unit trending down year over year (unusual; typically ramps toward a stabilized level).
- DSCR (if computed downstream) under 1.20x on a permanent loan.

A small number of pre-existing `#N/A`/`#NUM!` results on a freshly blanked template (specifically in the far-right columns of the Property Returns sensitivity grid) are expected until the core purchase and loan inputs are populated — they are not a validation failure at intake, only after full population.

## 8. Output Reading and Goal-Seek

Read results only from: `Property Returns` (IRR, equity multiple, NCF yields, per-unit pricing), `Summary Info` (dashboard read-out), `CF & Returns` (monthly cash flow and waterfall detail), `Venture`/`JV Metrics` (promote and JV-level returns), `Debt` (debt yield, DSCR if present).

**Goal-seek protocol:** vary only one named input at a time through its approved write coordinate (e.g., `Property Returns!D7` for "what price hits a 15% levered IRR"). Maximum 10 iterations, tolerance ±0.05% on the target metric. If unconverged after 10 iterations, report the closest-tested value and the resulting metric, not a guessed answer.

## 9. Guardrails

- No structural modification of the workbook (no new sheets, no reordering, no renaming named ranges).
- Do not recreate this model from scratch under any circumstance.
- Do not compute IRR, NOI, or any model output outside the workbook — the workbook's formulas are the sole calculation authority.
- Do not write a datamart-sourced value without following the fallback order in Section 4.
- Do not silently override a value the user has already confirmed in an earlier turn.
- Do not resolve or repoint the external link on `DD CapEx Schedule` — investigate its target only if asked, never edit it.
- Do not populate `DD Tracker` / `DD CapEx Schedule` / `DD Changes` during initial intake.