 Good morning everyone. It's 1045. I'm going to get started. So welcome to day two of Drupalcon. I'm glad that you chose to join me for this session. And I really hope that that doesn't happen again. Okay. Don't touch the court. So this talk is called Don't Trust Your Gut, Agency Operations Metrics. So we're going to go over some of the metrics that I track in my role as Chief Operating Officer at BlueSpark. So I track these things to make sure that everything is running smoothly. That's kind of in a nutshell what operations is. Keeping everything running. So we're going to spend quite a bit of time looking at some spreadsheets. So I hope that you're all prepared for that. I have posted the links of them over there because the screen is a little small. So I just want to make sure that you can see the detail. It may be easier to follow along on your own computer. First though, we'll get into just kind of why did I do this before I jump into the spreadsheets and start actually sharing how it all works. So first I'd like to tell you just a little bit more about myself. So we're going to go over the metrics of me. I am 13,006 days old today. I've read five books so far in 2018. I average 6.3 hours of sleep per night. It's not enough. And I dance 4.5 hours per week. That's again an average. Sometimes I skip a class. I do about four hours of cooking every week. I love to cook. And zero hours of commuting because I work in a distributed agency. So where have I spent my life? This is a super fancy pie chart that shows you the percentage of my lifetime that I've spent in different cities. I was born in Milwaukee, grew up in Jainesville, Wisconsin. It's a fascinating place. So you definitely don't need to go there. Then I lived in Bourges, France for a year in Paris for 14 years and Chicago for five. And that's where I live now. I have two children with a one-to-one ratio of boy to girl. And I have been married to my husband for 50% of the time I have known him. I have spent one-third of my career at Blue Spark. And now I have spent about 1.5 minutes of this presentation on some meaningless metrics. So let's move on to more important things. So why were those personal metrics kind of meaningless? It was because I wasn't really giving you a whole lot of context. So to tell you I've been married to my husband for half the time that I have known him is not really the same thing if we've been married for five years or 40. That would be a very long time to know somebody. So the context is really what makes the data meaningful. I'm going to share spreadsheets with you. The numbers and the data that is in those spreadsheets can give you part of the story but you definitely are going to need more context and more analysis to really understand what's going on. It can tell you where you need to be looking and what might need some tweaking in your agency but it's never going to tell the whole story. So to help you understand a little bit of the context around the metrics that I track at BlueSpark and what's in the sheets, I need to tell you just a little bit about BlueSpark because the metrics that I track may not be useful for your situation. So BlueSpark was founded in 2009. We have a current team size of about 20 people. We have both in-house design and development. We do time and materials billing so pretty much every hour logged is an hour invoiced and we have had growth of 61% in terms of revenue and 50% in terms of team size since 2015. So why 2015? I'd like to take it all back to DrupalCon LA 2015. Sorry I think I skipped one didn't I? Yep so at DrupalCon LA I attended a session by Sean Larkin. It was entitled Scaling Your Business Starts with the Right Spreadsheets Performance Metrics. So at the time BlueSpark didn't really have much in the way of metrics. We were definitely just using the trust your gut method to plan for our growth and we knew that we wanted to grow. We didn't have necessarily much of a plan how and we didn't really have the numbers to analyze well what would it take to get there. So I am somebody who likes numbers in general and I like to make data driven decisions and I also really like spreadsheets. So Sean's talk really inspired me to take some of the tools that he shared and apply them to our agency. The tools that he shared aimed to answer questions like what is the right volume and mix of client projects to afford hiring staff. What's the weighted value of all work under contract plus likely new business. What's your billable hours target for a particular month based upon staff availability, training and internal resource needs and what's your projected effective billing rate for the next six months. So his talk is awesome. I'm not going to try and rehash the entire thing for you but I have linked to it there in the title of this slide. So if you haven't seen it please go and watch it. It's really great. He goes into a lot of detail about how he built the spreadsheets. I am going to walk you through just really quickly what his spreadsheets do because I've built on it but you know for the full detail you definitely should watch his talk. It's really great and you know very much worth your time. His talk was focused more on pipeline, agency health and scaling. He was in a different role than I am. He was a CEO. So you know what he was trying to do was really look at the overall health of his company and plan for the growth that he wanted to see and he was really also focused on pipeline. I'm making sure you know CEOs when you're in smaller agencies are often really focused still in sales. So the sales pipeline was incorporated in what he was doing as well. So you'll see that I've adapted his tools to kind of skew them more towards operations. So more specifically projecting the work that we have, resourcing it and then measuring the performance for a professional services team. So I am going to like I said quickly walk you through his stuff but not going into all the detail since he did a good job of that already in his talk. So the two tools that he shared and that I built upon were the billable hours matrix and the links that I provided are like my versions of these things. The original links can be found in his talk. So what the billable hours matrix does it is you can set your rate and your monthly expenses and I know this is super small it wasn't intended for you to be able to get in there and see what was going on. You can set your rate set your monthly expenses and just kind of play with those numbers to see what does this do to my profit to my and to my revenue for the year. It allows for you to plan for maximum billable utilization so with your team working and billing every single hour of the day every single week and then realistic billable utilization because that's not realistic to expect that of people people take vacation people get sick they attend conferences they spend time in internal meetings they spend time contact switching so he has it set up so that you can say essentially how many hours per week somebody would spend on all of those activities and then give them a more realistic target for billable hours. It then compares your revenue profit and total hours build between the realistic and the maximum billable rates for your team. There's another tab in there that allows you to plan for team growth where you can add you know new roles over the course of the year at specific months you can plan for their salaries and also see what that does to your profit and to your revenue which is calculated in a separate tab as we'll see and then you can also plan for events so you know there's a week like maybe over the holidays most of your team is gone so you can add deviations to say okay well we're going to lose 300 billable hours this month because everybody's gone or this yeah that month because everybody's gone one week during that month so that's the billable hours matrix short version. The second spreadsheet that he shared is called under contract so this one was really looking at your current work runway it lists all of your projects and it has basically like how many billable hours are in the project who is working on it and then he also had the sales pipeline added in there so work that was not yet signed with weighted values of how likely it was to close so in that way he could calculate okay if you know 50% of the sales pipeline is going to close how much work am I going to have coming up and and try and project you know his his his upcoming work that way so yeah it calculates the value and the timeline for your existing portfolio and then it calculates future workload based on likelihood to close of the sales pipeline so as I said I've adapted these things the billable hours matrix is something I really use it more for just annual planning so it's something that like in September I'll start to put together for the next year to plan for team growth plotting out like what our goals should be when can I add to the team what rules might I need how much can I afford to pay these people and determining what my sales targets need to be to fill my team's capacity that part ties in to the other document though there's some overlap from you know the data of it but so what I've added to this it's not that much his tool was really awesome I've basically added just a section that calculates the potential revenue per role so just looking at somebody in a UX role development role design role with their total reasonable or realistic billable hours for a year how much revenue would that person bring in and then revenue and profit at different utilization rates we'll see that in a second I just said I can't screen share and show you guys slides at the same time so we will walk through all that in a minute for the adaptations to under contract so this one as I said my role is different from Sean's so his was much more an overview and he was bringing in sales pipeline I don't do that so I'm not looking at all at things that are in the sales pipeline I've removed that from under contract I'm only looking at contracted work so what I'm really trying to get into as you know COO is more of the nitty gritty details of operations so I had to give this spreadsheet the capability to tell me at a glance how booked my team is how are we performing and how much revenue can we expect expect month over month so I modified this one quite a bit and we call it internally the mega sheet this one you know I said the other spreadsheet I use like on an annual basis it's more for annual planning this one I update weekly so it really like I lay everything out for the year but then I fine tune it and update it because things change on projects you know timelines sometimes shift for many many reasons that I'm sure you've all encountered somebody's availability may change maybe a contract is extended you know maybe the client is waiting for you know stakeholder alignment and meetings aren't happening when they need to and things just kind of get pushed back many for many reasons you know projections may need to be updated because things change so I'm often in here updating it and trying to keep the data fresh I use the spreadsheet to calculate many things basically calculate actuals utilization rate the value of all of our current accounts timeline and budget for all current accounts velocity needed to complete projects on time remaining budget on projects projected work for the year projected revenue for the year so some of the questions that I can answer with the mega sheet and I'll come I'll circle back to these at the end once we've like walked through it is will my current team configuration be able to complete the work that we have lined up how many people do I need to assign to this project to complete it on time how much available capacity does my team have how utilized is my team how did my team perform last month compared to projections how much revenue will my company bring in in future quarters with the signed portfolio and how much additional sales do I need to close to bill at capacity in future quarters so we're gonna get into the spreadsheets now careful these are just the links that I have posted up there because the screen is small I'm not sure how much you'll be able to see once I start screen sharing so I just wanted to get those up there for a second you can pull them up and follow on yes it's an owl that's totally my fault I thought it was an I I'm sorry okay so that's an owl and it's lower case and I misread it sorry thank you for pointing that out to everybody would have been not looking at the right thing okay so I'm going to exit out of here I'm gonna start mirroring so now you get to see my entire okay might be all right I think we're gonna try and work with this okay so this is the first sheet this is a billable hours matrix kind of explained a little bit you know how this works already this again is mostly taken from Sean I use it for the annual planning and have just added a few things to it so I told I said at the beginning that you can figure out you can kind of play with your rate here and with your expenses expenses are right there so you know changing those numbers is going to affect the all of the calculations and the formulas for profit revenue things like that that are in this sheet but what this gives you is a list of your team here so I'm going to try and highlight things as I go so these are the team the different roles that they're in and then you have the number of hours that they are working per week because maybe you have people who are part-time and then all of this here so this whole section is figuring out that utilize that realistic utilization so you know as somebody spending two hours per week in internal calls and three hours per week contributing like this is where you would lay all of that out to figure out what a realistic target is for this person then at the bottom you have totals it just basically pulls all those numbers together crunches them and tells you like what your maximum revenue maximum profit maximum profit margin would be weekly monthly annually and what the realistic version of those numbers are because people aren't machines and they don't bill 40 hours a week 52 weeks a year so that's the first tab that first tab is all the mechanics that feeds into the rest of the document so the second tab is revenue targets so it takes all of those wonderful people on your team and it pulls their realistic target from the previous tab and then it assumes that they're you know billing that number of hours per month so it just lays out each of your months here with you know the number of hours that that person is billing you have a row here for deviations so that's where you can say oh we're all going to triple con so we need to remove you know some time from that month um and then you get your totals with you know total hours that your team is available how much they are booked booked is something that I added and that is being pulled from the mega sheet that's where I'm calculating how booked there um then booked revenue so just times your rate how much revenue is that booking worth uh and then you have what your revenue would be at full capacity so where they booked to capacity what your revenue would be and then we have a line for uh including a 20 percent rate that we charge for uh project management and account management so then you get your total annual revenue this little section here is something that I added that's just uh calculating what your potential revenue is for specific rules in in the agency so that can help when you're trying to you know plan for for growth and add in new people which is part of what this uh this tab is for you can see at the bottom there's new hires where they're you know planned to begin in April um so you can add people in and and then see you know how that affects your revenue to suddenly have 140 additional billable hours in a month for that person um so then the third tab profit targets this one is now looking at all that revenue that you just brought in from the first tab and it's subtracting your expenses so you have just your monthly expenses at the top um after that you have then uh monthly salary for every person on the team um obviously none of this is real data that I'm sharing with you um and then at the bottom you have your totals so projected costs per month your projected revenue that's being pulled from the previous tab projected profit projected profit margin what I've added here and what I found useful um to calculate in this is what happens to your revenue your profit and your profit margin at different utilization rates because while we are being realistic with our targets you know life does happen sometimes that project that you thought was a sure deal doesn't end up happening or you know so you may end up with some downtime during the year we all know that agency life is not steady it's kind of it can be a little up and down um so this is just if your average utilization rate for the entire year is 98 percent so and that would be 98 percent of the realistic target not 98 percent of like maximum so calculating based on for us 30 hours per week available time not 40 um what does that do to your revenue at what point are you just barely at break even in this instance with this you know fake data that I put in there um somewhere between 80 and 85 percent you start to lose money so then the fourth tab and this is one that I've added completely this was not in here before but this was also something that was kind of calculated since sales pipeline was in the other document it was there um in Sean's version so this just pulls in from the other tabs deviations uh so things that are you know events that are affecting availability in specific months then the total number of available hours that you have how much of that time is booked uh what your sales target is in hours to hit capacity and your monthly sales target uh so how much like unbooked revenue you have so it would it doesn't do me any good to sell $22,000 in January that's too late because what this is telling me is my sales target about two months before that should be $22,000 so that in January I can do $22,000 worth of work that I don't currently have booked so it then it gives you then your annual sales target for this team it's $22,000 again a $22,000 sale in December isn't going to help me very much like that'll be really nice for 2019 but it doesn't help this team hit their capacity this year okay so now we're going to move into the megashit uh so megashit the first tab of this is kind of the data the project data that then gets that that I then use for the the resourcing and the projections um so what this is you have a list of all projects it's divided into different sections so all projects with them you have client name what the project is about you know project like title and then oh sorry I'm there we go that's better um and then there's just a brief section with the different people who are assigned to the team and then you get into the timeline section so timeline is here um so the timelines that I'm using here are for projections I'm trying to plan and resource projects trying to look at an overall portfolio of projects this is not from a project management perspective I'm not you know actually working actively on these projects and trying to make sure you know that all these tickets are completed on time that's a whole another level of planning that needs to happen beyond this so this is kind of the you know bigger picture version of this portfolio of projects let's get them planned out and more or less figure out when the work is getting done um so the way this works when I add a new project um I would add in then the start date when we you know expect to start the end date so for each phase so project start date here sorry UX completion date because I'm assuming that there's UX on this project if there's you know if there's not then you would just jump to production uh and then UI start date UI completion date production start date production completion date so it's basically your rough timeline for each project phase so I would add that here and then this next little section weeks to complete is calculated with a magical formula where it looks at those two dates and it says oh between you know April 1st and April 30th there are four weeks um and it does take into account today's date so if today's date is April 15th it's going to say no you have two weeks left for that phase the next section then is the contract so how much money is this project worth so you would fill in your rates you would fill in then more I fill in uh how many hours are for each phase so how many hours do I have for UX how many hours do I have for UI how many hours do I have for production if there's you know no design as I said we have design on our team but maybe this project doesn't have design you just fill in the production hours that then is going to calculate the total value of your contract over here so I do not go in there and say this is a hundred thousand dollar project I just fill in the rate how many hours and it calculates that part for me then you have time logged so time logged is the number of billable hours again for each project phase in JIRA these are not pulled directly from JIRA I pull them manually it really doesn't take me that long um and I add them to the actuals tab that we'll look at later so these are being pulled from the actuals tab from tab three um but it's just basically the total number of hours that have been logged for each of those phases then you get the total number of hours and the other really cool magical formula that uh tells you what the velocity to complete is for each phase so this is looking at the number of hours that you have left and the number of weeks that you have left from those previous sections and calculating how many hours you need to log per week to get through that work on time and then it's also calculating remaining budget sorry that's this column right there based on the hours that have been logged in this section right here okay so that's all the data that's then going into the next part this is where it gets really fun I promise um okay I'm going to zoom out though because I would like to first kind of let you see how much is in this okay there we go this is the entire megashit almost um so what's going on in this thing this here is the project so each of these little sections right here this is one project and this here is the project data and I will zoom back in don't worry um so it's the project title the different people who are assigned to the project for each specific phase and it's pulling in from the previous tab the dates for those phases and the number of hours from the velocity so the number of hours that I have to log per week to complete this thing on time each of these columns represents one week out of the year so I go in and I will add in like I basically plan out the team so if I need you know a hundred hours on a project and I have three maybe three and a half people working on it you know I put in those four the four people here and then I would add hours in the corresponding weeks like if one person's going to be working 15 hours I says and then the other three are working 30 I would add you know 15 30 30 30 spread it out for the duration of the project I'll zoom back in to show you how that works but so each week where you see a number that means somebody's working those are you know hours that we project to work and to log and bill okay so these are just then all the projects and if you when you get down to the bottom you get your totals so this is where your analysis can happen so the totals you have each team member and this is every single you know it's totaling how many hours they are projected to work or planned to work for each week of the year I have color coding that I will explain when I zoom back in but the color coding is really what helps me see things at a glance and I'm very very sorry if anyone is colorblind it is not accessible for that um so booked versus capacity is then in the section beneath that so it's just analyzing how much people are booked versus their capacity there's a section then with revenue projections so taking all those hours that are planned and multiplying at times our rate to get us you know projected revenue that we would bring in and then the bottom section is a comparison to the actuals which are in the next tab so okay I'm gonna go back up to the top I'm gonna zoom back in because I know it's a little scary the super zoomed out version where you can't really see anything there we go okay so we're gonna look at a couple of projects um so as I said this is all getting pulled in from from the other sheet and I'm using this part to actually or from the other tab sorry from the first tab and this then is just used to actually plan out the work and and the team so I want to use this one as my example so because it hasn't started yet so it makes it a little bit easier to use as my example okay so I can see here that for the UX phase of my project uh it needs to begin May 1st it needs to end June 1st so I have about four weeks for it and I need to log 18.67 hours to get through the total number of hours that I have for that phase so I've assigned it to David David R from my my list of UX people and he's then gonna be working you know 18.5 hours for those four weeks and you can see that matches up to May 6 okay then you know same thing for UI same thing for development for the development team in this instance it's a little bit different because there's there's more hours than one person can do so I have to add a couple of rows and then assign to the different people on the team who will be doing that work so in this instance you know there's 93.3 hours per week so I've planned for 30 per person you know things always deviate just a little bit from the plan it doesn't mean that we'll have you know probably an extra week of work for one person at the end but yeah that's something that you know you would adjust as you go it's it's a planning tool it's not like the law um and then you may notice that here I have assigned some of the work to unknown so sometimes I don't know who's going to do the work um I'm just you know haven't decided yet or you know maybe I'm waiting for another project to finish up there could be various reasons why I don't know who's going to do the work when I'm trying to plan it out so I do have as variables unknown front end unknown back end and that can also allow me to plan for kind of overflow work when I know that my team is booked and I really don't have anyone available for this I can assign it to unknown and if I have three projects that I've assigned to unknown when I get to the bottom and I'm analyzing things I know okay I got a problem I have you know way too much work for for my team but we'll I'll show you that a little bit more in a minute um so this is then planning for more of like a a new like a redesign a build that's happening in multiple phases we also work with retainer clients and some smaller uh like maintenance support clients so it works a little differently for them I try to generally assign one person to keep a study resource working with that client you know it helps build the client relationship and the trust and they really then have a deep knowledge of the project that they're working on I do have always a backup who's aware of the project and who can fill in but generally it's one person so they're a little bit easier to plan you just you know need to assign the person who's working on that project and and a lot of instances for retainer clients uh we would have like a monthly cap of hours right there's a monthly budget limit that you're working with uh so you just basically spread that out evenly through the entire year and then that's what's happening here with these this one's a little bit larger there's two people on it they're both working more or less full time for the entire year with maintenance um well smaller support projects this is definitely where there can be a lot of difference between the plan and the actuals because a lot of like you know if somebody has 20 hours a month it doesn't really make sense to be working just a little bit each week on that it can be a lot more efficient to you know maybe concentrate that work in two separate weeks so spreading it out like this does mean that it doesn't necessarily line up with the actuals so then if we go down to the bottom all right we have our totals so team member the total annual hours that that person is booked what their weekly capacity is their average weekly booking and then their average utilization so based on booking um and then so these I said earlier this is pulling in the totals from all the projects planned above per person and the color coding here is red means that person is not booked at all orange means they are booked zero to 49 percent yellow means they are booked 50 to like 98 percent green they are booked 98 to about 101 and blue means that person is totally underwater they're booked you know over capacity at like 102 all right so then beneath that we have booked versus capacity so this is just analyzing those bookings and the teams availability in different ways so you have you know the total number of hours that the entire team is booked the and then it's translated then into the available hours for the development team in a given week the available hours for the design team in a given week and then the percentage that your development team is booked and the percentage that your design team is booked so just different ways of looking at the numbers beneath that we have the revenue projections so actually taking all those bookings and multiplying at times your rate to get a a sense of how much revenue you would bring in with all those billblowers so these are separated out weekly you know we've been planning all of all this project work on a weekly basis so you have the total per week and then it gets pulled in for the month then there's the adjusted monthly revenue here this is important because I found that there were sometimes quite large differences between the actuals and my projections and the reason was you know some months fall you know over five weeks or four weeks and it doesn't always line up with the number of days in the month so it's just basically taking that weekly planning and adjusting for the reality of you know monthly monthly invoicing and the fact that the days don't always fall you know right on on the weeks that you had planned for then you have the total number of monthly hours that are booked and the actuals comparison so actuals this there aren't actuals yet really for April since we're only weekend so this is pulling the total the actual revenue the actual number of billblowers from the following tab that we'll go into in just a second and then it's giving you the variance so how accurate were your projections I do like I said go back and fine tune these I don't just plan out the work and do projections like once at the beginning of the year and then you know oh well that was the plan I don't know I wasn't very accurate I update this so I have found that they're generally maybe five percent variance uh it's it's been pretty accurate and does help for planning for cash flow and things like that all right so then actuals the actuals looks the same as projections because it's the exact same uh structure so essentially at the beginning of I'll accept something's going on there with conditional formatting just ignore that and I'll fix it later um so it what I would do at the beginning of the year I would take my projections tab once that's all ready and I would duplicate it because that way I have you know the team assignments and things like that as they should be in all the numbers and then I would take all the numbers here and remove the values and my actuals tab is ready to go so every week uh I pull all the hours from my team from JIRA so I pull a report where it's basically each project and then the person and then the total number of hours that they logged in that week and I just go through this it really only takes like five minutes and uh and log how many hours each person has from JIRA for the week so these are all then the actual numbers and then when you scroll down to the bottom you get your uh the actual number of hours that each person logged the same thing with the totals the same color coding all the same principles apply and then you have I mean what I then jump to here is what the actual revenue is for the month I try and kind of track like am I still on point like if it's the third week of the month how much do we have to log in this next week to hit our projection uh you know that's where I can see these things really happening is here like you can see for April you know the monthly revenue there is $7,000 we only have a week logged here um but the projection is $25,000 so you know how much you have to make up at that point well it's um then what the bottom at the very bottom is utilization rate so I have all of the team members again uh quarterly utilization rate and then weekly so this pulls from their uh pulls from the hours that are logged above and it basically just divides that into their uh their targets so the targets that are also um well they were defined in the first sheet that I showed you that's where I would be planning that out for the year what the realistic target is but then they're also here so it's pulling from here in the sheet with the weekly capacity um it's important to note that I do make adjustments to the utilization rate when people are on vacation or you know if they're out for two days at a conference or something like that um I then reduce their target for that week to whatever it was for the actual days they were working um so that it doesn't count against them I mean essentially it helps I think I hope uh morale because you know they're seeing that I worked really hard for those three days before I was out for a long weekend and I still you know I hit my targets for those three days so their their utilization rate then is still 100% if they hit their target for those three days it's not you know 70 because they were out um and that's actually what's happening here this would be the first week of the year there was vacation um so everybody was just at 100% so and then at the very bottom you have your average utilization rate for your development team and for your design team so just separating that out so um I'd said that there were some questions that this allows me to answer I'm going to go through those really quickly and then I'll open things up for your questions um so if I'm asking myself will my current team configuration be able to complete all my contracted work so for that I would look at my projections tab and I would go right here to my color code and I can see okay with all the work that I have planned this team is way under utilized like yes they're going to be able to complete that for sure and I probably need to sign some more contracts or I may need to look at layoffs in this instance if this is going to be a prolonged situation where I have this much red but that's not always how things work so I prepared a different little scenario over here for you um so this team is way overbooked this team has too much work uh so you can see some people are uh you know some people are underwater they're blue um but not just a little bit this like this team has six this person has 60 hours to sign 90 hours 40 hours you know they're not going to be able to complete all that work themselves um one thing to note as well when I am planning sometimes I know that somebody's going to be underwater like I cannot they can't possibly do these two projects that I'm assigning to them but when I'm when I just am adding a new project starting to figure out the projections and planning for the portfolio I kind of plan ideally and then I would come down to the bottom and say okay yeah it's you know either it confirms what I thought or it's worse than I thought and I really need to shift that project to somebody else um but at least by just kind of doing what what I would ideally do um I can then you know reassess the situation once I get down here um and not once I've seen the total number and I'm not just trying to adjust in my head when I'm figuring out the project at the top um but yeah so this team is very busy they have a lot of you know they're either all at capacity or way over capacity and there's a bunch of hours assigned to the unknown back end and unknown front end um you know they have quite a few you know hours that that well quite a few hours that you could be either contracting out you know maybe it's time to look at some freelance help maybe it's time to look at outsourcing um maybe if this is a prolonged situation you need to make a lot of hires really quickly maybe you need to go talk to your clients and work on readjusting some of the time frames that you're working with on these projects because all that work is not going to get done in that time frame by that team it's it's not going to happen that's not at all realistic um okay how many people do I need to assign uh to a project to complete it on time so that's the velocity and that's what we were looking at earlier where you can see you know you have 93 hours in this instance uh per week so you just divide that by the capacity for the people who you are going to have working on it and then okay well I need to have three people working on that project if my capacity is 30 hours um you know maybe one of them only has partial availability so maybe you're going to spread those hours over two people you know those are kind of the fine tuning and the the analysis that you do on you you know after after you figured out how many people need to be assigned um so how much available capacity does my team have that's back at the bottom with our totals and that would be all here in the book versus capacity uh areas you can see you know in like the week of August 5th the development team has 108 hours available uh the design team has 68 hours available but you can see over here you know in October when that team is really busy they don't have any availability they have minus 127 hours plus I will note that those hours do not pull in uh unknown so unknown dev uh front end back end are not accounted for in the capacity because I would throw off all the other calculations um so it's actually worse than that when the team is overbooked um how utilized is my team so for utilization I would go to the actuals tab go down and look at the utilization rates um and you know this is this team is not very utilized as we saw the planning showed that they were not going to be um but some people are still a little bit underwater we still have some people at like 110 hours so maybe they're really you know working a bit of overtime on this project to get it done in in a certain time you know there's like I said the numbers only are going to get you so far then you have to kind of figure out what's happening on this project like why do I have people who are at 60% but this other person's at 110 what's going on um how did my how did my team perform last month versus the projections so that you would look into in the projections comparison part and that's repeated in both of the tabs in actuals and in projections there's the comparison pulled in um so you can see that in February this team uh did not hit they did not hit their uh expected revenue their adjusted monthly revenue uh sorry I'm in the actuals tab adjusted monthly revenue was $15,000 uh their projected revenue was $24,000 so they were $8,000 below then how much revenue will my company bring in in future quarters with the current sign portfolio so that I would look at the projections tab looking down at revenue projections you can just um I would just highlight the uh the number sorry adjusted monthly revenue for those three months and then you get the sum at the bottom $79,000 for the quarter and it would be very very easy to just pull that into another tab that's one improvement that I have yet to make but that that is how you can assess quarterly um how much additional sales do I need to close to billet capacity in future quarters that's pulling from the other spreadsheet as I was showing uh at the beginning that's this whole tab um I do add the booked number uh manually from from the projections tab once that's figured out so it's something that'll come back in here and update it um so those are the questions that I'm able to answer with this uh I do want to go back and where did my other thing go here we go all right so if after watching all of this you're like so excited and you want to put in place really massive spreadsheets to track your own metrics um you know I didn't I've given you template versions feel free to take them change them do whatever you want with them to make it work for your agency and for your your needs um to get started I would suggest starting small it takes a lot of time to get them initially set up with your own data so try and do it in manageable chunks and just make slow and steady progress you know do not take the spreadsheet and say oh I'm gonna do this in one day because that's it's a lot of work you want to focus on it and make sure that you're not messing up formulas and things like that um then you know adapt and evolve them over time this is what works for my agency and what I need to track in my role and with our configuration and our needs but your needs are probably different so you know if you're seeing oh I really need to know this specific metric and it's not in there you know figure out the formula that'll get you that calculation add it to the spreadsheet and you've got it then you can track that um and then when you've done something really super cool and added to this and made you know a super mega sheet or something um come back and share it with everybody uh so that is it thank you very much for listening to this and looking at all the spreadsheets with me and now I'll take questions hey thanks for sharing all that it's really great um I was curious on a sort of weekly basis how much time you are spending on keeping this up to date and are the project your project managers involved in keeping this up as well so the project managers are involved in reporting back to me what's happening on their projects you know maybe a new you know an additional PO was signed or something on a project or maybe you know there was an additional scope that was added or something didn't get completed on time whatever's happening on the project they would report that to me so that I know that I need to go and adjust the projections accordingly and the timeline got pushed whatever it is um things definitely change and evolve on projects but I mean it's rarely all the projects that are changing in one week so updating the projections maybe half an hour in a week it's it's not huge um it's really getting all the data there to begin with that that's really time consuming and then the actuals and the utilization rate I pull the numbers I plug them in and then I adjust for vacation that part takes maybe 15 minutes yeah hey there love all of that that was amazing thank you for sharing you're talking my language two questions if I can one do you find that the needs of the business and how you measure the business have changed as you grow and like what worked when you had 20 people in 10 projects doesn't work anymore when you have 50 people in 20 projects question one question two we built a pressure spreadsheet looks like almost exactly like that so yay for us for being creative but like boy we built the same thing should we have tried to look for some software off the shelf that kind of does that like if you looked at professional services automation software that could do that stuff so we have to build it ourselves okay so we're at 20 so I don't quite know how this scales and how it works once you get to 50 I mean what I would assume is that it's still definitely a workable tool it's just going to take you more time to set it up because you have more projects that you're adding in there but I mean the analysis part that's happening down at the bottom is still probably very useful and still a good a good like short story to get you you know to help you understand what's going on with your portfolio even if you have you know 50 projects up above so you probably just end up spending more time using it but I think it would still probably work for for a larger team than than what we have as far as software that's out there that does this there's definitely software I have not actually looked at a whole lot of outside tools because I had this and this works for me and I like being able to change it and make it do what I need it to do I sometimes find that software you know it does most of the things that I wanted to do but not all of them and and here I can make it do whatever I want it to if I can just figure out the formula so yeah if I can answer that question for the gentleman who just asked quick books has a tool and they have some add-ons that you can use that can also do that I guess most of the agencies use quick books for their accounting so you will know your utilization rate and invoicing like that my question is so you have two options one is your gut instinct that you used to make decisions and then you have the spreadsheet I understand that once you know your data which is utilization rate and build rate I guess apart from just pushing your sales team or looking at revenue increasing what else am I getting from this maintaining these spreadsheets I mean what other decisions am I changing yes I do know with a team of 10 or 20 people that what projects how they're lined up and what's the delivery and all that stuff but your team is able to measure their performance because they can see how many hours they've logged they can see what their utilization rate is and how effective they were at hitting their targets and not just the people who are logging that time but everybody on the team you know it's a communication tool for everybody and everybody can like this is something that's open I don't just do this off in my corner the entire team can see it and and can consult it at any time so it's also gives a a glance or an overview of agency health like we were supposed to you know the plan was to bring in $20,000 well we you know we only log 10 if over several months like people are going to know that there's a problem or or vice versa right if they're seeing like every single month we're hitting we're exceeding our projections like that's telling them the agency is really healthy like we're working really hard like something great is going here it you know it's it's their key performance indicators essentially for the entire team they can see their performance and how we're doing together and then the other thing is cash flow yeah so those are the those are the two main takeaways for you by maintaining the spreadsheet and preparing and planning would you say that would be fair assessment or is there anything else that I may have missed that is the takeaway for investing time and effort in maintaining the spreadsheet that's a question I'm asking I'm not telling you what you're doing it's just that I'm asking is that or anything else I'm missing I mean I just talked for 45 minutes with lots of takeaways on what this spreadsheet can give you so if that's what you took away from it that's great I'm happy that you took away something thanks hi there thanks again for sharing those really interesting and intense compared to what I do currently so I've got a lot of ideas I'm curious how this kind of reflects back to reality so you've got a really good plan let's say everyone's like well utilized for a couple months and then you know your client takes four weeks to approve something instead of the one that you planned for you know then you've got some people with overages and then some people for a couple weeks are underutilized like how do you how does the balance and what you find in the spreadsheet reflect back on how you in reality deal with the clients so reflect back on how we deal with the client so what it does do is it allows us when dealing with clients so that client that's taking four weeks to approve something you can say well I'm sorry but in four weeks my team availability is lesser than it is now so like I have you know team members who are lined up and booked on these other things as you know in my projections and so you know if it's going to take four weeks well you may lose this person who was available and who was ready so that's one way that it can tie back into your client communication and like actual you know how you're running the project you're right that sometimes some things take longer but then there's like overtime on other things so I have found that in some instances all of the like life happening evens out and the projections still are relatively accurate even though one thing took longer and I had to you know go back and say oh okay well these 40 hours I had planned are actually going to happen two weeks from now because this came up but then in the meantime we were really you know pushing to get something down on another project so there was some overtime and just sometimes it ends up evening out and sometimes it doesn't and then your numbers are just off but you should know that before you actually pull them an invoice and then you're like oh whoa I didn't really bill as much as I thought I was going to this month because you're tracking it week over week so thank you yeah thanks for the presentation I had a question about so I remember last year from your estimations talk you talked about how you guys add a 20 percent for project management and I noticed in your sheet you have that down at the bottom for your projections but then you also have the project managers listed out as individuals and you're planning their time so I don't quite understand is I didn't understand the full team is planned uh and that's just to understand like what a reasonable billable uh what a reasonable target is for them but the project managers do not work with target so while it is kind of planning out about how much time they should be spending on you know unbillable work in a week and versus billable work um I remove them from the revenue yeah okay gotcha cool thanks they're just zeroed out yeah anybody else just like 30 seconds for one question yeah all right thank you so much for coming have a great day