Forecasting = loan planning or depreciation??

G

Gizmo63

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.
 
P

Philip J Smith

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_stock,0))),"Never",INDEX(Week,1,MATCH("Stockout",Closing_stock,0)))

Hope this helps

Regards
Phil

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