Yet another excel question thread...

[Handcruser 2815]

See below image.

 

I am trying to write a script/function that solves the following problem.

 

I need to be able to adjust pricing via the GMP in order to be more competitive with our quotes. Internally, we have a team that takes the competitive intel and works up this spreadsheet. However, we obviously could be more competitive overall if we provide deeper discounts on our high margin items on a per quote basis, because items change from quote to quote.

 

A few points-

I26 is our quoted price with discounts

L26 is our competitors pricing for this client

M26 is how much we’d save them off the invoices we were given.

 

It’s not a lot. So I need to change that.

 

What I need-

K26 to be an average of K2-K25

Then, and this is what I really need, I want to be able to change K26 and have it adjust (lower) the GMP per item starting with the highest margin item and working its way down but never going below 21. Example

 

Let’s say, for this sheet, K26 is 26.2. I’d like to be able to replace that cell with 25, and it would (in theory) reduce K12 first (thereby reducing price in I12, then reduce K10 a little, then K8, etc. this function would most likely take more (as a %) off something that we still had a 72% margin on than it would something at 47%.

 

If that doesn’t give the desired result (which is M26 showing the savings I want) then I’d go from 25 to 24 and the script/function runs again.

 

The cells with GMP over 30 is where the bulk of the function will operate.

 

 

Thoughts?

 

 

Sent from my iPhone using Tapatalk Pro

[Huckleberry 34593]

Couple of questions:

1. Do you really want the average of K2-K25 or do you want an overall GMP? The average of those cells is 25.6% but your overall GMP is 20.1% if you sum up G2-G25 and compare to the sum of I2-I25.

2. Can you go lower on I11 and I18 or does "Best" mean that you can't go any lower on those items?

[Handcruser 2815]

1. Thanks. This is a good question. Loon at the GMP in k3 and tell me if that makes sense. I think I have other problems here....so averaging K isn’t telling me the story.

2. Best means that’s it...can’t do any lower. But when you compare to other margins this line I. The sand doesn’t make sense. I now think this whole thing is fucked. Agreed?


Sent from my iPhone using Tapatalk Pro

[SDG 3336]

Yeah you prob want a weighted average in k26 so something like k26=sumproduct(i2:i25,k2:k25)/sum(i2:i25). 

Edit to add that to get your second part you are probably going to use a goal seek or solver type function to auto adjust pricing based on rules you set (never going below 21). 

Edited November 14, 2018 by SDG

[Huckleberry 34593]

Yes, on an individual basis each GMP value looks correct. But when looking at the overall order they should be comparing totals, not averaging each line item. I uploaded a Google Sheet to show the difference. First tab is based on averaging each line item, second tab is based on looking at the totals (which I think is correct). I included both because if your group wants to control margins on an individual item basis instead of overall orders then it's an option.

So on the first, your two entry fields are N2 and O2. Individual Item GMP Floor is where you enter the GMP level where you don't want to adjust items if they're already below that mark. I set up a conditional formatting catch for any individual line items that were at or above this number before the adjustment but fell below after it.

The second tab is a bit more complicated, and keep in mind that your initial GMP for the entire order is already only 20.1% at this point. So if we set an individual item GMP floor of 21% then the math already screws us, so we have to go lower. I set it at 17.5% to start.

I tried to show every step in separate columns so I intentionally didn't consolidate formulas.

https://docs.google.com/spreadsheets/d/1zB8CpjDVTT3CQf35TIFlotb9RVUsIJ2Rn9MY-VryEZg/edit?usp=sharing

 

[Doc Holliday 199]

How many HDMI ports? 

[Dahobbs 18307]

1 hour ago, Huckleberry said:

Yes, on an individual basis each GMP value looks correct. But when looking at the overall order they should be comparing totals, not averaging each line item. I uploaded a Google Sheet to show the difference. First tab is based on averaging each line item, second tab is based on looking at the totals (which I think is correct). I included both because if your group wants to control margins on an individual item basis instead of overall orders then it's an option.

So on the first, your two entry fields are N2 and O2. Individual Item GMP Floor is where you enter the GMP level where you don't want to adjust items if they're already below that mark. I set up a conditional formatting catch for any individual line items that were at or above this number before the adjustment but fell below after it.

The second tab is a bit more complicated, and keep in mind that your initial GMP for the entire order is already only 20.1% at this point. So if we set an individual item GMP floor of 21% then the math already screws us, so we have to go lower. I set it at 17.5% to start.

I tried to show every step in separate columns so I intentionally didn't consolidate formulas.

https://docs.google.com/spreadsheets/d/1zB8CpjDVTT3CQf35TIFlotb9RVUsIJ2Rn9MY-VryEZg/edit?usp=sharing

 

This spreadsheet works, but I'm not clear why anyone would want to set a target GMP like this without considering actual product mix. The value you selected only applies if the company sells an equal amount of every item. Presumably certain items are higher volume than others, and that is going to substantially change the end of day GMP. I would think you would need a target/estimated units sold for each product in order to get this to work correctly. 

Having said that, I'm lawyer and have no interaction with any of this stuff, so I probably don't know what I am talking about. 

[Huckleberry 34593]

Completely agree but he made it sound like this was an existing order with one item per line.

[Handcruser 2815]

Yes, on an individual basis each GMP value looks correct. But when looking at the overall order they should be comparing totals, not averaging each line item. I uploaded a Google Sheet to show the difference. First tab is based on averaging each line item, second tab is based on looking at the totals (which I think is correct). I included both because if your group wants to control margins on an individual item basis instead of overall orders then it's an option.
So on the first, your two entry fields are N2 and O2. Individual Item GMP Floor is where you enter the GMP level where you don't want to adjust items if they're already below that mark. I set up a conditional formatting catch for any individual line items that were at or above this number before the adjustment but fell below after it.
The second tab is a bit more complicated, and keep in mind that your initial GMP for the entire order is already only 20.1% at this point. So if we set an individual item GMP floor of 21% then the math already screws us, so we have to go lower. I set it at 17.5% to start.
I tried to show every step in separate columns so I intentionally didn't consolidate formulas.
https://docs.google.com/spreadsheets/d/1zB8CpjDVTT3CQf35TIFlotb9RVUsIJ2Rn9MY-VryEZg/edit?usp=sharing
 

Ok let me digest. I’ll be back.


Sent from my iPhone using Tapatalk Pro

[Guest]

You guys are brilliant.

[Handcruser 2815]

@Huckleberry

Ok, I'm tracking.  I agree with your assessment of how the actual GMP needs to be calculated.  

Is there any way you can:

A. email this to me so I can edit.

B. Make W26 on "totals" sheet and U26 on "line items" sheet perform the 2nd part of my request (adjusting GMP individually)?

@Dahobbs

To answer your question above, we might sell 9 product X on one order, 0 on the next, 2 on the next, etc.  But that price needs to be locked in per unit.  So volume/quantity doesnt matter for this exercise.  If a client is going to increase ordering on, lets say lidocaine, then we can certainly formulate new pricing to account for that.  This worksheet is for comparison pricing against actual invoices (in which that particular invoice is not completely reflective of their ordering habits (ex: they order lidocaine once a quarter and it is not on the invoices i received)).

[Handcruser 2815]

I have a meeting internally about this in the am, and I'd like to demonstrate the usefulness of a tool such as this and highlight the disparity in how we (read, someone else) assesses gmp. 

[Dahobbs 18307]

36 minutes ago, Handcruser said:

@Huckleberry

Ok, I'm tracking.  I agree with your assessment of how the actual GMP needs to be calculated.  

Is there any way you can:

A. email this to me so I can edit.

B. Make W26 on "totals" sheet and U26 on "line items" sheet perform the 2nd part of my request (adjusting GMP individually)?

@Dahobbs

To answer your question above, we might sell 9 product X on one order, 0 on the next, 2 on the next, etc.  But that price needs to be locked in per unit.  So volume/quantity doesnt matter for this exercise.  If a client is going to increase ordering on, lets say lidocaine, then we can certainly formulate new pricing to account for that.  This worksheet is for comparison pricing against actual invoices (in which that particular invoice is not completely reflective of their ordering habits (ex: they order lidocaine once a quarter and it is not on the invoices i received)).

You can download it directly from his link. Just save as an excel spreadsheet. There should be an option in your browser, I think download is in the top right corner. I am not on a pc, so I can't look it up right now, but I did it myself. 

[Huckleberry 34593]

1 hour ago, Handcruser said:

@Huckleberry

Ok, I'm tracking.  I agree with your assessment of how the actual GMP needs to be calculated.  

Is there any way you can:

A. email this to me so I can edit.

B. Make W26 on "totals" sheet and U26 on "line items" sheet perform the 2nd part of my request (adjusting GMP individually)?

@Dahobbs

To answer your question above, we might sell 9 product X on one order, 0 on the next, 2 on the next, etc.  But that price needs to be locked in per unit.  So volume/quantity doesnt matter for this exercise.  If a client is going to increase ordering on, lets say lidocaine, then we can certainly formulate new pricing to account for that.  This worksheet is for comparison pricing against actual invoices (in which that particular invoice is not completely reflective of their ordering habits (ex: they order lidocaine once a quarter and it is not on the invoices i received)).

Not sure I follow on B. What do you want it to accomplish? And Dahobbs is correct, you can download from the link by going here:

Edited November 16, 2018 by Huckleberry

[Handcruser 2815]

i added to my drive but dont have editing capabilities.  My comp took a shit the other day and i lost office and havent had time to reinstall so im working through drive. 

in regards to your question, I want to be able to lower pricing across all products by lowering the gmp so it reduces the highest gmp item first, then the next, and so on.

Example:

In the "totals" sheet, i could replace 20 in w26 with 19, and my new price would become $1392 (example)  in V26, X26 would then show savings of $119.xx. This would happen by the GMP in W decreasing slightly per item, starting with the highest gmp item, which would also adjust the items accordingly in V.  

 

Make sense?

[Dahobbs 18307]

22 minutes ago, Handcruser said:

i added to my drive but dont have editing capabilities.  My comp took a shit the other day and i lost office and havent had time to reinstall so im working through drive. 

in regards to your question, I want to be able to lower pricing across all products by lowering the gmp so it reduces the highest gmp item first, then the next, and so on.

Example:

In the "totals" sheet, i could replace 20 in w26 with 19, and my new price would become $1392 (example)  in V26, X26 would then show savings of $119.xx. This would happen by the GMP in W decreasing slightly per item, starting with the highest gmp item, which would also adjust the items accordingly in V.  

 

Make sense?

If I am understanding you, his spreadsheet does that, just not in that order. You set desired GMP in O2. W26 gives you the actual new total GMP, which is necessary because your target is not always reachable. The spreadsheet does what you are asking, but in a different order by decreasing each item in proportion to the total savings. GMP of the new line item is then calculated based on the adjusted price. 

 

Edited November 16, 2018 by Dahobbs

[Handcruser 2815]

oh ok.  i cant edit so missed that.  

 

[JSB 677]

My question will be much more simple, but it has me stumped. I've got a tab with accounts numbers down the left column, unique identifiers across the columns that I want to pull data from a separate data dump tab, without manipulating that data dump tab too much. At first I thought an INDEX formula, but the data dump tab is all in rows, meaning the account number and the unique identifier are both in the same row, and the data I want to pull is also in the same row. This is what I tried and it didn't work, I presume because my IF formula refers to an entire column.

=IF(D$7='Data Dump Tab'!E:E,VLOOKUP(Main Tabi!$B102,'Data Dump Tab'!L:V,11,FALSE),0)

where D7 is the unique identifier on the main tab

Column E on the Data Dump tab is the unique identifier

B102 is the account number on the main tab, and the rest of the VLOOKUP is normal. 

 

Any brilliant ideas?

[Dahobbs 18307]

15 hours ago, JSB said:

My question will be much more simple, but it has me stumped. I've got a tab with accounts numbers down the left column, unique identifiers across the columns that I want to pull data from a separate data dump tab, without manipulating that data dump tab too much. At first I thought an INDEX formula, but the data dump tab is all in rows, meaning the account number and the unique identifier are both in the same row, and the data I want to pull is also in the same row. This is what I tried and it didn't work, I presume because my IF formula refers to an entire column.

=IF(D$7='Data Dump Tab'!E:E,VLOOKUP(Main Tabi!$B102,'Data Dump Tab'!L:V,11,FALSE),0)

where D7 is the unique identifier on the main tab

Column E on the Data Dump tab is the unique identifier

B102 is the account number on the main tab, and the rest of the VLOOKUP is normal. 

 

Any brilliant ideas?

I'm not understanding your problem with an INDEX formula. If the data in the datadump tab has everything in rows, INDEX should work great?

Your IF statement looks all sorts of messed up. With either VLOOKUP or combining INDEX and MATCH formulas, you do not need an IF statement at all. And, that first IF statement isn't going to work anyway. 

Power Query is probably going to be your best bet at any rate, but I really don't understand your sheets. 

[JSB 677]

I am most definitely not doing this the most efficient way, but I ended up just making a pivot table of my data dump tab with the unique ID going across the top, and the account numbers down the side, then using an INDEX and MATCH formula to get it to work. 

[Dahobbs 18307]

19 minutes ago, JSB said:

I am most definitely not doing this the most efficient way, but I ended up just making a pivot table of my data dump tab with the unique ID going across the top, and the account numbers down the side, then using an INDEX and MATCH formula to get it to work. 

Learn power query. It comes with excel (under data tab, Get Data). It'll do a lot of things very easily that it sounds like you are trying to do the hard way. 

[JSB 677]

You've definitely piqued my interest in it. Never heard of it until now.