Negative sales targets and percentage commissions

A while ago, I received an email from a distraught salesman. He believed his sales commissions were wrongly calculated, and asked me to shed some light.

Note that I’m not using the exact numbers he gave in his email.

The story goes that Michael (as I’ll call him) and his colleagues were given sales targets that were negative. How could sales targets be negative? Shouldn’t you be trying to sell something? The reason given was that the current economy was disastrous, and basically each sales person was trying to not lose sales.

You’re gonna bleed. It’s how much you bled.

Anyway, given Michael’s negative sales target, he managed to exceed it. He didn’t manage to bring in sales (positive sales numbers), but he didn’t lose too much money (slight negative sales numbers). But his sales commissions didn’t reflect that.

Now I’m not going to discuss how that works out. I can’t presume to understand the business logic behind the sales commission in this case, but I’ll discuss the mathematics behind the numbers.

The normal sales targets and commission

Let’s say your sales target for this month is $1000. This means you’re expected to sell about $1000 worth of products or services. We’ll ignore the condition that you will get some commission based on what you sell, regardless of how much you sold (my brother’s a sales person), as well as other types of commissions.

Let’s say the sales commission is based on how much extra you sold beyond your sales target. Makes sense, right? Let’s use simple percentages.

If you sold $1100 worth of products or services, then your percentage commission might be calculated as follows:
(Difference between Your Sales and Your Sales Target) / (Your Sales Target)

Or ($1100 – $1000) / ($1000) = 10% commission.

This is assuming that your sales amount exceeded the sales target, of course.

The case of negative sales targets

Now if the sales target is negative, as in Michael’s case, the mathematical formula still applies. But you have to note the negative sign. For some reason, “business” people (no offense to business people) tend to see -4567 as larger than 12, even though 12 > -4567. They see the magnitude first, not the value itself. (It’s also why I get emails about calculations involving negative numbers… anyway…)

Let’s say the sales target is -$1000. Everyone’s expected to lose money, but you try not to lose more than $1000. At least that’s what I’m interpreting it as.

Let’s say Michael managed to lose only $50. Or -$50 to be clear. The formula
(Difference between Your Sales and Your Sales Target) / (Your Sales Target)

have to be modified to this
(Difference between Your Sales and Your Sales Target) / (Magnitude of Your Sales Target)

In maths and programming terms, the “magnitude” part refers to the absolute function. Meaning you ignore any negative signs. Actually, the modified version works for the normal case too (which is why you should use it for the normal version anyway to take care of weird cases like this but I digress…).

So, we get (-$50 – [-$1000]) / abs(-$1000) = $950 / $1000
= 95%

Actually, you should use this:
abs( [Your Sales] – [Your Sales Target] ) / abs(Your Sales Target)

That’s the “foolproof” version. Consider it a bonus for reading this far. Frankly speaking, any competent programmer should be able to come up with that formula, even without much maths background. You just need to think about the situation a little carefully (ask “what if?” more often).

Michael’s calculated commission

When Michael wrote to me, he said his commission was calculated as follows (given that he only lost $50):
-$50 / -$1000 = 5%

Let’s say someone else lost -$900 that month. With the above calculation, that person gets:
-$900 / -$1000 = 90%

Clearly it makes more sense to lose more money! This was why Michael wrote to me.

I don’t propose the method I gave is correct, business-logic-wise. Michael didn’t give me any details on what he’s selling, or what his company is (or even why it’s acceptable to have negative sales targets, regardless of the economy). So I cannot give any help other than from a pure mathematical point of view. But I hope it’ll at least give Michael a fairer commission amount.

Questions

Given Michael’s situation, what do you think is an appropriate calculation formula?

Can you think of (or know of) a realistic situation where a negative sales target is acceptable? I say “acceptable”, but seriously, no company should “accept” that they lose money every month.

Negative numbers in business reports

Some of my articles here aren’t what you call mainstream. They’re just different. I think a lot of them weren’t that big a deal, but it turns out some people find them useful. Here’s one that I get queries about: negative numbers in percentage calculations. The general question distills to: “There are some products, and one or more has negative contribution. How do I display percentage contributions?” I’ve gotten enough queries about it that I don’t want to keep explaining it. Hence this article. Before I talk about that, here’s the back story why I wrote the original article. If you want to know the display solution, skip ahead.

A few years ago, I was working in a team, whose most important project then dealt with revenue sharing. Our company offered a service, but we don’t have content (ring tones, movies, tv shows). The content providers have content, but don’t have marketing and distribution leverage. So partnerships formed.

What happened was customers signed up with us (the company I worked for), getting the content they want. Monthly subscriptions (television shows) and one-time payments (pay per view, ring tones). We charged the customers for the content use. Then we split the profit with the content providers, in a process we termed as “settlement“.

My users (the company product manager and team) used to have only an Excel spreadsheet to help. That was when the split was simple, like 30/70 (we get 30%, content provider gets 70%). Then the number of content providers grew, and certain rules came in (like minimum payment. I’ll talk about this in a bit), and the users needed help. So they contacted us (my department was IT support for the billing departments).

“Ok, so where are the negative numbers?” you ask. “None of the values should ever be negative.”

I hear you. My experience in the billing department also taught me that just because a number is positive, doesn’t mean it’s good. My very first task in the job was look at financial reports. In particular, financial reports on debt. All the numbers were positive. But they were all debt. I’ve seen debt sum totals of 8 digits. It’s scary, and it’s also made me a little blasé about money. It’s not that I hate money or don’t care about money, just that big figures don’t shock me as much (hey I still need to eat).

Anyway, back to the content provider story. The typical situation for a negative number is adjustments. For whatever reason, we’ve paid out more to the content provider than was correct (a waiver, a price change we didn’t detect, a product was retracted before we could do something). So in the next settlement cycle, we put in the adjustment, effectively reducing the amount we pay to them to adjust for the “mistake” for the last settlement cycle.

Let’s talk about minimum payment, to show you how complicated the settlement process could be. It’s a business rule that some of the content providers want. If for a particular month, the profit for them is below a certain number, don’t bother sharing with them. Accumulate the profit, rolling it over to future months until it hits the minimum requirement, and then send them the profit.

Why would content providers not want profit as soon as possible? Usually as part of the minimum payment rule, they get the minimum payment profit share in the first month. Let me give you an example, say the minimum payment is $400 and that the sharing is 50/50. In the first settlement month, we will pay out at least $200 to them, regardless of the number of customers signing up for the service. Of course, if the profit to them is more than $200, we pay out more too. So if the content wasn’t doing well, at least the content provider had some cash (which was guaranteed by the minimum payment) coming in on the first month. There are other reasons, such as the company is too big to want profits of low digits every month (!?!?! Yes, it happens…).

There are other business rules too. Such as withholding tax. What happens if the content provider is foreign (that is, non-Singaporean)? And there’s local tax (there was a transition from 3% to 4%, to 5% and to the now current 7% tax, the GST or Goods and Services Tax in Singapore).

The whole point is that the settlement process became fairly complicated (remember it started out as an Excel spreadsheet). So, well, uh, mistakes could happened (and did). Let’s just say I helped the user with correcting the data using Excel and database update statements. These corrections weren’t “standard” enough so they couldn’t be programmed into the settlement software. I became quite good in Excel data manipulation. Let me preempt you. I’m not manipulating numbers in the sense of fraud. It’s because the adjustment input/data couldn’t be easily added as part of the standard input process. I’m hijacking the process so that all the data required is correct and is there for the settlement calculations to do its job. The first few days of the month were always exciting for me (millions of database records, close deadline, Excel files flitting between me and the user). So to correct the wrong numbers in previous cycles, adjustments were made for the current cycle.

To make things even more complicated, the content providers want a breakdown of their profit by products. For example, if SonicToons received a profit (after the settlement split) of $120, they wanted to know that HotJamming made them $60, CrystalMood $40, and ViolinClassics $20. For analysis purposes, this meant the product HotJamming was hot, so they should make more products like that.

If you add negative numbers to that mix, it gets fun mighty soon. Do you set the negative numbers as a separate item? Do you split the negative value among all the products? It’s just hard. And so, that’s the reason I wrote the percentage calculation article, because the users (and the content providers) wanted a breakdown of the various product’s profit contribution.

So somewhere in that period of working on the project, my colleague asked me to help with formulating the rules of calculation. You know, because I studied maths and all. One of his problems was assigning the last single cent being shared among the products. I told him the solution took up an entire semester’s work (it’s called operations research) in university. He was stunned. Then I gave him a simpler alternate solution. This article is already very long, so I’ll tell you that solution in a later article.

The display solution

Well, you made it. You’ve skipped over several hundred words of back story. Congratulations! Let me expand on the general question first. Your company has two products. Product A made a profit of $200, and product B has a loss of $300. So in total, your company lost $100 ($200 – $300).

According to the calculation method in my original article, the percentage contributions for each product is as follows:
Product A contributed abs(200) / ( abs(200) + abs(-300) ) * 100% = 40%
Product B contributed abs(-300) / ( abs(200) + abs(-300) ) * 100% = 60%

So product A and product B contributed 40% and 60% respectively to your company’s bottom line.

The confusion of the commenters to that article and the readers who emailed me sets in. How can product B, clearly a bad investment, be contributing more?

The confusion is due to the business terms pre-assigned to the values. I will assume that you want your company’s bottom line to be of growth. However, that doesn’t mean the term assigned to it has to be “growth”. Of course, it didn’t help that “percentage contribution” implied a positive aspect.

For example, you don’t state in the business report:
Net gain = $200

That can be a line in the report, or an Excel column. I will suggest a slight change in terms.
Net change = $200

Or even just
Net = $200

You don’t have to use the word “change”, but it should be something neutral without proposing a positive or negative direction. So “change” is better than “growth”, “gain”, or “loss”. But don’t use “net delta” either. They won’t understand it…

“Net profit” is complicated. The reader of your report should be open to the idea that a number under “net profit” can be negative. The word “profit” typically invokes a positive growth mindset. But if your report’s readers (or the accounting department dictates it to be so) are ok with it, then go ahead and use that.

So in interpreting our example, product A contributed 40% to the net change. Yes, it’s a positive growth (+$200), but it’s just not positive enough. Product B contributed 60% to the net change. Because it’s a negative (-$300), and it’s the highest contributor, it caused the net to be a loss.

A blog reader, Jasper recently emailed me on this. Here’s an extract from my reply:

In terms of rewarding growth samples and punishing loss samples, calculate the percentage contributions as above. Then separate the samples by the sign of their original numbers, then sort by percentages. Let me illustrate:

Growth samples (positive sign)
2 (20%)

Loss samples (negative sign)
-4 (40%)
-3 (30%)
-1 (10%)

This way, you can see which samples do well, and which didn’t. So even though -4 contributed the most (40%), it’s a bad sample because it’s negative.

Don’t pre-assign terms to the values in the business report. Let the values (and the sign of the values) speak for themselves.

Percentage calculation with negative numbers

Suppose you have 2 numbers. You want to sum them up, and calculate the percentage of each number upon that sum. Say, 4 and 6. So 4 contributes 40% and 6 contributes 60% to their sum 10.

What happens when you have negative numbers?

I did some simple research, and the relevant articles have someone trying to calculate percentage changes from one number to another. Like financial growth reports.

My question is more of, how much each component number contributes to the sum, as a percentage. The problem comes when one of the numbers is negative. Consider the trivial case, 1 and -1. The sum is 0. You already get a division by zero error when calculating the percentage (1/0) * 100.

The solution, which is the same as that in my research, is to take the difference between the two numbers and use that as the basis. So difference of 1 and -1 is 2. So 1 contributes (1/2)*100 = 50%.

What about -1? Use the absolute function. ( abs(-1) / 2 ) * 100 = 50%.

The difference method works fine if you have only two numbers. What if you work with several? My friend actually posed this question to me. Suppose you have 6 numbers, and you want to calculate the percentage contribution for each number towards their sum.

My suggestion? Absolute everything. The percentage contribution of n1 is
abs(n1) / ( abs(n1) + abs(n2) + abs(n3) + abs(n4) + abs(n5) + abs(n6) ) * 100

Then my friend posed a killer question. What if all the numbers are zero? What’s the percentage for each number then (even though each number is zero)?

It’s for a reporting application, and my friend was wondering how to calculate the percentages. Now the sum of a bunch of zeroes is also zero. You hit the division by zero error. Even the absolute-everything method fails, since each number is zero, so there’s nothing to “absolute”.

Since there’s no defined way of calculating when all the numbers are zero, I gave the 2 obvious solutions. The first is that, since each number is zero, and the sum is zero, therefore each zero contributed 100% to the zero sum. The second is, since each number is zero, therefore each contributed 0%.

My friend chose the second solution. The most compelling reason for that choice was that it’s easier to explain the logic behind that choice to the user. It’s an edge case. When there’s no right answer, choose the answer which is easier to explain.

UPDATE: Steve has given 2 more alternative solutions.

UPDATE: I wrote an article to explain some of the confusion by some readers. How can a poorly performing product contribute the highest percentage in a company’s bottom line? Read the explanation here.

If you enjoyed this article and found it useful, please share it with your friends. You should also subscribe to get the latest articles (it’s free).