 Good morning or good afternoon Welcome to database decision-making for small and weak medium web development agencies So normally I would introduce myself in my company, but I need all the time today, so we're going to jump right in This is a lot of information my slides And all of my speaker notes will be shared online. I only have 30 minutes So I can't speak as slowly as I would like, but don't worry everything. I say is written down You can read it later. I will also share all of the spreadsheets and all of the tools that I talk about today Also, if you have another way of doing these things at your company that you would like to share Please feel free during the Q&A. I have been doing this for 23 years But I certainly don't know everything and I love to improve things at my company too So I would love to hear your experiences This does it sound weird to you guys or it's fine Okay So today we're going to talk about how to use data generated by your work to decide how to manage your business And in the title I say it's focused on small and medium businesses The advice really applies to all web development firms The difference is really that as we get bigger and our revenue grows We tend to purchase more software as a service to Collect this data and help us make decisions So some of the techniques I talk about can really be outsourced to software when you can afford it You have to figure out at what point saving time is worth spending money So in surveys that I've seen about a third of US companies are not using any services for issue tracking project management Capacity planning and even accounting and US companies tend to be pretty big So I thought it would be good to share some of the solutions We have found or built ourselves that don't require a subscription Why are we talking about data-driven decisions? What's the point? So typically our goal is to increase profit This may sound obvious but growing your business taking on more clients building more sites is not really a good way to measure business success We don't really care about the money we make as much as the money we get to keep So for example one company can work a hundred hours make a hundred thousand bot and have ten thousand bot profit Or you can work a thousand hours make a million bot and still only have that same ten thousand bot profit We want to be the first company We want to work less time for our money and I want to make sure you know how to control the profit You get for the time you're putting in for the work you're putting in so to do that We need some data So we're going to cover business decisions about your finances How much did you charge your clients? How do you know which of your clients are profitable? Workflow how many projects can you do when can you do do them? How can you set deadlines and estimates? How long is the work going to take? No one knows But sometimes we have to guess that anyway and these things are all connected We need to do estimates to know our workflow. We need to know workflow to do staffing We need to know financial to basically do anything at all So I left estimates for last because I could do an entire presentation on that and I think I have But we need to know all of this stuff so we can set our prices and control the amount of work We do for each of the clients and those are the factors that determine profitability So let's dive into finances first One of the most important decisions as a company is how much to charge your clients That's a big factor in how much money you make So we know that revenue minus expenses is your profit But even if you're charging clients by the project by story points by the month by the page Whatever at the end of the day you're paying employees for their time and you're charging clients for your employees time So it does basically come down to hours no matter what kind of engagement you have and it also helps us do our math later So we want to know what profit per hour so we can know how much to charge clients generally and Whether each individual client is profitable So first we need to find our company's total expenses for the year and it's up to you how you want to average your expenses You can calculate this once per year and adjust your rates. You can use a rolling average So at any point you can say what? How much of our expenses been in the past 12 months? You can guess ahead to next months next year's budget and do it based on that Your expenses are all the things you spend money on as a company So salaries benefits rent subscriptions hardware hosting if you pay for it as a company It's an expense then we need your billable hours. These are only the hours that the clients are actually paying for Once we have our expenses in our billable hours as a company we can divide total company expenses by total company billable hours This is our break even hourly rates This is how much we need to charge for every hour that we bill in order to exactly pay our expenses This is one of the most important numbers we have in our business and it's the basis for our pricing decisions So obviously it's really hard to know exactly how much your expenses are going to be or how many hours available time You're going to have across your company So we add more to each hour to make sure we can pay our expenses and also make a profit So you can decide how much to charge clients by deciding how much profit you want to make The profit margin is the amount of money that you get to keep above the amount you need to pay your expenses So there's the money you get to keep if you're the owner of the company or you can use it to upgrade equipment Give raises give bonuses or other investment back in your company. So in this example, we want to make 20% profit So we'll take our break even made up hourly rate of a thousand bot and increase it by 20% So that's the money we can keep after we pair of expenses You can also work the math backwards if you have a profit goal in mind So let's say we want to make a million bot next year if my expenses are 5 million I need to bring in 6 million in order for my profit to be 1 million That would give me a profit margin of 16.7% and a sales goal of 6 million bot. Okay easy Now looking at how much we have to charge per hour to make 1 million bot in profit We know our expenses are 5 million. Let's say our billable hours are 10,000 hours That is probably around six or seven people working full-time for a year We have to charge 500 bot per hour to pay our expenses to keep 1 million bot in profit We know our revenue goal is 6 million. So we would have to charge clients 600 bot per hour to keep 1 million bot in profit Okay, so that is the basis If you're a really small company or you don't do time tracking for whatever reason you can guess at your billable hours So you start with the standard working hours in your country in America's 2080 you subtract some estimates for non billable time and you use that as a guess for each employee's billable hours So looking at my company's actual billable hours for the past five years We were like 67 58 61 55 52 So 59% billable on average, but it does change year to year So sometimes some years we're doing a lot of conferences working on our internal site or internal products We have people going on parental leave or other types of non billable time So it's good to take an average and think about how plants in the coming year might affect your billable hours We also really want to know if each client is profitable. So you will have some clients who Argue over hours as for ask for estimates that they never approve You have to work on a technology You're not an expert with and you can't really bill them for all of it Or they just find other ways to make you spend time on them that you can't bill them If you're if your clients are paying for all of the time you spend on them You are winning you are doing a great job You're a hero if you find that you're spending a lot of non billable time on a particular client You may need to increase their rates to make up for it So these are just some example rates, but you want to have a scale in mind for how much you will charge in different situations Typically, you will charge the most if a client has a very small project They only need a few hours of work or if they're high maintenance So these are the clients where you spend of a lot of internal time Compared to the amount of time that you're billing them and it usually makes sense to reduce the cost per hour According to how much total money they're going to give you so if it's a large retainer or a build Usually that means you can charge less per hour and still make a good profit You may want to reduce your rates for very large projects for companies that you want to help like you care about their mission Or in situations where your staff doesn't have a lot of work and they're not going to be doing anything anyway So it makes sense to take less profitable projects just to fill their time I filmed this chart I made in 2013 and it's called chart of how much we like our clients This was a joke, but I was trying to make a point to our client services team that we have a lot of clients Where we do a lot of work but make a low profit and we have other clients where we do a little work But make a lot of profit so keep in mind that we do need revenue to pay our expenses So sometimes we still want those clients who give us a lot of revenue without a lot of profit But you need to know that's happening. You need to be in control of how much Time you're spending on these clients for how much money you're getting So we currently use a sheet like this to track revenue changes by client both by quarter and over years And we use this to see how our client revenue and business line revenue are changing over time. I made a template for you It's at that URL on all of these spreadsheets and all of the templates I made are in my speaker notes as well, which I will share when it's posted online This is another profitability sheet and this gets more granular by client So each client has a row and I track their actual hours versus our revenue To see what we would have to charge to break even what we are actually making per hour And what our profit and our profit margin are so you can see I do this for retainer clients Hourly clients and projects which are our builds so I do this every quarter and then I make another sheet for the year Keeping previous year totals to compare So this shows you how much you're really making on each client and will clearly show you which clients are not being charged enough for The work that you're doing Now we know how much to charge for work if our prices are calculated Well, the more work we do the more money we make But we do have a limited number of developer hours available So we have to figure out how many projects we can do and when we can do them The first thing you need to figure out is what resources you need for the project and that starts with doing an estimate Which we'll also talk about later and this can be organized based on the size of the project by Story points by hours by weeks by sprints Once you know how much time the project will take you can look at your project schedule to see where you can fit it in and Depending on your company You may have to plan multiple team schedules or you just the teams that are really busy So for me, I have to really carefully keep track of our front and back end developers But like project managers design it their schedules are usually fine So you need some kind of project schedule for large companies. We're talking about Gantt charts They let us map our workflow over time and by resource Since we typically have agile teams that are working together long term for small to medium companies You can make your own resource planner with Google sheets or Excel. You can use software like a Jira integrations Harvest forecast Monday comm resource guru, which is what I use and Tens of other software services that let you visualize the work that you have planned for your company So I found this old spreadsheet also from 2013 when I love spreadsheets to keep track of our maintenance works Or non-build tickets and this was when we use base camp as our project management tool now We use Jira, so I have custom filters set up to do this But if you're a small company you can do something like this to allocate your build resources If you don't want to pay for a software subscription for resource tracking So as we book work or we move through the sales process for work that's not booked yet I put it into the sheet according to our team's internal estimate when we have a new project Coming I can scroll forward in time until I see an opening for the resources that I expect to need So if a client asked to have a site improvement done in two weeks But I see that we're fully booked. I can ask the client to move the deadline I can get in touch with our outsourcing partners to ask them if they have availability to help Or I can tell the client that we just can't you know handle the work for them So once we see our potential start date We can go back to our estimated resource needs and figure out what the deadline would look like if everything went perfectly Our estimate was 100% accurate and nothing went wrong in the entire build And then we take that deadline that will never happen and we figure in real life So depending on how close to reality your team's estimates tend to be and how complex the project is You want to increase your estimate by some margin. So somewhere usually somewhere between 20 and 300 percent Please do not book your developers for eight hours in an eight-hour work day There will be some days when people can or have to code the entire day, but that's really not realistic as a guideline It's not a good way to do a schedule So we use five to seven hours per day of output depending on the complexity of the project How well they know the underlying technology whether they've worked on that site before and how dedicated the developer is to that project If you have them switching between different types of work, you can expect their output to go down for sure I also find that we tend to estimate underestimate the time needed to do Final QA of beta sites as well as the final edits in the small scope creep driven by clients So if you don't add that time into your schedule, it's going to cause major problems for the start time of the next project That you have booked for those resources Obviously if it's major scope creep that work needs to be flagged as such for the client and scheduled the way any new work is scheduled So if you are a dev company, that's not a hundred percent retainer driven It is very hard to know how much work your company will have Emergencies come up estimates go bad clients have new ideas We rely heavily on freelancers and outsourcing work to other dev firms that helps us handle overflow We do sometimes have to tell clients we just can't do it But we try to partner with other companies who might have resources free to handle that overflow work So if you want to do that try to find those resources try to find those partners before you need them Do a test project get to know each other make sure they work with your workflow and you guys can trust each other And then you know who to turn to when you when you need help First of all it is not your team that is bad at estimates It is not developers who are bad at estimates Human beings are really really bad at estimates. We are bad at estimating everything Literally someone won the Nobel Prize proving that So a survey by the project manager Institute showed only about half of their members projects are Completed on time and on budget and that's better than a lot of stats. I've seen Obviously the easiest most accurate and most profitable approach is to build with agile on a time and materials basis Like it's awesome if the client just pays for all of the time that you work But a lot of clients need that upfront pricing even if it's like um, you know Maximum price or they don't understand agile methods at all So what can we do to make estimates better when we have to do them? This template is open source and is linked to my slide notes Before you work on an estimate for a new project project you need to decide whether it's even worth doing the project at all So is it a good fit for the technologies that your company uses? Do you have the staff available for it? Is it a good client or work that you're excited to do? You can fill this out yourself as we do have a few people on different teams give their opinions and see if the work Even makes sense in the first place Most companies need to be selective about the work they pursue in order to avoid overextending your team Or also getting into projects that are just a terrible fit for your company If we decide to take on a new project We normally give the client some kind of estimate even if it's a range or do not exceed price Since our developers don't know the future it can help to look at the past So just like you can use reference stories to create a basis for story points You can find previous projects that your team worked on that's similar to the new project and use the time you spent on those To give you an idea of the range to expect If you don't use a time tracking tool that gives you a historical reference that profitability tracking sheet We looked at actually does provide a record of that time So the most important step in improving estimates is to break the work down This only works if you have decent specs for the project if you don't have specs Please do not expect your developers to give you accurate estimates When a project is not defined we tell the client that we can't do an accurate estimate with the information that we have So we give them a range of what we think the project might end up in You know thinking about other similar projects we've done in the past But we tell them that we may need to change that if the when the project becomes more defined And we actually know what work we're going to do if we have good specs we can start breaking the work down So obviously if it's a single task you can write a user story and the developer can estimate in story points When things get larger we use templates to help break down tasks and also keep in mind the non-story work That needs to be done. So this is our story points template We use this for requests that are less than one sprint of work, which for us is two weeks The first sheet is the summary sheet So this creates totals for all of the tabs at the bottom You can add as many tabs as you need for the work you're doing Just make sure to edit the summary page to include all the new tabs so that your totals are correct So here we have tabs by a type of work You can also add tabs for any work that you want to estimate separately So if there's you can give the client options as to what work they want to buy It helps them decide what features they can develop for their budget as we discussed earlier Even if you work in sprints or story points many clients pay in hours with their retainers You pay your employees for the hours that they work more or less even if they're on salary So ultimately hours are pretty closely tied to pricing no matter how you work This is the system we use to convert points to hours You can develop your own table that makes sense for your team Our rule is that anything over eight points need to be broken down into smaller stories And we don't put anything over eight into estimates But this is the math that's powering that story point spreadsheet If your client understands what 950 story points means then you do not need this You don't need to convert into hours But most small and medium agencies are not dealing with the type of client who can understand that So if we go back to the summary sheet when each team member fills in the separate tab The home page converts the story points into hours It then uses the hourly rate that we want to charge for our project To create a price range that we can give to the client And I also included. Oh, no, I'm sorry a little bit later So looking at one of the tabs we use the top section We have a pre-filled list of common tasks and this is to help our team not forget Work that is not coding So they might want to talk they might want to include time for meetings planning Deployment and just other things that we don't think about when we're planning these features out Then the bottom section can be short like this if the work is simple Or you can include as many lines of user stories as you want if the work is complex And you can see that we only have options for one three five or eight story points so that Nobody can put anything bigger than that in there mmm So I included this older sheet Because it shows this is about eight years ago When government clients used to make us create sites that were backwards compatible to old versions of IE that was a good time When responsive was something that clients paid for instead of the default way to build sites And when we charged extra for creating style guides or we didn't do it at all So but this gives you an option to add extra add-ons for pricing for things that you are that you don't typically Include in a build you can just add them in a separate area and it sums it up from the other sheets And then this one also has so it has a break even price a proposed what we would normally charge and then it has a Premium and that's for clients who are rushing us who are Making us do something that's kind of out of our wheelhouse not something we're good at or whatever reason we want to overcharge them So here's an example template that estimates simple tasks But lets us add a pain in the ass like what wha factor for clients or projects that are difficult In this example the hours came out to three point five, but the estimate is seven There's a doubling of time for expected complications and this is useful if you are starting to build in it in a Technology that your team is not familiar with if the client is really bureaucratic So for instance dealing with government or big universities sometimes there is really a lot of overhead Piled on the actual things you're building So this will help you adjust your estimates in hours or story points and calculate different pricing tiers based on that So if the work is at least one sprint of work we instead of that story points template We use the sprint estimate template so each person estimates the number of sprints They will need to work to complete the project Note that you can adjust Utilization or sprints like 50% utilization for two sprints is the same as a hundred percent utilization for one sprint So make sure to tell your team if that difference is how you're planning the sprint out So they know whether it matters Then you just to just adjust the project values to how much you want to charge per hour How many weeks are in your sprints? How many hours there are in a week for your developers? Which is not every hour that your company is open and if you want to add a flat project rate Management fee on top so for your pms You can add a certain percent or you can add separate lines for your pms. BAs whatever you have As with our other template we can add tabs at the bottom to estimate out different features separately as you need You can also see in our version that I link to our tracker For historical project hours so that way everybody who goes to make an estimate can look at all of the real project Hours that we spent on every build we've ever done for 20 years So those are the templates we used to do estimates But I wanted to share a few other tips to help increase estimate accuracy Estimate work for other people believe it or not We do more accurate estimates if we imagine another person is going to be doing the work So once if you're the one doing it imagine some other developer who has your same job is Going to be doing that work and you will create a more accurate estimate Use past projects as a guide add that buffer time and also think about worst-case Situations in reality most projects turn out longer than we thought was even possible Okay, let us wrap this up. So in summary collect data use it to make business decisions and profits Thank you all for coming. I really appreciate it and