CSC Reconciliation Calculator

A calculator for tribal health programs to track Contract Support Cost entitlement, compare estimated vs. actual direct costs, and identify CSC underrecovery throughout the contract year.

Overview

This calculator helps tribal health programs track Contract Support Cost entitlement on 638 contracts throughout the year. It compares estimated direct program costs (used for initial CSC funding) against actual direct costs (which determine true CSC entitlement), and surfaces underrecovery before year-end so programs can prepare accurate settlement documentation.

Designed for: Tribal health programs operating IHS 638 contracts under ISDEAA, especially those managing braided funding where 638 direct costs depend on cost allocation across multiple funding streams.

Pairs with: CSC Reconciliation Guide

Calculator Structure (Excel/Google Sheets)

Tab 1: Contract Setup

One row per 638 contract. Enter at the start of each contract year.

Column	Field	Example	Notes
A	Contract ID	638-BH-01	Unique identifier
B	Contract Name	IHS Behavioral Health Program
C	IHS Area Office	Portland Area
D	Contract Year Start	10/1/2025
E	Contract Year End	9/30/2026
F	CSC Rate	19.7%	From contract terms or IHS needs assessment
G	DOI-IBIA Indirect Rate	24.1%	Current negotiated rate (for reference — not used in CSC calculation)
H	DOI-IBIA Rate Expiration	6/30/2027	Alert if within 6 months of expiration
I	Estimated Annual Direct Costs	$780,000	From contract budget
J	Estimated Annual CSC	$153,660	(formula: I × F)
K	CSC Funding Received YTD	$153,660	Update as payments are received

Multi-contract support: Add rows for each 638 contract. The calculator aggregates across contracts for a portfolio-level view.

Tab 2: Direct Cost Categories

Define what constitutes "direct program costs" for each contract. This determines the CSC base.

Column	Field	Example
A	Contract ID	638-BH-01
B	Cost Category	Personnel — Program Director
C	Allocation Type	Direct (100% 638)
D	Annual Budget	$85,000
E	Monthly Budget	$7,083

Contract ID	Cost Category	Allocation Type	Annual Budget	Monthly Budget
638-BH-01	Program Director (100% 638)	Direct	$85,000	$7,083
638-BH-01	Clinical Staff (allocated to 638)	Shared — 25% of $245K pool	$61,250	$5,104
638-BH-01	Benefits (638 personnel)	Calculated at 28%	$40,950	$3,413
638-BH-01	Program Supplies	Direct	$24,000	$2,000
638-BH-01	Travel — Program	Direct	$18,000	$1,500
638-BH-01	Equipment	Direct	$12,000	$1,000
638-BH-01	Contracted Services	Direct	$35,000	$2,917
	Total		$276,200	$23,017

Note: For shared personnel, the "allocated to 638" amount comes from the cost allocation methodology. If using the Cost Allocation Schedule Template, the 638 allocation feeds directly into this tab.

Tab 3: Monthly Actuals Tracker (Core Calculator)

Enter actual direct costs each month. The calculator computes running CSC entitlement and variance.

Input section — one row per month:

Month	Personnel (Allocated)	Benefits	Supplies	Travel	Equipment	Contracted Svcs	Monthly Actual	Cumulative Actual
Oct	$26,800	$7,504	$1,800	$2,100	$0	$3,200	$41,404	$41,404
Nov	$27,200	$7,616	$2,100	$800	$0	$2,800	$40,516	$81,920
Dec	$25,600	$7,168	$2,400	$0	$12,000	$3,500	$50,668	$132,588
Jan	$28,400	$7,952	$1,600	$1,500	$0	$2,900	$42,352	$174,940
Feb	$28,400	$7,952	$1,900	$0	$0	$3,100	$41,352	$216,292
Mar	$28,400	$7,952	$2,200	$3,200	$0	$3,400	$45,152	$261,444
Apr
May
Jun
Jul
Aug
Sep

Calculated section — updates automatically:

Month	Cumulative Actual	Cumulative Estimate	Direct Cost Variance	CSC Based on Actuals	CSC Based on Estimate	CSC Variance	Status
Oct	$41,404	$65,000	-$23,596	$8,157	$12,805	-$4,648	Under estimate
Nov	$81,920	$130,000	-$48,080	$16,138	$25,610	-$9,472	Under estimate
Dec	$132,588	$195,000	-$62,412	$26,120	$38,415	-$12,295	Under estimate
Jan	$174,940	$260,000	-$85,060	$34,463	$51,220	-$16,757	Under estimate
Feb	$216,292	$325,000	-$108,708	$42,609	$64,025	-$21,416	Under estimate
Mar	$261,444	$390,000	-$128,556	$51,505	$76,830	-$25,325	Under estimate

(In this example, actuals are running below estimates — the program may owe back CSC at year-end unless spending accelerates.)

Formulas:

Cumulative Estimate = (Months Elapsed / 12) × Annual Estimated Direct Costs
Direct Cost Variance = Cumulative Actual - Cumulative Estimate
CSC Based on Actuals = Cumulative Actual × CSC Rate
CSC Based on Estimate = Cumulative Estimate × CSC Rate
CSC Variance = CSC Based on Actuals - CSC Based on Estimate
Status = IF(Variance > 0, "Underrecovered — owed additional CSC", IF(Variance < 0, "Under estimate — potential CSC return", "On track"))

Tab 4: Year-End Reconciliation Summary

Auto-populated from Tab 3 after all 12 months are entered.

CONTRACT: IHS Behavioral Health Program (638-BH-01)
CONTRACT YEAR: October 1, 2025 — September 30, 2026
CSC RATE: 19.7%

ESTIMATED DIRECT PROGRAM COSTS:        $780,000
ACTUAL DIRECT PROGRAM COSTS:            $534,508
VARIANCE:                               -$245,492

ESTIMATED CSC (RECEIVED):               $153,660
CSC BASED ON ACTUAL DIRECT COSTS:       $105,298
CSC SETTLEMENT:                         -$48,362

RESULT: Actual direct costs were below estimate by $245,492.
        CSC overpayment of $48,362 requires settlement with IHS.

(Or, in an underrecovery scenario:)

ESTIMATED DIRECT PROGRAM COSTS:        $511,400
ACTUAL DIRECT PROGRAM COSTS:            $534,508
VARIANCE:                               +$23,108

ESTIMATED CSC (RECEIVED):               $100,746
CSC BASED ON ACTUAL DIRECT COSTS:       $105,298
CSC SETTLEMENT:                         +$4,552

RESULT: Actual direct costs exceeded estimate by $23,108.
        Additional CSC owed to tribe: $4,552.

Tab 5: Multi-Contract Portfolio View

For programs operating multiple 638 contracts, an aggregated view:

Contract	Estimated Direct	Actual Direct (YTD)	CSC Rate	Estimated CSC	CSC on Actuals (YTD)	Projected Annual CSC	Projected Variance
638-BH-01	$780,000	$261,444	19.7%	$153,660	$51,505	$104,178*	-$49,482
638-CW-01	$420,000	$225,600	18.5%	$77,700	$41,736	$83,472*	+$5,772
638-PH-01	$310,000	$168,200	19.0%	$58,900	$31,958	$63,916*	+$5,016
Total	$1,510,000	$655,244		$290,260	$125,199	$251,566	-$38,694

*Projected annual = (YTD Actual / Months Elapsed) × 12 × CSC Rate

Portfolio insight: Even though two contracts are trending above estimate, one large contract trending below pulls the portfolio into projected net overrecovery. The program should investigate whether 638-BH-01 will catch up in remaining months or whether a mid-year adjustment with IHS is warranted.

Tab 6: Settlement Documentation Generator

Pre-formats the reconciliation package for IHS submission.

Section 1: Summary Statement Auto-populated cover page with contract details, estimated vs. actual comparison, settlement amount.

Section 2: Monthly Direct Cost Detail Formatted version of Tab 3 — monthly detail by cost category, with totals matching the summary.

Section 3: Variance Explanation Template for narrative explanation of significant variances:

Cost Category	Budgeted	Actual	Variance	Explanation
Personnel	$330,000	$348,600	+$18,600	Mid-year hire (April) at higher step than budgeted; 2-month vacancy filled at higher rate
Travel	$18,000	$15,800	-$2,200	One planned training trip cancelled due to scheduling conflict
Contracted Svcs	$35,000	$38,200	+$3,200	Additional interpreter services required for expanded outreach

Section 4: Cost Allocation Methodology Reference Summary of the allocation methodology used for shared costs (personnel allocated across braided streams). References the full methodology document.

Section 5: Accounting System Reconciliation Template showing how direct costs claimed tie to the organization's general ledger. Columns for GL account, GL amount, 638 allocation %, 638 allocated amount.

Quick Calculator (Simplified Version)

For programs that want a quick estimate before building out the full tracker:

Inputs

Field	Value
Total 638 Direct Program Costs (Budgeted)	$ ________
CSC Rate	______ %
Months Completed This Year	______
Actual Direct Costs Spent YTD	$ ________

Outputs (Auto-Calculated)

Metric	Formula	Result
Annual CSC Entitlement (Estimated)	Budget × CSC Rate	$ ________
Monthly Burn Rate (Budgeted)	Budget ÷ 12	$ ________
Monthly Burn Rate (Actual)	YTD Actual ÷ Months	$ ________
Projected Annual Direct Costs	Actual Burn × 12	$ ________
Projected CSC (Based on Actuals)	Projected × CSC Rate	$ ________
Projected CSC Variance	Projected CSC - Estimated CSC	$ ________
Projected Annual Underrecovery (if positive)		$ ________

Implementation Notes

For Google Sheets

Use data validation for contract IDs across tabs
Use SPARKLINE functions in Tab 3 for visual trend indicators
Use conditional formatting: green (within 5% of estimate), yellow (5-10% off), red (>10% off)
Share with read-only access for tribal leadership dashboard view

For Excel

Use named ranges for CSC rates and contract details
Use pivot tables for multi-contract analysis
Consider Power Query connections to accounting system exports for automated actuals entry

For Web-Based Interactive Tool

Input: Contract details + monthly actuals
Output: Real-time CSC variance tracking with visual dashboard
Feature: Email alert when projected CSC variance exceeds threshold (e.g., $5,000)
Feature: PDF export of settlement documentation package

Integration with Cost Allocation Template

If using the Cost Allocation Schedule Template:

Tab 4 of the allocation template (Monthly Allocation Schedule) produces the 638 direct cost figures
Those figures feed directly into Tab 3 of this calculator
The two templates can be linked (in Excel: cross-workbook references; in Sheets: IMPORTRANGE)

Download this calculator at [grantbridges.com/tools/csc-calculator]. For the full reconciliation methodology, see the CSC Reconciliation Guide.