Retainer arithmetic sheet
Enter your effective hourly rate, then one row per month for the last 12 months from your invoices and the supplier's usage reports. The hours lost, their cost and the reading at the side update as you type. It takes about 20 minutes and produces the one number to take into renewal.
Before you start
- Work out your effective hourly rate: the total you paid for the retainer last year divided by the total hours purchased. If you buy in days, convert to hours at the day length written in the contract.
- Convert everything to hours before you fill in a row, so the columns add. If the supplier reports in days or points, ask for the hours behind them.
- If the package was discounted, use the discounted rate here and keep the discount figure to hand for the renewal lines at the side.
- Hours lost are hours purchased that expired unused. The sheet works them out from the purchased and used columns, so there is nothing to enter there.
Worked example (illustrative figures, not measured)
| Month | Hours purchased | Hours used | Hours lost | Cost of hours lost |
|---|---|---|---|---|
| Month 1 | 40 | 22 | 18 | 18 x rate |
| Month 2 | 40 | 44 | 0 | 0 (4 hours bought ad hoc, see overflow) |
| Month 3 | 40 | 9 | 31 | 31 x rate |
Month 2 shows the other failure. You ran short and paid an ad hoc rate in the same quarter that month 3 left 31 hours unused. Both belong on the sheet.
Fill it in below. Your result updates as you go.
Your last 12 months
If hours roll within a quarter, leave both columns blank in the first two months of each quarter and enter the whole quarter's purchased and used hours in the third.
| Month | Hours purchased | Hours used | Hours lost | Cost of hours lost |
|---|---|---|---|---|
| 1 | 0 | 0 | ||
| 2 | 0 | 0 | ||
| 3 | 0 | 0 | ||
| 4 | 0 | 0 | ||
| 5 | 0 | 0 | ||
| 6 | 0 | 0 | ||
| 7 | 0 | 0 | ||
| 8 | 0 | 0 | ||
| 9 | 0 | 0 | ||
| 10 | 0 | 0 | ||
| 11 | 0 | 0 | ||
| 12 | 0 | 0 | ||
| Total | 0 | 0 | 0 | 0 |
Overflow in the same year as hours lost. You paid twice for the same capacity, once for hours that expired and again for hours bought ad hoc. This is the strongest line on the sheet, so lead with it.
Overflow bought outside the retainer
One row for any month where you paid for hours beyond the block, as ad hoc hours, a change request or a small separate project.
| Month | Hours bought outside the retainer | Rate difference against the retainer rate | Extra cost | |
|---|---|---|---|---|
| 0 | ||||
| 0 | ||||
| 0 | ||||
| Total | 0 | 0 |
A conversation about how your aftercare is structured.
Open to readers who intend to stay with their current supplier. We look at the shape of the arrangement, not at replacing it.
Find the right AI starting point.
Align the business case, governance and first implementation—without starting a large programme.