Monitor financial sustainability risks early

The problem

You want early warning of financial trouble - declining reserves, over-reliance on single funder, or expenditure trending above income - but currently only notice when it's already a crisis. By the time problems appear in monthly accounts, you've lost valuable time to respond.

The solution

Build a dashboard of key financial health indicators with automatic alerts when thresholds are breached. Start with a simple Google Sheets approach using conditional formatting, or use Looker Studio for more polish. Track metrics like months of reserves remaining, concentration risk (% from largest funder), unrestricted vs restricted funds trend, and expenditure vs income growth rates. Review monthly at senior team meetings.

What you get

A financial health dashboard showing 5-8 key indicators with red/amber/green status against thresholds. Automatic email alerts when any metric breaches threshold. Monthly trend charts showing whether indicators are improving or worsening. Board-ready summary of financial sustainability status.

Before you start

• Access to accounting data (ideally via API or regular exports)
• At least 12 months of financial history (13+ months needed if using the income growth calculation)
• Defined reserves policy or target reserves level
• Agreement on what thresholds constitute "red alert" vs "amber warning"
• Ensure only authorised staff have access to the dashboard and that data exports to third-party cloud tools are handled securely

When to use this

• Organisation has survived initial startup phase (12+ months operating history)
• Multiple income streams to monitor
• Board wants proactive financial oversight rather than reactive
• Recent or ongoing financial challenges requiring close monitoring
• Planning for growth or major changes that could affect sustainability

When not to use this

• Single-funder organisation (concentration metrics less meaningful)
• Very early stage with insufficient history for trends (< 12 months)
• Financial position is stable and predictable with no risk factors
• No capacity to act on alerts (knowing about risks doesn't help if you can't respond)

Steps

1. 1

   Define your key financial health indicators

   Choose 5-8 metrics that matter for your organisation: (1) Months of reserves remaining (unrestricted reserves / monthly expenditure), (2) % of income from largest funder, (3) Unrestricted vs restricted funds ratio, (4) Income growth rate vs expenditure growth rate, (5) Debtor days (how long to collect income - particularly critical for organisations delivering contracted services or high-value grant claims, less relevant if primarily funded by small individual donations), (6) Staff costs as % of income. Tailor to your specific risks.

2. 2

   Set thresholds for each indicator

   For each metric, define: Green (healthy), Amber (watch closely), Red (urgent action needed). Example for reserves: Green = 6+ months, Amber = 3-6 months, Red = < 3 months. Base thresholds on your reserves policy, sector norms, and risk appetite. Get board input on what levels trigger escalation.

3. 3

   Set up data connection to accounting system

   Ideal: Use API integration (Xero, QuickBooks, Sage have APIs) to pull data automatically. Simpler: Export key data monthly to CSV and import to dashboard. Need: Income by funder, expenditure by category, reserves balances, debtor aging. Automate as much as possible to reduce manual work.

4. 4

   Build dashboard (Spreadsheet approach)

   Create Google Sheet with: (1) Data import sheet, (2) Calculations sheet computing each indicator, (3) Dashboard sheet with visual indicators (conditional formatting for red/amber/green), (4) Trend charts showing last 12 months for each metric. Use =GOOGLEFINANCE or manual input for data. Simple but effective. This approach works well for most small to medium charities.

5. 5

   Build dashboard (BI tool approach)(optional)

   Use Looker Studio (free) or Power BI to connect to your accounting data. Create visualizations for each metric with threshold indicators. Add trend lines showing direction of travel. Enable scheduled email reports. More polished than spreadsheet and better for sharing with board.

6. 6

   Set up automatic alerts

   Configure email alerts when any metric breaches amber or red threshold. In Google Sheets: use Apps Script or tools like Zapier. In Looker Studio: set up scheduled reports with conditional formatting. Alert should go to senior management team, not everyone. Include: which metric, current value, threshold, trend.

7. 7

   Establish monthly review rhythm

   Schedule 30-minute monthly review at senior team meeting. Review dashboard, discuss any amber/red metrics, agree actions, update forecast. Take 5-minute summary to board each quarter. This discipline is what makes monitoring valuable - spotting trends early and responding before they become crises.

Example code

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Load financial data
df = pd.read_csv('monthly_financials.csv', parse_dates=['month'])

# Calculate key indicators
def calculate_indicators(df):
    # 1. Months of reserves remaining
    latest_reserves = df.iloc[-1]['unrestricted_reserves']
    monthly_expenditure = df.iloc[-1]['total_expenditure']
    months_of_reserves = latest_reserves / monthly_expenditure

    # 2. Income concentration (% from largest funder)
    funder_income = df.iloc[-1][['funder_1', 'funder_2', 'funder_3']]
    largest_funder_pct = funder_income.max() / df.iloc[-1]['total_income'] * 100

    # 3. Unrestricted vs restricted ratio
    unrestricted_restricted_ratio = (
        df.iloc[-1]['unrestricted_income'] /
        df.iloc[-1]['restricted_income']
    )

    # 4. Income vs expenditure growth (last 12 months)
    # Note: requires at least 13 months of data
    if len(df) >= 13:
        income_12m_ago = df.iloc[-13]['total_income']
        income_growth = (df.iloc[-1]['total_income'] / income_12m_ago - 1) * 100

        expenditure_12m_ago = df.iloc[-13]['total_expenditure']
        expenditure_growth = (df.iloc[-1]['total_expenditure'] / expenditure_12m_ago - 1) * 100
    else:
        income_growth = 0  # Insufficient data
        expenditure_growth = 0

    # 5. Debtor days (simplified)
    debtors = df.iloc[-1]['accounts_receivable']
    debtor_days = (debtors / monthly_expenditure) * 30

    return {
        'months_of_reserves': months_of_reserves,
        'largest_funder_pct': largest_funder_pct,
        'unrestricted_restricted_ratio': unrestricted_restricted_ratio,
        'income_growth_pct': income_growth,
        'expenditure_growth_pct': expenditure_growth,
        'debtor_days': debtor_days
    }

# Apply thresholds
def apply_thresholds(indicators):
    status = {}

    # Months of reserves
    if indicators['months_of_reserves'] >= 6:
        status['reserves'] = 'green'
    elif indicators['months_of_reserves'] >= 3:
        status['reserves'] = 'amber'
    else:
        status['reserves'] = 'red'

    # Concentration risk
    if indicators['largest_funder_pct'] <= 40:
        status['concentration'] = 'green'
    elif indicators['largest_funder_pct'] <= 60:
        status['concentration'] = 'amber'
    else:
        status['concentration'] = 'red'

    # Add more threshold checks...

    return status

indicators = calculate_indicators(df)
status = apply_thresholds(indicators)

print("Financial Health Dashboard")
print(f"Months of reserves: {indicators['months_of_reserves']:.1f} ({status['reserves'].upper()})")
print(f"Largest funder: {indicators['largest_funder_pct']:.1f}% ({status['concentration'].upper()})")

Tools

Resources