Agency Utilisation Rate: Track Billable Hours and Spot Overload With AI

Calculate agency utilisation rate as billable hours divided by available working hours for the same period, multiplied by 100. Export timesheets, classify each entry consistently, and calculate the ratio for each person before rolling up an agency figure. Ask AI to flag repeated high ratios, overtime and missing non-billable time; a manager must investigate each flag before changing assignments.

A single agency percentage can conceal a person delivering too many client hours while another has spare capacity. The denominator also matters: contracted hours, hours after leave, and hours after internal meetings answer different questions. Choose one definition, show it beside every figure, and keep the raw entries available for review.

Follow me on Instagram@sagnikteaches

Decide what an available hour means

Start with a week because managers can act on next week's work. Available hours are the hours someone could reasonably work after approved leave and holidays, before assigning client or internal activities. If a full-time employee is contracted for 40 hours and has eight hours of leave, the weekly denominator is 32. Do not remove routine meetings or training from that denominator merely to inflate the utilisation figure: they are real calls on capacity. Show them as categories in the numerator side of the analysis.

Connect on LinkedInSagnik Bhattacharya

Billable time means time you can legitimately charge, or time consumed under an agreed client fee that your practice treats as billable. Record a separate client-work category for unbilled scope changes, write-offs, speculative pitch work and unpaid rework. On a fixed-fee project, client delivery time may be economically relevant even if there is no hourly invoice. Agree on the classification with finance before counting it. Otherwise a manager can apparently improve utilisation by changing labels while the workload and margin stay the same.

Subscribe on YouTube@sagnikteaches

Keep three views rather than forcing one ratio to answer everything. Billable utilisation is billable hours divided by available hours. Client load adds non-billable client work to show delivery pressure. Total load includes internal duties, pitch work and training. A person with 65% billable utilisation and 100% total load has a different problem from someone with 65% billable utilisation and 70% total load. AI should distinguish those cases in its flags.

For a practical classification, use the same categories in the time tool and analysis sheet: client billable, client non-billable, new-business pitch, internal operations, development, absence and unclassified. If your existing codes differ, create a small mapping table. A code called “client admin” might mean routine billable project management on one account and unbillable invoice chasing on another. Resolve that ambiguity with the account lead instead of guessing from the label.

Export one clean row per time entry

Ask for a CSV export with entry date, person ID, role, project ID, task code, duration in hours, billable status and approval status. Add the source entry ID if the system provides one. The ID helps you find duplicates and trace a correction to the original record. Keep a separate capacity table with person ID, week, contracted hours and approved absence hours. Avoid putting personal medical reasons, performance notes or client confidential descriptions into an AI prompt; coded categories and anonymised IDs are enough for the calculation.

Before calculating anything, check that durations are positive, dates are within the reporting window, and each person has a matching capacity record. Group by person and week, then compare total logged hours with available hours. A 44-hour total against 32 available hours might be true overtime, a duplicate import or an entry dated to the wrong week. Mark it for review. Do not silently cap it at 32, because that would hide the very overload you want to find.

Here is an illustrative export defect: entry 1847 shows 3.5 hours for a design review, and entry 1920 repeats the same person, date, project and duration after a mobile sync. If the second row is a genuine second session, the task description or source time stamp should support it. If it is a duplicate, remove it from the analysis while preserving an exception log. A three-hour error can move a 32-hour week by more than nine percentage points.

Another common issue is a missing Friday. A strategist records 27 hours for a 40-hour week, while calendar and project updates show a client workshop on Friday. Treat the gap as missing time, not spare capacity. Ask the person to correct the source system. AI can produce a list of absent days and unusually low totals, but it cannot know which missing activity was billable. For a broader collection decision, see ways to collect time without traditional timesheets.

Calculate the agency view from people, not averages

Suppose an illustrative six-person design agency has a 40-hour contracted week. One person has eight hours of leave, so team available hours are 232 rather than 240. Approved billable entries total 158 hours. Team utilisation is 158 ÷ 232 × 100, or 68.1%. That is a useful capacity signal, but it says nothing about whether the work is distributed fairly. Preserve the person rows behind the headline.

The six billable figures sum to 158, and the denominators sum to 232. Do not average the six percentages: that would give the 32-hour week the same weight as each 40-hour week. Use the sum of billable hours divided by the sum of available hours for the agency figure. Show each person's ratio separately. Total logged time above availability for A and B warrants a conversation, especially because A also has five hours of internal work in the same week.

Let spreadsheet formulas produce these figures before any AI sees them. With time entries in an Excel table named Time (columns Person, Week, Hours, Class) and capacity in a table named Cap (columns Person, Week, Available), a summary sheet with one row per person-week, person in column A and week in column B, needs only a few SUMIFS calls. The agency figure divides the two column totals; it never averages the rate column.

C2 Billable hours   =SUMIFS(Time[Hours],Time[Person],A2,Time[Week],B2,Time[Class],"Client billable")
D2 Available hours  =SUMIFS(Cap[Available],Cap[Person],A2,Cap[Week],B2)
E2 Billable rate    =IF(D2=0,"",C2/D2)
F2 Total load       =IF(D2=0,"",SUMIFS(Time[Hours],Time[Person],A2,Time[Week],B2)/D2)
Agency rate         =SUM(C2:C7)/SUM(D2:D7)

Format columns E and F as percentages. The IF guard stops a person on a fully absent week from showing a divide-by-zero error, which an AI summary could misread as zero utilisation. For designer A, F2 returns 45 ÷ 40, or 112.5%, beside a billable rate of 92.5%: the pair says more than either number alone. If the agency rate in your sheet differs from the one in the time-tracking tool, check the class mapping first, because one code mapped to the wrong class is enough to move it.

In this illustration, A is at 92.5% billable and has 45 logged hours. The person may have chosen to work longer for a deadline, or a task may be miscoded. Either way, “92.5%” is an observation, not a diagnosis. B has 41 logged hours and 85% billable; the one-hour excess could be a normal fluctuation. D has a low billable rate because account leadership includes seven hours of unpaid client service and ten hours of internal work. Ranking D against designers would punish a role for doing its actual job.

For a second illustrative calculation, a copywriter has 28 billable hours in a 35-hour available week. The rate is 80%. If six of the remaining seven hours are internal review and only one is free, the person cannot accept a 10-hour urgent project. The weekly ratio and the next two weeks' bookings must be read together. Use a timesheet-based work-in-progress view when you also need to track unfinished client work.

Ask AI to return reasons you can verify

Use an approved business AI environment or run the analysis in a controlled spreadsheet. Supply a sanitised table containing person IDs, week, role, available hours, billable hours, other client hours, internal hours, approved leave and future booked hours. Do the arithmetic in spreadsheet formulas first. AI's job is to inspect patterns and write a review queue, with row references, rather than become the sole source of numbers. A manager should be able to reproduce every reported percentage with a calculator.

Review this anonymised weekly agency capacity table. Do not change any figures.
For each person, show billable utilisation = billable / available * 100.
Flag: two consecutive weeks over the role's review threshold; total
logged hours above available hours; future booked hours above remaining
capacity; and missing or unclassified time. Cite person ID and week.
Separate arithmetic exceptions from possible workload risks. Do not
recommend a staffing or performance decision. Return questions for a
manager to check with the person and project lead.

An illustrative AI output might say: “Designer A, weeks 21 and 22: billable ratios 91% and 93%; logged time exceeds available time by four and five hours; ask whether the campaign deadline is temporary and whether quality review was deferred.” That is useful because it names the rows and asks a question. If the output says “A is underperforming on time management,” remove that judgement. The table cannot establish cause. Check its arithmetic and inspect the task codes before forwarding any note.

Ask for a separate anomaly list. For example: “Person C, week 20, 40 available hours but 27 recorded; missing Friday entry; do not treat 13 hours as capacity until confirmed.” This stops the tool presenting a false spare-capacity recommendation. Another useful flag is “Project P17 has nine client non-billable hours across three people this week, compared with two previously; confirm whether a scope change or rework occurred.” That finding belongs in project margin review, not an employee scorecard.

Do not upload raw timesheet notes if they contain staff matters, sickness details, or sensitive client plans. Substitute person and project IDs and retain the mapping in your own system. If a tool will process personal information, check its access, retention and training settings under your practice's policy. The same principle applies to anonymising client data before AI analysis: remove what is unnecessary while preserving the fields needed to answer the question.

Set review thresholds by role and duration

A target is a planning assumption, not a universal benchmark. Start by looking at eight to twelve recent weeks of complete data, excluding weeks distorted by leave or major events. For each role, compare billable rate, total load, overtime, delivery quality, project margin and backlog. A designer who routinely handles production may have a different billable expectation from a practice lead responsible for sales and supervision. Record who approved the target and when it will be reviewed.

Use two thresholds: a planning band and an overload review trigger. As an illustrative policy, a design role might plan around 70% to 80% billable utilisation, then trigger a check after two consecutive weeks above 85% billable or any week with more than five hours logged beyond availability. These are example rules for one agency, not industry standards. A practice lead might have a lower billable band because management duties must fit somewhere. Adjust the numbers to your actual staffing model, contract commitments and quality signals.

Suppose a four-person account team reports 82%, 81%, 79% and 77% billable utilisation for two weeks running. Those ratios alone do not establish overload. If the same people also have 18 hours of late-night work, a high revision rate and 24 hours of booked client work next week before internal duties, the combined evidence is stronger. The manager can move a review, negotiate the deadline or assign a suitably skilled colleague. AI can assemble the pattern; it should not decide whose work is reassigned.

Watch the opposite mistake: an 87% ratio can arise because someone recorded only 30 available hours after a day of leave and worked 26 billable hours. That may be sustainable. A 66% ratio can conceal overload if the remaining time is consumed by unpaid client coordination and pitches. Display billable rate, total load and next-week bookings together. Where excessive unpaid client work is the issue, examine scope and the relationship between utilisation and project pricing rather than merely demanding more billable entries.

Turn the flag into a workload conversation

Give each flagged person a short review card: week, data quality status, billable rate, total logged hours, booked hours for the next fortnight, project deadlines, and the manager's open questions. Include the source entry IDs for unusual items. A person should be able to correct a timesheet mistake without the error lingering as a performance record. Record the resolution separately: data corrected, temporary deadline, scope issue, capacity gap, or no action required.

For example, an illustrative card for designer B reads: “Week 22: 34 billable of 40 available; 41 total logged. Next week: 38 booked client hours plus four planned internal review hours. Check whether the client sign-off can move and whether another designer can take the two-hour asset export.” After a conversation, the lead learns that 10 booked hours depend on an approval that has slipped. The actionable fix is to refresh the schedule, not hire from a single alarming percentage.

Another card may show a new joiner at 45% billable for three weeks. Before calling it spare capacity, check onboarding, shadowing and supervision commitments. A third card may show an account lead at 55% billable but 104% total load because client meetings are not charged. The intervention is to review contract scope and meeting cadence. In both cases the person-level view prevents a crude utilisation league table from driving the wrong response.

Keep the human check respectful. Ask what has changed, what work is invisible, what could be delayed, and whether a project lead has promised work that is absent from the schedule. Discuss patterns privately rather than distributing named rankings. Do not use AI-generated labels such as “low performer” or “flight risk”. The output is an operational queue. If you need to change policy or make employment decisions, involve the appropriate qualified adviser and your normal people process.

Check the number after making a change

Refresh the export weekly with the same reporting cut-off. Recalculate from approved rows and keep the prior snapshot so late edits are visible. Compare the agency total with the finance or time-system report, and sample a few people by opening their underlying entries. The calculation passes if totals tie, all people have capacity records, unclassified hours have owners, and managers can explain every overload flag. A dashboard with a pretty trend but unresolved missing entries is not yet trustworthy.

For the illustrative agency, moving six billable hours from A to a suitably skilled colleague might reduce A from 37 to 31 billable hours, or 77.5% of 40 available. It only helps if the colleague can genuinely do the work and the transfer does not add six hours of handover. Measure the next two weeks: total load, delivery dates, revisions, margin and the affected people's accounts of the workload. An improved utilisation chart with worse client outcomes is not a successful intervention.

Separate capacity planning from retrospective billing as well. A time entry may later be written off, and a fixed-fee project may still require substantial delivery time. Keep the original work category, the approved billable classification and any write-off as distinct fields. If the discrepancy becomes a repeated project pattern, review the quote or scope. For a wider agency adoption plan, an AI rollout plan for a marketing agency covers ownership and staff habits beyond this specific utilisation calculation.

Keep exceptions visible when the data changes

A late timesheet correction can change a prior week's ratio, so retain the export date and label a week provisional until entries are approved. In an illustrative month-end review, designer E adds four missing internal hours to week 22. Billable utilisation stays at 68.8% because neither the billable hours nor the available hours changed, yet total logged time rises from 31 to 35 hours, three above the 32 available. A dashboard that displays only the ratio would miss the more important change. Show a revision note beside the week and ask the manager to reopen the workload conversation if the new total changes its conclusion.

Check whether overtime is recorded as extra available capacity in your source tool. If it is, decide whether the management view should use contracted capacity or the expanded denominator. An illustrative employee with 36 billable hours and 44 total hours has 90% billable utilisation against a 40-hour contract. Against 44 actual hours, the ratio is about 81.8%. Both calculations are arithmetically correct but answer different questions. For overload, the first tells you the client load against the sustainable plan; the second tells you the mix of hours worked. Label each view explicitly and never switch denominators mid-series without an annotation.

Finally, use a short acceptance rule for the AI review queue: each flag must cite a person-week, a visible input value, an explicit rule and a proposed human question. Reject a flag that gives only a red label or asserts a cause. If the team has too many flags to discuss, tighten the rules around persistence and booked work rather than hiding people from the report. The purpose is a manageable set of conversations that protects delivery and staff capacity, not an automated judgement of individuals.

Questions about utilisation and workload

Should annual leave be counted as available hours?

Decide your denominator before comparing people. For a weekly capacity view, subtract approved leave and public holidays from contracted hours, and label the result available hours. Keep a separate view based on contracted hours if you need a consistent longer-term trend. Never mix the two denominators in one ranking.

Does a high utilisation rate prove someone is overloaded?

No. A high ratio is a prompt to inspect the work, time pattern and near-term commitments. Some logged hours may reflect corrections or a short deadline. Ask the person and project lead whether the schedule is sustainable, whether non-billable duties were postponed, and whether quality or recovery time is being squeezed.

Can AI set the right target for every agency role?

AI can calculate a proposed range from your historical data and explain its assumptions, but managers set targets. A delivery specialist, account lead and practice manager have different duties. Use the proposed target alongside margin, service quality, leave and development needs, then review it with each role owner.

Further reads

• AI Content Workflow for Agencies: From Brief to Approved Draft — See how approval work sits within agency delivery.
• AI Payroll Checks: Catch Timesheet Errors Before Payday — Check payroll impacts of inaccurate time entries.
• Which Agency Tasks AI Handles Well, and Which It Doesn't — Separate suitable agency automation from judgement-heavy work.
• Turning Hours Saved by AI Into Advisory Work Clients Pay For — Consider how released capacity can support higher-value work.
• Marketing Agency Pricing After AI: Retainers, Packages and Deliverables — Reprice agency retainers line by line instead of cutting everything: a worked $4,000 retainer, three pricing models and how to answer an AI discount request.
• Timesheet Software for Agencies: Budgets, Billable Hours and AI — Four timesheet tools compared on project budgets, burn alerts, billable-hour reports and AI, with a worked fixed-fee project and a weekly AI budget digest.
• Content Automation With AI: A Weekly Pipeline for a Small Agency — A Monday-to-Friday content pipeline for a small agency: briefs, AI drafts, human edit, client approval gates and scheduling, with a quality checklist.
• Web Design Change Order Template: Handle Scope Creep With AI — A copy-ready change order for web studios: what to include, how to price extras, the approval line, and prompts that draft it from a client's email.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: agency timesheet and capacity method explained as an illustrative management workflow; no vendor features or benchmark targets asserted.