Calculate Work Days forward coverage of inventory

[cckeeler]

I am hoping someone can help me write a formula that will calculate the number of work days I currently have in inventory. An example of my data is below.

	Jan	Feb	Mar	Apr	May	Jun
Beginning Inventory	1000​	700​	500​	200​	600​	600​
Forecast	500​	200​	500​	600​	200​	200​
Receipts	200​	0​	200​	1000​	200​	100​
WorkDays	22​	21​	24​	22​	22​	23​
Days Forward Coverage	57.4​

I manually calculated the 57.4 which is saying that I have enough inventory to last me through Jan, Feb, and part of March (22 + 21 + (24*0.6)). I just can't figure out how to get a formula to do that for me. I need to be able to drag the formula so that say in February I know, based on the beginning inventory how many days forward coverage I have each month. Is there a way to do this in a formula?

 

[Eric W]

Welcome to the MrExcel forum!

Quite tricky, I had to add a helper row:

Book1
	A	B	C	D	E	F	G
1		Jan	Feb	Mar	Apr	May	Jun
2	Beginning Inventory	1000	700	500	200	600	600
3	Forecast	500	200	500	600	200	200
4	Receipts	200	0	200	1000	200	100
5	WorkDays	22	21	24	22	22	23
6	# of full months	2	2	1	0	2	1
7	Days Forward Coverage	57.4	45	24	7.333333	Beyond end of data	Beyond end of data
Sheet1

Cell Formulas
Range	Formula
B6:G6	B6	=IFERROR(MATCH(B2,MMULT(IF(ROW(INDIRECT("1:"&COLUMNS(B2:$G2)))>=TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(B2:$G2)))),B3:$G3,0),ROW(INDIRECT("1:"&COLUMNS(B2:$G2)))^0)),0)
B7:G7	B7	=IFERROR(IF(B6=0,B2/B3*B5,SUM(OFFSET(B5,0,0,1,B6))+(B2-SUM(OFFSET(B3,0,0,1,B6)))/OFFSET(B3,0,B6)*OFFSET(B5,0,B6)),"Beyond end of data")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cckeeler]

Welcome to the MrExcel forum!

Quite tricky, I had to add a helper row:

Book1
	A	B	C	D	E	F	G
1		Jan	Feb	Mar	Apr	May	Jun
2	Beginning Inventory	1000	700	500	200	600	600
3	Forecast	500	200	500	600	200	200
4	Receipts	200	0	200	1000	200	100
5	WorkDays	22	21	24	22	22	23
6	# of full months	2	2	1	0	2	1
7	Days Forward Coverage	57.4	45	24	7.333333	Beyond end of data	Beyond end of data
Sheet1

Cell Formulas
Range	Formula
B6:G6	B6	=IFERROR(MATCH(B2,MMULT(IF(ROW(INDIRECT("1:"&COLUMNS(B2:$G2)))>=TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(B2:$G2)))),B3:$G3,0),ROW(INDIRECT("1:"&COLUMNS(B2:$G2)))^0)),0)
B7:G7	B7	=IFERROR(IF(B6=0,B2/B3*B5,SUM(OFFSET(B5,0,0,1,B6))+(B2-SUM(OFFSET(B3,0,0,1,B6)))/OFFSET(B3,0,B6)*OFFSET(B5,0,B6)),"Beyond end of data")
Press CTRL+SHIFT+ENTER to enter array formulas.

This worked perfect, Thanks!