📊 Budget & Reforecast Automation
This solution automates the end-to-end workflow for annual budgeting and quarterly reforecasting across 400+ tenants and 100+ commercial buildings, syncing inputs from leasing and accounting teams.
- Leasing Budget Pipeline: Automates forward-looking projections using SQL + Python based on leasing assumptions.
- Leasing Reforecast Pipeline: Syncs near-final 2025 actuals from ABF and overlays rent projections from Yardi.
- CapEx Pipeline: Automates both Building Improvement capex and Leasing Capital capex for project-level actuals, reforecasting, and approvals. (Coming Soon)
✅ Business Impact
- 500+ hours saved annually
- Aligned Leasing + Accounting + Finance across shared forecasts
- Single source of truth for both Budget and Reforecast comparison
🧠 It All Starts With Year 1 Leasing Budget
At the heart of this automation is the Year 1 leasing forecast — a dynamically built model combining:
- In-place lease data pulled from Yardi (via SQL + Athena)
- Leasing team assumptions on renewals, new leases, and downtime
- Override logic that eliminates duplication and reflects accurate rent steps
- Smart Excel interface for scenario runs and real-time rent projection updates, and dashboard
This leasing engine is fully detailed in Leasing Pipeline →, and forms the backbone for all budget and reforecast workflows.
🔗 Explore More
Follow along as I share how to automate the full budgeting lifecycle — from leasing assumptions to actuals alignment to executive-ready scenarios.
🔄 Syncing Budget & Reforecast Without Rebuilding
Each year, two tasks need to happen in sync:
- Reforecast for 2025–2029: Updates to last year’s budget model to reflect actuals and material changes
- New Budget for 2026–2030: Extending the model by one year (2030) for next-year planning
Thanks to this setup, we never rebuild from scratch. Instead, we reuse the same hybrid model by:
- Pulling "Final Budget Book" from ABF for archived 2025–2029 baseline
- Pulling "IPP Budget Book" from ABF for active reforecast (same structure, updated by Accounting)
- Renaming columns dynamically via Power Query (e.g., “/2025” → “/2026”)
- Overlaying leasing rent projections for 2026–2030 from the Python pipeline
The overlay logic uses the exact same core process from Year 1:
- Leasing assumptions + in-place leases are processed via Python and SQL
- Rent steps are unnested into month-level granularity
- Results feed into the Excel workbook, populating the future years dynamically
🧩 Hybrid Workbook — One Model, Two Purposes
Rather than build two separate files, I created a single Excel-based engine that serves both:
- 2025–2029 Reforecast: Syncs to archived “Final Budget Book” and updated “IPP Budget Book” from Yardi ABF
- 2026–2030 Budget: Reuses same base logic and overlays new leasing projections using Python pipeline
Accountants use Power Query to pull actuals and material changes from ABF, while Leasing inputs feed Python-powered projections. The result:
- Fast, flashback-ready scenarios for budget planning
- No rebuild required — just sync and extend forward
| Process | Data | Tools | What Tools Do | Consolidation | Outputs | Objectives |
|---|---|---|---|---|---|---|
| Budget (2026–2030) | Database of in-place leases | SQL + Athena | Clean, project, and load rent data | Excel Workbook (Syncing Budget & Reforecast) |
Scenario Analysis, Flashback Results | Minimize Rework |
| Shared Process | Leasing Assumptions (Renewals, TI, Downtime) |
Python + xlwings | Inject leasing assumptions, Read/Write Excel | Unified Dashboard | Seamless Integration | |
| Reforecast (2025–2029) | ABF (Final + IPP Budget Books) | Python + Power Query | Combine Queries & Connection | Variance vs Original Budget | Frequent/Quarterly Reforecast |
📊 Forecast Manager vs. My Custom Pipeline: Executive Summary
This comparison highlights how my in-house forecasting pipeline outperforms Forecast Manager across speed, flexibility, and analytical depth — even when Forecast Manager is later introduced for workflow standardization.
| Feature | Forecast Manager | My Custom Pipeline | Rigidities of Forecast Manager |
|---|---|---|---|
| Reforecast vs Budget Comparison | ✗ | ✓ | Cumbersome reforecast process, not fully aligned with dynamic reforecasting needs |
| Real-time Scenario Flashing | ✗ | ✓ | No portfolio-level flashing or support for forward-looking projections |
| Integration: SQL, Python, Excel | ✗ | ✓ | Lacks integration with modern analytics tools and scripting workflows |
📌 Conclusion: While Forecast Manager is effective for enforcing standardized budgeting workflows, my custom pipeline enables agile scenario modeling, seamless integration, and deep analytical insights — empowering faster, data-driven decisions at both asset and portfolio levels.
⚙️ What Powers This Automation
| Component | Purpose |
|---|---|
| SQL + Athena | Pull clean rent roll, charge steps, and property-level data |
| Python (Pandas) | Process leasing team inputs, override logic, and rent projections by month |
| Power Query in Excel | Map ABF Final Budget vs IPP Reforecast dynamically and support column renaming |
| Excel Interface | Allows accountants to review actuals, compare projections, and export to ABF/Yardi |
🚀 Why It Works
- Carry-forward modeling: One model supports both reforecast and next-year budget
- Reusable Excel + backend logic: No rebuilding, just syncing and extending
- Dynamic year mapping: 2025 → 2026 handled programmatically in Power Query
- Seamless Python integration: Rent projections flow into budgeting without manual re-entry
All these steps make budget planning and reforecasting fully aligned — accountants, finance, and leasing all work off the same system with full transparency and speed.