Hour bank tracker template for software work

An hour bank tracker is a sheet where each row is a month: how many hours were in the bank at its start, how many were bought, how many were used and how many are left. A month's closing balance is the next month's opening balance. The file below the text calculates this on its own; here each part is explained, along with how to fill it in.

The structure: six columns

Month;
Opening balance: what is left from the previous month;
Hours purchased: a block the client bought that month, if any;
Hours used: the total hours logged against tasks that month;
Closing balance: opening plus purchased minus used;
Notes: anything that explains an unusual number.

Two columns calculate themselves, and three are filled by hand: the month, hours purchased and hours used. The notes are not decoration. A year from now they are the only place that says why a given month looks the way it does.

If the bank has an expiry date, write it in the notes of the month it was bought, so the date does not disappear inside the agreement.

The calculation, and why the balance carries over

There is one formula: closing balance equals opening balance, plus hours purchased, minus hours used. In the file, the opening balance of every month from the second one on is a formula pointing to the previous month's closing balance. So nobody can "adjust" the start of a month without the change showing in every row after it.

Only in the first month is the opening balance typed by hand: the balance when you started tracking, or zero for a new bank.

If a past month's number really has to be corrected, correct it in its own row and add a note. Every row after it updates by itself, and the note explains why today's balance differs from what was sent back then.

A negative balance: an overrun to report

When more hours were used than the bank held, the closing balance drops below zero, and in the file it shows in red. That is not an error in the sheet, it is information: work went beyond what was bought.

The client should get that information before the invoice, not inside it. The usual practice is to tell the client as the balance nears zero, and ask whether to buy another bank or offset against the next one, according to the agreement.

A balance that stays negative several months in a row usually means the bank is smaller than the real need. That is a conversation about the size of the bank, not about this month's overrun.

What goes in the notes

which agreement or order the purchased hours came from;
unusual usage, and what caused it: a major incident, a requirement change, a small job that went into the bank;
hours that expired or rolled over, under the rule in the agreement;
an hour logged late that belongs to an earlier month.

An example row: the month, an opening balance carried from the previous month, no purchase, the hours used according to the monthly report, a closing balance calculated by the sheet, and in the notes "high usage because of an incident in the payment system".

How to fill it in each month

At month end, total the hours from the monthly report, not from an estimate;
record a purchase, if there was one, in the month it took effect;
check the closing balance. If it is low or negative, tell the client before the invoice goes out;
add a note to any number that would surprise someone six months from now;
send the tracker together with the monthly report, so both sides see the same balance.

Once a quarter, look at the whole table and not only the last row: usage that keeps rising, or a balance that builds up untouched, is a reason to resize the bank before the next renewal.

Where the hours-used number comes from

The tracker is only as good as the number in the hours-used column. If it comes from hours logged against tasks, it can be broken down at any moment. If it comes from memory, the argument is only postponed.

SLAtech Vantage does not calculate an hour bank balance. The hours themselves are logged against tasks, with the date, who logged them and the rate in force, and they go into the monthly report the client sees in the portal; that is where the number for the tracker comes from. Hours can also come from time trackers such as Toggl Track, Clockify and Harvest, and from Jira worklogs.

Download the template (Excel)

Frequently asked questions

How do you calculate an hour bank balance?

A month's closing balance equals its opening balance, plus the hours purchased that month, minus the hours used in it. The closing balance becomes the next month's opening balance, and in the first month the opening balance is entered by hand. When the calculation is a formula pointing to the previous month, nobody can quietly change a balance without it showing in every row that follows.

What do you do when the hour bank balance is negative?

A negative balance says more was worked than was bought. First, tell the client, before the invoice goes out. Then follow the agreement: buy another bank, offset the overrun against the next one, or bill the extra hours at the agreed rate. In that month's notes, write what caused the overrun, so it can still be checked later.

Should an hour bank be tracked in hours or in money?

In hours, if the agreement is measured in hours. When tracking is done in money, a rate change in the middle of the term quietly reduces the hours left, and the client only finds out at the end. If the bank was bought as a sum of money, write the rate in force in the notes, so any change to it is visible and recorded in the same row.

How do you use the hour bank tracker Excel file?

Download the file and, in the first row, type the month and the opening balance. From there each row is a month: fill in the month, the hours purchased and the hours used, and the closing balance calculates itself and carries over as the next row's opening balance. A negative balance shows in red. The file has twelve rows, which is a year of tracking.