Months on Hand Inventory

[TG2812]

Hello All,

I'm currently looking for a way to calculate months on hand inventory. Looking around this forum I could not really find a simple way to get rid of my problem.
My dataset is as follows;

Model	Region	Configuration	Measure	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19
Model 1	Region 1	Configuration 1	My sales forecast	3	2	0	2	12	8	10
Model 1	Region 1	Configuration 1	My inventory	23	20	20	18	6	-2	-12
Model 1	Region 1	Configuration 1	My Inventory cover (in mths)

<tbody>
</tbody>

If the returned results is greater than 9 mths of coverage, then formula should return "> 9 mths"

Thank you all in advance for your time and great help.

 

[marious]

	A	B	C	D	E	F	G	H	I	J	K	L	M
4	Model	Region	Configuration	Measure	19-Jan	19-Feb	19-Mar	19-Apr	19-May	19-Jun	19-Jul
5	Model 1	Region 1	Configuration 1	My sales forecast	3	2	0	2	12	8	10
6	Model 1	Region 1	Configuration 1	My inventory	23	20	20	18	6	-2	-12
7	Model 1	Region 1	Configuration 1	My Inventory cover (in mths)	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F6 =E6-E5 E7 =IF(SUM(E5:M5)<E6,"> 9 mths",FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[TG2812]

Hi

I tried your formula but it does not work..not sure I understood what you were trying to do here.
Could you please shed some more lights?

Thanks!

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Model	Region	Configuration	Measure	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19
2	Model 1	Region 1	Configuration 1	My sales forecast	3	2	0	2	12	8	10	1	3	3
3	Model 1	Region 1	Configuration 1	My inventory	40	37	35	35	33	21	13	3	2	-1
4	Model 1	Region 1	Configuration 1	My Inventory cover (in mths)	9	8	7	6	5	4	3	2	1	0
sheet2

Cell Formulas
Range	Formula
E4	{=MATCH(TRUE,E$3:N$3<0,0)-1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[TG2812]

Hi ,

It unfortunately does not work..

 

[marious]

Hi

I tried your formula but it does not work..not sure I understood what you were trying to do here.
Could you please shed some more lights?

Thanks!

Sorry about that <e6, <e6="" ,<e6,<e6,<e6,"="">=IF(SUM(E5:M5) < E6 ,"> 9 mths",FALSE)

</e6,>

 

[Fluff]

Hi ,

It unfortunately does not work..

Are results shown in post#4 what you are looking for?

 

[TG2812]

Hi Fluff, Marious,

Thank you both for your time. Unfortunately, it still does not work.

However, I found a UDF on this forum which does the trick. What I'm trying to achieve here is to transform it into a native Excel formula where result >9 should be written "> 9 mths"
I did enter this function in cell E4 = MOH(E2,F3:N3)


Function MOH(Inv As Double, MOHArray)

MOH = 0
Dim i As Variant

For Each i In MOHArray
If Inv = 0 Then Exit Function
If Inv < i Then
MOH = MOH + Inv / i
Exit Function
Else
Inv = Inv - i
MOH = MOH + 1
End If
Next i

End Function

 

[Fluff]

Can you show us the results you would expect from your data & explain how you get that result.

With the data you supplied that function returns 0.15 for E4

 

[TG2812]

So here is the dataset i used for the example:

Model	Region	Configuration	Measure	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19
Model 1	Region 1	Configuration 1	My sales forecast	3	2	0	2	12	8	10	1
Model 1	Region 1	Configuration 1	My inventory	40	37	35	35	33	21	13	3
Model 1	Region 1	Configuration 1	My inventory cover (in mths)	8.67 (cell E5)	7.33	5.67	5.33	9.00	9.00	9.00	1.00

<colgroup><col><col><col><col><col><col span="3"><col span="4"></colgroup><tbody>
</tbody>

Cell E5 formula = MOH (E4,F3:N3). I then dragged this formula across the fifth row.

Hope this helps.