Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
perpetual inventory - stock level - math flow??
I am developing an excel spreadsheet that tell you the current inventory of
an item. The inventory count is dependent on how many we receive from an order, how many are returned by customers and how many we sell. These quantities can change daily. What I am interested in is to know just how many we have at any one time. I know it is the sum of all three but I am having difficulty ignoring yesterdays returns when I am entering today's data (returns). Any imput is greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
perpetual inventory - stock level - math flow??
I am developing an excel spreadsheet that tell you the current
inventory of an item. The inventory count is dependent on how many we receive from an order, how many are returned by customers and how many we sell. These quantities can change daily. What I am interested in is to know just how many we have at any one time. Here's one way. Using Sheet2 as a work space, enter these values: A1: "date" B1: "received" C1: "returned" D1: "shipped" E1: "current" A2: yesterday's date; for example, "04/20/2009" E2: starting inventory amount; for example, 1234 A3: =A2+1 then extend A3 down for as many days as needed. E3: =E2+B3+C3-D3 then extend E3 down for as many days as needed. The idea is to enter the activity for each day in columns B, C, and D for that day. The changing inventory level is tracked in column E. To have the current inventory level appear in a cell in another sheet use this formula the =LOOKUP(2,1/(Sheet2!E1:E1000<""),Sheet2!E1:E1000) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stock inventory | Excel Worksheet Functions | |||
minimum stock level | Excel Worksheet Functions | |||
Stock Inventory | New Users to Excel | |||
Effective yield rate - non-level premium flow | Excel Discussion (Misc queries) | |||
Effective yield rate - non level premium flow | Excel Worksheet Functions |