Stock Covers in Weeks Formula (MRP)

[Shady91]

Hello all,

I currently need help with a formula to calculate the Stock Covers in weeks as follows:

Week 1

Week 2

Week 3

Week 4

Week 5

Week 6

Week 7

Week 8

Order

OS

Cover

Req

Order

OS

Cover

Req

Order

OS

Cover

Req

Order

OS

Cover

Req

Order

OS

Cover

Req

Order

OS

Cover

Req

Order

OS

Cover

Req

Order

OS

Cover

Req

100

3.0

50

50

2.0

40

10

1.0

5

10

15

2.0

10

5

1.0

5

0

0.0

5

10

5

1.0

5

0

0.0

5

<tbody>
</tbody>

The formula should read that I have OS (Opening Stocks) = 100 in Week 1, then it would cover the requirements of Weeks 1,2,3 (50+40+5), then the Cover for Week 1 should be "3.0". Similarly, for Week 2 would be "2.0", etc. The table would be extended for 52 Weeks. Any ideas?

Edit: Opening Stocks for Week 2 = Opening Stocks for Week 1 - Requirement of Week 1 + Order-In of Week 2

Thanks in advance

 

[dermie_72]

you've stated the formula already, just translate that onto the sheet.
Week 2 OS
=(click on Week1 OS)-(click on week 1 Req)+(click on Week 1 Order)
If everything is the same click on week 2 OS once the above formula is in place
paste it into each week OS
If you have multiple SKU's, drag the formula down and adjust the Week1 OS as required.

 

[Shady91]

Hi Dermie_72,

I'm sorry for misunderstanding, I don't want the Opening Stocks Formula. I want to calculate the weekly covers (The numbers in red). Thank you though

 

[dermie_72]

It would be much easier doing it via a macro, as your have a variable on the opening stock, as well as the weekly demand.