Leasing Budget Automation

💼 Executive Summary

This solution automates leasing budget forecasting across 400+ tenants across 100+ commercial properties, covering more than 700 active lease exposures. It bridges Yardi data queried by SQL, Excel inputs, and Python processing to generate a 5–7 year outlook for base rent, vacancy, and GLA — fully integrated into a dynamic Excel workbook.

• Portfolio: Office, Retail, Industrial properties
• Forecasting Horizon: 2025 to 2029
• Tools: Excel, Python (pandas, xlwings), SQL (Athena), SharePoint, Task Scheduler
• Output: Excel workbook with dynamic projections, rent roll validations, and summary reports

Fully integrated into a dynamic Excel workbook.

📈 Business Impact

• ✅ Replaced manual workbooks with automated Python logic
• ⚡ ⏱️ Reduced from 3 weeks to 10 minutes
• 🔍 Created a single source of truth for all leasing assumptions
• 🧾 Enabled quick summary generation for budget sign-off

Simplified Leasing Flow

This diagram provides a high-level overview of the leasing process used to generate projections from multiple data sources.

🔄 This diagram outlines how leasing data flows from raw Yardi exports and team assumptions through Python logic into monthly rent projections, vacancy tracking, and Excel reports — all fully automated.

Leasing Automation in Action

This animation walks through the 6-step leasing forecast pipeline — from pulling in-place leases from Yardi to auto-generating Excel dashboards.

① SQL pull → ② Leasing inputs → ③ Python logic → ④ Writeback → ⑤ Power Query → ⑥ Dashboard output

📝 From Input to Output

Below are examples of how leasing team assumptions entered in Excel (purple fields) directly impact summary reports. After updating assumptions, simply run the Python script and click Refresh All in Excel to instantly generate updated visuals:

🔧 Leasing Inputs (Editable Fields)

✏️ Leasing assumptions are entered here — including lease start/end dates, rates, and incentives — which directly drive downstream rent projections and KPIs.

📊 Net Rent Comparison (2024F vs 2025B)

📈 Yearly Net Rent Trend (2023–2029)

📌 These visuals help validate budget assumptions and explain key variances across time, asset class, and tenant level — making sign-off faster and more accurate.

📊 Leasing Pipeline Overview

Explore how data flows from our Yardi systems through Excel and Python automation pipelines.

📈 Dataflow Architecture (Detail)

This visual summarizes the full-stack integration across systems:

Technical Flow

1. SQL Data Pull: Extracts 4 core tables from 1,000+ via Athena, including historical and future rent rolls
2. Load to Excel: Results populate 7yrBaseRent, RentRoll, and Sqft tabs
3. Read via xlwings: Python loads both Yardi data and leasing inputs from Inputs tab
4. DataFrame Logic:
   • Combine Yardi leases + leasing team assumptions
   • Unnest each row into monthly charges (2023–2029)
   • Inject free rent as negative entries
   • Amortize TI, LC, admin fees across term
   • Calculate NER at tenant and property level
5. Vacancy Integration: Identify unoccupied months by comparing against a unit-level GLA baseline
6. Output to Excel: Write to all_unnest, GLA_Month, and Summary Report
7. Validation: Merge with rent roll to detect mismatch
8. Backup: Archive Inputs and validation data daily via SharePoint

🔍 Full Walkthrough: From Raw Data to Instant Rent Forecasts

This section walks through how the leasing budget automation works behind the scenes — from SQL to Excel to Python — and shows real code examples for each part of the pipeline.

1. 🔄 SQL Data Extraction

2. 📥 Load Queried Data into Excel

• 7yrBaseRent
• RentRoll
• Sqft
• Inputs (for leasing assumptions)

3. 📊 Read Excel via xlwings

def load_and_combine_tables(wb):
    df_base = pd.read_excel(wb.fullname, sheet_name='7yrBaseRent')
    df_input = pd.read_excel(wb.fullname, sheet_name='Inputs')
    return pd.concat([df_base, df_input], ignore_index=True)
        

4. 🧮 Unnest Charges Over Time

def unnest_charges(df, wb):
    df_expanded = []
    for _, row in df.iterrows():
        for month in pd.date_range(row['start_date'], row['end_date'], freq='MS'):
            rent = row['yearly_rate'] / 12
            if month in row['free_rent_months']:
                rent = 0
            df_expanded.append({**row, 'month': month, 'monthly_rent': rent})
    return pd.DataFrame(df_expanded)
        

5. 🏚 Vacancy Detection

def integrate_vacancy(df, wb, all_months):
    df_sqft = pd.read_excel(wb.fullname, sheet_name='Sqft')
    df['is_vacant'] = ~df.apply(
        lambda row: (row['unit_no'], row['month']) in
        df_sqft[['unit_no', 'month']].itertuples(index=False),
        axis=1
    )
    return df
        

6. 💾 Output to Excel

sheet = wb.sheets['all_unnest']
sheet.range("A2").value = df_final
        

7. 🧾 Validation

def update_status_with_rent_roll_and_inputs(wb):
    rentroll = pd.read_excel(wb.fullname, sheet_name='RentRoll')
    status = df_final.merge(rentroll, on='unit_no', how='left', suffixes=('', '_actual'))
    status['discrepancy'] = status['monthly_rent'] != status['monthly_rent_actual']
    return status
        

8. 🔁 Backup and Refresh

Backups of inputs and summary reports are archived daily to SharePoint using Windows Task Scheduler.

🧪 Python Modules

🔁 1. load_and_combine_tables(wb)

📆 2. unnest_charges(df1, wb)

🏢 3. integrate_vacancy(df_final, wb, all_months)

📏 4. process_sqft_month(wb)

📊 5. update_status_with_rent_roll_and_inputs(wb)

All Excel interaction handled via xlwings — ensuring seamless link to open or saved workbooks.

🛠 Challenges Solved

• Duplicate assumptions: Detected overlap with activated leases → override logic added
• Excel memory limits: Large formula-based workbook → replaced with Python calculations
• Manual errors in rent roll match: Created full validation logic across merged keys

🔍 Reflections

• This was not just a coding task — it redefined how leasing forecasts are created and validated
• Team buy-in increased as confidence in data grew
• Demonstrated value of domain-specific automation in Finance