Trying to make an Inventory control in Excel

[saracen]

I am trying to manage my inventory in excel. My stock sheet has a column of unique skus of each product in column "A" and stock quantities in column "B".

I get excel files for my daily sales containing skus and its respective sold quantities. e.g If I have sold one sku twice its presented separately in the saily sales sheet.

I want to create a formula that can sum the total sale of each sku in that day and then subtract it from the stock sheet so I can keep my stock sheet updated.

Is it possible to do it in one go?

I hope I have explained it well enough.

[Arkadi]

saracen, I don't see an easy way to do this using formulas, I think you'd need to use macros to accomplish this, particularly since it sounds like you need the "inventory" workbook to open other files to get information?

[saracen]

Hi Arkadi
Thanks for your reply. Its not a lot of data. I can perform the complete operation on a single sheet and then copy it to another.
I manage to sum up the sale for each sku on the daily sale sheet using kutools. So below is the data from my daily sale sheet.

SKU (column C) QTY (column D)
70100501 9
85001101 12
83002901 4
70100123 4
70100494 1
50001201 11

Now this is my stock data
SKU (column A) QTY (column B)
85001101 112
83002901 14
70100501 91
70100494 31
50001201 111
53001011 32
71500102 44
70100001 14
83001551 33
70100201 34

The formula I am looking for is:
Subtract column D from column B if Column C is in column A. If we take SKU 70100501 as an example then subtract D9 from B3 because C1 is present in A3.

Thanks

[Arkadi]

Fair enough, but a formula would require your inventory to have a static starting quantity. Then another column with current level. And if you delete the daily shipment data, then your formula is original - 0 so your inventory level goes up. To update a value permanently you'd have to keep adding quantity sold, or you have to update and save the value, not the formula that gives the total.... was that clear? I feel it is hard to explain.

[Arkadi]

Ok Assuming you want the totals in a new column... E. Column A is stock part, B is stock level, C is stock part of daily sales, and C is qty sold. In column E you would paste:

Please Login or Register  to view this content.

Then copy down. This assumes One daily sales record only for each part number, and the value in column E is the new balance for the Item listed in A1. It will not error out if you didn't sell any items of a given stock part.

Edit: in the formula C1:D10 is the list of daily sales, may want to go to D10000 or something

[saracen]

Brilliant!
What I have done is that I have created three sheets.
1. Opening stock (is the static as you mentioned)
2. Stock (Tells me about the current level of stock)
3. Sales (Record the sales)

The formula I used is ='Opening Stock'!B2-IF(ISNA(VLOOKUP('Opening Stock'!A2,sale!$C$3:sale!D32,2,FALSE)),0,VLOOKUP('Opening Stock'!A2,sale!$C$3:sale!D32,2,FALSE))

This way the exercise would be to populate sales sheet everyday with daily sales data and then copy the results from stock sheet (it will become closing stock at the end of the day) to opening stock sheet for next day sales.

Or the other option I was thinking is to create 2 columns representing each day of the month containing SKU and sold QTY.

DAY 1 DAY 2 DAY 3
SKU QTY SKU QTY SKU QTY
85001101 10 70100201 2 83002901 9
83002901 5 50001201 2 83001551 5
83001551 6 53001011 1 70100201 2
70100201 8 50001201 5
50001201 5
53001011 4


I populate each day with sales data and it basically performs the earlier formula twice. Once on the sales sheet and then on the stock sheet.

Do you think thats doable or is it asking for too much? I was thinking may be after the 31st day on the sales sheet we put a formula for total sales that accumulates the daily sale according to SKUs. And the formula you have written earlier takes the value from that total sales.
That way the daily closing stock exercise will become a monthly closing stock.