Impact of line item on over all sales

[Number4]

I'll try to type this out first, will provide a spreadsheet later if needed. I'll have to build it in a sharable format.

If I sell 100 items (100 line entries, multiple items per line) that have varying selling prices as well as costs and units sold I end up with varying gross profit per line.
These 100 items roll up into a line that gives me total sales, gp$ and gp%. Columns A, B and C.
In Columns D, E and F, I have last years sales for these same line items.
Columns G, H and I show me the sales increase and gp$ increase as a percentage and the basis points increase in GP% as a number

Hopefully my input from Excel will come across, if not I'll use dropbox. Basically, in the example below, the all in line shows a basis point change of -22 points (going from 37.26% to 37.05% GP.) How does one calculate the impact of each of the 100 lines to determine which lines are hurting you the most?

Okay, I'll have to add the example by drop box tomorrow. If there's an easier way than drop box, please let me know.

A|B|C|D|E|F|G|H|I
ALL|104,107|38,568|37.05%|110,112|41,029|37.26%|-5%|-6%|-22
May 2016|May-15|vs. May 2015
Item|Sales|GP$|GP%|Sales|GP$|GP%|Sales Inc|GP$ Inc|BP Chg
41096|41,776|15,204|36.39%|28,495|12,165|42.69%|47%|25%|-630
9064|1,354|290|21.45%|0|0|0.00%|0%|0%|2,145

 

[MARK858]

Okay, I'll have to add the example by drop box tomorrow. If there's an easier way than drop box, please let me know.

I don't know if easier/harder but have look at my signature block below for some links for producing usable screenshots.

You will get more response from usable screenshots as some won't/can't download from sites.

Just for info avoid posting images like JPeg's as they can't be copied/pasted directly into cells.

 

[Number4]

Here's a link to my spreadsheet....hopefully.
https://db.tt/F88PbP60

The goal, is to know what items you sold negatively impacted your year over year number.

When you open the sheet, cell J3 is the bases point impact to Gross Profit for 2016 sales vs 2015 sales.
The "voodoo" math to the right (Columns L-P) is a means that someone else came up with to calculate the impact of each line on the total sales.
Thing is, it works most times, or appears to. Usually not off by more than one point. You'll see -52 in cell J3 and -53 in cell K3.
When you add up column K, it'll equal cell K3, which should be the same (or one point off) from cell J3.

Use the dropdown in H2 (DIV) and select 'Mr._Ts' and you'll see that J3 and K3 still "matches."
Using the dropdown in I2 (DIST) and select 'Mr._Ts_1' you'll see it "matches."
But choose 'Mr._Ts_2' and 'Mr._Ts_3' they don't match.

If you navigate the different drop down options, you'll find some that match exactly, some are one point off and others aren't even in the same ballpark.

The problem I have, is I can't conceptually verify that this "voodoo" math is legit.
But, I've searched the web and can't find anyone talking about identifying the impact of unit sales on the whole. Seems like something more people would want to know.