Stock Control with Excel 2003???

[computime]

Hello All

I'm trying to create a stock system using excel 2003, but i'm not to
sure where to start. What i have is some cells with products ,
quantity and parts, what i want to do is to minus 1 product which will
minus 1 out of each part used to make the product... if that makes
sence, i've tried all sorts of sums, but nothing seems to work.
can anyone give me an idea where to start....

 

[flummi]

Is this your approach?

product____QtyPod___Part________QtyPart

123456____65_______abcd1_______14
123456_____________fghww2______40
123456_____________lljjhh77______17

If you deduct 1 from the product quantity you want the above to look
like this?

product____QtyPod___Part________QtyPart

123456____64_______abcd1_______13
123456_____________fghww2______39
123456_____________lljjhh77______16


Or do you want something like this?

Product stock
product_____Qty

123456_____65
123457_____112
123458_____17

Parts stock
Part________Qty
abc11______17
abdf44______45
xdfg88______60

Product Structure
Product____part______usage
123456____abc11____1
123456____abdf44____2
123456____xdfg88____1

If you consume quantity n of your product you want to decrease product
stock by n, parts stock for every part used in the structure by n x the
usage of that part?

Hans

 

[computime]

yes its the top 1, i need to do...

like: product_____Quantity______parts_____Quantity
12345 10 12345 10
ndkjnd 10
adada 10

and to: product______Quantity______parts______Quantity
12345 9 12345 9
ndkjnd 9
adada 9

God knows how you configure it....

 

[flummi]

Okay.

How do you want to specify your "consumption" of 1 product?

1. You enter the product in a cell and the comsumption in another, hit
an "update" button and a macro does the stock correction.

2. You have a "log" of all your consumptions and a stock figure for
every product i.e. the actual stock is at all times inventory stock
minus all comsumptions in the log (for each product of course).

Remember:

A stock system always starts with some kind of inventory (albeit 0) and
then you keep entering your additions or consumptions which are
deducted from the stock. And at some stage you do another inventory and
correct your stock by means of a "correction consumption/addition".

I guess you would want to know at all times what you have consumed and
for what?

And another issue is that you would probably want to have your own,
separate stock of parts maintained along the same lines?

So, maybe the second approach in my first response would be the better
option?

Before you start "coding" it's always good to know what you are trying
to achieve.

So, if this is about your idea of "stock system" we could start
building a template.

Hans

 

[computime]

Hello Hans

Each time i remove a product, i want it to remove 1 part from each of
the parts that make the product, the product amount does'nt matter,
that can be anything. Just want to know what parts i have in stock.

Darren.

 

[flummi]

okay. To start the process, here's a simple proposal.

inv. actual
Product part stock Consumption stock
123456 123 14 <-- 8 6
123456 234 20 8 12
123456 345 15 <== 8 7
234567 123 30 <-- 7 23
234567 222 17 7 10
234567 345 10 <== 7 3

Consumption log
date product qty
01.02.2006 123456 1
03.02.2006 123456 2
04.02.2006 234567 1
07.02.2006 234567 1
08.02.2006 123456 4
10.02.2006 123456 1
11.02.2006 234567 2
13.02.2006 234567 3
insert new consumption before this line

The formula that updates the stock is in F3: =C3-E3

The formula that calculates the consumption is in E2:
=SUMIF($B$12:$B$20;A3;$C$12:$C$20)

Of course you need to adapt the locations and ranges.

If this is not how you would like to do it, let us know how would like
it to do i.e. in what steps.

Hans

 

[computime]

cheers Hans

I'll try it, and let you know the outcome...

Darren..

 

[computime]

Hello Hans

I had a look, and i'm still not sure how to get your formula into my
project

what i have is>
_________A_______B___________C_________D
1_____product__Quantity______Parts____Quantity
2_____12345______100________abcd______40
3___________________________efgh_______38
4___________________________ijklm_______16

and want to get this to get this>

_______A________B____________C________D
1___product___Quantity_______parts____Quantity
2____12345______99__________abcd______39
3___________________________efgh______37
4___________________________ijklm______15



but i need to change the parts quantity with it changing the product
level.

thanks again..
Darren

 

[flummi]

Hi Darren,

if you don't mind email me a sample sheet and I'll send you a proposal.
That seems to be the easiest solution.

Hans

 

[computime]

Hello Hans

Whats your email address, could you PM it to me..

Thanks

Darren.

 

[flummi]

(e-mail address removed)

Hans