Help setting up Workbook...

[saskfb1]

Good day!
I've been charged with a task I've never really had to tackle on such a scale before. Basically I have to organize weekly sales data in a manner in which stock variations are easily resumed.
So, every week ''my company'' recieves a rundown of my product sales from each and every store in which they are sold and each and every product that I sell the store. So, I receive an excel file of some 3000 rows and 15 or so columns with various data. I've ''developed'' a macro to get rid of the many rows where sales are ''0'' and get rid of the columns which I don't need. All said, I usually have around 400 rows and 7 columns: Fiscal week, Store Name, Store Number, SKU Desc, VPN, Reg. Sales $, Reg. Sales Units.
Right, I've prepared a pivot table which nicely resumes everything. However, my ''boss'' wants to have the excel tell him stock levels once he imports the fresh weekly data.
How do I start organizing things? I have probably 100 stores, multiple stores in some cities ('store name'=city), 30 or so products. What is the most straight-forward way of creating this? Tabs for each city with an initial stock level (something not yet created) and then using vlookup to control levels...
So as of now I have one tab with weekly data from the past 10 weeks and in another a pivot table that nicely resumes things...
Anyhelp would be great, if it's of use I'm using excel 2007

[MarvinP]

Hi saskfb1 and welcome to the forum,

If you know pivot tables, I'd try to keep using them. I'd use a positive number for stock going into a store and negative numbers for it leaving the store. A pivot that sums this stock would shot how many are left in the store. I'd also use a filter on the Pivot Table so you can filter each Store or SKU.

I'm not sure this helps much without a sample of your workbook to see exactly what you have and exactly what number you need to see. You can attach a sample of your data using "Go Advanced" and then the Paper Clip Icon above the message area.

[saskfb1]

Thanks a bunch, I have a feeling I'll be trolling around this forum for many hours from now on, very useful.
So, I've attached the file. It's pretty easy to figure out, the pivot table that I have in the second tab is just supposed to be a quick run down of everything, though I couldn't seem to get it perfect. The filters on top are what I want, the store numbers and SKU desc are also in the right place, the problem is with the units sold, I would like to have this come up as per product once the store is dropped down and per store without the drop-down, I've only been able to do that by putting it as an 'average'. Basically if you can make it make more sense, please go ahead!
As for the stock data, putting another pivot table in that tab or in a new sheet would be fine, I'm just not sure how to do it, how to set it up in the data tab. I'm thinking if I put a column for stock added, which could be entered manually in whatever week (volume is not so great and only stock sold shows up in the excel that is sent). If you could suggest something in this excel, well, I'd probably buy you a virtual beer.
Thanks again, very much!

[MarvinP]

Hi,
I think I would have the starting stock in the same table as the weekly numbers. I'd marke this with a date of 2011 00 (beginning week of 2011).
Then if each SKU going to the store is a Plus number and each sold it a Negative number the total in the Pivot Table should be what is in the store.

When I do Pivot Table, I drag and drop many different ways to see what might look best. See the attached with the columns as weeks.

Where your fileds go in the Pivot is based on what you need to see. If you only care about a single sku then maybe the SKU goes in the filter so you can see it only. If you want store numbers then maybe the store goes in the top filter.

Try different designes with Filters, Rows and columns. See which gives you the most/best info based on your need.

It just occured to me that Pivots can also show Running Totals. I'm not sure how this might play out but It might show totals over time.

[saskfb1]

Great, great!
Looks good, I've tweaked it a little bit in order to look by store number. Now I'll have to wait for the data, create a 'Week 0' with initial stock by product and store (I see that this is going to be a nightmare). When 'we' ship products to the store will the easiest way to reflect this be manually entering the data under 'reg sales units', I don't see any alternative.
Another question, is there a way to group the stores by region in the initial filter without having to manually select them each time?

Great! Awaiting your reply....

[MarvinP]

Hi,

Why don't you create a Region Column and fill it in for your data. This way you could group/sort by Region as well as store.

Also if stock is sent from one store to another you will need to enter a negative number fo the store it is leaving and a positive for the store it goes to. This will keep the stock totals accurate.

You need a way, other than by hand, to enter inventory control. It is a pain to enter all this stuff daily/weeky.