Excel to track inventory?

[Gary]

I'm currently using MS Excel (2002) to keep track of inventory and sales in
a VERY small home-based business. Would it be possible to set up something
in Excel so that when I entered a sale on one worksheet it would
automatically be subtracted from inventory and, if so, where can I find out
how to do it? I know a little bit about macro's but not a whole lot.

Thanks for any help you can give me!

 

[Guest]

Gary,

you will need to learn about programming macros to do that. While the macro
to do this is not very sophisticated as far as programmed macros go, it is
too sophisticated for it to be recorded. Suggest you learn more about VBA or
get someone to help with that specific issue.

Darryl

 

[KC Rippstein]

You could set up a very simple Transactions worksheet to record all your
sales (out of inventory) and purchases (adding inventory). Date goes in
column A, product name or ID goes in column B, quantity (+ or -) goes in
column C, and total cost goes in column D (this one would be formula). If
you have taxable sales, track sales tax separately in column E using a
simple formula like =D2*6.5% to keep yourself better organized.

Then your Inventory worksheet would have a master list of all possible
inventory items in column A, then highlight all your item names and give
that range a name (like Inventory_List). Use the white Name Box to the left
of the formula bar.
Then you could do the following:
- in column B, enter the item's cost
- in column C, enter your retail price
- in column D, track Purchases Qty using this formula in D2
=SUMPRODUCT((Transactions!B2:B5000=A2)*(Transactions!C2:C5000>0)) and then
copy that formula down for all inventory items
- in column E, track Sales Qty using this formula in E2
=SUMPRODUCT((Transactions!B2:B5000=A2)*(Transactions!C2:C5000<0)) and copy
that formula down for all inventory items
- in column F, track Sales Revenue using this formula in F2 =C2*E2
- in column G, track Profit using this formula in G2 =F2-B2*D2

Back on your Transactions page, that formula in D2 is
=IF(C2<>"",C2*IF(C2>0,VLOOKUP(B2,Inventory!A:B,2,0),VLOOKUP(B2,Inventory!A:C,3,0))*-1,"")
and copy that formula down to row 5000. Finally, highlight B2:B5000 and go
to Data | Validation, select list, and in the Source box type
=Inventory_List

Now you are all set for 5000 records of sales and purchases with a very
rudimentary but functional inventory tracking and revenue tracking system.
If you apply an auto filter to the Transactions sheet, then you'd also be
able to do some other drill-down reporting, like totals sales for a
particular month or how many scarves you sold in February.

Hope that helps a bit.
-KC

 

[Gary]

KC, thanks for the help. I've set this up but I think that I may be doing
something wrong. Is this supposed to allow for purchase and/or sales
quantities other than "1"? When I tried putting anything but a "1" or "-1"
in column C on the transaction sheet, column D or E on the inventory page
only added "1". It seems to be counting transactions rather than adding
quantities. Is this what it's supposed to do? Thanks, again.

 

[KC Rippstein]

You are right. I was missing one more thing on those sumproduct formulas.
After testing for <0 or >0, put this in there (just before the last
parenthesis):
*(Transactions!C2:C5000)
The way I had it was a count. Adding the above makes it a sum.

Also, you need to put a $ sign in front of each 2 and 5000. I forgot that
as well. Sorry, I was in a hurry to get to our sonogram today...after 2
boys, we are now having a girl!

Good luck!
-KC

 

[Gary]

Congratulations!!!!!!! Good luck to you and your growing family!!!!!
Thanks, again for taking the time to help me with this!

 

[KC Rippstein]

This:
=IF(C2<>"",C2*IF(C2>0,VLOOKUP(B2,Inventory!A:B,2,0),VLOOKUP(B2,Inventory!A:C,3,0))*-1,"")
should also be corrected to:
=IF(C2<>"",C2*IF(C2>0,VLOOKUP(B2,Inventory!A:B,2,0),VLOOKUP(B2,Inventory!A:C,3,0)*-1),"")
Basically, it's saying if you added inventory (C2>0), multiply C2 by your
cost. Otherwise, multiply C2 (a negative number) by your retail price and
then multiply it by -1 (since you want positive revenues, right??). My *-1
was incorrectly positioned after that parenthesis and should have been
inside the parenthesis.
That should do it. Thanks for the congrats!

 

[KC Rippstein]

Disregard that last post. I had it right the first time and should not have
second guessed myself. By leaving the *-1 outside the parenthesis, it not
only converts sales to positive numbers (deposits into your account) but
also converts inventory additions to negatives (purchases from your
account). Then if you wanted, at the top you could put a totals row that
uses the SUBTOTAL function to give yourself correct totals, even if you
apply a filter.
Hopefully my brain will work better today.

 

[Gary]

Thanks, again for all your help! I think that I've got enough to manage my
inventory very nicely.

Again, congrats and good luck with the new baby!

 

[Patricia A. Brannan]

I am looking for the answer to your question? May I ask if you received any
replies. Have to solved your inventory tracking problem?

 

[Mick Smith]

I have a similar query Lucy and Gary. Did you get an answer

Dasha