web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Forecasting, setting R...
Microsoft Dynamics RMS (Archived)

Forecasting, setting Restock Level and Reorder Points.

(0) ShareShare
ReportReport
Posted on by ShabbirEzzi 75

I am using Microsoft  RMS V1.3R, I would like to set my Restock Level and Reorder Point automatically based on quantity sold within a specified time frame, given the lead time to  replenish the inventory (X No. of days). Then have the Reorder point be the number of items sold within the time window.  Next have the Restock level be percnetage higher than the Reorder point. I would like to do this with a query in Store operations Administrator with SQL query...

This is what i was thinking... This will do take care of 90-95% of the inventory, and then will have to tweak seasonal items accordingly....

X = Qty Sold with within Time Frame

Y = Number of days in Time Frame

Z= Lead time to replenish Stock

T = % Buffer (Amount of conservative)

Reorder Point = Round Up ( (x / y) * (Z) * (t/100)  )

Restock Level = ( Reorder Point ) * 20%

 

Then for all item set new Reorder Point and Restock Level

 

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    I have an automatic replenishment module that does the job for u, please send me email to ahmed.nashat@gmail.com so that I can send u a demo

  • Community Member Profile Picture
    Community Member on at

    This module suggest PO quantities analyzing the sales history along with the supplier delivery schedule to calculate the reorder point and restock level and therefore estimates the needed quantity until the next delivery

  • Verified answer
    Ron Rahhal Profile Picture
    Ron Rahhal 545 on at

    Here's something to get you started.

    First, run this query, and modify it as needed:

    select i.ItemLookupCode,i.Description,sum(te.quantity) as Sales,s.SupplierName

    from Item i

    join transactionentry te

    on i.ID = te.ItemID

    join [Transaction] t

    on t.TransactionNumber = te.TransactionNumber

    join Supplier s

    on i.SupplierID = s.id

    where t.Time between '01-01-2011' and '03-31-2011'

    group by i.itemlookupcode,i.Description,s.suppliername

    order by s.SupplierName,i.ItemLookupCode

    This will give you total sales for a 90 day period, sorted by supplier (of course, you'll modify the date range).  Now, there a lot of things you can do at this point, but given the information, I would do the following:

    1. Divide the quantity sold by 12.85 for the vendors from whom you order weekly.  This will give you an idea of your average weekly sales (90 days divided by 7 = 12.85).  For suppliers with a different ordering scedule, simply modify the formula.

    2. Use these results to supply a formula for Reorder Point and Restock Level.

    Please post back if I can assist further.

  • ShabbirEzzi Profile Picture
    ShabbirEzzi 75 on at

    Thanks Ron for your help.. This is exactly what I was looking for, is  there a way i can include the calculations in the query and update the Reorder pt and Restock Level all in step.

  • Ron Rahhal Profile Picture
    Ron Rahhal 545 on at

    Hi Shabbir,

    I can do that.  It would be a bit more complex; I would create views based on the supplier, allow you to browse the changes (the fields might be ItemLookupCode, Description,  Supplier, Old Reorder Point, New Reorder Point, Old Stock Level, New Stock Level) and would take me a little while, so I would have to charge for it.  If you'd like to explore this, email me at ron AT bestpossales.com.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans