perpetual inventory - stock level - math flow??

[tcek]

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.

 

[MyVeryOwnSelf]

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 there:
=LOOKUP(2,1/(Sheet2!E1:E1000<>""),Sheet2!E1:E1000)