# Stock Covers in Weeks Formula (MRP)

##### New Member
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

Last edited:

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.

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

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.

Replies
5
Views
552
Replies
4
Views
204
Replies
8
Views
499
Replies
11
Views
171
Replies
15
Views
533

1,219,957
Messages
6,151,145
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back