Balance Sheet Variance Analysis — Fake, Inc. FY2026¶
A month-over-month analysis of every balance sheet account, built to catch the kind of unexplained swing a controller would want to understand before an auditor asks about it — not after.
This notebook takes the general ledger (Allocated Transactions) and Chart of Accounts from the Fake, Inc. Excel model, builds a monthly trial balance for every account, computes month-over-month change, and flags anything that moves more than expected.
Why this matters: a balance that jumps 40% in one month isn't necessarily wrong — but it's exactly the kind of thing that should have an answer ready before someone asks. This is the Python-native version of the disciplined reconciliation habit that shows up throughout the Excel workbook.
1. Load the data¶
In [1]:
import pandas as pd
import plotly.graph_objects as go
import plotly.io as pio
pio.renderers.default = "notebook"
pd.set_option('display.float_format', lambda x: f'{x:,.2f}')
transactions = pd.read_csv('allocated_transactions.csv')
coa = pd.read_csv('chart_of_accounts.csv')
transactions['Date'] = pd.to_datetime(transactions['Date'])
transactions['Month'] = transactions['Date'].dt.to_period('M')
print(f"{len(transactions):,} transaction lines across {transactions['Month'].nunique()} months")
transactions.head()
1,933 transaction lines across 13 months
Out[1]:
| Date | JE Number | Cost Center | Account Number | Account Name | Account Type | Description | Debit | Credit | Total | Source | Month | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-05-30 | JE-1345 | 2,010.00 | 1,110.00 | Grants Receivable | Asset | Signed Latin American restricted grant | 70,000.00 | NaN | 70,000.00 | GL | 2026-05 |
| 1 | 2026-05-30 | JE-1345 | 2,010.00 | 3,100.00 | Net Assets - Temporarily Restricted | Equity | Signed Latin American restricted grant | NaN | 70,000.00 | -70,000.00 | GL | 2026-05 |
| 2 | 2025-07-01 | JE-1321 | 2,010.00 | 3,100.00 | Net Assets - Temporarily Restricted | Equity | Reclassify restricted grant revenue to 3100 - ... | NaN | 36,609.78 | -36,609.78 | GL | 2025-07 |
| 3 | 2025-07-01 | JE-1321 | 2,020.00 | 3,100.00 | Net Assets - Temporarily Restricted | Equity | Reclassify restricted grant revenue to 3100 - ... | NaN | 27,465.59 | -27,465.59 | GL | 2025-07 |
| 4 | 2025-07-01 | JE-1321 | 2,030.00 | 3,100.00 | Net Assets - Temporarily Restricted | Equity | Reclassify restricted grant revenue to 3100 - ... | NaN | 27,465.59 | -27,465.59 | GL | 2025-07 |
2. Build a monthly trial balance¶
For every account, every month, we need a running ending balance — not just the annual total. Each account's balance is its prior month's ending balance, plus that month's activity, signed according to whether the account is normally a debit or credit balance.
In [2]:
# Merge in each account's normal balance side from the Chart of Accounts
coa_lookup = coa.set_index('Account Number')[['Account Name', 'Account Type', 'Normal Balance']]
txn = transactions.merge(coa_lookup, left_on='Account Number', right_index=True, how='left',
suffixes=('', '_coa'))
# Fill missing Debit/Credit with 0 BEFORE the signed calculation -- using
# `or 0` against a NaN value does not work as expected in pandas (NaN is
# truthy), so we explicitly fillna first.
txn['Debit'] = txn['Debit'].fillna(0)
txn['Credit'] = txn['Credit'].fillna(0)
# Signed monthly activity: Debit-normal accounts increase with Debit, decrease with Credit; reverse for Credit-normal
txn['Signed Activity'] = txn.apply(
lambda r: r['Debit'] - r['Credit'] if r['Normal Balance'] == 'Debit'
else r['Credit'] - r['Debit'],
axis=1
)
# Only balance sheet accounts (Asset, Liability, Equity) for this analysis
bs_accounts = coa[coa['Account Type'].isin(['Asset', 'Liability', 'Equity'])]['Account Number'].tolist()
bs_txn = txn[txn['Account Number'].isin(bs_accounts)]
monthly_activity = bs_txn.groupby(['Account Number', 'Month'])['Signed Activity'].sum().reset_index()
monthly_activity.head(10)
Out[2]:
| Account Number | Month | Signed Activity | |
|---|---|---|---|
| 0 | 1,000.00 | 2025-06 | 185,000.00 |
| 1 | 1,000.00 | 2025-07 | 206,111.20 |
| 2 | 1,000.00 | 2025-08 | 226,349.97 |
| 3 | 1,000.00 | 2025-09 | 196,242.38 |
| 4 | 1,000.00 | 2025-10 | 181,172.45 |
| 5 | 1,000.00 | 2025-11 | 234,430.67 |
| 6 | 1,000.00 | 2025-12 | 234,769.98 |
| 7 | 1,000.00 | 2026-01 | 116,048.00 |
| 8 | 1,000.00 | 2026-02 | 318,325.35 |
| 9 | 1,000.00 | 2026-03 | 227,885.67 |
In [3]:
# Pivot to one row per account, one column per month, then take a running
# cumulative sum to get ENDING balance each month (not just that month's activity)
pivot = monthly_activity.pivot(index='Account Number', columns='Month', values='Signed Activity').fillna(0)
pivot = pivot.sort_index(axis=1)
running_balance = pivot.cumsum(axis=1)
running_balance = running_balance.merge(coa_lookup[['Account Name']], left_index=True, right_index=True)
running_balance.round(2)
Out[3]:
| 2025-06 | 2025-07 | 2025-08 | 2025-09 | 2025-10 | 2025-11 | 2025-12 | 2026-01 | 2026-02 | 2026-03 | 2026-04 | 2026-05 | 2026-06 | Account Name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Account Number | ||||||||||||||
| 1,000.00 | 185,000.00 | 391,111.20 | 617,461.17 | 813,703.55 | 994,876.00 | 1,229,306.67 | 1,464,076.65 | 1,580,124.65 | 1,898,450.00 | 2,126,335.67 | 2,305,246.13 | 2,492,699.52 | 2,698,103.45 | Cash - Operating |
| 1,010.00 | 42,000.00 | -107,196.88 | -258,842.16 | -414,598.36 | -571,650.70 | -723,375.75 | -877,719.80 | -1,030,459.60 | -1,187,083.23 | -1,340,544.79 | -1,495,261.85 | -1,639,860.09 | -1,791,166.35 | Cash - Payroll |
| 1,020.00 | 250,000.00 | 250,977.64 | 251,751.58 | 252,581.90 | 253,350.96 | 254,213.13 | 255,182.11 | 256,073.91 | 256,839.09 | 257,751.75 | 258,591.75 | 259,623.43 | 260,638.36 | Cash - Money Market/Reserve |
| 1,100.00 | 38,500.00 | 149,351.02 | 149,351.02 | 149,351.02 | 228,902.22 | 228,902.22 | 228,902.22 | 318,875.24 | 318,875.24 | 318,875.24 | 415,893.56 | 415,893.56 | 415,893.56 | Accounts Receivable - Trade |
| 1,110.00 | 96,000.00 | 82,449.09 | 80,523.58 | 89,691.22 | 92,922.89 | 72,485.31 | 83,860.20 | 83,860.20 | 63,309.01 | 38,832.55 | 41,687.54 | 124,888.04 | 123,166.75 | Grants Receivable |
| 1,200.00 | 0.00 | 5,399.99 | 2,699.98 | -0.03 | 5,399.96 | 2,699.95 | -0.06 | 5,399.93 | 2,699.92 | -0.08 | 5,399.92 | 2,699.92 | -0.08 | Prepaid Expenses |
| 1,300.00 | 145,000.00 | 145,000.00 | 145,000.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | 163,500.00 | Property and Equipment |
| 1,310.00 | 62,000.00 | 64,450.00 | 66,900.00 | 69,350.00 | 71,800.00 | 74,250.00 | 76,700.00 | 79,150.00 | 81,600.00 | 84,050.00 | 86,500.00 | 88,950.00 | 91,400.00 | Accumulated Depreciation |
| 1,400.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | 8,000.00 | Security Deposits |
| 2,000.00 | 23,763.80 | 8,497.15 | 13,194.33 | 26,140.95 | 37,242.58 | 44,015.34 | 53,618.32 | 64,809.74 | 70,490.20 | 81,192.52 | 94,072.07 | 100,407.53 | 112,376.71 | Accounts Payable |
| 2,010.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | 31,000.00 | Accrued Payroll |
| 2,020.00 | 18,500.00 | 36,455.40 | 58,090.82 | 80,376.42 | 103,877.15 | 132,984.59 | 161,583.30 | 195,584.92 | 234,109.16 | 270,891.44 | 309,105.83 | 346,780.16 | 388,112.32 | Accrued Vacation/PTO |
| 2,100.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 41,000.00 | 16,000.00 | 16,000.00 | Deferred Revenue |
| 2,200.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | 22,000.00 | Refundable Grant Advances |
| 2,300.00 | 15,000.00 | 15,000.00 | 15,000.00 | 15,000.00 | 15,000.00 | 15,000.00 | 15,000.00 | 15,000.00 | 55,000.00 | 55,000.00 | 55,000.00 | 55,000.00 | 55,000.00 | Notes Payable - Current |
| 2,400.00 | 85,000.00 | 83,820.00 | 82,640.00 | 81,460.00 | 80,280.00 | 79,100.00 | 77,920.00 | 76,740.00 | 75,560.00 | 74,380.00 | 73,200.00 | 72,020.00 | 70,840.00 | Notes Payable - Long Term |
| 3,000.00 | 310,000.00 | 265,179.30 | 310,000.00 | 310,000.00 | 290,187.70 | 310,000.00 | 310,000.00 | 310,000.00 | 310,000.00 | 310,000.00 | 297,362.14 | 310,000.00 | 310,000.00 | Net Assets - Unrestricted |
| 3,100.00 | 138,300.00 | 183,120.70 | 138,300.00 | 138,300.00 | 158,112.30 | 138,300.00 | 138,300.00 | 138,300.00 | 138,300.00 | 138,300.00 | 150,937.86 | 208,300.00 | 208,300.00 | Net Assets - Temporarily Restricted |
3. Compute month-over-month variance¶
For every account, every month: dollar change versus the prior month.
In [4]:
month_cols = [c for c in running_balance.columns if c != 'Account Name']
dollar_change = running_balance[month_cols].diff(axis=1)
dollar_change.insert(0, 'Account Name', running_balance['Account Name'])
dollar_change.round(2)
Out[4]:
| Account Name | 2025-06 | 2025-07 | 2025-08 | 2025-09 | 2025-10 | 2025-11 | 2025-12 | 2026-01 | 2026-02 | 2026-03 | 2026-04 | 2026-05 | 2026-06 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Account Number | ||||||||||||||
| 1,000.00 | Cash - Operating | NaN | 206,111.20 | 226,349.97 | 196,242.38 | 181,172.45 | 234,430.67 | 234,769.98 | 116,048.00 | 318,325.35 | 227,885.67 | 178,910.46 | 187,453.39 | 205,403.93 |
| 1,010.00 | Cash - Payroll | NaN | -149,196.88 | -151,645.28 | -155,756.20 | -157,052.34 | -151,725.05 | -154,344.05 | -152,739.80 | -156,623.63 | -153,461.56 | -154,717.06 | -144,598.24 | -151,306.26 |
| 1,020.00 | Cash - Money Market/Reserve | NaN | 977.64 | 773.94 | 830.32 | 769.06 | 862.17 | 968.98 | 891.80 | 765.18 | 912.66 | 840.00 | 1,031.68 | 1,014.93 |
| 1,100.00 | Accounts Receivable - Trade | NaN | 110,851.02 | 0.00 | 0.00 | 79,551.20 | 0.00 | 0.00 | 89,973.02 | 0.00 | 0.00 | 97,018.32 | 0.00 | 0.00 |
| 1,110.00 | Grants Receivable | NaN | -13,550.91 | -1,925.51 | 9,167.64 | 3,231.67 | -20,437.58 | 11,374.89 | 0.00 | -20,551.19 | -24,476.46 | 2,854.99 | 83,200.50 | -1,721.29 |
| 1,200.00 | Prepaid Expenses | NaN | 5,399.99 | -2,700.01 | -2,700.01 | 5,399.99 | -2,700.01 | -2,700.01 | 5,399.99 | -2,700.01 | -2,700.00 | 5,400.00 | -2,700.00 | -2,700.00 |
| 1,300.00 | Property and Equipment | NaN | 0.00 | 0.00 | 18,500.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 1,310.00 | Accumulated Depreciation | NaN | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 | 2,450.00 |
| 1,400.00 | Security Deposits | NaN | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2,000.00 | Accounts Payable | NaN | -15,266.65 | 4,697.18 | 12,946.62 | 11,101.63 | 6,772.76 | 9,602.98 | 11,191.42 | 5,680.46 | 10,702.32 | 12,879.55 | 6,335.46 | 11,969.18 |
| 2,010.00 | Accrued Payroll | NaN | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2,020.00 | Accrued Vacation/PTO | NaN | 17,955.40 | 21,635.42 | 22,285.60 | 23,500.74 | 29,107.44 | 28,598.71 | 34,001.63 | 38,524.23 | 36,782.28 | 38,214.39 | 37,674.33 | 41,332.15 |
| 2,100.00 | Deferred Revenue | NaN | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -25,000.00 | 0.00 |
| 2,200.00 | Refundable Grant Advances | NaN | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2,300.00 | Notes Payable - Current | NaN | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 40,000.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2,400.00 | Notes Payable - Long Term | NaN | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 | -1,180.00 |
| 3,000.00 | Net Assets - Unrestricted | NaN | -44,820.70 | 44,820.70 | 0.00 | -19,812.30 | 19,812.30 | 0.00 | 0.00 | 0.00 | 0.00 | -12,637.86 | 12,637.86 | 0.00 |
| 3,100.00 | Net Assets - Temporarily Restricted | NaN | 44,820.70 | -44,820.70 | 0.00 | 19,812.30 | -19,812.30 | 0.00 | 0.00 | 0.00 | 0.00 | 12,637.86 | 57,362.14 | 0.00 |
4. Flag unusual swings¶
A fixed dollar threshold doesn't work equally well across every account — a $5,000 swing means something very different for Petty Cash than it does for Accounts Payable. Instead, flag any month where an account's change exceeds 1.5 standard deviations from that account's own average month-over-month change across the year. This catches genuinely unusual months relative to each account's normal behavior, not just large accounts in general.
Some accounts (like a note payable with a perfectly flat monthly payment) will have zero variance and are correctly excluded — there's nothing to flag when a balance moves the exact same amount every month.
In [5]:
flags = []
for acct in dollar_change.index:
changes = dollar_change.loc[acct, month_cols].dropna()
if len(changes) < 3:
continue
mean, std = changes.mean(), changes.std()
if std == 0 or pd.isna(std):
continue
for month, val in changes.items():
z = (val - mean) / std
if abs(z) > 1.5:
flags.append({
'Account Number': acct,
'Account Name': running_balance.loc[acct, 'Account Name'],
'Month': str(month),
'Change ($)': round(val, 2),
'Z-score': round(z, 2),
})
flags_df = pd.DataFrame(flags)
if len(flags_df):
flags_df = flags_df.sort_values('Z-score', key=lambda s: s.abs(), ascending=False).reset_index(drop=True)
print(f"{len(flags_df)} flagged account-months out of {len(dollar_change) * len(month_cols)} total")
flags_df
15 flagged account-months out of 234 total
Out[5]:
| Account Number | Account Name | Month | Change ($) | Z-score | |
|---|---|---|---|---|---|
| 0 | 1,300.00 | Property and Equipment | 2025-09 | 18,500.00 | 3.18 |
| 1 | 2,100.00 | Deferred Revenue | 2026-05 | -25,000.00 | -3.18 |
| 2 | 2,300.00 | Notes Payable - Current | 2026-02 | 40,000.00 | 3.18 |
| 3 | 2,000.00 | Accounts Payable | 2025-07 | -15,266.65 | -2.94 |
| 4 | 1,110.00 | Grants Receivable | 2026-05 | 83,200.50 | 2.87 |
| 5 | 1,010.00 | Cash - Payroll | 2026-05 | -144,598.24 | 2.35 |
| 6 | 1,000.00 | Cash - Operating | 2026-02 | 318,325.35 | 2.29 |
| 7 | 3,000.00 | Net Assets - Unrestricted | 2025-08 | 44,820.70 | 2.08 |
| 8 | 3,000.00 | Net Assets - Unrestricted | 2025-07 | -44,820.70 | -2.08 |
| 9 | 1,000.00 | Cash - Operating | 2026-01 | 116,048.00 | -1.96 |
| 10 | 3,100.00 | Net Assets - Temporarily Restricted | 2026-05 | 57,362.14 | 1.93 |
| 11 | 3,100.00 | Net Assets - Temporarily Restricted | 2025-08 | -44,820.70 | -1.90 |
| 12 | 1,100.00 | Accounts Receivable - Trade | 2025-07 | 110,851.02 | 1.69 |
| 13 | 2,020.00 | Accrued Vacation/PTO | 2025-07 | 17,955.40 | -1.61 |
| 14 | 1,020.00 | Cash - Money Market/Reserve | 2026-05 | 1,031.68 | 1.52 |
5. Visualize a flagged account¶
Grants Receivable is a strong example of what this analysis is built to catch: a genuine, explainable event rather than noise. The account jumps by $83,200.50 in May 2026 — a real grant recognized and billed to a funder that had not yet been collected as cash. That kind of swing is exactly what a controller should be able to explain in one sentence during a close review, and this analysis surfaces it automatically instead of waiting for someone to notice it by eye.
In [6]:
def plot_account(acct_number, running_balance, flags_df, month_cols):
name = running_balance.loc[acct_number, 'Account Name']
y_vals = running_balance.loc[acct_number, month_cols].values
x_vals = [str(m) for m in month_cols]
fig = go.Figure()
fig.add_trace(go.Scatter(
x=x_vals, y=y_vals, mode='lines+markers', name='Ending Balance',
line=dict(color='#1F4E78', width=3),
hovertemplate='%{x}<br>Ending Balance: $%{y:,.0f}<extra></extra>',
))
acct_flags = flags_df[flags_df['Account Number'] == acct_number] if len(flags_df) else pd.DataFrame()
if len(acct_flags):
flag_y = [running_balance.loc[acct_number, pd.Period(m)] for m in acct_flags['Month']]
fig.add_trace(go.Scatter(
x=acct_flags['Month'], y=flag_y, mode='markers', name='Flagged month',
marker=dict(color='#C0392B', size=13, symbol='x'),
hovertemplate='%{x}<br>Flagged change<extra></extra>',
))
fig.update_layout(
title=dict(text=f'{acct_number:.0f} — {name}: Monthly Ending Balance', font=dict(size=18)),
xaxis_title='Month', yaxis_title='Ending Balance ($)', yaxis_tickformat='$,.0f',
template='plotly_white', height=480, margin=dict(l=60, r=30, t=60, b=50),
)
return fig
fig = plot_account(1110.0, running_balance, flags_df, month_cols)
fig.write_html('balance_sheet_variance_chart.html', include_plotlyjs='cdn', full_html=True)
fig.show()
What this catches that a static spreadsheet doesn't¶
A single annual Trial Balance shows you the beginning and ending position — it can't show you when something moved or whether that movement was normal for that account. This notebook rebuilds the full monthly path for every balance sheet account and statistically flags the months worth asking about, which is the actual question a controller needs answered before close: not "does the balance sheet balance" (it always should), but "does every material movement have an explanation I could give an auditor right now."