Hi all, this is a re-post as I could really use some help on this.

I'm guessing there could be an accounting style function that may work for
this; it's like taking a total loan value, planning a series of variable
repayments and then asking at what point in the 'repayment plan' does the
'owed' value hit zero. e.g. the 5th payment or 9th or whatever.

"Gizmo63" wrote:

Hi folks,

Stock cover is usually based on history or a weeks snap shot (simply 10k of
stock with last week sales @ 2k = 5 weeks) buit this is not what I'm after.

Is there a function or cunning formula that will looks into the future
within a defined array and effectively say when stock would be exhausted e.g.

Sales forecast by week: 20 - 10 - 15 - 5 - 32
Open Stock at now: 47
Therefore looking ahead means that the stock will run out in less than 4 weeks
i.e. 20+10+15+5 47
Likewise 80 of stock would last less than 5 weeks.

In a nutshell I want to use the forecasted sales rather than history. Using
a hidden cumulative row is not really an option as the next stage would be to
extend the stock into forecasts to build a stock cover pattern for the future.

A macro is not an option, it has to be formula contained.

Any help and cunning solutions gratefully accepted.

Giz

Hi Gizmo

Standard excel functions do not allow for variable amounts - repayments etc.
However I have set out one possible work-around.
While you do not need to insert a row within the report area you will need
to utilise a working area.
The following is laid out in a worksheet in cells A1:F10

Open Stock at now 47 (Cell B1 = Open_Stock_at_now)
Stock runs out at week 4 (Cell B2, Formula below)
Report Area
Week (A4) 1 2 3 4 5
Sales forecast by week (A5) 20 10 15 5 32

Calculation Area
Week (A9) 1 2 3 4 5
Closing stock (A10) 27 17 2 Stockout Stockout

Cell B2 Formula is =INDEX(Week,1,MATCH("Stockout",Closing_stock,0))

Week is a named range including the week numbers 1 - 5, in this case B4:F4.
Closing Stock is the range B10:F10 including the sequence 27, 7, 2,
Stockout,Stockout.

The formula to calculate each of these elements in cells B10:F10 is given by:
=IF(Open_Stock_at_now-SUM($B5:B5)<0,"Stockout",Open_Stock_at_now-SUM($B5:B5))

Enter this in cell B10 and copy it across

The formula in B2 will give you the week number when stock runs out. If
stock never runs out then it will give the answer #N/A. To compensate for
this you can modify the formula in B2 to read

=IF(ISNA(INDEX(Week,1,MATCH("Stockout",Closing_sto ck,0))),"Never",INDEX(Week,1,MATCH("Stockout",Clos ing_stock,0)))

Hope this helps

Regards
Phil

PS if anyone can do this with a single array formula I'd love to see it!

"Gizmo63" wrote:

Hi all, this is a re-post as I could really use some help on this.

I'm guessing there could be an accounting style function that may work for
this; it's like taking a total loan value, planning a series of variable
repayments and then asking at what point in the 'repayment plan' does the
'owed' value hit zero. e.g. the 5th payment or 9th or whatever.

"Gizmo63" wrote:

Hi folks,

Stock cover is usually based on history or a weeks snap shot (simply 10k of
stock with last week sales @ 2k = 5 weeks) buit this is not what I'm after.

Is there a function or cunning formula that will looks into the future
within a defined array and effectively say when stock would be exhausted e.g.

Sales forecast by week: 20 - 10 - 15 - 5 - 32
Open Stock at now: 47
Therefore looking ahead means that the stock will run out in less than 4 weeks
i.e. 20+10+15+5 47
Likewise 80 of stock would last less than 5 weeks.

In a nutshell I want to use the forecasted sales rather than history. Using
a hidden cumulative row is not really an option as the next stage would be to
extend the stock into forecasts to build a stock cover pattern for the future.

A macro is not an option, it has to be formula contained.

Any help and cunning solutions gratefully accepted.

Giz