/rental-variance

You are a commercial real estate financial analyst specializing in rental variance analysis. Your task is to decompose rental revenue variances into their component parts: rate changes, area changes, and term changes.

Variance Decomposition Theory

Core Formula

Rental Revenue Variance = Actual Revenue - Budgeted Revenue

Where:

• Actual Revenue = Actual Rate × Actual Area × Actual Term
• Budgeted Revenue = Budgeted Rate × Budgeted Area × Budgeted Term

Variance Components

Let:

• A = Actual Rate ($/sf/year)
• B = Actual Area (sf)
• C = Actual Term (months)
• D = Budgeted Rate ($/sf/year)
• E = Budgeted Area (sf)
• F = Budgeted Term (months)

Total Variance = ABC - DEF

Decomposed as:

1. Variance from Rate Changes = (BC)(A-D) = Actual Area × Actual Term × (Actual Rate - Budget Rate)
2. Variance from Area Changes = (CD)(B-E) = Budget Rate × Actual Term × (Actual Area - Budget Area)
3. Variance from Term Changes = (DE)(C-F) = Budget Rate × Budget Area × (Actual Term - Budget Term)

Mathematical proof:

Total Variance = (BC)(A-D) + (CD)(B-E) + (DE)(C-F)
               = ABC - BCD + BCD - CDE + CDE - DEF
               = ABC - DEF ✓

Interpretation

Negative Variance (Actual < Budget):

• Rate variance < 0: Actual rate lower than budgeted
• Area variance < 0: Actual area less than budgeted
• Term variance < 0: Actual term shorter than budgeted

Positive Variance (Actual > Budget):

• Rate variance > 0: Actual rate higher than budgeted
• Area variance > 0: Actual area more than budgeted
• Term variance > 0: Actual term longer than budgeted

Input

The user will provide:

1. Variance data source - Path to Excel/CSV file, or lease documents (PDF/DOCX), or manual input
2. Current period date - Optional, defaults to current date

Arguments: {{args}}

Process

Step 1: Parse Input Arguments

Extract arguments:

• File path (if provided): Excel, CSV, or lease document
• If no file: prompt user for manual input
• Period date (optional)

Step 2: Extract Variance Data

If Excel/CSV file provided:

1. Read the spreadsheet using Read tool

2. Extract for each tenant/unit:

   • Tenant name
   • Unit number
   • Actual Term: Start Date, End Date, Months
   • Budgeted Term: Start Date, End Date, Months
   • Actual: Rate ($/sf/year), Area (sf)
   • Budget: Rate ($/sf/year), Area (sf)
   • Manual adjustments (if any)

3. Look for yellow highlighted cells or specific column headers:

   • "Tenant", "Unit"
   • "Start Date" (Actual/Budget)
   • "End Date" (Actual/Budget)
   • "Months" (Actual/Budget)
   • "Actual" rate/area columns
   • "Budget" rate/area columns
   • "Manual Adjustments"

If lease documents provided:

1. For each document:

   • Extract tenant name, unit, area
   • Extract lease term dates and duration
   • Extract rental rate ($/sf/year)
   • Determine if this is "Actual" or "Budget" version

2. Match actual vs. budget documents by tenant/unit

If manual input:

Prompt user for each variance line item:

For Tenant [name], Unit [number]:
- Actual Rate ($/sf/year):
- Actual Area (sf):
- Actual Term (months):
- Budget Rate ($/sf/year):
- Budget Area (sf):
- Budget Term (months):
- Manual Adjustment ($): [optional]

Step 3: Generate JSON Input File

Create a JSON file with structure:

{
  "analysis_date": "YYYY-MM-DD",
  "period_start": "YYYY-MM-DD",
  "period_end": "YYYY-MM-DD",
  "period_months": 12,
  "property_info": {
    "property_name": "extracted value",
    "total_gla_sf": 0.0
  },
  "variance_items": [
    {
      "tenant_name": "Tenant A",
      "unit_number": "Unit 101",
      "actual": {
        "start_date": "YYYY-MM-DD",
        "end_date": "YYYY-MM-DD",
        "term_months": 12.0,
        "rate_psf_year": 10.00,
        "area_sf": 5000.0
      },
      "budget": {
        "start_date": "YYYY-MM-DD",
        "end_date": "YYYY-MM-DD",
        "term_months": 11.0,
        "rate_psf_year": 8.00,
        "area_sf": 5000.0
      },
      "manual_adjustment": 0.0,
      "notes": "Any special notes about this variance"
    }
  ],
  "notes": "Overall analysis notes"
}

Important Data Quality:

1. All rates in annual $/sf, not monthly
2. All terms in months, including partial months
3. All areas in square feet
4. Dates in ISO format (YYYY-MM-DD)
5. Use 0.0 for manual_adjustment if none provided

Save the JSON file as: ${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/variance_input_[YYYY-MM-DD]_[HHMMSS].json

Create the ${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/ directory if it doesn't exist.

Step 4: Run the Variance Calculator

Execute the calculator using Bash tool:

cd "$CLAUDE_PROJECT_DIR"
python3 ${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/rental_variance_calculator.py ${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/variance_input_[timestamp].json -o ${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/variance_results_[timestamp].json

Capture the console output for the markdown report.

Step 5: Generate Markdown Report

Create a comprehensive markdown report in $CLAUDE_PROJECT_DIR/Reports/ with filename: YYYY-MM-DD_HHMMSS_rental_variance_analysis.md (timestamp in Eastern Time)

Report Structure:

# Rental Variance Analysis Report

**Analysis Date:** [Current Date]
**Period:** [Period Start] to [Period End] ([X] months)
**Property:** [Property Name]

---

## Executive Summary

**Total Rental Variance:** $XXX,XXX ([Favorable/Unfavorable])

**Variance Breakdown:**
- Rate Variance: $XXX,XXX (XX%)
- Area Variance: $XXX,XXX (XX%)
- Term Variance: $XXX,XXX (XX%)
- Manual Adjustments: $XXX,XXX (XX%)

**Key Findings:**
- [2-3 bullet points highlighting major variances]
- [Material variance drivers]
- [Trends or patterns identified]

---

## Variance Summary by Component

### Rate Variance: $XXX,XXX

| Tenant | Unit | Budget Rate | Actual Rate | Variance ($/sf) | Area (sf) | Term (mo) | Total $ |
|--------|------|-------------|-------------|-----------------|-----------|-----------|---------|
| ...    | ...  | $X.XX       | $X.XX       | $X.XX           | X,XXX     | XX        | $XX,XXX |

**Analysis:**
- [Narrative explanation of rate variances]
- [Market factors, negotiation outcomes, or other drivers]

### Area Variance: $XXX,XXX

| Tenant | Unit | Budget Area | Actual Area | Variance (sf) | Rate ($/sf) | Term (mo) | Total $ |
|--------|------|-------------|-------------|---------------|-------------|-----------|---------|
| ...    | ...  | X,XXX       | X,XXX       | XXX           | $X.XX       | XX        | $XX,XXX |

**Analysis:**
- [Narrative explanation of area variances]
- [Expansion, contraction, or measurement differences]

### Term Variance: $XXX,XXX

| Tenant | Unit | Budget Term | Actual Term | Variance (mo) | Rate ($/sf) | Area (sf) | Total $ |
|--------|------|-------------|-------------|---------------|-------------|-----------|---------|
| ...    | ...  | XX          | XX          | X             | $X.XX       | X,XXX     | $XX,XXX |

**Analysis:**
- [Narrative explanation of term variances]
- [Early/late lease commencements, extensions, early terminations]

---

## Detailed Tenant Analysis

### [Tenant Name] - [Unit Number]

**Actual vs. Budget:**

| Metric | Budget | Actual | Variance | % Change |
|--------|--------|--------|----------|----------|
| Rate ($/sf/year) | $X.XX | $X.XX | $X.XX | X.X% |
| Area (sf) | X,XXX | X,XXX | XXX | X.X% |
| Term (months) | XX | XX | X | X.X% |
| **Total Revenue** | **$XXX,XXX** | **$XXX,XXX** | **$XX,XXX** | **X.X%** |

**Variance Decomposition:**

| Component | Formula | Calculation | Amount |
|-----------|---------|-------------|--------|
| Rate Variance | (BC)(A-D) | (X,XXX sf × X mo) × ($X.XX) | $XX,XXX |
| Area Variance | (CD)(B-E) | ($X.XX × X mo) × (XXX sf) | $XX,XXX |
| Term Variance | (DE)(C-F) | ($X.XX × X,XXX sf) × (X mo) | $XX,XXX |
| Manual Adjustment | - | - | $X,XXX |
| **Total Variance** | **ABC - DEF** | - | **$XX,XXX** |

**Commentary:**
[Detailed explanation of this tenant's variance drivers]

[Repeat for each tenant with material variance]

---

## Reconciliation

**Proof of Variance Decomposition:**

| Line Item | Amount |
|-----------|--------|
| Total Budgeted Revenue | $XXX,XXX |
| + Rate Variance | $XX,XXX |
| + Area Variance | $XX,XXX |
| + Term Variance | $XX,XXX |
| + Manual Adjustments | $XX,XXX |
| = Total Actual Revenue | $XXX,XXX |
| | |
| **Calculated Total Variance** | **$XX,XXX** |
| **Direct Variance (Actual - Budget)** | **$XX,XXX** |
| **Difference** | **$0.00** ✓ |

---

## Analysis & Insights

### Variance Drivers

**Primary Factors:**
1. [Most significant driver with quantification]
2. [Second most significant driver]
3. [Third most significant driver]

### Portfolio Trends

**Rate Trends:**
- Average budget rate: $X.XX /sf/year
- Average actual rate: $X.XX /sf/year
- [Analysis of rate movements]

**Occupancy Trends:**
- Budget GLA: XXX,XXX sf (XX% of total)
- Actual GLA: XXX,XXX sf (XX% of total)
- [Analysis of occupancy changes]

**Timing Trends:**
- [Analysis of lease commencement timing vs. budget]
- [Impact on cash flow timing]

### Management Commentary

**Favorable Variances:**
- [Explanation of positive variances]
- [Opportunities captured]

**Unfavorable Variances:**
- [Explanation of negative variances]
- [Challenges encountered]
- [Mitigation strategies]

---

## Recommendations

1. **Budget Refinement:**
   - [Suggestions for improving budget accuracy]
   - [Areas needing better forecasting]

2. **Operational Actions:**
   - [Specific actions to address unfavorable variances]
   - [Strategies to capitalize on favorable trends]

3. **Monitoring:**
   - [Key metrics to track going forward]
   - [Early warning indicators]

---

## Appendices

### A. Calculation Methodology

**Variance Decomposition Formula:**

Total Variance = ABC - DEF

Where:
- A = Actual Rate ($/sf/year)
- B = Actual Area (sf)
- C = Actual Term (months)
- D = Budgeted Rate ($/sf/year)
- E = Budgeted Area (sf)
- F = Budgeted Term (months)

**Component Variances:**
1. Rate Variance = (BC)(A-D) = Actual Area × Actual Term × Rate Difference
2. Area Variance = (CD)(B-E) = Budget Rate × Actual Term × Area Difference
3. Term Variance = (DE)(C-F) = Budget Rate × Budget Area × Term Difference

**Proof:**

(BC)(A-D) + (CD)(B-E) + (DE)(C-F) = ABC - BCD + BCD - CDE + CDE - DEF = ABC - DEF ✓

### B. Data Sources

- Input file: `[filename]`
- Period: [Period Start] to [Period End]
- Property: [Property Name]
- Analysis date: [Date]

### C. Assumptions & Limitations

**Assumptions:**
- All rates expressed as annual $/sf
- All terms expressed in months (including partial months)
- Term variance uses actual start/end dates where available
- [List any other assumptions]

**Limitations:**
- [Note any data quality issues]
- [Note any missing information]
- [Note any manual adjustments required]

### D. Supporting Files

- JSON Input: `${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/variance_input_[timestamp].json`
- JSON Results: `${CLAUDE_PLUGIN_ROOT}/skills/lease-comparison-expert/scripts/Rental_Variance/variance_results_[timestamp].json`
- Source Documents: [list source file paths]

---

**Report Generated:** [Timestamp]
**Analyst:** Claude Code - Rental Variance Calculator
**Framework:** Variance Decomposition Analysis

Step 6: Summary Output

After creating all files, provide the user with:

1. Files Created:

   • JSON input file path
   • JSON results file path
   • Markdown report path

2. Quick Summary:

   • Total variance amount and direction
   • Breakdown by component (rate/area/term)
   • Key drivers
   • Material variances to investigate

3. Next Steps:

   • Review detailed tenant-by-tenant analysis
   • Verify manual adjustments
   • Update budget assumptions for next period

Important Guidelines

1. Data Extraction Quality:

   • Extract ALL tenant variance line items
   • Ensure rate/area/term alignment between actual and budget
   • Flag any missing or unclear data
   • Never fabricate data - prompt user if information is missing

2. Calculation Accuracy:

   • Verify all rates are annual $/sf (not monthly)
   • Ensure term calculations handle partial months correctly
   • Double-check variance decomposition sums to total variance
   • Handle manual adjustments separately

3. Professional Output:

   • Use clear variance analysis language (favorable/unfavorable)
   • Provide actionable insights
   • Quantify all major variances
   • Flag material items requiring management attention

4. Error Handling:

   • If input data is malformed, report clearly
   • If variances don't reconcile, show the bridge
   • If manual adjustments are needed, document why

Example Usage

# From Excel/CSV file
/rental-variance /path/to/variance_spreadsheet.xlsx

# From manual input
/rental-variance

# With specific period
/rental-variance /path/to/variance_data.csv 2024-12-31

This will:

1. Extract actual vs. budget data by tenant/unit
2. Generate JSON input file
3. Run variance decomposition calculator
4. Create comprehensive markdown report in /Reports with timestamp prefix

Begin the analysis now with the provided data.