My standard way of working with clients is a fixed price, fixed duration automation sprint. The actual hours spent on a sprint will vary as no two sprints are exactly the same.
If you don’t keep an eye on it, you might end up putting in more hours than you thought you would. You might think, “Actually, I would have been better off just being on an hourly rate than this.”
This is the same problem when doing projects at a fixed price.
So how exactly do you keep an eye on it?
You can do this by calculating your “effective hourly rate”. The effective hourly rate is simply the price you charge divided by the hours you have put into the sprint.
If your effective hourly rate is lower than you expected, you can make the necessary adjustments in the next sprint (or project).
I track all my client projects in Airtable, including calculating the effective hourly rate.
The Client Hub
I have an Airtable base called The Client Hub, which contains all the data about my clients, projects and sprints.
The sprint table
It all starts with the sprint table in my Client Hub base. That’s where I schedule out the sprints for each client, with a start date and end date. The table also has a calendar, which makes it easy to see which week the sprint is scheduled for.
It also includes the number of days the sprint is for, which auto-calculates based on the start and end date. It could be one day, or up to five days.
The daily rate is located in the client table, not the sprint table. The client table has the name of the client and the daily rate they pay. When I add a sprint into the sprint table, I have a related field that relates it to the client. There is a lookup field that gets the daily rate from the client table and puts it into the sprint table.
The price for the sprint is the number of days multiplied by that daily rate. That is divided by the number of hours to get the effective hourly rate (EHR).
From the table above:
- Days: the number of days for the sprint
- Price: the days multiplied by the rate for the client
- Hours: the number of hours I spent on the sprint
- EHR: the sprint price divided by the hours
From there I can see, for each sprint I’ve completed, which ones are the most profitable from an hourly-rate point of view.
Where the hours come from
Once the sprint is finished, I just enter the number of hours. I calculate that using another app called Sunsama. In Sunsama, I have all the tasks I need to do for the sprint for that week, and I track the time against each task.
I have a Sunsama context called “clients”, and within that client context, I have a channel for every single client. Sunsama has a weekly review feature, and in that weekly review it gives me the total for the client. I then pop that total into the hours field.
I’m only doing one sprint per week per client, so it’s super simple. That’s why I’m using Sunsama to get those hours rather than using Airtable. If I were using Airtable directly, I’d need to plug in some kind of timer and although I know Airtable lets you put a timer into a field, I’m using Sunsama anyway. As it has time tracking built in, it’s easier to use.
What counts as an hour
Anything that is for the client in relation to that particular sprint counts.
If there’s a kick-off call before the sprint, or any support after it, that is included in the same record in the sprints table. There is a field for pre-sprint planning and a field for post-sprint support.
Rolling it up to projects
Sometimes I’m working on a bigger project for a client that spans multiple sprints, and I want to see how profitable it is. So I also have a projects table. When I’m working on a sprint, I link that sprint to a project.
Linking to a project from the sprint table:
The project table uses a roll-up to get the hours and days from the sprint table.
What I do when the number drops
If one sprint is lower than the rest and it’s not the first sprint for that client, I’ll bear that in mind for next time. I’ll either scope it better or try to reduce the number of hours for the next sprint.
If it’s a one-off for a particular client, it’s less of a problem. But if the effective hourly rate keeps decreasing, I’ll either increase the price or decrease the scope, or better define the scope so the number of hours is lower than the previous sprint.
Often it’s because the scope was wrong or something took longer than expected. A particular API doesn’t work the way I expected. A particular tool integration takes longer than expected. The client has a lot of back-and-forth. I need to review it case by case, because the actual reasons depend on the client, the sprint, and the project.
Sometimes it takes longer than expected because I’m using a new tool and the API required figuring out things I didn’t know beforehand. It shouldn’t happen again, so it’s a one-off cost to me.
What it might tell me is that I need to limit the number of apps I work with going forward, so I don’t end up working with a brand new SaaS product or a brand new API in a sprint that I’ve never used before.
So it lets you keep an eye on it and make adjustments if you need to.
Wrapping up
I love working in sprints with clients rather than billing by the hour, but I do need to be careful as to how much time I’m putting in as I don’t want to end up worse off. Calculating the effective hourly rate is a great way of monitoring it.
And this is a big advantage in using a database tool like Airtable to manage client projects. It makes these sorts of calculations and simple analysis easy to do.