# 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.

## 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.

Effective hourly rate: 

| Month | Hours purchased | Hours used | Hours lost | Cost of hours lost |
|---|---|---|---|---|
| 1 |  |  |  |  |
| 2 |  |  |  |  |
| 3 |  |  |  |  |
| 4 |  |  |  |  |
| 5 |  |  |  |  |
| 6 |  |  |  |  |
| 7 |  |  |  |  |
| 8 |  |  |  |  |
| 9 |  |  |  |  |
| 10 |  |  |  |  |
| 11 |  |  |  |  |
| 12 |  |  |  |  |
| **Total** |  |  |  |  |

**Under a tenth lost.** The block is roughly the right size for last year. Ask what happens to unused hours anyway, because one busy quarter next year will change the picture.

**Between a tenth and a quarter lost.** Compare the cost of hours lost against the package discount you received. In most cases the discount did not cover it, and the difference is what you paid for the supplier's scheduling convenience.

**Over a quarter lost.** The shape of the arrangement is wrong, not the size. Buying less next year will move the loss into a queue in busy months. Ask for banked capacity with a defined horizon instead of a smaller block.

**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 |
|---|---|---|---|
|  |  |  |  |
|  |  |  |  |
|  |  |  |  |
| **Total** |  |  |  |

Take three lines into renewal: the total cost of hours lost, the package discount you received over the year, and the difference. If the difference is positive, expiry cost you more than the discount saved, and banked capacity at an undiscounted rate would have left you better off.