[RAILhead]

Hey

Okay, I'm not posting this in software because it goes beyond that (to me, at least). Here's what I'm wanting to do...

I have a Main Widget that contains 20 sub-widgets. When I sell a Main Widget, I want to reduce the inventory listing the sub-widgets by 1 (or by however many Main Widgets sold). The thing is, sometimes the Main Widget will have 2 or 3 sub-widgets to reduce from inventory.

Further, I have Main Widget Two, and that may have some of the same sub-widgets.

Basically, I'm just talking about inventory management -- but I want to do this in Excel, mainly because I can't find any inventory management software for the Mac.

Does anyone have any recommendations? I'm just starting off on this, so I don't really have anything setup just yet -- just gathering ideas...

TIA,
Maury

[Doofy]

Reckon you're going to have to bite the bullet and run a copy of QuickBooks Pro on Parallels. That's the best/easiest solution I've come up with so far - it'll do all that kind of stuff automatically.

[turtle777]

So, basically, the MainWidget is the Finished Good (FG), the SubWidgets are raw materials (Raw), and you have a Bill of Material (BOM) describing how many RAW each FG will use up.

Upon selling a FG, you'd like the RAW inventory to be adjusted accordingly.

While this is technically feasible to implement in Excel, I would not recommend it.

SInce this sounds like a business venture, you'd need not only to keep track of inventory volumes, but also inventory values.
See, you might purchase SubWidget A for $ 5.00/100 units in Jan, $ 6.00/100 units in Feb and $ 4.50/100 units in Mar. At the end, you'd wanna know the inventory value for SubWidget A. In addition, it makes a difference if you use FIFO or LIFO, or moving averages for inventory valuation. All this will have an impact on your COGS (Cost of Goods Sold).

So really, I second Doofy's suggestion, get a software that does it right, because come tax time, you'd need to comply with IRS regulations as well, as far as inventory valuation and calculation of your COGS goes.

-t

[Laminar]

Here's a quick and dirty Excel sheet:
http://dl.getdropbox.com/u/19953/mauryinventory.xls

You selected Main Widget 1 (MW1) or Main Widget 2 (MW2) from a drop down box and type in the quantity you just sold. The sheet checks a bill of materials for each main widget, then subtracts the appropriate number of subwidgets from the inventory based on which main widget was sold.

I didn't add in a way to add to the inventory, but if you think this is something you could use, I can do that. I'm sure this isn't the best way to keep track of your stuff, but if simple is all you need then it fits the bill. I use a much more complicated version of this same idea to keep track of my budget.

[RAILhead]

Thanks all -- and yes, we use QB for accounting, but we've never looked into the inventory features. I'll have The Wife check into that while I'm away tonight.

I'll also check out the spreadsheet, Lam -- thanks!

[mduell]

You don't want to do inventory management in a spreadsheet. Use QuickBooks or another database (Bento, etc).

[Person Man]

Originally Posted by mduell 

(Bento, etc).

Are you serious? You do know that Bento doesn't let you store things in separate files. They have one central store for all your databases, whether they're related or not.

[mduell]

Originally Posted by Person Man 

Are you serious? You do know that Bento doesn't let you store things in separate files. They have one central store for all your databases, whether they're related or not.

Better than abusing a spreadsheet.

[OreoCookie]

Don't use Excel (or any other spreadsheet app) for that, just don't. People have a tendency to do that, because it's quick and `easy.'

[Person Man]

Originally Posted by mduell 

Better than abusing a spreadsheet.

Marginally, for RAILhead's purposes.