BaseBrainers | Airtable Resource and Capacity Planning: Allocations, Timeline Views, and Utilisation Dashboards

Most teams that ask us to "fix the project tracker" do not have a project tracking problem. They have a capacity problem. Projects are visible, deadlines are visible, but nobody can answer the two questions that actually decide whether a quarter goes well: who is free in three weeks? and are we about to sell work we cannot staff?

Spreadsheet capacity models answer those questions once, on the day they are built, and then rot. This tutorial walks through building resource and capacity planning directly in Airtable, so the answer updates itself every time a project moves.

The mistake almost everyone makes first

The first instinct is to add hour fields to the Projects table: Designer Hours, Developer Hours, PM Hours. It feels tidy for about two weeks. Then someone asks for hours per week, or a project needs two developers, or one person is 40% on a project for a month and 100% for the final sprint, and the schema has nowhere to put that.

Hours are not an attribute of a project. They are an attribute of the relationship between a person, a project, and a period of time. That relationship needs its own table.

Step 1 — The four-table schema

Build these tables in a dedicated base (resource planning almost always deserves its own base, synced to delivery bases rather than crammed into them):

People

Name (single line text, primary)
Role (single select: Designer, Airtable Developer, Solutions Architect, PM, QA)
Weekly Capacity Hours (number — contracted hours, e.g. 40 for staff, 20 for a part-time contractor)
Employment Type (single select: Staff, Contractor, Subcontractor)
Status (single select: Active, On leave, Offboarded)
Cost Rate and Bill Rate (currency — optional, but it makes the margin views later possible)

Projects

Project Name (primary)
Client (link to Clients, if you have one)
Stage (single select: Prospect, Committed, In Delivery, On Hold, Complete)
Confidence % (number — for anything at Prospect stage)
Start Date / Target End Date (date)
Sold Hours (number, from the statement of work)

Allocations — the table that does the real work

Allocation ID (formula primary, e.g. Person & " · " & Project & " · " & DATETIME_FORMAT(Week Starting, "DD MMM"))
Person (link to People)
Project (link to Projects)
Week Starting (date — always store the Monday)
Planned Hours (number)
Actual Hours (number, optional, filled by sync or import from your time tracker)

Weeks

Week Starting (date, primary)
Week Label (formula: DATETIME_FORMAT({Week Starting}, "[W]W · YYYY"))

The Weeks table looks like bureaucracy and is anything but. It gives you a record per calendar week to roll allocations up against, which is how you get a company-wide capacity view that shows empty weeks, not just weeks that happen to contain bookings. Generate two years of Mondays once via a script or a pasted column and forget about it.

Link Allocations to Weeks as well as to People and Projects. One extra link field buys you the whole dashboard.

Step 2 — Capacity maths with rollups

On People, add:

Planned Hours (All) — rollup of Planned Hours from Allocations, SUM(values)
Planned Hours (This Week) — rollup of Planned Hours from Allocations with a filter on the linked records where Week Starting is within this week
Utilisation This Week — formula:

IF(
  {Weekly Capacity Hours} = 0,
  BLANK(),
  ROUND({Planned Hours (This Week)} / {Weekly Capacity Hours}, 2)
)

Capacity Flag — formula, and this is the field people actually look at:

IF({Utilisation This Week} = BLANK(), "⚪ No bookings",
  IF({Utilisation This Week} > 1.1, "🔴 Overbooked",
    IF({Utilisation This Week} >= 0.85, "🟢 Healthy",
      IF({Utilisation This Week} >= 0.5, "🟡 Part-booked", "🔵 Bench")
    )
  )
)

On Weeks, add rollups for Total Planned Hours (SUM of Planned Hours from Allocations) and, if you want a true company picture, a Total Capacity number you maintain per week or derive with a script from active People. Then:

ROUND({Total Planned Hours} / {Total Capacity}, 2)

That single column, charted, is your pipeline-versus-delivery conversation for the next quarter.

Weighting the pipeline

Unsold work should not compete with committed work at full weight. On Allocations, pull Stage and Confidence % from the project via lookups and add:

IF(
  {Project Stage} = "Prospect",
  {Planned Hours} * ({Project Confidence} / 100),
  {Planned Hours}
)

Call it Weighted Hours and roll that up alongside the raw number. Your delivery lead plans on committed hours; your MD plans on weighted hours. Same base, no arguing about which spreadsheet is right.

Step 3 — Views that get opened

A capacity model nobody looks at is a capacity model that is wrong. Build the views around the question each person is asking.

Allocations → Timeline view, grouped by Person, dated on Week Starting. This is the "who is free" view. Colour records by project stage so provisional bookings are visually softer than committed ones.
Projects → Gantt view, Start Date to Target End Date, grouped by Client. This is the delivery calendar. On plans where Gantt is not available, a Timeline view grouped by project gets you 90% of the way.
People → Grid view, filtered to Status = Active, sorted by Utilisation This Week descending, showing Capacity Flag. This is the Monday stand-up view.
People → Grid view filtered to Capacity Flag is "🔵 Bench" or "⚪ No bookings". This is the sales-team view, and it is the one that pays for the build.
Allocations → Grid view filtered to Week Starting is within the next 4 weeks, grouped by Person, with a summary bar showing SUM of Planned Hours. Fast sanity check before anyone commits to a new start date.

Step 4 — Interface Designer for the humans

Delivery leads should not be editing a grid. Build one Interface page with:

A record picker list of active People on the left, filtered by role.
A timeline element of that person's allocations for the next 12 weeks.
Number elements for utilisation this week, next week, and the four-week average.
An editable list of the selected person's allocations so hours can be nudged in place.

Add a second page, Project Staffing, that takes a project and shows sold hours versus planned hours versus actual hours as three number elements, with a bar chart by week. A project manager who can see "sold 120, planned 168" in one glance will catch scope creep two months before your invoicing does.

Give sales a third, read-only page: bench list, next four weeks, weighted pipeline chart. Read-only access to the right slice is what stops people exporting their own private copy.

Step 5 — Automations that keep it honest

Three automations cover most of the value:

Weekly overbooking alert. Trigger At scheduled time, Monday 08:00. Find records in People where Capacity Flag = "🔴 Overbooked", and if the list is non-empty, post to Slack or email the delivery lead with names and hours. Skip the message entirely when the list is empty — silent weeks train people to trust the noisy ones.
Allocation scaffolding. Trigger When record matches conditions on Projects where Stage becomes "Committed". Run a script that creates one draft Allocation per assigned person per week between start and end date, splitting sold hours evenly. Planners adjust from a sensible baseline instead of a blank grid. Batch your createRecordsAsync calls in chunks of 50.
Leave blocks capacity. If you track time off in the same base, create Allocations against an internal "Leave" project when a leave request is approved. Holiday that does not consume capacity is the most common reason these models quietly lie.

Watch the record count on the Allocations table as you scale: one person × 52 weeks × a few projects adds up quickly across a 40-person team. Archive allocations older than a year to a separate base on a schedule rather than letting the table grow without limit, and keep rollup filters tight so views stay quick.

Step 6 — Closing the loop with actuals

Planned hours alone will drift. Pull actuals in from wherever time is recorded — Harvest, Toggl, Clockify, or a time entry table in Airtable itself — and write them to Actual Hours on the matching allocation, keyed on person plus project plus week. Then add:

{Actual Hours} - {Planned Hours}

as Variance, and roll it up per project and per person. Two months of variance data tells you more about how your team actually estimates than any retrospective will, and it makes the next statement of work considerably harder to get wrong.

Common failure modes

Daily granularity. Resist it. Weekly buckets are accurate enough to plan with and cheap enough to maintain. Daily allocations quadruple your record count and get abandoned within a month.
One base for everything. Resource planning is cross-project by nature. Keep it in its own base and sync in what you need; do not bolt it onto a single client's delivery base.
No named owner. Someone has to own Monday's five minutes of tidying. Without that, the model is stale by week three and everybody goes back to guessing.
Fractional people. Never model "0.5 of a developer". Model hours. Percentages hide part-time contracts, leave, and internal work.

Where this usually goes next

Once allocations, bill rates, and actuals live in the same base, project margin and forecast revenue are a rollup away, and the finance conversation stops being a monthly reconstruction exercise. That is normally the point where teams want the model hardened: proper permissions, synced delivery bases, tidy interfaces, and automations that do not fall over.

If you would rather not build this from scratch, that is precisely the sort of thing we do. Tell us how your team plans work today and we will map it to a schema you can live with — or review the Airtable business operations and reporting and analytics services first.