I want to do something a little bit different. I want to talk about a report that I’ve been working on for the past week or two. This time it’s not a client’s report, it’s an internal report that’s going to help us handle demand and capacity planning. The report is called “Demand and Capacity Reporting”. Couldn’t come up with a better name. So that’s what I called it. First, I want to talk a little bit about our practice and what makes this report so important. I believe this is applicable to a lot of you out there that are trying to manage demand and capacity.
First, LeapFrogBI is a company that I work for and we’re a small practice, but we’re growing. On day one, we would serve clients with just one or two of us. We would all do everything, pretty much like every company. But as we have grown, we’ve more people involved and it’s no longer feasible for any one person to do everything. We can’t really have the same person serving clients, handling client relationships, doing development, doing peer reviews, etc. It just doesn’t make sense anymore from a business perspective to continue to operate that way. We are in the process of migrating from “one person, does everything” type of an approach to being more compartmentalized. We are compartmentalizing specifically around the actual development process and the analysts. We really need to separate those two functions. That’s our internal situation, but I think this applies to pretty much any organization that’s trying to understand how much demand you have for your services or products and then how much capacity you must actually serve that demand.
Another thing that makes us a little bit unique, there’s probably a lot of companies like this, we’re 100% remote. We have people across the country that are working on all our clients’ projects. So, we’re not sitting in the same office. We’re not looking at what each other’s doing. So, communication, reporting, and standard business processes are very important to us. It’s very important that we’re able to keep track of what each other is doing without having to go and ask each other individually. It’s just not feasible again for a practice manager to go out and ask every developer, “Okay, what are you working on today?” That’s not an efficient process and as soon as that question is asked, the answer is now out of date because that’s going to change.
We have a plan and then the plan changes, but it’s still important that the practice manager can understand what’s going on. The value opportunity that we have here at LeapFrogBI is easy to quantify. We could take someone and make it their job to just go out and pull all this information together and ask people constantly what they’re doing. That would be a very expensive proposition, just simply keeping track. Keeping track of the demand that we have for our services would be an overwhelming responsibility. We use Kanban boards to track all the feature requests that our clients give us. Someone would have to go out and look at hundreds, if not thousands of requests and try to figure out what all that means.
It’s not something that any one person would probably be able to do. It would take a couple of people just to do that part. Then, you have somebody that can ago and keep track of what the developers and the analysts are doing. They would need to figure those things out and synchronize the information. They would ultimately have to come up with something that says here’s the demand and capacity. Either we’re under capacity or over capacity, etc. That would very likely cost over $100,000 a year, if not significantly more than that. But maybe more importantly is that would bottleneck our business.
This demand and capacity report that I’m going to talk about today is crucial for us here at LeapFrogBI. And I think that a lot of companies find themselves in this situation that we’re in now, where we’re a rapidly growing company.
We’re doing revenue growth of 50% year over year right now. And we’ve been doing that for a couple of years in a row. It means there’s more people involved, there’s more more clients, so we need to have a little bit more structure. So that we don’t just simply lose control of things, and we can keep our quality where we want it to be.
Here is a little bit about the approach that I took to building this report. Again, we’re not a hundred-person company. We’re a small company. I think we’re at 12 right now and hiring. If any of you out there are interested in getting into the business intelligence or data warehousing world, and you have skills in the SQL Server side of things, well, reach out to me and I will get you in contact with someone to talk to.
I was pulling this report together myself, as you can imagine. Like most of you I’ve got a limited amount of time. I wanted to have something that I could rely on. I took the approach of doing what I would call an operational data store. I didn’t build a data warehouse. I didn’t build any real ETL or anything like that. I just used off the shelf tools that are, I would call a low-code approach. I specifically used Power Automate, which used to be called Flow. I used that tool to scrape all the data I needed from calendars. We use time blocking at LeapFrogBI. It’s part of our process, all of the developers time block what they’re going to be doing each day and into the future as they have commitments.
That’s an important component of understanding how much capacity we have. To pull all that information in, we use Power Automate and just share the calendars within an account. Then Power Automate can easily pull that data into a database for us on a regular basis. Then we use Power Automate to scrape all our planner boards. We use Microsoft Planner for all our clients’ Kanban boards and all of the cards on those boards can be scraped with Power Automate as well. Now all of that information is staged in a database that we maintain. And then we just did what we had to do to get the calendar appointments aligned with the actual planner cards. The appointments have a marker in them that gives us an identifier that can be also found on a planner card. So that’s how we tie the capacity coming from calendars with the demand coming from our planner boards.
Now all of that part of the process of doing the data wrangling was pretty quick. I would say that probably took me a few hours to pull that together. But the actual coming up with the logic to deal with some of the more intricate details of the reporting, took a little more time. In any case, we took a low code approach to this by primarily using Power Automate. We built a little operation data store and then we were building reports in Power BI.
I wish we had these reports years ago, frankly. It’s just one report with two pages in a Power BI report. The first page is called planned and unplanned commitments. We get feature requests from clients and that’s our demand.
We must go out and design, build, test and deliver on all those requests. So, the question is, have we allocated the capacity to serve that demand? And the way that we allocate capacity here is through time blocking. We just use all of our individual calendars and we time block whatever time we need to serve any type of request. So, if a client comes to me and says, “Paul, I need you to go build this report for me.” Then I’m going to go into my calendar and I’m going to time block the time that I need to build that report in line with the client’s request. If they need it by next week, well then, I’m going to have that time blocked in a way that they get it delivered to them in that amount of time. So, this first page of the report is planned and unplanned commitments.
The planned commitments would be the feature requests that we have coming in, which can be found on one of our developers’ calendars as a future or current time block. What we’re trying to do here is align those two things. We take all of our feature requests and look at all of our calendars and find the requests that are pending and have a matching calendar meeting or event. And if there is a match, well, that’s a planned commitment, but if there’s no match, then it’s an unplanned commitment. Both of those are very important to us, of course, because that’s information that we can help make sure that we’re adding the resources that we need to meet client demand. It helps us with several different things. It can prevent us from having a task fall through the cracks. We again run Kanban boards and whenever we have a feature request get into the development column, which is the point of the process where developers should be working on or have that work planned, we look to have a card in development. If we don’t have it on a developer’s calendar, well, that’s a risk. We don’t want to have a situation where we’re not delivering on the client’s expectations. It’s important that the practice manager can readily see that information.
This report page is laid out very simply. There are several slicers on it. And the slicers allow you to focus on the segment that you’re concerned with. You can select the client and development bucket. I talked about the development bucket being particularly important to us. At this point we should have the resources allocated so you can select the bucket or the phase of work that you are focusing on.
There’s other outlier situations as well. You could have a feature get into development, but for one reason or another, it might be blocked, or it could be placed on hold. So, we must have ways of labeling these features or at least having some way to segment that. There are other items as well, we must make sure that it’s a feature that we’re going to be working on. Sometimes we work alongside our client teams and some of our clients have resources that are taking on tasks as well. We have a way of identifying the tasks that are assigned to LeapFrogBI versus an external team. And then finally we can quickly toggle between planned and unplanned commitments just by a simple button. By clicking that button, you can quickly see all the commitments.
We also have features that are in development but are not planned. This is a new process for us. As we’re moving into this, it’s quite eye opening to look at how many commitments that we must meet with our team. We all know that we’re very busy all the time, but there’s nothing like looking at this report. We can see for example, that right now, we have 63 commitments in development that we need deliver on soon. 63 is not a huge number, but that’s the number of items that we should be working on right now. It’s very, very difficult to get your head around 63 different feature requests because every one of those requests have a lot of information that the developer needs to know, and the analyst needs to know. It’s quite difficult to really get your head around that many things that are in development at one time. This report is going to be, instrumental in helping our practice manager keep track of these things and make sure that we’re not allowing something to fall through the cracks.
As far as presenting this in information, it’s actually a very simple presentation. We just have a table with a set of columns on the left side of the table and a set of columns on the right side of the table. The left side of the table is the feature requests or the planner cards. Then on the right side would be all of the calendar events. If there is a calendar event that lines up with the card, well the calendar event is described. If there is no calendar event found, then it just says none. At a glance, you can just look at this and see some things that we need to pay attention to right away. You can see that there’s things that we need to have resources allocated for that we simply don’t and that’s a risk to us. It’s risk to our clients. So, this report is really going to be a game changer for us.
In addition to this table, we have three data visuals and they’re all three column charts. One of them is just showing the number of features by client. Each client is a different column on the chart. So, you can see if we’re looking only at planned work, we can see how much work is coming in that’s in development right now for a particular client. Then we break that down by the person that created the card. Finally, we break that information down in a third visual by who that feature is assigned to. Now all of these visuals are in Power BI if you’ve worked with these platforms, you probably are familiar with this, but they interact with one another. I can click on one of the columns in the “assigned to” column chart and that will automatically filter down all the other visuals if I design it that way, and that is the way that I designed it because you can imagine if you’re a practice manager and you see that you have a lot of features that are assigned to a certain person but don’t have calendar events assigned, well that might be a training issue. It’s easy to see when it’s in a visual form, like a column chart right there in front of you.
So again, this first page is all about understanding the commitment. Have we allocated the resources to meet the commitments that we’ve made or have we not? And how can we stay on top of that when you have so many commitments coming and going on a daily, if not by the hour basis. This report will be critical in helping us keep track. Ultimately, they help us serve our clients better. It also helps all the developers reduce their level of stress. The developers and the analysts are not going to want to feel like they’re not meeting the requirements and now they can have a safeguard. They know that if they don’t get something on their calendars, they will be able to find it without having to go look through every client board and figure it out on their own. That’s the first page, all about planned and unplanned commitments.
The second page and final page in this report is just called “card count by bucket”. Now let me just describe a little bit about how we set up our Kanban boards. It’s pretty much the same for every client that we serve. If you’re not familiar with the Kanban board, it’s just a board that has lanes in it or in our case, columns. Each column being a queue, all of the cards in a queue represent some type of a request. For example, the first column is a backlog. The backlog is where we put feature requests that our clients give us that we’re not ready to start collecting requirements on. We’re not starting development or anything like that. They’re just an idea that our client has something that we eventually want to get to. And we allow the clients to prioritize those cards top to bottom with top being the highest priority.
The next thing that happens, is we move a card that’s the top priority into Requirements. And of course, all the cards that are in Requirements will be listed in that column. After we have requirements on our card, then we move it into “On Deck”. On Deck is where all of these feature requests live until we have the capacity to move them into the development phase. Development is the next column. You get the idea; we’re moving from left to right. All the way from Backlog to Requirements, to On Deck, to Development, to Testing, to a queue for promoting to Production and then finally into Validation. I describe all of that because that’s important to us. This is how we run our business. We’re serving many clients and we’re trying to do so in a consistent way so we can keep our quality up and ensure we’re serving the clients well. We want to make sure that our developers and our analysts have what they need as well. Structuring this report in a way that aligns with our business process was a no-brainer. The goal of this page was to understand where each of these features live within that set of queues that I just described that are on all of our client boards. That’s very important to us because we can’t go to all of our client boards, every hour and figure out, what’s the state of that board because our clients could be working on the board. They may be adding cards or moving cards. Plus, our developers are always going to be moving cards, and commenting on cards, etc.
It’s important that we have something that can go and collect that information for us. It can give us the information we need and the kind of information we need. Do we have any clients that don’t have anything in development? If they don’t, is there stuff on deck that’s ready to go? Why don’t we have it in development? That’s something that we need to know. Do we have any clients that don’t have a healthy backlog? The backlog is a list of things that we are hopefully going to deliver for our clients. Larger clients typically have more cards than smaller clients. But if you don’t have any cards in the backlog, we don’t have any insight into what we’re doing past the next couple of weeks. Then of course, do we have clients that don’t have any cards at all? What’s going on there? Is the client on hold for some reason, we need to understand those things as well.
Let me describe how this page is set up. Basically, it’s laid out in the same exact way as I described on the first page, which is planned and unplanned commitments. All the slicers are across the top. It’s the same slicers. Most of them are synced. So, if you change it on one page, it automatically changes it on the other. You can slice by client, by bucket, by label completeness, whether it’s assigned to our LeapFrogBI resource and whether the work is planned or not planned. Then the main portion of the report is actually a matrix. Each row represents a client, and each column represents one of those queues that I just described on our clients’ Kanban boards. You have a column for Backlog, Requirements, On Deck, Development, Testing, promotion to Prod, and Validation. Then the cross section tells us how many cards currently live at that point. When I first built this, it was just amazing to see this. Even though we work on these boards all the time, I have never seen on an aggregate level across all our clients where these cards are living. And now we’re talking about not just 50, 60 cards, we’re talking about all the cards, not just the ones that are in development here. We’re talking about nearly 1,000 cards, which is, as you can imagine, there’s no way in the world that any one person could ever get their head around 1,000 feature requests.
This report is not going to give any one person any deep insight into what those requests are. That’s not the intent of this report. The intent is simply to say, you know, where’s the work? How do we stand currently with all our clients? Again, do we have something on deck, but not in development? Do we have a lot of stuff in testing and why? Do we have a lot of stuff on hold that’s in promote to prod? There’re all these different things as you look at this information that just jump right out at you, it does to me and the same would be true for any of you out there that are trying to manage these types of processes. When you have this information just presented to you in this clear of a manner, it’s eye opening. It leads to so many more questionsa, so many more ways that you want to look at this information. The matrix is the main part of the visual. Then we have a stacked column chart that has the same exact columns I just described. They represent the queues within our Kanban boards, Backlog, Requirements, On Deck, Development, Testing, promote to Prod, and Validation. And the stacks represent who the card is assigned to. So, the more cards someone has assigned to them, the larger portion of the stack that they will represent. This is important because it gives you a visual cue about this information that I had never seen this before. For us, we’ve got a large backlog of features that have been requested and that’s the far left of this stacked column chart. Then it tapers down all the way through Requirements, On Deck, Development. It’s just tapering down with each queue until you get to the validation column and then it picks back up. The validation column gets a little higher, because of a business process. We don’t complete cards until we meet with the client and the client says we met the need, we validated this. Then, we can close that feature out. It’s been delivered successfully. We have cards that sit there as a holding process until that conversation has been had. The stack column chart is nice. It’s a visual that I’d never seen before, and I just love it. It is interesting to see. I’m so interested to watch this now, as we learn about this information and figure out what should we change about our business process so that this is a more efficient operation.
We added a couple of other visuals to this page as well. We’ve got one donut chart that just shows the breakdown of the different buckets. So, you can see; for example, that 47% our cards live in the backlog. While on the far other end of the equation, about 16% or a little over is in the validation column and in development. So, development would have about 8%. At any one time, we’ve got about 8% of our cards, at least right now in development. And then we have another vertical bar chart that, gives you a visual representation of the queues and how many cards are in them. This time, not broken down by who it’s assigned to, just an overall breakdown of these different buckets.
One more thing came up as I was working on this report. We did have a client with no cards at any bucket on the Kanban board. So that basically means that that client wouldn’t be represented in this chart. They could be, I could have shown the clients even if they have no cards. But, I didn’t really want to go that route. You can drill into the client and underneath the client you can break down the client counts by assignee, and you can drill under that and see the individual card. It’s a little more complicated, but I didn’t want to go the route of just showing everything regardless if there was any information to be represented. So that made it important that we have this other section that shows clients that don’t get represented anywhere else in this report. So that’s the second page. And again, the second page I called “card count by bucket”.
It just gives a practice manager an understanding of the overall practice level demand. Which is something that before this report, we didn’t really have a good way of viewing. We’re meeting with clients every week. We’re running the boards every week and we’re time blocking, but it’s different now. When we look at this on an aggregate level, it gives us a much different view of things.
Now, I did put one more page in. I said two, but there was three, but the third one is just a glossary. I shouldn’t say “just” because I started doing this with a number of the clients that I work with by just adding a page for a glossary. The glossary just describes some basic information about the report. Like how often is it refreshed? Where is the data come from? There’s a couple of business rules in here. Communicating what those rules are, is important for the person viewing the report to understand. One of the most important ones is what I called “Is planned”, which is just a flag.
I mentioned before that we want to be able to toggle between commitments that are planned or unplanned. Well, how do we determine that? And it’s a combination of things that we require for a commitment to be considered planned. We need to find a matching meeting on somebody’s calendar. We need to make sure that meeting is not in the past. It needs to be something that’s either happening now or in the future. It needs to be a meeting that’s in a busy status. It can’t be a tentative meeting. It can’t be a free time or out of office, you get the point. It needs to be a busy meeting and it can’t be an all-day event. We don’t consider all day events in our time blocking to be time blocked. We want to put down a particular duration for a particular feature that’s going to be worked on. We don’t allow this to be an all-day event.
In the glossary, we have a couple other things like making sure our bucket names are conformed, etc. But the point of the glossary page is just to make sure that the user, understands what they’re looking at. The user is the person using this report, which in our case is going to be the practice manager, me, or it’ll be the developers. There’ll be a lot of people using this report. It’s very important that they understand what they’re looking at and this glossary can go a long way to make it the reality.
Ok, so that’s a little bit about the report. I want to say a couple of things in addition here,as I was building this thing, I’m now kind of in this process where I’m thinking about next week’s podcast and I’m taking notes. And I took a few notes while I was doing this and the first thing I would say is that when you first put a report , it is never perfect. I mean, it may be perfect from the standpoint that it validates perfectly. But I’m talking about perfect from the standpoint of meeting the business need. What I mean by “meeting the business need” is for example, we use Microsoft Planner to run our Kanban Boards and I was doing some validation and when I got past that point, I was starting to look at the report and understand what’s going on. And then I realized that I have a time block on my calendar next week that’s not represented as planned work in that planned and unplanned commitment page. When I started to look at it, my first thought is “is there a technical problem with this report?” But I found no technical problem. And then I went and looked at the actual plan and the plan itself had not yet been shared with the account that is doing this data collection. It’s a brand-new client that started in January. It’s just something that we haven’t done yet. That was just through using the report, even though it was working technically correctly. I had not collected all the information that I needed to figure out what was going on in that case. That was kind of interesting.
Another thing that came up was another meeting that I had scheduled with one of the developers next week. I was showing up as being planned for the commitment, but that other developer was not showing up as being planned. I went and looked at it and I figured out that the developer had not yet accepted the meeting invitation. So that meeting invitation was tentative. The report is set up to only include calendar events that are considered busy. So, that’s just learning something about the report. It’s not a technical problem with the report. It’s exactly how I want it to behave. I don’t want it to say that if someone is tentatively planned on doing something that that’s is a commitment. That’s not a commitment. You need to mark it as busy; you need to have a proper duration and it needs to happen in the future.
Things like that were pretty interesting to learn. The point being that this is a process that’s constantly improving. I’ve been working on this for maybe a week or two and I’ve probably edited these visuals, selected the type of visualization, and edited the interaction. Early on I ditched one approach to the whole thing and took a different direction all in. So, it really takes a little bit of time to get your head around these reports. Sometimes you just have to get the information in front of you and see what’s going to work. At least that’s the way it is for me. I like to look at it and then figure out, what makes a lot of sense and then run with that.
Now the last thing I want to mention is this is all about delivering on a value opportunity. I describe this early on where I’ve said that basically if we wanted to have someone go out and do this stuff by hand, it probably costs us $100,000 a year if not more. More importantly, it’s going to bottleneck our business. We’re not going to be able to succeed unless we can find a way to automate some of this stuff. The value is there for us to capture. So, this report will help us capture that opportunity. But for that to work, it must be embedded in our business processes.
So, one of the things we’re going to do between me, and the practice manager is have a company meeting. We’re going to sit down with all the developers. We’re going to talk to the entire company. We’re going to look at the processes, we already have in place. We’re going to make a couple of little tweaks that need to happen. We’ll say this is what we’re managing too. This is how we’re going to serve our clients well. And this is how we can play our part. All of us must time block, not just 50% of the time, but we’ve got to time block every single commitment that we make. And when you do that, it’s a wonderful thing. When we’re talking to a client and we’re wanting to deliver on some feature request, we can’t really know when we can do that work unless we’ve time blocked. Unless your capacity has been allocated for all the other commitments that we’ve made and you can look at that and say “next time I can do this, or the next time that a developer can do this, is at this point in time…”. You’re just not going to be able to make an accurate estimate there. I think that’s what puts a lot of pressure on people, they unknowingly overcommit. A time blocking process goes a long way toward preventing that situation. Nobody wants to work through the weekend because they’ve overcommitted. At the end of the day, we’re going to deliver. If we’ve made a commitment, we’re going to deliver on that commitment no matter what. So it’s best that we have our time blocked off and we’re tying that to each of the commitments that we made.
That’s a little long winded, but I just want to say that we are going to embed this entire report within our business process. Everybody will have access to the report. Everybody will understand what they need to do in order to meet the expectations. And everybody will know how to find the exceptions. If there’s cases where we have an unmet commitment or an unplanned commitment, how do we see that without having to go through everybody’s schedule and everybody’s board. This is how you can do that now. And there’s probably a dozen or more such business processes or questions that can be answered just from these couple of pages in one simple report that took a week or two to pull together. This report will evolve. It’s a constantly evolving process.
