 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007Excel to track inventory?
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!  SignatureGary Visit Lucy & Gary at www.under-1-roof.com 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 > 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 [quoted text clipped - 3 lines] > > Thanks for any help you can give me! 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 > 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 [quoted text clipped - 3 lines] > > Thanks for any help you can give me! 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. SignatureGary Visit Lucy & Gary at www.under-1-roof.com > You could set up a very simple Transactions worksheet to record all your > sales (out of inventory) and purchases (adding inventory). Date goes in [quoted text clipped - 42 lines] >> >> Thanks for any help you can give me! 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 > 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 [quoted text clipped - 49 lines] >>> >>> Thanks for any help you can give me! Congratulations!!!!!!! Good luck to you and your growing family!!!!! Thanks, again for taking the time to help me with this! SignatureGary Visit Lucy & Gary at www.under-1-roof.com > ... Sorry, I was in a hurry to get to our sonogram today...after 2 boys, > we are now having a girl! > > Good luck! > -KC 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! > Congratulations!!!!!!! Good luck to you and your growing family!!!!! > Thanks, again for taking the time to help me with this! [quoted text clipped - 4 lines] >> Good luck! >> -KC 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. > This: > =IF(C2<>"",C2*IF(C2>0,VLOOKUP(B2,Inventory!A:B,2,0),VLOOKUP(B2,Inventory!A:C,3,0))*-1,"") [quoted text clipped - 15 lines] >>> Good luck! >>> -KC 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! SignatureGary Visit Lucy & Gary and do the jigsaw puzzle at www.under-1-roof.com > 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, [quoted text clipped - 24 lines] >>>> Good luck! >>>> -KC I am looking for the answer to your question? May I ask if you received any replies. Have to solved your inventory tracking problem? I have a similar query Lucy and Gary. Did you get an answer Dasha
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.