inventory formulas

[marsha]

I want to use a spreadsheet to track inventory and value of inventory.
I think a 4 column spreadsheet would do it the way I need to track items.
The first column would be Items. Tomato sauce might be item one
for instance.

The second column would be Quantity. Under Quantity
on the Tomato row might be 12 cans.

The third column would be Case or Tub Price and would provide the
units per case and case price (so it might read 5 per case $25).

I need a formula for the fourth column which would be Price per item or
pound
(i.e. $5/can) which is derived by dividing the number of items per case
into the total cost of case.

The last and fifth column would also need a formula and would be
the Value of Inventory. So if there were only 4 cans, the value for that
row (the tomato sauce) would be 4 x $5 = $20.

I'm sorry to cover this in such detail but I have been doing this all by
hand every day. How do I put in the formulas to automatically get the
correct amounts without having to do the math every day??

Thanks very much to the kind soul who helps.

[JMB]

You won't be able to combine the Case Price and Units Per Case. Excel will
treat this as text and your formulas won't work. If we have 5 columns

Column A - Item Description
Column B - Quantity
Column C - Case Price
Column D - Units Per Case
Column E - Inventory Value

A2 = Tomatoes
B2 = 4
C2 = 25
D2 = 5

In cell E2 type

=C2/D2*B2

then select cell E2, click on the black cross in the bottom right corner of
the cell and drag your mouse down as far as you need to go. Be sure not to
combine text with your numbers as Excel will treat the entire thing as text
instead of numeric data. Of course, if Column D is 0, you will get a divide
by 0 error in Column E. To clean this up you can change the formula to

=IF(D2=0,"",C2/D2*B2)





"marsha" wrote:

> I want to use a spreadsheet to track inventory and value of inventory.
> I think a 4 column spreadsheet would do it the way I need to track items.
> The first column would be Items. Tomato sauce might be item one
> for instance.
>
> The second column would be Quantity. Under Quantity
> on the Tomato row might be 12 cans.
>
> The third column would be Case or Tub Price and would provide the
> units per case and case price (so it might read 5 per case $25).
>
> I need a formula for the fourth column which would be Price per item or
> pound
> (i.e. $5/can) which is derived by dividing the number of items per case
> into the total cost of case.
>
> The last and fifth column would also need a formula and would be
> the Value of Inventory. So if there were only 4 cans, the value for that
> row (the tomato sauce) would be 4 x $5 = $20.
>
> I'm sorry to cover this in such detail but I have been doing this all by
> hand every day. How do I put in the formulas to automatically get the
> correct amounts without having to do the math every day??
>
> Thanks very much to the kind soul who helps.
>
>
>

[marsha]

Wow, thanks!!!!!!!


"JMB" <[email protected]> wrote in message
news:[email protected]...
> You won't be able to combine the Case Price and Units Per Case. Excel
will
> treat this as text and your formulas won't work. If we have 5 columns
>
> Column A - Item Description
> Column B - Quantity
> Column C - Case Price
> Column D - Units Per Case
> Column E - Inventory Value
>
> A2 = Tomatoes
> B2 = 4
> C2 = 25
> D2 = 5
>
> In cell E2 type
>
> =C2/D2*B2
>
> then select cell E2, click on the black cross in the bottom right corner
of
> the cell and drag your mouse down as far as you need to go. Be sure not
to
> combine text with your numbers as Excel will treat the entire thing as
text
> instead of numeric data. Of course, if Column D is 0, you will get a
divide
> by 0 error in Column E. To clean this up you can change the formula to
>
> =IF(D2=0,"",C2/D2*B2)