Formula for Sales Disparity

alanj · #1 $Old$ October 23rd, 2009, 07:32 AM

Hello, one and all. I am a newbie on this forum. Can anyone help me with this problem?

I am a programmer writing a sales report for sales across three web sites. The aim of this report is to focus on items which sell well on one site but not on the others. I'm referring to this as "sales disparity," which I hope is good terminology. The main thing I need is a number to sort on (a "sales disparity index") which will bring out-of-balance items to the top. The formula needs to examine the distribution of sales across the sites, and quantify the distribution in terms of proportion, without letting bulk volume influence the result at all. For example, sales of 50, 50, and 50 are completely in balance and should go to the bottom, while sales of 5, 0, and 0 are completely out of balance and should rank high. There should be a progression from most out-of-balance to least out-of-balance throughout the report. I've tried several things, but volume always seems to creep into the picture. Any suggestions?

Wilmer · #2 $Old$ October 23rd, 2009, 09:56 AM

5 random cases:
1:100,0,0
2: 80,5,0
3: 5,0,0
4: 5,5,5
5: 100,100,100

2 worse than 3 because 80-5 = 75 and 5-0 = 5 ?

4 and 5 as shown, or the other way ?

alanj · #3 $Old$ October 23rd, 2009, 10:16 AM

If the proportion of the distribution is equal, then it doesn't matter which volume comes first. As long as example items 4 and 5 are together at the bottom, either may come first. Similarly for 2 and 3.

After further contemplation, I've tried the following idea which looks like it may work:

(((Abs(SiteA - SiteB) / Total) * 100 ) +
((Abs(SiteA - SiteC) / Total) * 100 ) +
((Abs(SiteB - SiteC) / Total) * 100 ) +) / 3

Since I'm omitting products with zero sales, divide by zero is not an issue. I thought this would give me a value from 100 down to 0. In practice, this seems to give me a scale from 66.66667 for items having all sales on one site, down to 0.0000 for items having equal sales on all three sites. The actual scale doesn't matter as long as it's in sequence. Pending further examination, this appears to fit the bill. There can be some looseness in the results, since corrective measures will be taken on all items which tend to fall near the top. If you can suggest improvements or a more promising approach, I would be most grateful. In any case, thank you for taking time to look at this problem.