• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Re-Order Point Calculation for Stock Management

E

Emily4774

New Member
I have created a document that shows me how many installs of my companies product can be achieved based on stock levels each month (all components are required for 1 install) and compared it to what the forecasted installs are.
I have also incorporated the predicted receipts of stock that should be coming into the warehouse for the remainder of the year.
Moving forward I want to add a re-order calculation section so that Excel will flag up in which month I need to order new stock (lead time for each component is 2 months)
However standard order quantities are different for each and I don't know how to amend the formula to account for the fact it has already told me to order more stock the previous month.
Any advice would be much appreciated.
Focussing mainly on the 'Re-Orders' section of my document.
Thanks
 

Attachments

  • Draft Forecast.xlsm
    52.8 KB · Views: 25
Hi ,

I have not understood your requirement.

Nowhere in your detailed description does any Excel worksheet reference find a mention.

You are likely to get an answer much faster if you explain your requirements using Excel worksheet cell / row / column references.

Which Excel worksheet is to be used ?

Which cells contain input data ?

Which cells should contain the output formula ?

What should be the logic to be used for deriving the output from the input ?

If you manually enter a few output values in the relevant cells , it will help to cross-check the correctness of the formula.

Narayan
 
Hi Narayan,

I am working on the TV120 worksheet.

Input data should be the opening stock and the forecasted installs to give output data in rows 31 to 35 and 68 to 72, 'Re-Orders'

I would like the formula to recognize that in two months time we will be low stock for a particular component and so flag that more stock needs to be ordered in X month in order to be there in Y month (2 months lead time).

I currently have a formula in rows 31 to 35 and 68 to 72, eg. =IF(E51>E63*1.5,"","1800") 1800 being the minimum order quantity that can be ordered (different for each component) however this formula does not take into account that if we do re-order in April that should be added to stock figures so that it doesn't flag up for us to order again the following month.

Again any advise would be appreciated.
 
You must log in or register to reply here.
Back
Top