Cost Allocation Schedule Template for Braided-Funded Organizations
A ready-to-use cost allocation schedule template designed for healthcare organizations managing multiple funding streams under 2 CFR 200, ISDEAA, CMS Medicaid, and state contract terms.
Overview
This template provides a multi-framework cost allocation schedule for healthcare organizations managing braided funding. It produces monthly allocation entries across funding streams and generates framework-specific outputs for reporting and audit documentation.
Designed for: CCBHCs, tribal health programs, FQHCs, and community health organizations managing 3-8 concurrent funding streams under multiple compliance frameworks.
Pairs with: Cost Allocation Across Multiple Compliance Frameworks
Template Structure (Excel/Google Sheets)
Tab 1: Funding Streams Setup
Configuration tab — enter once, update when grants change.
| Column | Field | Example | Notes |
|---|---|---|---|
| A | Stream ID | SAMHSA-01 | Unique identifier for the funding stream |
| B | Stream Name | SAMHSA CCBHC Expansion | Full name of the grant/contract |
| C | Funder | SAMHSA | Federal agency, state agency, or foundation |
| D | Compliance Framework | 2 CFR 200 | Options: 2cfr200, isdeaa, cms_medicaid, state_contract, private |
| E | Fiscal Year Type | Federal (Oct-Sep) | Options: Federal, State, Calendar, Custom |
| F | Budget Period Start | 10/1/2025 | |
| G | Budget Period End | 9/30/2026 | |
| H | Total Award | $425,000 | Total budget for the period |
| I | Indirect Cost Rate | 22% | The rate applicable to this stream |
| J | Indirect Cost Cap | — | Leave blank if no cap; enter cap % if applicable |
| K | CSC Rate | — | Only for ISDEAA streams; enter CSC rate |
| L | Notes | Expansion grant, Year 2 |
Validation rules:
- Framework must be one of the five options
- Indirect Cost Cap only applies to state_contract and private streams
- CSC Rate only applies to isdeaa streams
- Budget Period End must be after Start
Tab 2: Personnel Roster
All staff funded by multiple streams. Enter once per staff member, update when effort changes.
| Column | Field | Example |
|---|---|---|
| A | Employee ID | EMP-001 |
| B | Name | Maria Rodriguez |
| C | Position | Licensed Behavioral Health Clinician |
| D | Annual Salary | $72,000 |
| E | Benefits Rate | 28% |
| F | Total Annual Compensation | $92,160 |
| G | Monthly Compensation | $7,680 |
| H | Stream 1 ID | SAMHSA-01 |
| I | Stream 1 Effort % | 30% |
| J | Stream 2 ID | MEDICAID-01 |
| K | Stream 2 Effort % | 35% |
| L | Stream 3 ID | STATE-01 |
| M | Stream 3 Effort % | 20% |
| N | Stream 4 ID | FOUND-01 |
| O | Stream 4 Effort % | 15% |
| P | Total Effort | 100% |
Validation rules:
- Total Effort (column P) must equal 100%. Conditional formatting: red if ≠ 100%.
- Stream IDs must match values in Tab 1.
- Up to 6 streams per person (expandable).
Tab 3: Non-Personnel Shared Costs
Shared costs requiring allocation (facilities, technology, professional services, etc.).
| Column | Field | Example |
|---|---|---|
| A | Cost ID | NP-001 |
| B | Cost Category | Facilities — Rent |
| C | Monthly Amount | $8,500 |
| D | Allocation Base | Square Footage |
| E | Stream 1 ID | SAMHSA-01 |
| F | Stream 1 % | 25% |
| G | Stream 2 ID | 638-01 |
| H | Stream 2 % | 20% |
| I | Stream 3 ID | MEDICAID-01 |
| J | Stream 3 % | 18% |
| K | Stream 4 ID | STATE-01 |
| L | Stream 4 % | 12% |
| M | Indirect Pool | 25% |
| N | Total | 100% |
Standard cost categories to include:
- Facilities — Rent
- Facilities — Utilities
- Facilities — Maintenance
- Facilities — Insurance (property)
- Technology — EHR
- Technology — IT Infrastructure
- Technology — Software Licenses
- Professional Services — Audit
- Professional Services — Legal
- Professional Services — Consulting
- Insurance — Liability
- Insurance — Workers' Comp
- Supplies — Office
- Communications — Phone/Internet
Tab 4: Monthly Allocation Schedule (Core Output)
This is the primary working tab. One row per cost line per month. Generates automatically from Tabs 2 and 3.
| Column | Field | Source |
|---|---|---|
| A | Month | January 2026 |
| B | Cost Type | Personnel / Non-Personnel |
| C | Cost ID | EMP-001 or NP-001 |
| D | Description | Maria Rodriguez — Salary + Benefits / Rent |
| E | Total Monthly Cost | $7,680 / $8,500 |
| F | SAMHSA-01 Amount | (formula: E × effort% or allocation%) |
| G | 638-01 Amount | (formula) |
| H | MEDICAID-01 Amount | (formula) |
| I | STATE-01 Amount | (formula) |
| J | FOUND-01 Amount | (formula) |
| K | Indirect Pool Amount | (formula — non-personnel only) |
| L | Total Allocated | (formula: sum F through K — must equal E) |
| M | Variance | (formula: L - E — must be $0.00) |
Key formulas:
- Personnel:
Stream Amount = Monthly Compensation × Stream Effort % - Non-personnel:
Stream Amount = Monthly Cost × Stream Allocation % - Validation:
Total Allocated = Total Monthly Cost(variance must be zero)
Monthly summary section (at bottom of each month):
| Stream | Personnel Total | Non-Personnel Total | Allocated Indirect | Grand Total |
|---|---|---|---|---|
| SAMHSA-01 | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| 638-01 | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| MEDICAID-01 | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| STATE-01 | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| FOUND-01 | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Indirect Pool | — | $XX,XXX | — | $XX,XXX |
| Total | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
Tab 5: Indirect Cost / Overhead Recovery
Calculates overhead recovery by stream using the correct mechanism per framework.
| Column | Field |
|---|---|
| A | Stream ID |
| B | Framework |
| C | YTD Direct Costs (from Tab 4) |
| D | Allocated Overhead Share (from Tab 4 indirect pool, distributed by MTDC) |
| E | Recovery Mechanism |
| F | Applicable Rate |
| G | Recoverable Amount |
| H | Underrecovery |
Summary:
| SAMHSA (2 CFR 200) | 638 (ISDEAA) | Medicaid (CMS) | State Contract | Foundation | |
|---|---|---|---|---|---|
| YTD Direct Costs | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Overhead Share | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Recovery Mechanism | NICRA 22% | CSC 19.7% | Embedded | State 10% | N/A |
| Recovered | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Underrecovery | $0 | $0 | $0 | $X,XXX | $X,XXX |
Tab 6: Budget vs. Actual (Per Stream)
One section per funding stream. Compares allocated costs to budget by category.
| Budget Category | Annual Budget | YTD Allocated | YTD % of Budget | Remaining | Projected Annual | Variance |
|---|---|---|---|---|---|---|
| Personnel | $180,000 | $90,400 | 50.2% | $89,600 | $180,800 | +$800 |
| Fringe Benefits | $50,400 | $25,312 | 50.2% | $25,088 | $50,624 | +$224 |
| Travel | $12,000 | $4,800 | 40.0% | $7,200 | $9,600 | -$2,400 |
| Supplies | $8,000 | $4,200 | 52.5% | $3,800 | $8,400 | +$400 |
| Contractual | $24,000 | $11,500 | 47.9% | $12,500 | $23,000 | -$1,000 |
| Indirect | $39,600 | $19,800 | 50.0% | $19,800 | $39,600 | $0 |
| Total | $314,000 | $156,012 | 49.7% | $157,988 | $312,024 | -$1,976 |
Burn rate indicator: Conditional formatting — green if YTD % is within 5% of expected (based on months elapsed), yellow if 5-10% off, red if >10% off.
Tab 7: Fiscal Period Roll-Up
Aggregates monthly data by each fiscal year period for reporting.
Federal Fiscal Year (SAMHSA, HRSA, CDC grants):
| FFY Quarter | Period | SAMHSA-01 | CDC-01 | HRSA-01 | Total Federal |
|---|---|---|---|---|---|
| Q1 | Oct-Dec | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Q2 | Jan-Mar | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Q3 | Apr-Jun | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
| Q4 | Jul-Sep | $XX,XXX | $XX,XXX | $XX,XXX | $XX,XXX |
State Fiscal Year (state contracts):
| SFY Quarter | Period | STATE-01 | STATE-02 | Total State |
|---|---|---|---|---|
| Q1 | Jul-Sep | $XX,XXX | $XX,XXX | $XX,XXX |
| Q2 | Oct-Dec | $XX,XXX | $XX,XXX | $XX,XXX |
| Q3 | Jan-Mar | $XX,XXX | $XX,XXX | $XX,XXX |
| Q4 | Apr-Jun | $XX,XXX | $XX,XXX | $XX,XXX |
Calendar Year (Medicaid, foundations):
| CY Quarter | Period | MEDICAID-01 | FOUND-01 | Total CY |
|---|---|---|---|---|
| Q1 | Jan-Mar | $XX,XXX | $XX,XXX | $XX,XXX |
| Q2 | Apr-Jun | $XX,XXX | $XX,XXX | $XX,XXX |
| Q3 | Jul-Sep | $XX,XXX | $XX,XXX | $XX,XXX |
| Q4 | Oct-Dec | $XX,XXX | $XX,XXX | $XX,XXX |
Tab 8: Reconciliation Check
Validation tab that flags errors across the template.
| Check | Test | Status |
|---|---|---|
| All personnel effort = 100% | Every employee's effort percentages sum to 100% | PASS/FAIL |
| All non-personnel allocation = 100% | Every shared cost's allocation percentages sum to 100% | PASS/FAIL |
| Monthly totals balance | Total allocated = total actual for each month | PASS/FAIL |
| No double recovery | Indirect costs recovered ≤ indirect costs allocated per stream | PASS/FAIL |
| Cross-calendar consistency | Same months' data matches across FFY, SFY, and CY roll-ups | PASS/FAIL |
| Stream IDs valid | All stream IDs in Tabs 2-4 exist in Tab 1 | PASS/FAIL |
Implementation Notes
For Google Sheets
- Use data validation dropdowns for Stream IDs (reference Tab 1)
- Use QUERY functions for the fiscal period roll-ups
- Use conditional formatting for variance and burn rate indicators
- Protect formula cells to prevent accidental edits
For Excel
- Use named ranges for stream configuration
- Use SUMIFS for fiscal period aggregation
- Use Data Validation lists for framework and fiscal year type
- Consider pivot tables for ad hoc analysis
Getting Started
- Complete Tab 1 (Funding Streams) with all active grants/contracts
- Complete Tab 2 (Personnel) with all multi-funded staff and current effort %
- Complete Tab 3 (Non-Personnel) with all shared costs and allocation bases
- Tab 4 generates automatically — review the first month for accuracy
- Run Tab 8 reconciliation checks before relying on outputs
Monthly Maintenance
- Update effort percentages in Tab 2 when they change (new hire, role change, program shift)
- Update non-personnel allocations in Tab 3 when bases change (space reassignment, new contract)
- Review Tab 4 output monthly — the allocation schedule is the audit trail
- Check Tab 5 for underrecovery trends
- Review Tab 6 for burn rate alerts
Download this template to get started. For guidance on building your cost allocation methodology, see the Cost Allocation Methodology Guide.