Calculate the number of weeks the stock on hand will last 1

[cashback]

I need to calculate how many weeks my on hand stock will last based on the weekly demands going out 30 weeks (current limit of data for each item).
Column A has the item code
Column B has the On Hand stock (each row = 1 week)
Column C has the weekly Demand

Data example
Cell B2 has 245 units as the On Hand inventory. B3 is calculated (B2 - C2)and so on for each cell in Col B.
Cells C2 = 46
Cells C2 = 64
Cells C2 = 62
Cells C2 = 62
Cells C2 = 59
Cells C2 = 57
Cells C2 = 57

I need the formula in Cell D2 to look at the value in B2 and then work out how many rows of demand from column C it will take to consume the 245 units. (answer should be 4.2 rows)
245-46-64-62-62-(11/59)to 1 decimal = 4.2

Can anyone give me the correct function I need to do this..

 

[ETID]

The Formula in Cell D2:

=IF(B6>C6,"",IF(B6>0,ROUND(ROW()-2+B6/C6,1),""))

...fill down

 

[ETID]

Oooops....Change 6 to 2 in the formula cell refs. !!!!

 

[SkipVought]

This is a more general solution EXCEPT I have not figured out how to generally calculate the fractional part, which in this SPECIFIC instance is calculated by B6/C6
[tt]
=SUMPRODUCT((ItemCode=A2)*(OnHand>0))-1
[/tt]
using Named Ranges via Insert/Name/Create - Create names in TOP row.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[cashback]

Thanks for the replies..
ETID, I the formula you gave me gave me a blank cell as the result. I changed the formula to =IF(B2<C2,"0",IF(B2>0,ROUND(ROW()-2+B2/C2,1),""))
This gave me a result but it was more a calculation of the value in B2/C2 rather than what I'm looking for.
I need the formula to take the value of B2 (245) and where B2 > C2 deduct the qty in C2 from B2 and if B2 is still > the result of B2 - C2 then the array should be B2 - C2:C3, and so on until B2 is no longer larger than the sum of the cells in column C. Once the number of rows it took to make the value in the array larger than B2 is known I need that to be the result.
My original example has a slight typo the values in the demand column (C) should be:
C2 = 46
C3 = 64
C4 = 62
C5 = 62 (and so on...)

Skip, your formula was at least using the sort of array I think I need but did not give the correct answer. It seems to count the number of rows where the On Hand qty was greater than zero for the item.

Hope this helps

 

[SkipVought]

Exactly! The only piece missing the the FRACTIONAL week usage.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[TonyJollans]

Hi cashback,

I'm not sure I like this and kind of think it should be easier but this amendment to Skip's formula should give you the fractional part as well..

[blue][tt]=SUMPRODUCT((ItemCode=A2)*(OnHand>0))
+ROUND(OFFSET(B2,SUMPRODUCT((ItemCode=A2)*(OnHand>0)),0)
/OFFSET(C2,SUMPRODUCT((ItemCode=A2)*(OnHand>0)),0),1)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[cashback]

Thanks again but still not there. As this information is used to plan purchases the aim is to never let the value in col(B) be less than at least 3 weeks demand from Column C. Hence counting how many rows the value from B2 is greater than the value in Col (C) is not quite correct.
My aim was to create an interactive sheet which my planners could use to see when there stocks fell below a certain amount of weeks cover and raise purchase orders to ensure the correct stock level was maintained. They would add the order qty to another column which would increase the OnHand value in Col(B) and then recalculate the weeks cover on the fly. This would allow them to see out into the future and decide if the order quantity was the best fit.
Result with current formula

D2 = 20.3 should be 4.2
D3 = 20.8 should be 3.3
D4 = 21.4 should be 2.3
D5 = 20.5 should be 1.2
D6 = 21.5 should be 0.2
D7 = 20.5 should be 1.3

Hate letting this sort of thing beat me..
I do appreaciate the help..

 

[SkipVought]

I need the formula in Cell D2 to look at the value in B2 and then work out how many rows of demand from column C it will take to consume the 245 units. (answer should be 4.2 rows)

aim is to never let the value in col(B) be less than at least 3 weeks demand from Column C

Seems like you are changing the requirement, bud!


Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH!}

 

[cashback]

Sorry Skip,
I thought I made it clear but looks like I failed.
My Fault not yours..Thanks for your trying.

Kevin

 

[ETID]

A revision to consider

=IF(MAX(D$11)=0,IF(B$2>SUM(C$2:C2),"",(ROW()-1)+(B$2-SUM(C$2:C2))/C2),"")

 

[TonyJollans]

Hi cashback,

My formula may not be quite what you want but I'm afraid I don't understand the calculations in your reply.

However, have you looked at using either a simple Goal Seek or the more comprehensive Solver Add-In?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[cashback]

Thanks to all who tried to help. I'm going to try another approach which may simplify the formula but not the amount of fields I will need to accomplish the result.

I'll make sure I explain any new threads more clearly in future.

Cheers

Kevin

 

[ETID]

Assuming each row is a week (starting with row 2)...

This formula in D2 (fill down) returns 4.19 in the row where the demand for that week/row exceeds the balance of the inventory.

=IF(MAX(D$11)=0,IF(B$2>SUM(C$2:C2),"",(ROW()-1)+(B$2-SUM(C$2:C2))/C2),"")

 

[cashback]

Thanks, I was trying to get the result in cell D2 instead of the row where the demand for that week exceeds the demand from B2. It certainly returns the correct result but forces me to have multiple columns to enable the sheet to calculate each subsequent row of Inventory vs Demand in the same way.
My data is extracted from my ERP system and has multiple item codes down the page with 40 rows for each item.
Several thousand rows in all..

My ultimate goal here was to create a screen which would display 1 item at a time with 30 weeks of data visible.
The idea was to have 40 weeks of data so the weeks cover could be calculated beyond the 30 weeks I would display.
Perhaps this should be done with Access and not Excel.

 

[ETID]

OK...Custom Function Time!

Paste this into a new module

Function StockDepletedWeek(Inventory As Long, Usage As Range, digits As Integer) As Double
Dim StockDepleted As Double
For Each c In Usage
If Application.WorksheetFunction.IsNumber(c) Then
Select Case StockDepleted <= Inventory And c <= (Inventory - StockDepleted)
Case True
StockDepleted = StockDepleted + c
StockDepletedWeek = StockDepletedWeek + 1
Case Else
StockDepletedWeek = Round(StockDepletedWeek + (Inventory - StockDepleted) / c, digits)
Exit Function
End Select
Else
End If
null_val:
If StockDepleted > 0 And c = "" Then
StockDepletedWeek = StockDepletedWeek + 1
Else
End If
Next
End Function

'the usage in the worksheet any cell...
'=StockDepletedWeek(Inventory,UsageRange,DecimalPlaces)
'=StockDepletedWeek(B2,C:C,2)

 

[cashback]

Perfect...

Works likes a charm.

Thankyou..

 

[cashback]

Hang On - Spoke too soon.

Seems the calcs get a little strange as they run down the sheet. 7.8 vs 5.8 (seventh line)

Example of Results
Col 1 = Inventory
Col 2 = Demand
Col 3 = Result from new function
Col 4 = Correct Answer
245 46 4.2 4.2
199 64 3.4 3.2
279 62 4.8 4.7
217 62 3.7 3.7
155 59 2.7 2.7
240 57 4.1 4.0
453 57 7.8 5.8
396 57 6.8 4.8
483 72 8.3 5.5
411 95 7.1 4.5
316 95 5.4 3.5
365 94 6.3 4.8
271 94 4.6 3.8
321 63 5.5 5.1
402 79 6.9 6.5

Not sure why this is happening..

 

[ETID]

'I'm still a little fuzzy on how to treat "surplus" inventory at the end of the list...

'try this function to illustrate:

Function StockDepletedWeek(Inventory As Range, Usage As Range, digits As Integer) As Variant
Dim StockDepleted As Double
For Each c In Usage
If c.Row < Inventory.Row Then
GoTo align_row
Else
End If
If Application.WorksheetFunction.IsNumber(c) And Cells(c.Row, Inventory.Column) <> "" Then
Select Case StockDepleted <= Inventory And c <= (Inventory - StockDepleted)
Case True
StockDepleted = StockDepleted + c
StockDepletedWeek = StockDepletedWeek + 1
Case Else
StockDepletedWeek = Round(StockDepletedWeek + (Inventory - StockDepleted) / c, digits)
Exit Function
End Select
Else
GoSub null_val
Debug.Print c
End If
align_row:
Next
Exit Function
null_val:
If StockDepleted > 0 And Application.WorksheetFunction.IsNumber(c) Then
StockDepletedWeek = StockDepletedWeek + 1
Else
StockDepletedWeek = CStr(StockDepletedWeek) + "; Surplus: " + CStr((Inventory - StockDepleted))
Exit Function
End If
Return
End Function

 

[cashback]

I think the best way is to have the figure you have added the the last row of col D appear in as the last record for each change of Item code in Col A.
Given a specified number of rows for each item (40) in my case the item number in Col A changes at cell A42.
I noted that the values in cells B42, B43, etc still had an effect on the previous item weeks cover in Cells C41 and above.

Can you force the last row of each item to close off the calculation and the next row starts fresh. ie the value in B42 (new item demand) will not effect any of the previous items calculations.
I'm not really worried about the value in the last row of each item as I only intend to display 30 rows to the users.
Hopefully having 10 extra rows enables a fairly accurate calculation of the weeks cover in the 30th week, provided I dont have greater than 10 weeks stock at that time. If I do I've got bigger problems than worrying about the accuracy of the figure. My other option is to increase the number of rows even further to creater a larger buffer.

Thanks again, I think I will be able to make very good use of the new function. Great work.