Automatic MRP forecast - help needed

[brazill23]

Hi All,

Im building automatic order point but hit some wall Maybe anyone here is happy to help ?
I have managed to calculate weeks of cover but I want to now add formula with allow me to :

-if weeks of cover lower then 3 place an order
-calculate quantity for "planned orders" to top up back to 6 weeks of stock (how much stock to order to back to 6 weeks again).
- also when no forecast (0) its still deducting weeks not sure is it correct

Im happy to send file if this will help

[TABLE="width: 1048"]
<tbody>[TR]
[TD]Forecast[/TD]
[TD] 0[/TD]
[TD]500[/TD]
[TD]572[/TD]
[TD]1852[/TD]
[TD] 0[/TD]
[TD] 0[/TD]
[TD]2316[/TD]
[TD]1852[/TD]
[TD]1636[/TD]
[TD]1158[/TD]
[TD]1852[/TD]
[TD]1636[/TD]
[TD]1158[/TD]
[/TR]
[TR]
[TD]Opening Stock[/TD]
[TD]3,182[/TD]
[TD]3,182[/TD]
[TD]2,682[/TD]
[TD]2,110[/TD]
[TD]257[/TD]
[TD]257[/TD]
[TD]257[/TD]
[TD]-2,058[/TD]
[TD]-3,911[/TD]
[TD]-5,547[/TD]
[TD]-6,705[/TD]
[TD]-8,557[/TD]
[TD]-10,193[/TD]
[/TR]
[TR]
[TD]Planned Orders[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stock Cover[/TD]
[TD]5.1[/TD]
[TD]4.1[/TD]
[TD]3.1[/TD]
[TD]2.1[/TD]
[TD]1.1[/TD]
[TD]0.1[/TD]
[TD]-1.1[/TD]
[TD]-2.4[/TD]
[TD]-4.8[/TD]
[TD]-3.6[/TD]
[TD]-5.2[/TD]
[TD]-8.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Skip[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order[/TD]
[TD]Order
[/TD]
[/TR]
</tbody>[/TABLE]
Stock cover formula for 5.1 (week1) :

=IF(K60>K61,K61/K60,IF(K60+L60>K61,1+(K61-K60)/L60,IF(K60+L60+M60>K61,2+(K61-K60-L60)/M60,IF(K60+L60+M60+N60>K61,3+(K61-K60-L60-M60)/N60,IF(K60+L60+M60+N60+O60>K61,4+(K61-K60-L60-M60-N60)/O60,IF(K60+L60+M60+N60+O60+P60>K61,5+(K61-K60-L60-M60-N60-O60)/P60,IF(K60+L60+M60+N60+O60+P60+Q60>K61,6+(K61-K60-L60-M60-N60-O60-P60)/Q60,IF(K60+L60+M60+N60+O60+P60+Q60+R60>K61,7+(K61-K60-L60-M60-N60-O60-P60-Q60)/R60,"8+"))))))))