Questions

How to set up a formula in Excel for calculating inventory

+
0 Votes
Locked

How to set up a formula in Excel for calculating inventory

l_rmorin
I want Excel to calculate inventory on items and keep a running total as items are used.
For example I have 309 cases of Beef patties at the beginning of the week and 10 cases are used on monday and the another 5 are used on wednesday and another 5 ae used on thursday I want to show a running balance as they are used. I tried to set up a formula to do this and the end result always showed value instead of of the balance.
  • +
    0 Votes
    stevehab

    If your requirement is to show remaining inventory, and you have an reliable input on what has been removed from inventory, then there should be several ways to get where you want to be. Since I have no format to look at, this suggestion may fail to meet your needs.

    Once way would be to have a tab just for inventory. List dates in column A going down to show activity. Label what an entry is for , generally related to a meaning for "used", "purchased", or maybe adjustments for missing or spoiled. Begin with a row for beginning inventory. So, now you may have a list of 309, -10, -5, ... the sum of which is your current "book" inventory. My preference for this may be to leave a frew rows at the top, lock the rows so I can see my column headers and us the SUM function on the column with inventory numbers. This way you can refer to a fixed location if you are using this in another tab.

    Hope this helps or provides a spark that helps you.

    +
    0 Votes
    l_rmorin

    I can't show you exactly what I need as space here is limited.
    If I could e-mail you the worksheet I have set up then You would see what I am trying to do. What is below is not showing what I am trying to accomplish.

    Items Beginnig Amount Monday Tuesday Wednesday Thursday Friday Ending Amount
    Beginning Inventory 30 10 5 5 #VALUE!
    Beef Pattlies
    French Fries

    +
    0 Votes
    NickNielsen Moderator

    Do you want that total be the source for the next use? In other words, do you want to subtract the 10 cases on Monday to get a balance of 299, then open the program on Wednesday and see that 299 balance as your starting point? I don't know how you can do that in Excel without some serious VBA.

    A simple Excel solution is:

    Top row: starting inventory (manually entered)
    Next row: current balance (formula = starting inventory - sum(removals)
    Subsequent rows: individual removal entries.

    Or you could try a pre-built database solution such as this [free] one: http://www.freewarefiles.com/program_8_88_12345.html

    +
    0 Votes
    l_rmorin

    I start with 30 cases of an item and deduct 10 cases on monday which leaves me 20 cases for the rest of the week then on another day another day 5 cases are removed (used) which leaves me 15 cases for further use.

    I cant post the worksheet here as it soes not post correctly.

    +
    0 Votes
    l_rmorin

    Top row: starting inventory (manually entered)
    Next row: current balance (formula = starting inventory - sum(removals)
    Subsequent rows: individual removal entries.

    This gives me #Value instead of a resulting figure.

    +
    0 Votes
    NickNielsen Moderator

    The #VALUE means there is an error in the formula. Did you use "removals" as the variable without defining the range? This could cause the problem.

    Build the formuala like this:
    - click on the cell where you want the results displayed
    - type =
    - click on the cell containing the starting balance
    - type - sum(
    - click and drag from the first cell containing removal data to the last cell for that item.
    - press Enter
    - you should end up with a formula that looks something like this: =B6-SUM(C6:G6)

    I can also send you a sample file if you like.

    Edit: runaway bold

    +
    0 Votes
    l_rmorin

    I had not tried that. I will try it and I am certain that it will work.

    I was really getting frustrated with this. Thanks again.

  • +
    0 Votes
    stevehab

    If your requirement is to show remaining inventory, and you have an reliable input on what has been removed from inventory, then there should be several ways to get where you want to be. Since I have no format to look at, this suggestion may fail to meet your needs.

    Once way would be to have a tab just for inventory. List dates in column A going down to show activity. Label what an entry is for , generally related to a meaning for "used", "purchased", or maybe adjustments for missing or spoiled. Begin with a row for beginning inventory. So, now you may have a list of 309, -10, -5, ... the sum of which is your current "book" inventory. My preference for this may be to leave a frew rows at the top, lock the rows so I can see my column headers and us the SUM function on the column with inventory numbers. This way you can refer to a fixed location if you are using this in another tab.

    Hope this helps or provides a spark that helps you.

    +
    0 Votes
    l_rmorin

    I can't show you exactly what I need as space here is limited.
    If I could e-mail you the worksheet I have set up then You would see what I am trying to do. What is below is not showing what I am trying to accomplish.

    Items Beginnig Amount Monday Tuesday Wednesday Thursday Friday Ending Amount
    Beginning Inventory 30 10 5 5 #VALUE!
    Beef Pattlies
    French Fries

    +
    0 Votes
    NickNielsen Moderator

    Do you want that total be the source for the next use? In other words, do you want to subtract the 10 cases on Monday to get a balance of 299, then open the program on Wednesday and see that 299 balance as your starting point? I don't know how you can do that in Excel without some serious VBA.

    A simple Excel solution is:

    Top row: starting inventory (manually entered)
    Next row: current balance (formula = starting inventory - sum(removals)
    Subsequent rows: individual removal entries.

    Or you could try a pre-built database solution such as this [free] one: http://www.freewarefiles.com/program_8_88_12345.html

    +
    0 Votes
    l_rmorin

    I start with 30 cases of an item and deduct 10 cases on monday which leaves me 20 cases for the rest of the week then on another day another day 5 cases are removed (used) which leaves me 15 cases for further use.

    I cant post the worksheet here as it soes not post correctly.

    +
    0 Votes
    l_rmorin

    Top row: starting inventory (manually entered)
    Next row: current balance (formula = starting inventory - sum(removals)
    Subsequent rows: individual removal entries.

    This gives me #Value instead of a resulting figure.

    +
    0 Votes
    NickNielsen Moderator

    The #VALUE means there is an error in the formula. Did you use "removals" as the variable without defining the range? This could cause the problem.

    Build the formuala like this:
    - click on the cell where you want the results displayed
    - type =
    - click on the cell containing the starting balance
    - type - sum(
    - click and drag from the first cell containing removal data to the last cell for that item.
    - press Enter
    - you should end up with a formula that looks something like this: =B6-SUM(C6:G6)

    I can also send you a sample file if you like.

    Edit: runaway bold

    +
    0 Votes
    l_rmorin

    I had not tried that. I will try it and I am certain that it will work.

    I was really getting frustrated with this. Thanks again.