Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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

  • Ron Rahhal Profile Picture
    Ron Rahhal 545 on at
    Re: Forecasting, setting Restock Level and Reorder Points.

    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.

  • ShabbirEzzi Profile Picture
    ShabbirEzzi 75 on at
    Re: Forecasting, setting Restock Level and Reorder Points.

    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.

  • Verified answer
    Ron Rahhal Profile Picture
    Ron Rahhal 545 on at
    Re: Forecasting, setting Restock Level and Reorder Points.

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Forecasting, setting Restock Level and Reorder Points.

    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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Forecasting, setting Restock Level and Reorder Points.

    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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans