Inventory calculations

[vandy]

Hi All,

I have a situation were i need to calculate the current stock inventory of
items.

eg:

item no Instock Issued Qoh

aa 56 2 54
bb 30 1 29

next time when i go to item aa and bb and issue some more items than it
should update the qoh

eg
qoh. should get updated as instock for the next transaction.

item no Instock Issued Qoh

aa 54 4 40
bb 29 2 27

can this be done. I am not very good with VBA. Any help would be appreciated.
thanks in advance

 

[Tom Ogilvy]

in qoh, why not put a formula

=Instock-issue

so if instock was B10 and issue was C10, D10: =B10-C10

 

[vandy]

Hello Tom,

Thanks for your reply. that is the first thing i tried.

b10 c10 d10
instock issue qoh = instock - issue

56 2 54

next time when i go to issue and change it to 4 it will give me

56-4 = 52 which is wrong.

Should not the qoh = instock qty to make the qoh current. or am i missing
something here.

 

[Tom Ogilvy]

In your example, when you issued 4, you changed the 56 to a 54 and the 2 to
a 4. Then the formula would show 50 which is correct I believe.

 

[vandy]

I am sorry. I did not ask the question in the proper order.

Once the item is received as instockt the value does not change. The items
gets issued as per requirments and the QOH has to be calculated for every
issue made.

How to calculate the qoh and simultaneously update the instock amount to
reflect the qoh to keep it current.


item no Instock Issued Qoh

aa 56 2 54
bb 30 1 29

next time when i issue
item no Instock Issued Qoh

aa 56 3 53
bb 30 2 28

my qoh for the items should be

aa 54-3 = 51
bb 29-2 = 27

how can i do this.


thanks for your patience in reading this msg again!

 

[Ross Culver]

Vandy,

You need to separate your transaction recording from your current status.
And, by the way, you're confusing your terminology. Instock and QOH are the
same thing.

Set a range that indicates the beginning quantities:

item No Beginning Qty
aa 56
bb 30
cc etc.

Keep your transaction range as shown below:

Item No Issued
aa 2
bb 1
aa 3
bb 2

Then in a separate range you need (you could combine this column with the
beginning quantities range to save space):

Item No Beginning Qty QOH
aa 56 here you need a vlookup to
get the total qty issued and then subtract this number from beginning qty
bb 30
cc etc.

If you're not familiar with vlookups, let me know.

Ross
(e-mail address removed)

 

[Ross Culver]

I'm sorry. I left out something.

You can't get the resulting QOH with the vlookup. You should create a pivot
table to get the total quantites issued, and then use a vlookup to tie that
back to your Items range by subtracting the vlookup result from the
beginning quantity.

Ross

 

[Ross Culver]

Looks like this, though you can make it alot neater.

Item Beginning QOH Item Issued Sum of Issued
AA 50 43 AA 4 Item Total
BB 30 26 BB 3 AA 7
CC 25 19 AA 1 BB 4
CC 6 CC 6
AA 2 Grand Total 17
BB 1

 

[vandy]

Hello Ross,

Thanks for answering my question. I am not very familiar with Excel i could
manage this with Access but a lot of people are familiar with Exel and so i
need to automate the QOH via excel.

You are right about the qoh and instock value.

I am not familiar with vlookup so please do help me out on that.

Also when you say set a range do you mean use a space range say a1 .. d30 on
a worksheet or use another worksheet and link the data.

I am sorry if these are really basic questions but i wish to be able to
understand the process.

to set my transaction range does it mean typing the item no each time and
entering the issued qty.


Item No Issued
aa 2
bb 1
aa 3
bb 2

thanks again for your inputs.

 

[Ross Culver]

This really looks like crap after it gets to the forum. If you like, send
me an email and I'll send you a sample.

Ross