"""Athens Area SD General Fund projection model, 2026-27 through 2033-34. Run: python model.py (writes model__.csv and model_summary.csv here) Every input below is cited to a public document. Every assumption is labelled ASSUMPTION. Nothing here is a forecast of what will happen; it is arithmetic on the district's own 2026-27 budget, carried forward under stated rules. """ import csv, os HERE = os.path.dirname(os.path.abspath(__file__)) YEARS = ["2026-27", "2027-28", "2028-29", "2029-30", "2030-31", "2031-32", "2032-33", "2033-34"] # ---- Base year: 2026-27 Final General Fund Budget (PDE-2028), printed 6/16/2026 ---- REV_BASE = 47_262_473 # p.7 TOTAL ESTIMATED REVENUES AND OTHER SOURCES EXP_BASE = 48_904_482 # p.17 TOTAL EXPENDITURES (includes $100,000 budgetary reserve, p.16) SAL_BASE = 18_547_968 # sum of object 100 lines, pp.14-16 BEN_BASE = 15_000_678 # sum of object 200 lines, pp.14-16 DEBT_TRANSFER_BASE = 3_772_368 # 5200 interfund transfer out, p.13 FB_START = 4_098_969 # p.5 estimated beginning fund balance (6/30/2026) MILLS_BASE = 60.3694 # p.8 line i / p.12 STATE_PSERS_REIMB = 4_069_852 # 7820, p.6 STATE_FICA_REIMB = 926_894 # 7810, p.6 PLANCON_BASE = 523_609 # 7320 building reimbursement, p.6 # ---- Other documents ---- VALUE_OF_MILL = 279_427 # 2026-27 Proposed Final Budget presentation (May 2026), p.19 PSERS_RATE = 0.3359 # same presentation, p.9 ("33.59% PSERS contribution") FICA_RATE = 0.0765 # federal Social Security + Medicare employer rate RETIREE_HEALTH = 782_000 # same presentation, p.9 ("Retiree health insurance - $782K") # Debt service by fiscal year ending June 30 (FY2025 audit, Klacik, PDF p.41 / printed p.38) DEBT_SERVICE = {"2026-27": 3_655_368, "2027-28": 3_489_168, "2028-29": 3_192_214, "2029-30": 3_193_831, "2030-31": 2_481_525} # nothing due after FY ending 6/30/2031 CAPITAL_RESERVE_TRANSFER = 117_000 # presentation p.10 ("includes $117,000 transfer to capital reserve") # Teacher CBA (AAEA 2025-2029), PDF p.11: increase in total bargaining-unit cost incl. step CBA = {"2027-28": 0.033, "2028-29": 0.032} # 2026-27 3.4% is already in the base budget ACT1_2027_28 = 0.052 # PDE Act 1 adjusted index for Athens Area SD, 2027-28 (published Sept 2026) # SRU closure: Community Information document, Sept 21 2026, p.4 ("Conservative working net") SRU_NET = {"Y1": 1_719_760, "Y2": 1_943_000} TEACHER_LOADED = 132_000 # same p.4: six classroom teachers = $792,000 -> $132,000 each (salary + benefits) # Salary schedule 2026-27 (CBA PDF p.42): top of scale (PhD, step 11) and entry (Bachelor, step 1) TOP_SALARY_2627 = 104_068 BASE_SALARY_2627 = 69_770 # Staffing (PDE): 2015-16 active professional 162 + support 82 FT + 24 PT = 268, enrollment 2,128 # 2025-26 active professional 161 + support 90 FT + 19 PT = 270, enrollment 1,996 STAFF_1516, ENR_1516, STAFF_2526, ENR_2526 = 268, 2128, 270, 1996 SRU_POSITIONS_IN_ESTIMATE = 14 # p.4 lines: 6 teachers + 3 specials + 2 secretaries + 1 health-room asst + 2 aides (custodian count not stated) # ---- ASSUMPTIONS ---- POST_CBA_RAISE = 0.033 # ASSUMPTION: after Aug 2029, raises continue at the 2025-29 contract average (3.35%, rounded) HEALTH_GROWTH = 0.08 # ASSUMPTION: health and other non-pension benefits grow 8%/yr (district reported +7% and +8.2%, presentation p.4) OTHER_GROWTH = 0.03 # ASSUMPTION: non-personnel, non-debt spending grows 3%/yr INCENTIVE = 15_000 # ASSUMPTION: one-time retirement incentive per teacher (prompted range $10-20K; midpoint) # ASSUMPTION: all other revenue is held flat at the 2026-27 budget, except # - property tax: + (new mills - prior mills) x VALUE_OF_MILL (assessed value held flat) # - state PSERS and FICA reimbursement move in proportion to those costs # - PlanCon (7320) scales with debt service on the 2019/2019A/2020/2020A series and ends after 2027-28 PLANCON_SHARE = {"2026-27": 1.0, "2027-28": 2_946_652.50 / 3_434_702.50} CASES = {"low": 0.04, "mid": 0.05, "high": 0.07} def run(scn, idx, freeze_mills=False): sal, ben_other = SAL_BASE, BEN_BASE - SAL_BASE * (PSERS_RATE + FICA_RATE) other = EXP_BASE - SAL_BASE - BEN_BASE - DEBT_TRANSFER_BASE nontax_rev = REV_BASE - STATE_PSERS_REIMB - STATE_FICA_REIMB - PLANCON_BASE mills, fb, rows, onetime_done = MILLS_BASE, FB_START, [], False psers_ratio = STATE_PSERS_REIMB / (SAL_BASE * PSERS_RATE) fica_ratio = STATE_FICA_REIMB / (SAL_BASE * FICA_RATE) pos_cut = 0 # salary dollars removed by attrition / buyouts (grow with salaries) for i, y in enumerate(YEARS): prev_mills = mills if i > 0: g = CBA.get(y, POST_CBA_RAISE) if scn.get("freeze") and y >= "2029-30": g = 0.0 sal *= 1 + g pos_cut *= 1 + g ben_other *= 1 + HEALTH_GROWTH other *= 1 + OTHER_GROWTH rate = ACT1_2027_28 if y == "2027-28" else idx if not freeze_mills: mills = round(mills * (1 + rate), 4) onetime = 0.0 # attrition / buyouts take effect in 2027-28 if y == "2027-28" and not onetime_done: onetime_done = True if scn.get("buyout") == "eliminate10": pos_cut += 10 * TOP_SALARY_2627 * (1 + CBA["2027-28"]) onetime = 10 * INCENTIVE elif scn.get("buyout") == "replace20": pos_cut += 20 * (TOP_SALARY_2627 - BASE_SALARY_2627) * (1 + CBA["2027-28"]) onetime = 20 * INCENTIVE if scn.get("attrition"): pos_cut += scn["attrition"] * (TEACHER_LOADED / (1 + PSERS_RATE + FICA_RATE)) * 0.5 * (1 + CBA["2027-28"]) if scn.get("attrition") and y == "2028-29": pos_cut += scn["attrition"] * (TEACHER_LOADED / (1 + PSERS_RATE + FICA_RATE)) * 0.5 * (1 + CBA["2027-28"]) * (1 + CBA["2028-29"]) eff_sal = sal - pos_cut heads_cut = 0 if scn.get("buyout") == "eliminate10" and y >= "2027-28": heads_cut += 10 if scn.get("attrition") and y >= "2027-28": heads_cut += scn["attrition"] / 2 if y == "2027-28" else scn["attrition"] health_per_head = (ben_other - RETIREE_HEALTH * (1 + HEALTH_GROWTH) ** i) / STAFF_2526 eff_ben_other = ben_other - heads_cut * health_per_head psers, fica = eff_sal * PSERS_RATE, eff_sal * FICA_RATE debt = DEBT_SERVICE.get(y, 0) + CAPITAL_RESERVE_TRANSFER sru = 0.0 if scn.get("sru") and y >= "2027-28": sru = SRU_NET["Y1"] if y == "2027-28" else SRU_NET["Y2"] exp = eff_sal + psers + fica + eff_ben_other + other + debt - sru + onetime if i == 0: exp = EXP_BASE rev = nontax_rev + (mills - MILLS_BASE) * VALUE_OF_MILL + psers * psers_ratio + fica * fica_ratio \ + PLANCON_BASE * PLANCON_SHARE.get(y, 0.0) if i == 0: rev = REV_BASE surplus = rev - exp fb += surplus rows.append({"year": y, "revenue": round(rev), "expenditures": round(exp), "surplus_deficit": round(surplus), "ending_fund_balance": round(fb), "millage": mills, "tax_increase_pct": round((mills / prev_mills - 1) * 100, 2) if i > 0 else 5.10, "one_time_cost": round(onetime), # millage change (vs prior year) that would have exactly balanced this year "increase_needed_to_balance_pct": round(((mills - surplus / VALUE_OF_MILL) / prev_mills - 1) * 100, 2) if i > 0 else ""}) return rows ATTRITION = round(STAFF_2526 - ENR_2526 * STAFF_1516 / ENR_1516) - SRU_POSITIONS_IN_ESTIMATE # beyond the SRU estimate SCENARIOS = { "1_board_plan": {"sru": True}, "2_sru_attrition": {"sru": True, "attrition": ATTRITION}, "3a_buyout_eliminate10": {"sru": True, "buyout": "eliminate10"}, "3b_buyout_replace20": {"sru": True, "buyout": "replace20"}, "4_sru_pay_freeze_2029": {"sru": True, "freeze": True}, "5_combined": {"sru": True, "attrition": ATTRITION, "buyout": "eliminate10", "freeze": True}, } if __name__ == "__main__": summary = [] for name, scn in SCENARIOS.items(): for case, idx in CASES.items(): rows = run(scn, idx) with open(os.path.join(HERE, f"model_{name}_{case}.csv"), "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(rows[0])); w.writeheader(); w.writerows(rows) first_bal = next((r["year"] for r in rows[1:] if r["surplus_deficit"] >= 0), "never (through 2033-34)") below = next((r["year"] for r in rows[1:] if r["increase_needed_to_balance_pct"] < r["tax_increase_pct"]), "never (through 2033-34)") neg_fb = next((r["year"] for r in rows if r["ending_fund_balance"] < 0), "no") flat = run(scn, idx, freeze_mills=True) first_flat = next((r["year"] for r in flat[1:] if r["surplus_deficit"] >= 0), "never (through 2033-34)") def sustained(rs): # first year from which every remaining year through 2033-34 is balanced for k in range(1, len(rs)): if all(r["surplus_deficit"] >= 0 for r in rs[k:]): return rs[k]["year"] return "never (through 2033-34)" summary.append({"scenario": name, "act1_case": case, "index_after_2027_28": idx, "first_year_balanced_with_max_increases": first_bal, "balanced_every_year_from_with_max_increases": sustained(rows), "first_year_balanced_at_flat_millage": first_flat, "balanced_every_year_from_at_flat_millage": sustained(flat), "first_year_increase_below_max_would_balance": below, "first_year_fund_balance_negative_with_max_increases": neg_fb, "fund_balance_2033_34_with_max_increases": rows[-1]["ending_fund_balance"], "millage_2033_34_with_max_increases": rows[-1]["millage"]}) with open(os.path.join(HERE, f"model_{name}_{case}_flatmillage.csv"), "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(flat[0])); w.writeheader(); w.writerows(flat) with open(os.path.join(HERE, "model_summary.csv"), "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(summary[0])); w.writeheader(); w.writerows(summary) print("attrition positions beyond SRU estimate:", ATTRITION) for s in summary: print(s)