• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Excel to analyze and find largest surplus in inventory with multiple locat

Cknight

New Member
Hi all,


I have been using Excel to create a "short" list for products among my 5 stores. These items which need to be replenished since there is less in stock than what we have sold in the past 7 days. These are all on one Excel Sheet. The columns look as such:


Description// QOH// Sold in 7 days// How much Needed// Store#1 to Transfer// Store#2 to Transfer// Store#3 to Transfer// Store#4 to Transfer// Store#5 to Transfer


On a subsequent sheet, I have all of the products from the 5 locations with their current inventory in one column with the amount that was sold in the past 90 days. I have then run a Inventory - Item Movement in the last 90 days. These columns look as such:


Description// Store#1 Quantity on Hand// Store#1 Item Movement in 90 days// Store #1 Surplus or deficit from QOH - 90 day Item Movement// Store#2 Quantity on Hand// Store#2 Item Movement in 90 days// Store #2 Surplus or deficit from QOH - 90 day Item Movement etc


Is there any way to run a macro to take the items which appear on the short list and find which of the 5 locations has the largest surplus in terms of Quantity on Hand vs. Item Movement 90 days? I was hoping for Excel to fill the inter store transfer automatically, which would save me sooo much time.


Thanks in advance for any help. Please let me know if any clarification is neeeded.
 

fred

Member
Do you have some sample data to look at? It'll help me visualize what you are seeing.


Also, do you mind explain the relationship among the cells, which drive what cells. Thanks.


I think it can be done without macro, depending on your input.
 

Cknight

New Member
Fred,


Thanks so much for your help.


Here is a screenshot of my short lists based on the last 7 days of sales:


http://screencast.com/t/Uz05KwRS


Here is a screenshot of my 5 stores with their current quantity and item movement in 90 days.


http://screencast.com/t/TRpyzw5tvRLZ


With regards to the relationships among the cells, all of my data is derived from my Point of sales system. I then create the formula "Quanity on Hand" minus "Item Movement" to get the "Difference" column on the 5 store comparison sheet.


On the 7 day short list, I look at past sales trends to create the order column.


So on the items on the 7 day short list, I find the largest surplus of QOH vs. 90 day movement among the 5 stores on the 5 store sheet and mark that store to transfer the items on the 7 day short list sheet.


For example, I would look at the first item, 1 barrel rum, find it on the 5 store Comparison list and find that store KSO had the largest surplus. Then I would mark KSO Transfer to fulfill that shortage for store KSW.


I was hoping to find some way to automate that step as it takes up a large amount of time each and every week doing it manually.


I hope that describes and visualizes everything better for you. Please let me know if anything else would be of assistance. Thanks again!
 

fred

Member
wouldn't it be easy simply using vlookup? Use the product description as the lookup value and the 5store sheet as the table array. you can fill in the 7 days worksheet by store.


So using the example raised you have under KSO 6, LL 0, KSW -5, KSS 0 and KSE 1.


I think there should be a flexibility for mgmt to decide the final transfer, ie. human call.

One may want to transfer only 4 from KSO and 1 from KSE to fill the gap of -5 iat KSW. the reason for a human call is that you may have a product with 2 store in deficit and 3 stores in surplus. So what's the combination of the breakdown? This is a call to be made by the manager.


so the formula for KSO would be: vlookup (column C of 7day sheet,columns A:p on 5 store sheet,4,false)

for LL the vlookup formula would be: (column C of 7day sheet,columns A:p on 5 store sheet,7,false)

and KSW simply change the 7 from the formula above to 10;

KSS's vlookup change to 13 and KSE change to 16.


Note: I'm assuming all product descriptions are different to the last letter. Or else it won't work.
 

Cknight

New Member
Fred,


Did you mean all product descriptions must be the same to the last letter? The formulas are working with identical product descriptions but I'm not sure they would if all product descriptions were different.


Would you suggest putting these formulas in the KSO TR, LL TR, etc boxes on the 7 day worksheet for each appropriate store or somewhere else?


Otherwise, that will work greatly. Thanks for the help!
 

fred

Member
you put the formulae on the 7 days sales sheet under each of the store location.


so KSO's formula in H2, LL's in I2, KSW in J2...


Then you can copy and paste down the rows.
 

Cknight

New Member
I finally switched over to our new system with the excel file that was previously given and the vlookup formulas that were provided worked tremendously!


There are a few categories (such as soda, snacks,etc) that we always have a surplus at 3 or 4 stores and I am simply looking for the largest surplus and would like the amount on order to be placed in the appropriate stores Transfer column. This would pertain to my original post of having the appropriate transfer column automatically filled with the largest surplus.


Below is another screenshot of my new excel file format:

http://screencast.com/t/jWFxfKCO


I essentially am looking for the store for the largest positive differential in columns N:R, and for that corresponding store in columns H:L to display the desired order quantity that is contained in column F.


So in row 2, Store KSO needs 6 Coca Cola 2.0L transferred to it, the differential for the stores are KSO:-1 LL:6 KSW:12 KSS:18 and KSE: 24. With KSE having the largest surplus, I would then want a formula to fill KSE's transfer box (Column L) with the Order Quantity of 6 (Column F).


I thought it might have something to do with VLOOKUP, IF, and MAX but I cannot get anything to display properly. I am still trying to learn advanced formulas like this, but it is well over my head.


Thank you SO much in advance for taking a look at this.
 

Cknight

New Member
Faseeh,


Below is the link to my test excel file.

https://docs.google.com/spreadsheet/ccc?key=0AjXFJoHzXKDadDZzeGwtLVNsY29icFE5UHZlendEVUE


I suppose I would also need some sort of error to display if none of the stores have a high enough surplus to fulfill the request in the order column. Or maybe there is some sort of parameter that can be set.


The second worksheet is where the surplus or deficit is derived from.


Thanks for the reply!
 

Faseeh

Excel Ninja
Hi Cknight,


I have worked out a file, that is attached see if is in right direction... Better to read comments first.. :)


http://dl.dropbox.com/u/60644346/Copy%20of%20NEW%20SYSTEM%20TEST%20chandoo%20example.xlsx


Faseeh
 

Cknight

New Member
Faseeh,


The status and Excess column additions will be very useful for this file! The filled entries in columns H:L are not exactly what I was looking for, though. Perhaps I should have been more clear. The store in column A is the store that is short of product and needs product from other stores to be fulfilled. In your file you sent for me for example,in row 14, the store KSO needs 3 of the product Makers Mark 1.75L.

The formula input in the dropbox file is sending 3 bottles of Makers Mark 1.75L to itself from store KSO to store KSO. Is there a way in the formula to make it so if the store in column A matches the store column in H:L then that store is automatically dismissed. In this same example of row 14, A14 does equal H1, so store KSO should not be involved at all in this formula.


I am looking for a formula to take using that example in row 14 to see store KSO needs 3 Makers Mark 1.75L and that store KSS is plus 5 in excess as noted in column R14. Column K14 would then reflect the amount of 3 from column F to satisfy that order request.


If in addition it was able to use a combination of other stores surplus to get to the desired amount, that would be amazing. For example, store KSW needs 6 items of product XYZ. The stores have the following differentials in columns O:S KSO: -1 LL: 4 KSW: -6 KSS: 5 KSE: 1

I would love for a formula to be able to take 5 from KSS and the remaining 1 from LL.


Please let me know if that needs any more clarification. Thanks again, Faseeh!
 

Faseeh

Excel Ninja
Hi CKinght...


Ok dear let me reach my PC and will try to sattle the Issue as u said..... :)


@ dan_L


this list can turn any one thirsty... Lolz hehehehe Just Kidding...


Faseeh
 

Cknight

New Member
Faseeh,


You're the best! :)


@ Dan_l


Those were only the first few rows. The whole file is even more impressive and includes wine as well! Haha
 

Faseeh

Excel Ninja
Hi Ckingt,


Here is the file that is showing how much amount can be transferred, the problem i am tackling with is that if you stock is excess then demand, it is still showing all stock moved to the requesting store (where stock has to reach).. I am working over it..


http://dl.dropbox.com/u/60644346/Copy%20of%20NEW%20SYSTEM%20TEST%20chandoo%20example%20-%20Copy.xlsx


Faseeh
 

Cknight

New Member
Faseeh,


That looks great! It seems so close. If columns M:Q can transfer only the stock needed, it would save me SO much time. You have no idea how much time I spend on these 3 times a week, every week. Haha. I eagerly await the possible fix.


Chris
 

Faseeh

Excel Ninja
Hi Chris,


I think i have fixed it!!! :p Check out this file:


http://dl.dropbox.com/u/60644346/CKingt_Solution.xlsx


...and sorry for the late reply. :)


Faseeh
 

Cknight

New Member
Faseeh,


Everything looks amazing!! You are a magician with these formulas. :) It works out even better that it fills transfer requests from left to right as the KSO and LL are our "warehouse" stores and the majority of the transfers come out of those locations.


I cannot wait to try this on my next order. Thank you so so much for your help. It is much appreciated. :) Thanks again.
 

Faseeh

Excel Ninja
Hi Chris...


This is what i learned from this forum, you are welcome any time..


Take care n stay blessed.

Faseeh
 

SirJB7

Excel Rōnin
Hi, Kevin5678234!
You're posting in a very old thread (more than 5 years!). Please start a new thread and post a link to this old one if you think it might help.
Regards!
PS: Don't reply to this message. Thank you.
 
Top