Put in a Item number and get detailed sales history

[Lost_in_Excel]

Hi All,

Am a newbie to the site . I used to think excel was all about V look up and pivot tables.

Today one of my customers proved me wrong. I work in product management and sell to alot of retailers.

One of my retailers sat me down today and showed me how he analyses data and trends.

It was simple , he just put in the code for the product and at the bottom , it showed

1 - Sell out Data per store per month
2 - Quantity on hand per store per month ,

And a whole lot of other data such as if it was a A, B or C class store

He also put in a formula where as an example he wanted replenishment for all A Class stores for a particular product on minimum stock on hand.

Guys , i am totally lost and have no idea how to track sales for the 34 stores and 45 - 50 products .

Any help , advise , templates or videos on how to acheive this would be great.

I tried reading up on indexing , but i think i was barking up the wrong tree.

I look forward to your help and support all..

thanks

 

[NARAYANK991]

Hi ,

Before you think of putting in the formulae to give you results , do you have the data that will be used in the number-crunching ?

Instead of 34 stores and 50 products , can you upload a sample workbook which has data of 3 stores and 6 products ? What ever is done on this scale can be extended to your actual scenario.

There are any number of free templates available on the Internet ; check here :

1. http://www.skilledup.com/learn/business-entrepreneurship/best-free-excel-templates-dashboards/

2. http://office.microsoft.com/en-us/templates/excel-templates-FX102828204.aspx

3. http://chandoo.org/wp/free-excel-templates-download/

4. http://www.excel-skills.com/excel_templates.asp - This has paid for templates.

Narayan

 

[Lost_in_Excel]

Hi Narayan ,

Thanks for the tips , i have uploaded the raw data that i have to work with .

Ideally i would like to just put in the item or product number or code and get the sell out per month per store and the current stock on hand.

I know it can be done , because i saw my customer create just such a sheet , but how ... man i wish i knew.

Any help will be much appreciated.

Thanks

 

[Somendra Misra]

Hi Lost_in_Excel,

I assume Data in range I3:Q7 is the output format you want, and your source data is in range B2:F98.

Can you explain the values in the range I3:Q7 are some dummy values or they are from source and if from source what is the maths behind getting that value?

Say for example Product ip 2700 in store 1 the total quantity sold is 14 in months Jan, Feb & March, but you have shown 1. How you got 1 there.

Regards,

 

[Ram chander]

assuming that data in B2:F98 is sales data by product,store for each month
and data in I3:Q7 is cuurent stock in hand by product & store
and if you looking for the output something like below then..design the sheet as shown and follow below steps.

1) E3 =VLOOKUP(B3,'Raw Data '!B:D,3,0)
2) B2 =HLOOKUP(B$6,'Raw Data '!$I$3:$Q$7,MATCH($E$3,'Raw Data '!$I$3:$I$7,0),0)
and copy to till I7.
3) B11 =SUMIFS('Raw Data '!$E$2:$E$98,'Raw Data '!$D$2:$D$98,Sheet1!$E$3,'Raw Data '!$F$2:$F$98,Sheet1!$A11,'Raw Data '!$C$2:$C$98,B$10) and copy the same to other strores and months (i.e B11:I22)

Enter product code in cell B3 and you will get the desired output.

 

[Ram chander]

assuming that data in B2:F98 is sales data by product,store for each month
and data in I3:Q7 is cuurent stock in hand by product & store
and if you looking for the output something like below then..design the sheet as shown and follow below steps.

1) E3 =VLOOKUP(B3,'Raw Data '!B:D,3,0)
2) B7 =HLOOKUP(B$6,'Raw Data '!$I$3:$Q$7,MATCH($E$3,'Raw Data '!$I$3:$I$7,0),0)
and copy to till I7.
3) B11 =SUMIFS('Raw Data '!$E$2:$E$98,'Raw Data '!$D$2:$D$98,Sheet1!$E$3,'Raw Data '!$F$2:$F$98,Sheet1!$A11,'Raw Data '!$C$2:$C$98,B$10) and copy the same to other strores and months (i.e B11:I22)

Enter product code in cell B3 and you will get the desired output.