Untitled

import pandas as pd
import numpy as np

def create_office_forecast(
    rentable_sqft=50000,
    initial_occupancy=0.85,
    initial_rent_psf=45,
    operating_expenses_psf=18,
    starting_year=2025
):
    """
    Creates a 3-year cash flow forecast for a Miami office building
    
    Parameters:
    - rentable_sqft: Total rentable square feet
    - initial_occupancy: Starting occupancy rate (0-1)
    - initial_rent_psf: Starting rent per square foot annually
    - operating_expenses_psf: Starting operating expenses per square foot annually
    - starting_year: First year of forecast
    """
    
    # Initialize forecast parameters
    forecast_years = 3
    rent_growth = 0.03  # 3% annual rent growth
    expense_inflation = 0.04  # 4% annual expense inflation
    occupancy_improvement = 0.03  # 3% annual occupancy improvement
    
    # Capital expenditure assumptions
    capex_psf = 2.50  # Annual capital expenditure per square foot
    
    # Create forecast dataframe
    years = range(starting_year, starting_year + forecast_years)
    forecast = pd.DataFrame(index=years)
    
    # Calculate key metrics for each year
    for i, year in enumerate(years):
        # Occupancy calculations
        forecast.loc[year, 'Occupancy Rate'] = min(
            initial_occupancy + (occupancy_improvement * i),
            0.95  # Cap at 95% occupancy
        )
        
        # Rent calculations with annual increases
        forecast.loc[year, 'Rent PSF'] = initial_rent_psf * (1 + rent_growth) ** i
        
        # Operating expense calculations with inflation
        forecast.loc[year, 'OpEx PSF'] = operating_expenses_psf * (1 + expense_inflation) ** i
        
        # Occupied square feet
        forecast.loc[year, 'Occupied SqFt'] = rentable_sqft * forecast.loc[year, 'Occupancy Rate']
        
        # Revenue calculations
        forecast.loc[year, 'Rental Revenue'] = (
            forecast.loc[year, 'Occupied SqFt'] * forecast.loc[year, 'Rent PSF']
        )
        
        # Expense calculations
        forecast.loc[year, 'Operating Expenses'] = rentable_sqft * forecast.loc[year, 'OpEx PSF']
        forecast.loc[year, 'CapEx'] = rentable_sqft * capex_psf
        
        # NOI and Cash Flow calculations
        forecast.loc[year, 'NOI'] = (
            forecast.loc[year, 'Rental Revenue'] - forecast.loc[year, 'Operating Expenses']
        )
        forecast.loc[year, 'Cash Flow'] = forecast.loc[year, 'NOI'] - forecast.loc[year, 'CapEx']
    
    # Format currency columns
    currency_columns = ['Rental Revenue', 'Operating Expenses', 'CapEx', 'NOI', 'Cash Flow']
    for col in currency_columns:
        forecast[col] = forecast[col].round(0)
    
    # Format rate columns
    rate_columns = ['Occupancy Rate', 'Rent PSF', 'OpEx PSF']
    for col in rate_columns:
        forecast[col] = forecast[col].round(2)
    
    return forecast

# Generate forecast with example inputs
forecast = create_office_forecast()
print("\nMiami Office Building - 3-Year Cash Flow Forecast")
print("(All monetary values in USD)")
print("\n", forecast)

# Calculate key metrics
total_cash_flow = forecast['Cash Flow'].sum()
average_noi = forecast['NOI'].mean()
average_occupancy = forecast['Occupancy Rate'].mean() * 100

print(f"\nKey Metrics:")
print(f"Total 3-Year Cash Flow: ${total_cash_flow:,.0f}")
print(f"Average Annual NOI: ${average_noi:,.0f}")
print(f"Average Occupancy: {average_occupancy:.1f}%")