Available to sell

[Hugues]

Good afternoon,

I work for a company that imports what it sells and I need help telling my
sales dept what quantities we have for sale above and beyond the forecast.

Example; I have 1,000pcs of an item in stock. Forecasted sales per week is
50pcs. 12 weeks from now (when my stock balance is 400pcs), I am receiving
more goods (800pcs). My available to sell today, above and beyond the
forecast, is therefore 400pcs because it is my MIN balance before I receive
my next PO.

How may I translate this into Excel? I need a formula that always returns
the MIN value of stock before the next PO comes in.

Thanks,
Hugues

 

[Mike H]

Hi,

If i've understood correctly then try this. Build a table that looks like
this, mines in a1 to c2

stock weekly sales lead time weeks
1000 50 12

Put this in c2
=A2-(B2*C2)

Mike