Re: Excel to track inventory?

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" <gary@xxxxxxxxxxxxxxxx> wrote in message
news:uu3%23YU0ZHHA.2448@xxxxxxxxxxxxxxxxxxxxxxx
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 ***, 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.

--
Gary
Visit Lucy & Gary at
www.under-1-roof.com

"KC Rippstein" <kcrippsteinAThotmailDOTcom> wrote in message
news:%2305fWvwZHHA.4616@xxxxxxxxxxxxxxxxxxxxxxx
You could set up a very simple Transactions work*** 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 work*** 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 ***, 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" <gary@xxxxxxxxxxxxxxxx> wrote in message
news:eJcoI8qZHHA.3268@xxxxxxxxxxxxxxxxxxxxxxx
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 work*** 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!

--
Gary
Visit Lucy & Gary at
www.under-1-roof.com








.

Loading