Data Collection Guide
How to gather the audit data for the Social Care Capacity & Demand Tool · AmiHC
Method and evidence base
The approach here is not invented, it mirrors established practice. It is the same method used in the Stoke-on-Trent hospital social care programme, which modelled demand by sub-team using a complexity framework with time-per-case validated through operational audit, and measured each team's casework time (the share of the working week actually spent on client-facing work, as low as 45% in the safeguarding team).
The national picture supports it. The Department for Education's 2025 review of workload and caseload management (Research in Practice) describes exactly this kind of capacity tool, a fixed monthly hours allowance with deductions for leave, sickness, training, meetings, supervision, travel and admin, and stresses that case counts alone hide complexity, so cases must be weighted. In community health the equivalent is the Cassandra Matrix, a capacity-and-demand model that captures the multidimensional complexity of caseloads from a period of activity recording. North Tyneside and other councils have piloted adult social care caseload-weighting tools on the same principles.
Two consistent cautions run through that evidence, and this tool is built around them. First, a tool supports manager judgement, it does not replace it, and the numbers open the conversation rather than close it. Second, the output is only as good as the audited inputs, which is why gathering this data properly matters.
Before you start
You will build two pictures: your capacity (how many client-contact hours the team can actually deliver in a month) and your demand (how many hours the incoming work requires). A few principles keep the data honest:
- Work team by team. Group both staff and pathways by sub-team (for example hospital discharge, community and reablement, safeguarding, reviews) so you can see which teams are stretched and which have slack. A service can look balanced in total while individual teams are badly over or under pressure, and that is where rebalancing decisions are made.
- Count the staff you actually have in post, not the funded establishment. Record vacancies separately so you can model them as a scenario.
- Use an averaging period of at least three months, ideally six to twelve, so a single busy or quiet month does not distort the picture. Note seasonal peaks (winter discharge pressure) separately.
- Where a figure has to be estimated, write down the assumption. A stated assumption can be challenged and improved; a hidden one cannot.
- Good enough beats perfect. Start with best estimates, run the tool, then refine the inputs that make the biggest difference to the answer.
Part 1 — Capacity data (staffing)
You need one row per role or group of like roles. For each, collect the following. The final contact % and contact hours are calculated by the tool, so you provide the raw inputs.
| Field | What it means | Where to find it / how to estimate |
|---|---|---|
| FTE | Whole-time equivalents actually in post for this role (1.0 = one full-time post). | Team roster or establishment list. Add part-time posts as fractions (e.g. two half-time = 1.0). Exclude vacant posts; note them separately. |
| Hours per week | Standard contracted weekly hours for the role. | Employment contract or HR system (commonly 37 for full-time; 30, 22.5 etc. for part-time patterns). |
| Leave days/yr | Annual leave entitlement plus public/bank holidays, in days. | HR leave policy. Remember entitlement rises with service, so use a representative figure for the role. Enter the combined total: commonly 25 to 33 days leave plus 8 bank holidays, so 33 to 41 in this field. The tool's own worked example uses 33. |
| Sick % | Average working time lost to sickness absence. | HR absence reports, taking the percentage of available time lost over the last 12 months. Use the team or service average if role-level data is unavailable. |
| L&D % | Learning, development, CPD and mandatory training as a share of working time. | Learning management system logs, the mandatory training matrix, appraisal records. Convert days per year to a percentage (e.g. 6 days ÷ 220 working days ≈ 3%). |
| Meetings / supervision % | Team meetings, supervision (received, and given by seniors), duty handovers, multi-disciplinary team meetings, in short any time not spent with clients. | Recurring calendar entries and the rota, checked against the two-week diary below. |
| Admin % | Case recording, referral processing, emails, reports and other desk work that is not client contact. | Best captured by the two-week diary below, because this is usually underestimated. |
| Travel % | Travel between visits and to and from the office during work time. | Two-week diary, mileage claims, or an estimate from the geography of the patch. Often the single largest non-contact cost in community teams. |
The quickest reliable method: a two-week time diary
The sickness and leave figures come from systems, but the meetings, admin, travel and contact percentages are best measured directly. Ask a representative sample of staff (or the whole team) to record their time for two typical weeks in blocks of 15 minutes against a small set of categories. The proportion of time in each category gives you the percentages directly, and the leftover client-contact share is a reality check on the calculated contact %.
Client contactRecording / adminTravelMeetings / supervisionTrainingOther
Part 2 — Demand data (clients by pathway)
Decide your pathways first, meaning the distinct streams of work the team handles (for example hospital discharge, community reablement, safeguarding, reviews). Then collect the following for each.
| Field | What it means | Where to find it / how to estimate |
|---|---|---|
| Intake per month | New cases opened or allocated into this pathway each month. | Case management system report on cases allocated per month. Count allocations, not raw referrals, and use a 6–12 month average. |
| Severity split % | The proportion of intake that is low, moderate and high demand (must total 100%). | Apply the banding definitions below to a sample of recent cases, or use the team's informed judgement as a starting point. |
| Hours per case per month | Ongoing client-facing and case-related hours a single open case consumes in a month, for each severity band. | Derive from the case sampling sheet below, or tag the two-week diary by case. Practitioner estimates are a fair starting point to refine later. |
| Case length (months) | Average time a case stays open before closure, for each severity band. | Case management system: open date to close date on a sample of recently closed cases. This is what turns monthly intake into an active caseload. |
Defining the severity bands
Consistency matters more than precision, so agree the bands with the team and apply them the same way to every case. A workable starting point:
| Band | Typical characteristics |
|---|---|
| Low | Single, stable need; straightforward assessment or signposting; few contacts; short duration; one agency or none. |
| Moderate | Multiple needs requiring some coordination; several contacts over weeks; one or two other agencies involved; moderate duration. |
| High | Complex or fluctuating needs; multiple agencies; safeguarding or high risk; frequent contact; long duration and active review. |
Deriving hours per case and case length from a sample
Take a sample of recently closed cases in each pathway, and around 10 to 20 per severity band is enough to start. For each, record the severity, the open and close dates (giving the duration in months), and the total recorded hours across the life of the case. Then:
- Case length = average duration in months for the band.
- Hours per case per month = total case hours ÷ duration in months, averaged across the sample.
Capture sheet A — Staffing
One row per role or group of like roles. Percentages are of working time. Contact % is calculated by the tool.
| Team | Role | FTE | Hrs/wk | Leave days/yr | Sick % | L&D % | Mtgs/superv % | Admin % | Travel % |
|---|---|---|---|---|---|---|---|---|---|
Vacancies to model separately: ______________________________________________ Contingency buffer to apply: ______ %
Capture sheet B — Two-week time diary tally
Record time in 15-minute blocks. At the end of two weeks, total each category and convert to a percentage of the total to get the meetings, admin and travel figures.
| Category | Week 1 hours | Week 2 hours | Total hours | % of total |
|---|---|---|---|---|
| Client contact (face-to-face, phone, virtual) | ||||
| Recording / admin | ||||
| Travel | ||||
| Meetings / supervision | ||||
| Training / L&D | ||||
| Other | ||||
| Total | 100% |
Capture sheet C — Demand by pathway
One row per pathway. Severity split must total 100%. Hours are per open case per month; case length is the average months a case stays open.
| Team | Pathway | Intake/mo | Severity split % | Hrs/case/mo | Case length (mo) | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Low | Mod | High | Low | Mod | High | Low | Mod | High | |||
Capture sheet D — Case sampling
Use to derive hours per case per month and case length. Duration = close date − open date, in months. Hours/month = total hours ÷ duration.
| Case ref | Pathway | Severity | Open date | Close date | Duration (mo) | Total hours | Hours/mo |
|---|---|---|---|---|---|---|---|
Common pitfalls
- Treating contracted hours as available hours, when the gap is usually more than half once leave, sickness, admin, travel, meetings and training come out.
- Using funded establishment instead of staff actually in post, which overstates capacity.
- Counting referrals rather than allocated cases, which overstates intake.
- Forgetting that a case opened this month keeps consuming hours in following months, which is why case length matters.
- Sampling too few cases, or only the memorable difficult ones, which skews the severity mix.
- Ignoring seasonality, so capture a winter figure alongside the average if discharge demand spikes.