Project budget and retainer tracker

What it's for

• Fixed-price, time-and-materials and retainer projects in one place.
• Spotting a project that will run over before it does.
• Retainers where unused hours roll over to the next month.

What's inside

Projects

Type, client, dates, budget in hours and money, and retainer hours a month with rollover.

Rates

An hourly rate for each person or role, used to value time.

Time log

Date, project, person or role, and hours. Rate and value fill in.

Summary

Hours and money used, % of budget, hours left, monthly burn and the forecast at completion, as of a date you choose.

Retainers

Twelve months per retainer: hours available (with rollover), used and left.

Preview

Worked example: a fixed-price project, as of 15 March 2027

The 110 hours are 90 developer hours at 160 and 20 designer hours at 140, worth 17,200. After 43 days (1.41 months) that is a burn of 77.9 hours a month. The 46 days left (1.51 months) at that rate add 117.7 hours, so it lands at 227.7 hours: 27.7 over budget. On the Retainers sheet, a 20-hour monthly retainer with rollover that used 15 hours in January has 25 hours in February.

How to use it

1. On Rates, list each person or role with an hourly rate.
2. On Projects, add each project: its type, start and end dates, and a budget in hours, money or both. For a retainer, add its hours a month and whether unused hours roll over.
3. Log time in the Time log against a project and a person or role.
4. On Summary, "As of" shows today. Type a date to see the position on another day.
5. On Retainers, type the first month and pick each retainer project.

The formulas, explained

Monthly burn

=C5/((MIN($B$2,end)-start+1)/30.4375)

Hours used ÷ months elapsed. A month is 30.4375 days, the average month (365.25 ÷ 12).

Forecast at completion

=C5+J5*MAX(0,end-$B$2)/30.4375

Hours used plus the monthly burn for the months left.

Retainer hours available

=$B5+IF($C5="Yes",MAX(0,D51),0)

This month's hours, plus last month's unused hours if they roll over. Overruns aren't carried forward.

Retainer hours used

=SUMIFS('Time log'!$D$4:$D$503,'Time log'!$B$4:$B$503,$A28,'Time log'!$A$4:$A$503,">="&D$4,'Time log'!$A$4:$A$503,"<"&EDATE(D$4,1))

Hours logged against the retainer from the 1st of the month to the end of it.

Every formula uses functions that Excel, Google Sheets and LibreOffice all have. There are no macros or scripts.

Questions