How to automate Goal Seek into formula

  • Hi All,

    I have been racking my brain to build formula that gives me certain specific result (Just like How Goal Seek works).

    What i need is 1.8 in row 13, how to write a formula in row9 to meet make row 13 as 1.8

    I think it's easier to explain in excel sheet. This is about inventory planning.

    1) I have production Plan Row 9 which is already fixed until May, i can only influence from Jun.

    2) I have sales plan . (Row 10)

    3) There is arrival pattern which is dependent on Production Plan with some pattern (Row 11).This is more or less fixed until Jun because production is fixed until May,

    4) There is Ground Stock (Which is calculated as Last month Closing Ground Stock +Current Month Arrival - Current Month Sales Plan).

    5)There is Month of Supply which is current Month Ground Stock over next month sales plan (Row 13).

    What i need to do is build a formula in row 9 which throws how much volume to produce so that your row 13 becomes 1.8 for future months.

    Row 15 shows a reference point , this is what i need in Row 12 so that all futre month becomes 1.8, what i am struggling is how to reflect this in row 9 so that i get a result of row 15 in row 12. I tried to build a formula in cell G9, but when i drag it to other cells in row 9 ,it throws reference error.

    Similarly for scenario 2, i just have change of arrival pattern.

    I kinda know that it won't be exact 1.8 all months, but can there be formula in row 9 so that it becomes somewhere closer to 1.8 in row 13 from Jul months onwards.

    Any help will be greatly appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!