Stock maintaince

[excelcode9]

I am stuck with one excel sheet which i prepared for maintaining stock.

We sell our product both by carton and loose and one carton contain 24 packets.

Problem is I have 5 carton and 10 packets as loose in my stock and I sold 3 carton and 14 packets as loose by opening a new carton.

My excel sheets says 2 carton and -4 packets are left in stock

How do I command excel to deduct from carton automatically if the number of loose packets exceeds the number present in loose.

Please help

 

[MrVillareal]

Hi try this simple illustration and hope it helps.

Excel Workbook
	A	B	C	D
1	RAW DATA
2	Date	Total Item Sold	Carton	Packet
3	8/14/2011	86	3	14
4	8/15/2011	96	4	0
5	8/16/2011	100	4	4
6	8/17/2011	108	4	12
7
8	DAILY STOCK
9	Date	Total Item Stock	Carton	Packet
10	8/14/2011	1000	41	16
11	8/15/2011	904	37	16
12	8/16/2011	804	33	12
13	8/17/2011	696	29	0
Sheet9

Excel 2010

Cell Formulas
Range	Formula
C3	=INT(B3/24)
C4	=INT(B4/24)
C5	=INT(B5/24)
C6	=INT(B6/24)
C10	=INT(B10/24)
C11	=INT(B11/24)
C12	=INT(B12/24)
C13	=INT(B13/24)
D3	=B3-(C3*24)
D4	=B4-(C4*24)
D5	=B5-(C5*24)
D6	=B6-(C6*24)
D10	=B10-(C10*24)
D11	=B11-(C11*24)
D12	=B12-(C12*24)
D13	=B13-(C13*24)
B11	=B10-B4
B12	=B11-B5
B13	=B12-B6

In my suggested solution I just add a new column for the total items sold and break them down into carton and packet. Hope this help.

 

[excelcode9]

Hi Mr Villareal,

Thanks for a quick response.


Although this is one way of looking at the problem. Can't it be solved other way round by just mentioning no. of carton and loose one.

Can excel automatically deduct from carton column if the loose sold packets are more than loose packets in stock ???


Thanks anyways. Really appreciate your efforts.

 

[excelcode9]

Hi MrVillaReal,

The solution is not working for me, although its OK at its place.

This was only an example, We have a variety of more than 50 products and packaging of each differ from each other. Some have 24 packets, other have 480 pouches.

So each time I have to multiply the number of packets present in carton + the loose one, For maintaining inventory for a single product your solution works fine. But for a variety of product, the complication exists.

I just need to enter in my excel sheet the no. of carton sold + the no. of packets sold loose.

I want my excel sheet to automatically calculate the no. of carton as well as the no. of packets in stock.

Hope you are getting me well.

 

[excelcode9]

Hi Mr VillaReal,

I got the solution.

It actually should be done in a Zig-Zag manner.

A extra column should be added in delivery part calculating the total no. of packets and Stock should - (minus) this figure with the earlier stock to get a number.

Now this number will be broken to no. of Carton and loose packets.

that's it!

Ur answer helped me a lot. So whole credit goes to you.

Thanks Bro.

 

[MrVillareal]

You are welcome Glad I help