verification / datasets / 07-adult-social-care-finance / VARIABLES.md

Variables — Adult Social Care Finance Report (ASCFR), England, FY 2024-25

All variables are per council (CASSR), keyed on GEOGRAPHY_CODE (ONS code, e.g. E06000057) / DH_GEOGRAPHY_NAME. 153 councils.

Source file (verified): data/data-pack-2024-25/adult-social-care-finance-report-2024-2025-csv-data-tables.csv (509,352 rows, single year FY_ENDING = 2025). It is a cell-level long file: one row per statistic, value in ITEMVALUE. Every filter below was checked to return exactly 153 rows (one per council) unless stated otherwise.

Conventions (verified against the metadata ODS in the same folder):

Always filter GEOGRAPHY_LEVEL = 'Local Authority' (the file also contains Region and England rows).
99 in a dimension column means "total across that dimension"; '' (blank) means "not applicable".
Suppressed/missing cells are [x] — treat as NA, never zero.
Monetary ITEMVALUEs are £ thousands; UNIT COST values are £ per unit (per week for the cuts listed here); activity denominators are person-weeks.

1. Extracted variables

asc_gross_current_expenditure

File/filter: main CSV; GEOGRAPHY_LEVEL='Local Authority' AND DATA_Level='Gross Current Expenditure' AND CareType='99' AND AgeBand='99' AND PrimarySupportReason='99' AND SupportSetting='99'.
Unit: £000s.
Interpretation: total gross current expenditure on adult social care for the year — the headline "how much the council spends on ASC before deducting income". (Verified: Northumberland = 186,775 → £186.8m.)

asc_net_current_expenditure

File/filter: as above but DATA_Level='Net Current Expenditure'. Note: this grand-total row appears twice per council (once under DimensionGroup='Expenditure', once under 'Additional Data') with identical values — deduplicate or add DimensionGroup='Expenditure'.
Unit: £000s.
Interpretation: gross spend minus all income (client contributions, NHS, joint arrangements, other) — the cost falling on the council's own budget.

asc_income_client_contributions

File/filter: GEOGRAPHY_LEVEL='Local Authority' AND DATA_Level='TOTAL' AND FinanceType='Income' AND FinanceDescription='Client Contributions' AND CareType='99' AND AgeBand='99' AND PrimarySupportReason='99' AND SupportSetting='99' AND ComparativeTotals=''.
Unit: £000s.
Interpretation: income the council collects from care users themselves. Sibling rows with the same filter but FinanceDescription in ('Income from NHS', 'Joint Arrangements', 'Other Income', '99' = total income) extract the other income streams the same way.

asc_gce_long_term_care (and cuts by setting/age)

File/filter: DATA_Level='Gross Current Expenditure' AND CareType='Long Term Care'; the file carries every combination of SupportSetting in {Residential, Nursing, Community: Home Care, Community: Direct Payments, Community: Supported Living, Community: Other Long Term Care, Supported Accommodation}, AgeBand in {18 to 64, 65 and Over}, and PrimarySupportReason (or '99' for totals across it). Age-band totals for LTC as a whole: CareType='Long Term Care' AND SupportSetting='99' AND AgeBand='18 to 64'|'65 and Over' AND PrimarySupportReason='99'. (Setting-level rows exist only per age band — sum the two age bands to get a per-setting total.)
Unit: £000s.
Interpretation: gross spend on long-term care broken down by care setting, age band and primary support reason — the raw material for care-mix and unit-cost comparisons.

asc_unit_cost_res_nursing_care

File/filter: DATA_Level='UNIT COST' AND ValueType='UNIT COST' AND CareType='Long Term Care' AND SupportSetting='Residential and Nursing' AND AgeBand='' AND ActivityProvision='All'.
Unit: £ per person per week.
Interpretation: average weekly cost the council pays for a care-home placement (residential + nursing combined, all ages). (Verified: Northumberland = £1,009.14/wk; the companion ValueType='Numerator' row is spend in £000s and 'Denominator' is person-weeks — 97,690 / 96,805 × 1000.)

asc_unit_cost_by_setting_age_psr

File/filter: same as above with SupportSetting='Residential' or 'Nursing', AgeBand='18 to 64'|'65 and Over', any PrimarySupportReason, ActivityProvision in {All, and for Residential also In House / External}; plus CareType='Short Term Care' AND Purpose='To Maximise Independence' for reablement unit costs. 61 published unit-cost cuts in total, each 153 rows.
Unit: £ per person per week.
Interpretation: detailed unit costs, including the in-house vs externally-commissioned split for residential care — heavily suppressed ([x]) for small groups (e.g. nursing for sensory support), so expect gaps.

asc_activity_person_weeks

File/filter: DimensionGroup='Activity' AND DATA_Level='TOTAL', by CareType (Long Term Care with SupportSetting Residential/Nursing, or Short Term Care), AgeBand, PrimarySupportReason.
Unit: person-weeks of care provided in-year.
Interpretation: activity volumes matching the unit-cost denominators — how much care was actually delivered, not just spent.

2. Constructed variables

Population is not available in this dataset or verified siblings, so no per-resident variables are constructed; the natural size-normaliser available here is the council's own total service spend (sibling 01).

asc_share_of_service_spend

Formula: asc_net_current_expenditure / RSX_totsx_net_cur_exp (units already match: both £000s).
Inputs: asc_net_current_expenditure (above); RSX_totsx_net_cur_exp and, for cross-checking, RSX_asc_net_cur_exp from sibling ../01-revenue-outturn/data/Revenue_Outturn_time_series_data_v3.2.csv (year_ending=202503, join on ONS_code = GEOGRAPHY_CODE) — both columns verified present.
Unit: share (0–1).
Interpretation: how much of the council's total net service expenditure adult social care consumes. Use this to ring-fence ASC before whole-council efficiency comparisons, per the README caveat that ASC otherwise swamps everything.

asc_income_recovery_rate

Formula: asc_income_client_contributions / asc_gross_current_expenditure.
Inputs: both extracted above from the main CSV.
Unit: share (0–1).
Interpretation: proportion of gross care spend recovered from users — a charging-policy/efficiency signal (heavily shaped by local deprivation and care mix; compare within similar councils).

asc_share_care_home_vs_community

Formula: GCE(LTC, Residential + Nursing, both age bands) / GCE(LTC, all settings, both age bands); complement = community-based share (Home Care + Direct Payments + Supported Living + Other + Supported Accommodation).
Inputs: asc_gce_long_term_care setting-level rows (sum the two AgeBand rows per setting; LTC total from the SupportSetting='99' age-band rows).
Unit: share (0–1).
Interpretation: balance of long-term care delivered in care homes vs in people's own homes/community — a key strategy variable (community-weighted models are usually cheaper per person and preferred by users).

asc_care_home_unit_cost_premium

Formula: asc_unit_cost_res_nursing_care(council) / asc_unit_cost_res_nursing_care(England) where the England value uses the same filter with GEOGRAPHY_LEVEL='England'.
Inputs: unit-cost rows above (England row verified present in the same CSV).
Unit: ratio (1.0 = national average).
Interpretation: whether the council pays above or below the national average for a week of care-home care, before mix adjustment; refine with the age-band/PSR-level unit costs to control for case mix.

asc_inhouse_vs_external_residential_cost_gap

Formula: UNIT COST(Residential, In House) − UNIT COST(Residential, External) at matched AgeBand/PrimarySupportReason.
Inputs: asc_unit_cost_by_setting_age_psr (ActivityProvision='In House' vs 'External' cuts, both verified present, 153 rows each per cut).
Unit: £ per person per week.
Interpretation: how much more (typically) the council's own residential provision costs than externally commissioned care — an in-sourcing efficiency signal, subject to suppression for councils with little in-house provision.

3. Variable file index

One file per variable in variables/: