Revenue sharing and operations research – part 3

This is a mini-series on how revenue sharing and operations research are linked. You might want to read part 1 on the specific business problem I was solving, and part 2 for the mathematical formulation of the problem. In this final part, I’ll tell you what was the solution eventually used.

First, although I said it was basically an assignment problem in part 1, on further thought, I don’t believe it is. What I was thinking was “how to assign that $0.01 such that there are no rounding errors”. Thus the “assignment” part. I apologise for any confusion.

Second, I said the financial/maths formulation was an integer problem. But the values are money values with decimal values, right? How can it be an integer problem? Because it has to also be correct up to 2 decimal places. That means fixed arithmetic. Therefore it becomes an integer problem. Just think of the values as cents (instead of dollars correct up to 2 decimal places).

Now, if you’ve read part 2 (and I applaud you if you actually sat through that mass of maths formulations), you should have guessed that using operations research to solve the business problem was not advisable. It might not even be suitable.

However, the problem still needed to be solved. How do you get rid of any extra or missing money?

More maths…

Going back to the example I gave in part 1, there were 3 products with revenue to be split between 2 parties. So there were 6 parts. If each part generated a rounding error of +$0.01, then there was a maximum potential difference of $0.06 between the original revenue to be shared and the sum of the parts after revenue sharing calculations.

I remind you that whatever solution I came up with had to make mathematical sense and financial sense to the programmers and the users. There are actually 2 goals:

  • To have the sum of the parts be equal to the original revenue amount
  • To have each part’s amount (after rounding) be as close to the calculated value as possible

The 1st goal ensures no summation errors. After revenue sharing, there are no extra or missing money amounts. This part is actually easy to fulfill. The 2nd goal is fulfilled with a bit of adjustments. So here’s the solution.

The easy-to-understand maths solution

We split the revenue accordingly to each part first, rounding each part’s amount to 2 decimal places. Then we sort each part in ascending order. Then we sum all the part’s amounts together. If there’s a discrepancy, we correct the discrepancy by adjusting the largest amount. This calls for an example.

Here’s the original example used. Total revenue for the 3 products are:

  • ProductA: $63.13
  • ProductB: $20.75
  • ProductC: $16.12

Assuming a 30-70 percentage split, we have:

  • ProductA: $18.94 (us), $44.19 (them)
  • ProductB: $6.23 (us), $14.53 (them)
  • ProductC: $4.84 (us), $11.28 (them)

Sorting all the parts in ascending order, we have:

  • ProductC: $4.84 (us)
  • ProductB: $6.23 (us)
  • ProductC: $11.28 (them)
  • ProductB: $14.53 (them)
  • ProductA: $18.94 (us)
  • ProductA: $44.19 (them)

The sum of the parts’ amounts is $100.01, which is not equal to the original revenue being shared ($100). The discrepancy is a +$0.01. So we adjust the largest amount. Specifically, we deduct $0.01 from the largest amount (because our discrepancy is positive).

So the revenue share for the content provider for ProductA becomes $44.18, and thus the sum of the parts become $100.

This method ensures that the sum of the each part’s amounts is still equal to the original revenue, which is very important (because this is a financial operation!). This satisfies the 1st goal.

And for each part, the amount is rounded to the nearest 2 decimal place. So each part’s amount is as close to the calculated split value as possible. The only exception is the largest amount might be off a little.

Now I chose the largest amount to “absorb” any rounding discrepancy precisely because it is the largest amount. Note that the term “largest” refers to the magnitude, so if you happen to deal with negative values (it happens, even in financial situations. Consider debt as an example), use the maths absolute function to do the sorting.

Any discrepancy can be mathematically shown to be at most equal to (number of parts) multiply by $0.01 (rounding error).

D <= ± (N * $0.01) where D is the discrepancy and N equals the number of parts. Note that the discrepancy is bounded, which is the mathematical way of saying it has an upper and lower limit (or bound). Note also that in a fraction, a larger numerator means a larger fraction and a smaller numerator means a smaller fraction. A larger denominator means a smaller fraction and a smaller denominator means a larger fraction.

Now, whatever the discrepancy value is, it is bounded, it is fixed, and it is a small value. If we want any amount to “absorb” this discrepancy, then the larger the amount, the smaller the resulting error fraction or error percentage.

For example, if the discrepancy is $0.01 and the amount is $1, the resulting error percentage is 1% ($0.01 / $1.00 * 100%). If the amount is $5, the resulting error percentage becomes 0.2% ($0.01 / $5.00 * 100%).

Suppose the discrepancy is $0.02. We could spread the discrepancy error among the largest 2 amounts, each amount absorbing $0.01. But this makes the programming a little more complicated than it is. Also, it makes the algorithm a bit “dynamic”, which makes tracing any calculations by a programmer or user difficult.

Implementing it in code

All the revenue amounts were stored in the database. Because of this, I recommended that any revenue sharing calculations be done within the database environment itself. Namely, with stored procedures running SQL statements.

Benefits of using stored procedures within the database environment:

  • Can sort values easily (use the SORT BY clause)
  • Can handle dynamic number of values (with temp tables or the original tables)
  • Some calculations can be grouped into a single UPDATE statement
  • All the values are in the database!

The last benefit means there’s little switching of context from the database environment to the… whatever environment. In that project, it was scheduled Unix shell scripts combined with C programs that called the stored procedures. We didn’t want the context to switch back to a Unix environment to do calculations. Doing calculations in the Unix environment with C might be fast, but there are many content providers and many products. The context switching might eat up any performance benefits. Besides, having the calculations in a few stored procedures mean better modularity and separation of functions.

Further considerations

Because we’re in the business of … uh, doing business, we might want the customer to have a better deal. Or at least an easier report to read.

In the method above, we sorted the amounts in ascending order, regardless of whether it’s us or them. So it could well mean that the largest revenue share of the content provider be used to absorb the discrepancy.

This might mean when they read the revenue sharing report, they might question why that amount is $44.18 instead of $44.19. It might be just $0.01 to you, but it’s still money!

What we can do is sort the amounts by theirs first, then ours. And within each, sort by ascending order. So we could have this instead:

  • ProductC: $11.28 (them)
  • ProductB: $14.53 (them)
  • ProductA: $44.19 (them)
  • ProductC: $4.84 (us)
  • ProductB: $6.23 (us)
  • ProductA: $18.94 (us)

In this case, we adjust our revenue share for ProductA to be $18.93 (instead of $18.94) so the revenue sum is correct. Basically, we absorb any discrepancy, using our largest revenue share amount.

And that’s the end of the discussion of revenue sharing: the business part, the maths part and the implementing/programming part. Let me know if you have any questions.

Revenue sharing and operations research – part 1

I’ve been putting this one off for too long. First, it was a problem I encountered when I was employed, and I had to be careful about what I wrote. Second, it’s one of those articles where it’s a mix of mathematics and programming, and in this case, business as well. You might not know it, but I get a headache categorising articles like these… that’s why I just dump them into a generic category. Third, fully explaining the problem and the solution makes a long article.

You still here? Good.

I will write this in 3 parts. I’ll explain the business problem first. In the 2nd part, I’ll tell you why mathematics was involved, and how it eventually wasn’t involved because fully implementing the maths model in code wasn’t advisable. Also, I will then be the only one who can understand the code (because of the maths concept). In the 3rd part, I’ll tell you about the solution used (something simpler that other people could understand).

Ok, let’s get to it.

Revenue sharing

I have to tell you about the business situation first because the term “revenue sharing” means differently depending on the context. In that particular job, I was employed by a telecommunications company. One of my users dealt with content providers, companies who create content such as ring tones, downloadable phone backgrounds (I think?), songs, and eventually included videos-on-demand.

So we (the telecommunications company) charged the end users (people like you and me) based on the content consumed, which was included in the phone bill. The content providers needed us mostly because they don’t have the distribution (number of users) or because of the billing platform (the phone bills).

A parallel would be the Apple App Store. Apple charges the consumers, then gives a cut to the content providers (the app developers).

In the beginning, my users used a simple Excel spreadsheet to do the revenue sharing calculations. Most of the contracts were simple percentage splits, such as 30-70 (as in us 30% and content providers 70%). As the number of content providers and products (from the providers) grew, the simple spreadsheet was proving inadequate. Moreover, the spreadsheet solution was the limiting condition for approaching other content providers to partner with us (and thus growing the business).

There were tax considerations because overseas and local content providers were taxed differently. Withholding tax was a slight headache. The Singapore goods and services tax was a nightmare then, because at the time, we were moving from 3% to 4% then 5% (it’s now 7%). Some content providers wanted minimum sum conditions (“Don’t share revenue with me unless it hits at least $X per month”). Also, for internal accounting purposes, the Singapore dollar equivalent must be available (there were overseas content providers, remember?), so the currency exchange rate must also be used. Like I mentioned, the spreadsheet wasn’t working too well at that point…

Eventually, my users came to my department for help. Basically, we were to write up a software system to do those revenue sharing calculations for them every month.

“Uh, so what’s the problem?” I’m getting to that.

Rounding errors

In the report to the content providers, the exact breakdown of each product must be given. This presents the possibility of rounding errors. Let me give you an example.

Let’s say for a content provider, the total revenue is $100 for that month. Let’s use the 30-70 split. Also, there are 3 products involved. Here’s the total revenue breakdown for the products:

  • ProductA: $63.13
  • ProductB: $20.75
  • ProductC: $16.12

Splitting revenues, we have (rounded to the nearest 2 decimal point because of currency considerations):

  • ProductA: $18.94 (us), $44.19 (them)
  • ProductB: $6.23 (us), $14.53 (them)
  • ProductC: $4.84 (us), $11.28 (them)

Now, let’s get the sum total revenue shared.
We get $18.94 + $6.23 + $4.84 = $30.01
The content provider gets $44.19 + $14.53 + $11.28 = $70.00
(note the split for ProductB for the rounding “error”)

But $30.01 + $70.00 is not equal to $100! Therein lies the problem.

I did research on rounding. There’s “normal rounding” (1.14 becomes 1.1, 1.15 becomes 1.2). There’s rounding half up (1.15 becomes 1.2, but -1.15 becomes -1.1), and rounding half down (1.15 becomes 1.1, but -1.15 becomes -1.2). There’s even banker’s rounding.

The department I worked in supported users by providing programming assistance to financial problems. Let’s just say an extra $0.01 or a missing $0.02 causes great panic throughout the team…

So I was consulted on how to solve this problem. This is basically an assignment problem. Which are problems addressed by operations research, which I took a full semester’s course on.

Let’s just say my colleague was not pleased that we might have to implement a full-blown mathematical model just to do revenue sharing. In the next part, I’ll tell you how a maths model might be used, if it was implemented.