live inventory

Question

Level 1

0 points

live inventory

Hello! I need to create a spreadsheet in Numbers to track inventory for a small business. Here's my situation:

I took a physical inventory and created a spreadsheet. We sell apparel, so everything has an item number, item name, a color, a size, and a cost. I want to create an "order form" that allows us to track our inventory by calculating how many of each item (size and color included) have been ordered and add up the amount in a "Items Ordered" column.

I would also like the item name to populate next to the item number when the item number is selected on the order form(I think this is a LOOKUP function but I've gotten so overwhelmed with the first part that I can't even start on this).

I'd also like to do the same thing for shipping so that when we received a shipment, we can select the item number, color, and size, and have it also add up the amount per item in a "Shipments Received" colummn.

Once I have those two sheets in place, I should be able to do a simple addition/subtraction formula to live update the physical inventory from the items onhand.

I don't know how well I explained that, but if you could help me at all, I would be so grateful! I've been trying everything and searching everywhere, and I was told by Apple Support that these forums would be the best place to get my answer. Thanks for reading and have a wonderful day!

iMac, OS X Mavericks (10.9.1)

Posted on Apr 9, 2014 9:48 AM

Reply

Answer 1

Apr 9, 2014 1:06 PM in response to ashleyhiggs

Can you post an example of the inventory table?

Reply

Answer 2

ashleyhiggs Author

Level 1

0 points

Apr 10, 2014 8:08 AM in response to Wayne Contello

Here's what my inventory table looks like :

Item Number

Item

Name

Shoe Type

Classes Used For

Size

Color

Quantity in Stock

Unit Price

Inventory Value

Reorder Level

Quantity in Reorder

Customer Orders

Shipments Received

Current Quantity In Stock

N625C

Capezio Jr. Tyette

Ballet

1m

WHT

1

$30.00

1

I need to make an "order form" that updates the inventory without me having to go in manually to do it, and it has to find the item by item number, size, and color.

Thanks for your help in advance!

Reply

Answer 3

Apr 10, 2014 8:31 AM in response to ashleyhiggs

Ashley,

Right now I generally understand what you want. The specifics are always what get us.

Are there a limited number of the kind of items in the inventory?

Do you add new kinds of items?

What does the Order Form look like?

How do you plan to add new items to Inventory (for existing Item Numbers)?

How do you want to display the current status?

Reply

Answer 4

ashleyhiggs Author

Level 1

0 points

Apr 10, 2014 8:40 AM in response to Wayne Contello

Thanks for your help, Wayne! For some reason, I can't see your reply on my computer but I can on my smart phone, so I'll answer as best I can:

I am going to do a separate inventory sheet for each kind of item. Right now, it's shoes/clothing/tights, so I'll have three separate inventory sheets. (Unless you think that I should do them all together, which would be easier for my organization, but maybe more difficult to achieve.)
We probably won't be adding too many items. If we did, it's usually just one or two new things here and there (we stock standard dance apparel--I work at a dance school, so we like to have some things onhand for the kids).
I haven't made the order form official yet, but this is what I have so far:

Date	Customer Name	Item Number	Item Size	Item Color	Quantity
		N625C	1m		1

If I need to add new items, I actually have a "Shipments Received" form that looks like this:

Date	Vendor	Invoice Number	Item Number	Item Size	Item Color	Quantity	Cost

When you say current status, do you mean in or out of stock? I simply have a "Quantity on Hand" column at the end of the inventory sheet that will show how many we have minus the orders, plus the shipments received.

All this being said, if you think that there is a better or more efficient way to set it up, I'm definitely open to suggestions!

Thanks so much!

Reply

Answer 5

Apr 10, 2014 12:10 PM in response to ashleyhiggs

OK. So it seems you will need three tables:

1) "Shipments Received"

2) "Orders"

3) "Summary"

Reply

Answer 6

ashleyhiggs Author

Level 1

0 points

Apr 11, 2014 8:28 AM in response to Wayne Contello

Yes, that's precisely what I will need.

That being said, that's just what I thought would work best and I'm no expert on the matter. The reason I thought separate sheets would work is so anyone in the office could process an order or a shipment without messing with the actual inventory, which I'd like to be locked.

Reply