If We’re 99.9% Accurate, Our Software Will 100% Not Work

Learn how a sophisticated Sales Commission Module was brought to it’s knees because of one field.

Transcript

This is Steve Pestillo from P2 Automation. Now unless you’ve been living under a rock, you know Microsoft CRM product is way bigger than just a CRM. Microsoft CRM, which is now called Dynamics 365, is a Power App. Power Apps is built on the Power Platform, and you can build just about anything with the Power Platform.

So here’s something we built. We built a Commission Calculation Module that runs within Dynamics 365 that takes sales data from the ERP system, compares it to goals, and figures out commission checks every month. This system serves about a hundred salespeople, and it’s a little bit more complicated than you might imagine. Let me explain.

So for starters, every day the system calculates how far we are through the year in terms of days. So today’s January 10th, so we’re ten days into a 366 day year. What is that percentage? We calculate that. And as you can see right here, we are 0.0268 through the year. Now if you break out your calculator and take 10 divided by 366, you’re not going to come up with 0.0268. Why is that? That’s because the system uses a seasonality system. We know we’re busier in certain months than others, so it actually figures out, percentage wise, how busy we are throughout the year and uses that to weight the calculation. And then we take that number, multiply it by the annual goal for every customer, and that comes up with a number of where you should be with this customer on January 10th. Now we obviously don’t micromanage the sales team and say “Hey, this customer’s behind, this customer’s ahead.” We use that number to aggregate all the accounts for each salesperson and add it up, and we use that number on various Power BI graphs.

We also have several rate tables for each of our locations. Commissions also weighted here, so if you’re ahead of goal, you actually get a higher percentage of a commission and if you fall behind, you get a lower percentage. In addition to that, we actually rebalance the commissions throughout the year. So you may be way ahead of schedule in January and way behind in February. The system rebalances your January and February numbers and comes up with a commission amount. And to add another layer of complexity, we have a new customer bonus feature as well. And that’s sophisticated enough to say you may not be the salesperson on this particular account, but you brought it in, so you can actually have multiple sales people on an account. So in this example, even though you see the sales team up here at the top, we could record a different sales team down here that brought the account to us. And to add one more layer, the system actually keeps track of changes to the sales team throughout the year over here. So if somebody goes out on maternity leave or leaves the company or there’s a new person or they do a shift throughout the year, whatever that management needs, the system can handle it. Now the commissions are calculated throughout the month using a series of data scripts. Most of the work is done with a tool called Data Synchronization Studio, which we love, and it is pristine, it runs flawlessly. Well, almost flawlessly. It’s 99.9% accurate, and what does that mean to me? That means it will 100% not work. And let me explain what happened.

I mentioned at the beginning of the video we have a year-to-date sales percentage of 0.0268. Well we also have a year-to-prior-month sales goal percentage, and why do we do that? Well imagine it’s April 15th, and you’re doing commission checks. Well you’re going to do them for January, February, and March. Now when are you going to do those? Do we force you to do that work on April 1st or on March 31st? No, of course not. You get to it when you get to it. So we actually keep track of how far we were through the year on, in this case, March 31st. And that’s called year-to-prior month. So you might imagine on March 31st, you’re about a quarter of the way through the year so it’s about 0.25, and then you factor in the days, the actual days, and the seasonality and all that, and it’s going to be plus or minus. But here we are again back on January 10th, and although the system’s run flawlessly throughout all of 2023, it didn’t run right this month. So what changed this month? Well the year changed. In my business, so much can happen on the first week of the year, you’ll often hear me talking about the first day of the year back in June. Well this is going to happen on the first day in January. And people usually look at me funny, like we’ll cross that bridge when we get there. In my head I’m already there, I know this is going to be a problem, so I try to get in front of it. Well we missed this one. But luckily, the person that uses the Commission Module, Connie, she’s pretty smart. She knew the numbers were wrong as soon as she looked at it, and she had a good feeling as to why. So she pointed us in the right direction. Still for us, there’s a lot going on behind this system. Where do we look? It’s like finding a needle in a stack of needles. It really wasn’t that hard, it took us about twenty minutes. It came right down to this field here. So, it’s January 10th and we’re calculating the commissions for 2023, so it’s going right through December 31st. So you don’t have to be a math wiz to figure out how far percentage wise are we through the year on December 31st. Well we’re 100% through the year, so there should be a 1 in this field. Instead, there was a 0 in that field, and that 0 told the Commission Calculation Module that nobody met goal. But we know that’s not true, so let me explain how that happened.

We calculate these numbers, and let’s go back to the April 15th example. So on April 15th, we calculate these numbers one month at a time. So on January 31st, we’re about 8% through the year, and to February about 16%, little less than 25% in March. And those numbers are identical, so on the last day of the month, of course they’re identical. And then on April 15th, we say “Okay, how far are we through the year on April 15th?” And we put that number in the year-to-date percent field. So except for the last day of the month, those numbers will never be the same. So what happened this month? Well it’s January 10th. There are no prior months in 2024. So the system said 0. One simple mistake that was fixed in about two minutes made the whole thing break.

Which brings me back to the point of this video. In my business, if I’m 99.9% accurate, most likely the system will 100% not work.

If you’d like help making your system work 100.00% of the time, reach out to us today at www.p2automation.com.

The post If We're 99.9% Accurate, Our Software Will 100% Not Work appeared first on CRM Software Blog | Dynamics 365.