Formula to find months inventory coverage

[jolin78]

Hi,

Need help to find out the formulation to use to calculate the months of inventory coverage base on the Stock on hand ( Column B ) verus the monthly demand ( Column D to O )

 

[jasonb75]

There are a lot of things to take into consideration with such formulas, the simplest way would be to divide the stock on hand by the average demand of the last 12 months

=B2/AVERAGE(D2:O2)

Note that this method is simple, but far from accurate.

Entering 'inventory coverage' into google will provide you with a significant amount of information on the matter, the first couple of results list a number of factors that should be considered.

 

[Peter_SSs]

divide the stock on hand by the average demand of the last 12 months

=B2/AVERAGE(D2:O2)

From the dates in the headings, D2:O2 looks like the next 12 months?

@jolin78
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is this what you are after? The formula in C2 is an array formula so needs to be confirmed with Ctrl+Shift+Enter. You can then copy/drag it down.
Also note that OFFSET is a volatile function so might impact your sheet's performance if you have a very large number of rows with this formula.

20 03 09.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	SOH	Months Coverage	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21
2	425	7	23	39	69	54	94	77	21	90	64	96	94	86
3	7	0	10	39	57	24	50	11	10	86	49	44	61	40
4	289	4	35	92	4	74	90	85	10	84	36	55	62	99
5	500	11	90	1	7	1	8	73	50	46	66	31	92	43
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(MATCH(B2,SUBTOTAL(9,OFFSET(D2,,,,COLUMN(D2:O2)-COLUMN(D2)+1))),0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jasonb75]

From the dates in the headings, D2:O2 looks like the next 12 months?

I did notice that but thought it was a typo when creating the sample.

 

[Phuoc]

Or try this in C2:

=IFERROR(MATCH(B2,SUMIF($D$1:$O$1,"<="&$D$1:$O$1,D2:O2)),0)

Enter with Ctrl+Shift+Enter.

 

[Pkpdxb]

hi i need 3 months stock cover formula for excel
sales, open stock closing stock new order 3 months stock cover
using above tab please assist

 

[chrisaholmes]

Or try this in C2:

=IFERROR(MATCH(B2,SUMIF($D$1:$O$1,"<="&$D$1:$O$1,D2:O2)),0)

Enter with Ctrl+Shift+Enter.

this works but is there a way for it show to one decimal place?

 

[HeadyFinance]

this works but is there a way for it show to one decimal place?

Yes. I like to split it into several rows to not mash several formulas into one.

First, you have the full month coverage as specified above.

Then, you calculate how much stock you have covered, through a sum indirect address function. This function sums up the total stock x months forward, and refers to the full month coverage. In this case, it takes the next month's COGS and sums it with the following six months (as we have a full coverage of 7 months).

Next, you calculate how much of the month after the nth month you have covered.

And finally, sum that with the full month inventory coverage and you have the coverage to how many decimal points you want!

Hope that helps!