Count the number of cells required to add up to the reference cell.

[dwinter]

Hello,

I am a production planner. One of the sheets I use takes our inventory and compares it to this week's forecast to give me a days of supply. This is not the most accurate as our forecast can fluctuate quite a bit on some products from week to week. Is there a way to have it actually count the number of cells (weeks) that it will take to deplete the inventory? I would prefer this in a decimal, but I can adjust the forcast to a daily if needed.

A sample worksheet is attached. The formula I want to adjust is in column E of Sheet2.

 

[bosco_yip]

Welcome to the board.

Here is my formula solution based on your formula concept, together with 2 columns helper.

1] Forcast of DOS (days)

[F2]: =DATEDIF(1/1/2023,ROUNDDOWN(B$1-WEEKDAY(B$1,1)+1,0),"yd")

2] Forcast of DOS (inventory)

[F3] copied down: =XLOOKUP(ROUNDDOWN(B$1-WEEKDAY(B$1,1)+1,0),Forecast!$B$1:$M$1,Forecast!$B$2:$M$13,0)

3] As per criteria of DOS (days)

[G2]: =DATEDIF(1/1/2023,$B$1,"yd")

4] As per criteria of DOS (inventory)

[G3] copied down: =F3/F$2*G$2

5] Adjust of column E, DOS

[H3] copied down: =D3/G3*7

 

[dwinter]

Welcome to the board.

Here is my formula solution based on your formula concept, together with 2 columns helper.

1] Forcast of DOS (days)

[F2]: =DATEDIF(1/1/2023,ROUNDDOWN(B$1-WEEKDAY(B$1,1)+1,0),"yd")

2] Forcast of DOS (inventory)

[F3] copied down: =XLOOKUP(ROUNDDOWN(B$1-WEEKDAY(B$1,1)+1,0),Forecast!$B$1:$M$1,Forecast!$B$2:$M$13,0)

3] As per criteria of DOS (days)

[G2]: =DATEDIF(1/1/2023,$B$1,"yd")

4] As per criteria of DOS (inventory)

[G3] copied down: =F3/F$2*G$2

5] Adjust of column E, DOS

[H3] copied down: =D3/G3*7

View attachment 82761

Thanks for the reply! This is not quite what I am looking for. I would like something that can essentially look take my ending inventory, and subtract the forecast from it and go down the line until it hits zero.

For example, product 8 has 107,760 inventory. I would want it to do somethinig like this: 107760-30061(B9)=77699-27321(C9)=50,378-27734(D9)=22,644-27433(E9)=-4,789

So I would want the answer to be 3.83 (3 full weeks covered then 0.83 of the 4th week covered).

Is there a way I can do this?

 

[bosco_yip]

Thanks for the reply! This is not quite what I am looking for. I would like something that can essentially look take my ending inventory, and subtract the forecast from it and go down the line until it hits zero.

For example, product 8 has 107,760 inventory. I would want it to do somethinig like this: 107760-30061(B9)=77699-27321(C9)=50,378-27734(D9)=22,644-27433(E9)=-4,789

So I would want the answer to be 3.83 (3 full weeks covered then 0.83 of the 4th week covered).

Is there a way I can do this?

1] This part:
>>
"107760-30061(B9)=77699-27321(C9)=50,378-27734(D9)=22,644-27433(E9)=-4,789"

Q: What is the rule of deduction? your above example deducts 4 forecast amounts.

And

2] This 2nd part: "So I would want the answer to be 3.83

Q: How do you get 3.83 ???

Then,

It seems that your 2nd reply to explain and new requirement in post#3 does not match with your post#1.

As per forum rule " One post one question"

You need open a new thread for your new question with clear explanation/rule of expected results.

This post is closed

Regards