Apartment Quant
Back to projects

Automated Distribution Schedules: From Balance Sheets to Investor Reports

The Problem

Monthly investor distributions require calculating available cash across 30+ properties. Each property needs a distribution schedule showing operating cash balance, outstanding payables, reserves, and net distributable amount.

The data exists in three separate accounting reports: balance sheet CSV showing GL account balances, a property-to-GL mapping file defining which accounts represent operating cash vs. CapEx, and an AP aging report with unposted invoices not yet reflected in the balance sheet.

Building distribution schedules manually means opening three files, looking up 8-12 GL accounts per property, copying values into Excel, applying formulas, and formatting for executive signatures. For 30 properties, this takes 3-4 hours monthly. Mistakes happen when copying account numbers or missing unposted payables that should reduce distributable cash.

The Data Sources

Balance Sheet CSV:

Property Cash GL Accounts (mapping file):

AP Aging - Unposted Invoices:

Building the Extraction

Load source data and property list:


import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

# Read source files
df = pd.read_csv(input_path)  # Balance sheet
gl_accounts_df = pd.read_excel(gl_accounts_path)  # GL mappings
ap_aging_df = pd.read_csv(ap_aging_path)  # Unposted invoices

# Get unique properties
properties = df['Property'].unique()

GL account mappings for line items that are consistent across properties:


gl_mappings = {
    'Add: Undeposited funds': ['1234'],
    'Less: Accounts Payable': ['2345'],
    'Less: Retentions Payable': ['3456'],
    'Less: Construction Deposits': ['4567', '4568'],
    'Add: Accounts Receivable': ['5678', '5679']
}

Retrieve property-specific GL accounts from mapping file:


def get_property_gl_accounts(property_name, account_type):
    """Get GL accounts for a property by account type"""
    matching_rows = gl_accounts_df[
        (gl_accounts_df.iloc[:, 0] == property_name) &
        (gl_accounts_df.iloc[:, 3] == account_type)
    ]
    if matching_rows.empty:
        return []
    return matching_rows.iloc[:, 1].astype(str).tolist()

Sum values for specified GL accounts:


def get_gl_value(df, property_name, gl_accounts):
    """Sum values for given GL accounts for a specific property"""
    if isinstance(gl_accounts, str):
        gl_accounts = [gl_accounts]

    total = 0
    for gl in gl_accounts:
        rows = df[(df['Property'] == property_name) &
                  (df['Account'].astype(str) == str(gl))]
        if not rows.empty:
            value = rows.iloc[0][df.columns[3]]
            if pd.notna(value):
                value_str = str(value).replace(',', '')
                total += float(value_str)

    return total if total != 0 else None

Extract unposted invoices from AP aging:


def get_ap_aging_unposted(property_name):
    """Get sum of unposted invoices for a property"""
    matching_rows = ap_aging_df[ap_aging_df.iloc[:, 1] == property_name]
    if matching_rows.empty:
        return None

    total = 0
    for idx, row in matching_rows.iterrows():
        value = row.iloc[7]  # Column H
        if pd.notna(value):
            value_str = str(value).replace(',', '')
            total += float(value_str)

    return -total if total != 0 else None

Building the Workbook

Create workbook structure with formatting:


# Process each property
for property_name in properties:
    wb = Workbook()
    ws = wb.active

    # Remove gridlines
    ws.sheet_view.showGridLines = False

    # Set column widths
    ws.column_dimensions['A'].width = 44.29
    ws.column_dimensions['B'].width = 14.71
    ws.column_dimensions['C'].width = 13.57

    # Define styles
    grey_fill = PatternFill(start_color='D9D9D9', end_color='D9D9D9',
                            fill_type='solid')
    bold_font = Font(bold=True, size=11)
    thin_border = Side(style='thin', color='000000')
    double_border = Side(style='double', color='000000')

Title row with grey background and borders:


    # Row 5: Title and Date
    ws['A5'] = f"{property_name} Monthly Distribution Schedule"
    ws['A5'].font = bold_font
    ws['A5'].fill = grey_fill
    ws['A5'].border = Border(top=thin_border, bottom=thin_border)
    ws['C5'] = date_value
    ws['C5'].font = bold_font
    ws['C5'].fill = grey_fill
    ws['C5'].alignment = Alignment(horizontal='right')
    ws['C5'].border = Border(top=thin_border, bottom=thin_border)

Operating cash components:


    # Operating Ending Cash Balance
    ws['A7'] = "Operating Ending Cash Balance"
    operating_gls = get_property_gl_accounts(property_name, 'Operating')
    if operating_gls:
        value = get_gl_value(df, property_name, operating_gls)
        if value is not None:
            ws['C7'] = value
            format_currency(ws['C7'])

    # CapEx Funds Available
    ws['A8'] = "CapEx Funds Available in Secondary Account"
    secondary_gls = get_property_gl_accounts(property_name, 'Secondary')
    if secondary_gls:
        value = get_gl_value(df, property_name, secondary_gls)
        if value is not None:
            ws['C8'] = value
            format_currency(ws['C8'])

Reserve calculations with unposted payables:


    # Accounts Payable (posted)
    ws['A19'] = "Less: Accounts Payable"
    value = get_gl_value(df, property_name, gl_mappings['Less: Accounts Payable'])
    if value is not None:
        ws['C19'] = -abs(value)
        format_currency(ws['C19'])

    # Accounts Payable (unposted from AP aging)
    ws['A21'] = "Less: Accounts Payables (Unposted)"
    value = get_ap_aging_unposted(property_name)
    if value is not None:
        ws['C21'] = value
        format_currency(ws['C21'])

Formulas for calculated totals:


    # Operating Cash Balance formula
    ws['A13'] = "Operating Cash Balance"
    ws['A13'].font = bold_font
    ws['A13'].border = Border(top=thin_border, bottom=thin_border)
    ws['C13'] = "=SUM(C7:C11)"
    ws['C13'].font = bold_font
    format_currency(ws['C13'])

    # Total Excess/(Deficit) Distributable Cash
    ws['A26'] = "Total Excess/(Deficit) Distributable Cash"
    ws['A26'].font = bold_font
    ws['A26'].border = Border(top=thin_border, bottom=double_border)
    ws['C26'] = "=SUM(C17:C25)"
    ws['C26'].font = bold_font
    format_currency(ws['C26'])

Currency formatting with parentheses for negatives:


def format_currency(cell):
    """Apply currency formatting with parentheses for negatives"""
    cell.number_format = '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)'
    cell.alignment = Alignment(horizontal='right')

The Output

Each property gets an Excel file named `{Property Name} {Date}.xlsx` with:

The files are ready for distribution without manual data entry. Asset managers review the calculations, sign electronically, and forward to investors.

What This Enables

Monthly close for 30 properties previously required someone to spend 3-4 hours building distribution schedules. Opening three reports, looking up accounts, copying values, checking formulas, formatting cells.

Processing now takes 2 minutes. Drop the three source files in the input folder, run the script, get 30 formatted workbooks in the output folder. The same 180 minutes of work monthly, eliminated.

The unposted AP aging integration prevents distributions when invoices have been approved but not yet hit the GL. Without this, properties could distribute cash that's already committed to vendor payments, creating operational shortfalls the following month.

Formula-driven calculations in the output Excel files allow asset managers to verify totals and make manual adjustments for items requiring judgment (minimum cash reserves, pending inflows). The automation handles data aggregation. Judgment calls remain with asset managers.

Get in Touch