Calculating current cover based on forward requirements...

[Matty]

Hello All,

This should be fairly straight forward, but I'm struggling to construct a formula to give me the result I want.

I have drawn up a simple summary to show what I am trying to achieve:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10
2	Demand	10	15	12	8	16	20	13	15	7	25
3	Production	0	25	0	0	25	0	25	0	25	0
4	Stock	35	25	35	23	15	24	4	16	1	19
5	Cover(Days)	?	?	?	?	?	?	?	?	?	?
Sheet1

I want to calculate how much cover, in days, the current Stock position gives me, based on the forward Demand that exists. If the days cover is actually negative, the formula should treat it as such.

I'd greatly appreciate any suggestions people have got.

Thanks,

Matty

 

[RalphA]

Matty:

How about defining what "cover days" means? And, how about providing some examples, say for B5:B8?

 

[Matty]

Hello,

In Week 1, 35 represents the opening Stock figure. Based on the forward Demand, the stock available in Week 1 represents c.19.8 days worth of cover (covering Weeks 1, 2 and part of 3).

There is Production planned in Week 2 (25 units), so this figure would also have to be taken into account in terms of calculating days cover for that Week.

Is it clear what I am attempting to achieve?

Thanks,

Matty

 

[RalphA]

Almost clear! How about giving us more examples, like the 19.8 days for cell B5, PLUS an explanation at how that figure (19.8 days) is arrived at? Please don't make us try to guess at information that you can easily provide. We are trying to help you with a formula, but please don't expect us to know all the details of your spreadsheet.

And, oh yes, how many days does your week have?

 

[Matty]

Hello,

A Week contains 7 days.

19.8 days was established as follows:

Stock at Week 1 (35) covers the forward Demand for Weeks 1, 2 and part of Week 3. Week 1 Demand equals 10, Week 2 Demand equals 15, so this is a total of 25. If you then divide the Week 3 Demand (12) by 7 (days), it suggests that 1.7 of Stock will be used per day. Therefore, there is enough Stock (as at Week 1) to cover Week 1 Demand (10), Week 2 Demand (15) and 5.8 days of Week 3, so a total of 19.8 days (7 days +7 days + 5.8 days) is what would go in Cell B5.

Cell C5 would be 27.6 days, because there is a Stock position of 50 (Stock + Production as at Week 2), and this is enough stock to cover Weeks 2, 3 and 4, as well as 6.6 days of Week 5 (6.6 days because Week 5 demand equals 16, so 16 divided by 7 equals 2.3 a day, and with a Stock position of 15 (going into Week 5), the Stock will last 6.6 days (15 divided by 2.3)).

I hope this helps to establish what I need the formula to do.

Thanks,

Matty

 

[Domenic]

Try...

B5, copied across:

=(SUMPRODUCT(--(SUM(B3:B4)>=SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))))+ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))-SUM(B3:B4)-B2:$K2)/B2:$K2)))*7

Or, rounded to one decimal...

=ROUND((SUMPRODUCT(--(SUM(B3:B4)>=SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))))+ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))-SUM(B3:B4)-B2:$K2)/B2:$K2)))*7,1)

Hope this helps!

 

[RalphA]

Matty, thanks for the new information. I did all the calculations by hand, and got the exact, same answer as I obtained by using Domenic's "rounded to one decimal" formula! So, there's your solution, which seems tremendous, to me! I know that I could not even begin to match anything like Domenic's solution. I think his logic is truly awesome.

 

[Matty]

Many thanks for your excellent solution, Domenic. It works fantastically!

For my own knowledge, it would be useful if you could break down the formula so that I can understand how it is achieving the result.

Thanks also to RalphA for prompting me to supply the necessary information which no doubt helped Domenic to understand what I was trying to achieve.

Kind regards,

Matty

 

[Domenic]

This part of the formula...

SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))

...returns the cummulative demand for each week. It returns the following array of values...

{10,25,37,45,61.....141}

Here's how SUMPRODUCT(...) is evaluated...

SUMPRODUCT(--(SUM(B3:B4)>=SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))))

SUMPRODUCT(--(35>={10,25,37,45,61.....141}))

SUMPRODUCT(--({TRUE,TRUE,FALSE,FALSE,FALSE....FALSE}))

SUMPRODUCT({1,1,0,0,0.....0})

Notice that the double negative '--' coerces TRUE and FALSE into their numerical equivalent of 1 and 0, respectively. SUMPRODUCT then sums the array of values and returns 2, which represents the number of complete weeks covered by the stock on hand and production.

Here's how ABS(LOOKUP(...)) is evaluated...

ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(B2:$K2,,,,COLUMN(B2:$K2)-COLUMN(B2)+1))-SUM(B3:B4)-B2:$K2)/B2:$K2))

ABS(LOOKUP(0,({10,25,37,45,61.....141}-35-{10,15,12,8,16.....25})/{10,15,12,8,16.....25}))

ABS(LOOKUP(0,{-3.5,-1.6666667,-0.8333333,0.25,0.625.....3.24}))

Notice that the lookup array {.....} is made up of numbers listed from smallest to largest. LOOKUP returns the largest value in the array {.....} that is less than or equal to the lookup value. In this case, it returns -0.8333333. This number is then passed to the ABS function, which returns 0.8333333. So the values returned by SUMPRODUCT and ABS(LOOKUP(...)) are added togther to give us 2.8333333. This amount is multiplied by 7 to give us 19.833333, which represents the number of days covered by the stock on hand and production. Lastly, this number is passed to the ROUND function which rounds it to one decimal place and returns 19.8.

Hope this helps!

 

[Matty]

Thanks for your explanation, Domenic.

The formula you provided is working very well for me.

Matty