Working out "weeks cover"

[sheepshagarmy]

Hi all,

Using the very simplified and cut down (and I don't run a fruit stall!!!) version of a purchasing sheet that I am trying to improve for someone, I was wondering if I could have some advice please...


It's very simple, they use this sheet to decide what stock to buy. Opening stock minus expected sales plus what's planned to be purchase this week gives an expected opening stock next week (week 2 below). They then use the weeks cover section to work out what they think they need to buy next week (and so on - they do this for 12 weeks at a time). At the moment the weeks cover is the opening stock divided by the expected sales - so how many weeks are covered if that was the sale every week.

I've challenged them on this because the weekly sales are not flat - seasonal uplifts, promos, events etc are fed in via a completely separate system by the commercial guys. This gives an obscured view of the week cover. I would like to put in a formula to the weeks cover section that looks to the following weeks sales and provide an accurate weeks cover based on the sales pattern. For example, below Lemons show 2 weeks cover because week 2 sales are 2 (cell G2 below), but if week 3's sales were 25, then having 2 wouldn't be enough despite the weeks cover on wk 2 showing them that it is.

So I somehow need the formula to go and look at the following weeks and return a weeks cover value that covers the actual expected sales and only up that point - if you see what I mean...?

In my head, I would imagine that some IF and AND formulas would be appropriate but cant seem to get my head round the best way around

Help much be much appreciated!

SSA

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]O/S wk 1[/TD]
[TD]Sales wk 1[/TD]
[TD]Purchase wk 1[/TD]
[TD]O/S wk 2[/TD]
[TD]Sales wk 2[/TD]
[TD]WEEKS COVER wk 2[/TD]
[TD]Purchase wk 2[/TD]
[TD]O/S wk 3[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2.3[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]1.5[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]