First off, I shoud say that I don't expect a complete answer to this
question. Rather I am looking for hints or ideas on how to proceed, or links
to articles dealing with this issue.

I am tracking inventory/spoilage for items with a 7 day shelf life. I have
the following data:

Amount produced (& shipped to store 3 days a week)
Amount sold on a daily basis

What I want to track
Amount spoiled (ie unsold after 7 days on shelf)

My first stab at this was to look at cumulative figures, but that is not
going to work because I need to know how my spoils are changing over time. I
also will be using this data to plan future orders, so I need to know the
recent (last 1-3 weeks) spoilage rates. (The ordering will actually be
mostly based on previous sales, but the spoilage rates will help make the
ordering more accurate.)

I'm thinking of using a second column next to amount produced that will
start with the amount produced and decrement each time a sale is made. If
that column is non-zero for a particular date, then I increment my spoils 7
days after that date.

Does anybody have other suggestions for a better way to approach this?

TIA
Marcotte


I don't know if I need to use VBA to do this, but I have a feeling it might
come to that