• 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.

Order Proposal Formula

Kristen Sorenson

Kristen Sorenson

New Member
I was wondering if you would help me figure out the formula for the new order proposal. I was asked to create a new row in this supply schedule that calculate the difference between the current weeks on hand and the target weeks on hand and then calculate the quantity to be reordered to maintain a target inventory level. However I keep running into a circular reference error.


The order proposal is using (Target weeks – Projected Weeks on hand)*Weekly Forecast to generate the order up to quantity. However, the quantity that is ordered needs to be included on the Weeks OH + PO or else it will keep accumulating the quantities since it is using Weeks on Hand + PO to calculate the difference. As an example, you will notice that the order quantities starting on 11/9 continue to increase higher and higher because the Weeks on hand + PO is never accounting for the order. I need help figuring out this formula so that I don’t keep running into a circular reference and so that SEQ #11 orders the right amount.

I can be reached at: ksorenson@lci1.com if it is easier to communicate my email.

Thank you!
 

Attachments

Hi ,

The problem is you have phrased your problem in words that you are familiar with. If you phrase your problem in terms that others can understand , you would get your answer much faster.

The ideal would be to :

1. Give the cell reference where you want a formula

2. Mention how you would want to copy the formula , is it across columns or is it down rows

3. Mention what are the input cells which will be made use of by the formula

4. Specify the logic by which the formula should generate the output using the data in the input cells

Mentioning the circular error is not really so important , since the error is being generated by the formula that is present in the worksheet ; obviously , the formula which will be presented as a solution by other members of this or any other forum , will not be the same as the existing formula , and equally obviously , those presenting their formula as a solution would ensure that there is no circular reference when the formula is used.

I say this because doing something from scratch given a clear problem definition is probably easier than to understand a solution which does not work and then to modify it so that it works.

Narayan
 
@Kristen Sorenson,

I'm afraid I don't have a complete answer, but I offer the following observations.

It appears that you have approached the problem in two ways:
  1. running balance (similar to traditional accounting)
  2. predicted vs. actual usage
In the end, I believe that you are mixing the two different approaches resulting in circular references.

Perhaps the best thing to do is to isolate one approach (perhaps in an adjacent area of the worksheet) and follow it through without introducing any aspects of the other approach. It seems the simplest would be "starting balance + qty received - qty used". This "ending balance" could then be compared to the target quantity to keep on hand based on projections.

I hope that at least helps spark some ideas.

Regards,
Ken
 
You must log in or register to reply here.
Back
Top