Inventory Spreadsheet

[michelle]

I have basic knowledge of excel and have been reading myself nuts with
different functions. I am trying to put together a spreadsheet that will give
me live Inventory availability. I've tried all kinds of functions in all the
totals row to get the totals in "Available Inventory" and "Total ON
HOLD". Once I think I have it figured out then I won't have product in the
Hold column and it throws off my total in "Total ON HOLD". Thank you in
advance for any help. This is what I want it to look like.

Available Inventory 3,900
Total ON HOLD 100

Date HOLD Shipped Inventory Produced
3/1 Beginning Inventory 5,000
3/3 Shipped Product 2,000
3/4 Produced 1,000
3/5 Sold 100
Totals 100 2,000 5,000 1,000

 

[Roger Govier]

Hi Michelle

I think you only need 4 columns
Date Produced Sold Shipped

Put your opening inventory in the "Produced" column.
Available Inventory = SUM(B:B)-SUM(C:C)
On Hold = SUM(C:C)-SUM(D)
Total Inventory = SUM(B:B)-SUM(D)

 

[michelle]

Thank you for your assistance Roger, I really appreciate the help. The
functions don't seem to give me the numbers I'm looking for. Maybe I
misunderstood what you shared with me. We produce and quite often have stock
inhouse. Out of this stock we sell a product but it doesn't ship right away.
I want to put that amount of stock for that product on hold. So I need to
know the amount I have on hold (Total on HOLD) and the amount that is
available to ship (Available Inventory). If I ship product then that amount
should be subtracted from the available inventory. If I don't have
anything on hold this gives me a negative number. I've tried the if
functions but I can't seem to get those right either. Thanks again for your
help.
Michelle

Available Inventory = SUM(F7:F9)-SUM(G7:G9) gives me 2,500
Total ON HOLD = SUM(G7:G9)-SUM(H7:H9) gives me (500)
Total Inventory = SUM(F7:F9)-SUM(H7:H9) gives me 2,000

Produced Sold Shipped
500

2,000 500

 

[Roger Govier]

Hi Michelle

Send me your workbook and I will set it up for you
to mail direct send to
roger at technology4u dot co dot uk
Change the at to @ and dot to period and remove spaces to get the correct
email address.