Inventory Sheet on Excel

[ohmyganache]

Hello all. Does anyone with inventory experience have a suggested layout in Excel for keeping track of inventory? I've been playing with some different formats, but haven't been really happy with any of them. Does anyone have any suggestions? Thanks...

[ChefCrash]

I'm assuming this is for restaurant inventory.

Do you want to know what you have at hand at any given time? Do you want to automate ordering?

Could you give more info? What are you trying to do?

[ohmyganache]

Thanks for asking for more clarification...

My wife and I have started a small bakery, running it out of the house at the moment (hopefully not for too long). We're trying to keep track and what we've bought, what we have, and how that relates to a food cost. Food costing I can do with no problem... We'll probably to weekly inventory. I've always done inventory in the kitchen, but have never assembled a spreadsheet in the computer to keep track of it.

Our inventory is ridiculusly low at the moment, but that's why I want to tackle this now...

Thanks again.

(BTW- Here is the link to my thread about opening the business...)

Edited September 23, 2006 by ohmyganache (log)

[chefseanbrock]

I have a couple of different styles if you are interested

[Kouign Aman]

Basic guidelines:

1 column per category. NEVER merge cells. Fill each column completely, every row, even if the entry is identical for 100 lines. This lets you use the awesome sorting and filtering power of Excel to its greatest ability, also summing etc.

Add as many columns as you like: you can hide columns if you are not interested in that info today.

Good luck.

[Octaveman]

Honestly it doesn't need to be complicated. With a small inventory, you can keep track of each purchase on a seperate row. For example, if you bought two bags of flour on seperate days those two purchases would be listed on two different rows. This would also be good in identifying spoiled product on your inventory listing.

By keeping track of each individual purchase, the inventory should always equal the balance on the books. That's of course if you record the Cost of Goods Sold (COGS) correctly. The fail safe way would be to have a good starting inventory number, add your purchases each week (or month), subtract your ending inventory and the remaining difference is your COGS. Below is just a general format. Others may have a format the is more specific to your needs but I don't see why this wouldn't work.

Example of columns starting column A:

A - Item number

B - Description

C - Purchase date

D - Vendor

E - Quantity (use a measurement of your choosing)

F - Price per ? (use the original purchase price. You must be careful to match the quantity with the price per measured unit. If quantity = lb., then the price should be per lb. Basic, I know but you'd be surprised how people would put the total cost for 10 lbs as the price per lb)

G - Total

Then at the bottom of Column G would be a final inventory balance total that adds all the rows in that column. This format is basically the same as a data base to which you can sort to your hearts content. Matter of fact, as you add lines of product you purchase, you can sort by column A or B and group like items together.

Also keep in mind that this is just raw goods. A seperate workbook would be need for Finished Goods or food completed and available for sale. For that you would need to know the value of raw goods and direct/Indirect labor for each product plus apply a flat rate for overhead based on overall time to make each product.

Hope this helps. Let me know if you have any questions. I'm a corporate controller and I know Excel better than I know my wife. j/k.

Bob

Edited September 23, 2006 by Octaveman (log)