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.

Comments

  1. Narcisse Adjalla says:

    Hi Vincent, another approach is to use signed percentage as follow.

    Suppose you have 3 numbers (n1,n2 et n3), and you want to calculate the percentage contribution for each number towards their sum.
    For n1 : n1 / ( abs(n1) + abs(n2) + abs(n3))
    For n2 : n2 / ( abs(n1) + abs(n2) + abs(n3))
    For n3 : n3 / ( abs(n1) + abs(n2) + abs(n3))
    Total contribution : (n1 + n2 + n3) / ( abs(n1) + abs(n2) + abs(n3)).

    The above approach represents signed contribution of each numbers to the change (“market change”). Positve number and negative number contribute respectively, positively and negatively this the right weight.

    The total contribution will be 100% or -100% if the numbers have the same sign. Otherwise, the absolute value of the total contribution will be under 100% (and that the problem of this approach). But the sign of the total contribution determines if you are losing or are growing.

  2. Hey thanks Narcisse, that’s easier to display and explain than mine. I didn’t think of the “total contribution” stat. I guess it depends on what the business users are looking for. Otherwise, we can make up all kinds of stats.