Frequency formula required

[Gaz19]

Hi All

Need a bit of assistance with the below.
I'm in the process of building a very high level capacity model for our factory.
We have set run quantities and the frequency in which we want to run the set quantities.

The document uses our current stock on hand levels to determine when to first make stock, there after i need the set run qty to be run based on the frequency starting from the first production run. this will change weekly and the first production run can shift forward or further out and the weeks there after would need to change accordingly to ensure the frequency between runs remains the same.

i have attached a document, first table is what i would like the data to display as.
second table shows the yellow area where I'm battling to populate the data.
i can not attached the actual file as it contains confidential info.

Thanks!

[bebo021999]

Try in G7:

Please Login or Register  to view this content.

Drag down and accross.

[Gaz19]

Thanks this works but only if my start point does not move forward or backwards by a week.
based on our forecast accuracy we see a lot of our production dates moving to ensure we meet the right stock holding.

in my sheet above i had all three SKUs starting production in week 3, should this move forward to week 2 then the timing will be off or am i looking at this incorrectly?

Essentially need a formula to pick up where my first make point is and then shift the run qtys there after to be in the right run frequency.

[bebo021999]

Do you mean RunQty starts from Week 1?
In D7:

Please Login or Register  to view this content.

[Gaz19]

sorry might not be explaining this clearly.

we refresh our input data every week to pull the latest stock on hand info.
based on the stock on hand info we get a certain stock holding.
based on this stock holding we will only need to make the first run qty in a certain week - this might be week 1 this week but if sales don't go to plan it could then shift out to week 2 the next time we run the document.

so the very first run qty is our starting point and from that starting point i need it then calculate when next to drop in the next run qty based on the run frequency.
so it will never be the same starting point and can have a side ways shift every week.