goal seek can't change DOS over 30 days

[piroro19]

i am try to use goal seek to determine the required material to change the dos to over 30 days but it cant come up with an equation. i believe the formula used to calculate the dos is restricting it but i cant seem to figure out why it is restricting or where in the formula us restricting it.

[Richard Buttrey]

Can you clarify. Since the DOS calculation ripples through to the last column, is the goal to end up with 30 in J9. If so just set J9 to 30 using D3.

If this is not what you want please explain further.

[piroro19]

the goal is to be able to change any of the months DOS to any no of days. however if i try to use goal seek to get lets say 40 days for example, it wont change the numbers of days beyond 30 days. so i am guessing the calculation i used to solve for days of stocks is restricting it. so i am trying to change the formula so it doesn't restrict the DOS value. it is a large file which i have a vba code that uses goal seek to solve it but this is just one section of the file. Let me know if you need anymore clarification

[Richard Buttrey]

Please explain how you use this in practice. Give a step by step description of what you want to do and what you want to happen in ALL the periods

AT the moment I'm not clear. are you looking to see the chosen number of DOS the SAME in all months.

[josephteh]

Days of Stocks should be a simple average of Opening Inventory & Ending Inventory divided by Demand * no. of days.
Cell D9 =AVERAGE($D3,D8)/SUM($D7:D7)*COUNTA($D2:D2)*30.

Using Goal Seek to maintain DOS at 40 every month, the Order Qty would be per row 5.

[Richard Buttrey]

If I understand the request there's still much more we need to know.
How are you defining DOS. Presumably that's dependent on the Final Inventory, but then what?
Is the DOS the number of days it will take to exhaust the final inventory for any month based on the Demand in future months. i.e. a demand of say 3000 in the next month will produce a lower DOS statistic than a demand of 2000.
If not that then how do you define the DOS value since if it's to be a set number like say 40 then that will be affected by the values that define the Fina inventory

Given that days of stock are dependent on four other variables (rows 4,5,6,& 7, which one (of four) do you want the system to change? And in that case are you inputting the other 3 manually?
If you want the system to change more than one then the system will need to know a relationship between them.

It may be that the Goal Seek is not what you want if there are known relationships between the rows, in which case simple formulae may be sufficient.

[piroro19]

The DOS is determined by if the ending inventory one month is enough for the next month. i.e is the ending inventory in august enough for the demand in September.
if the ending inventory = demand then the days of stock will be 30 days but if the required DOS in a month is say 40 days then the ending inventory would have to be more than the demand.
The requirement would have to be updated to get said ending inventory needed. for example: if the DOS for september is to be 40 then the requirement in august has to be updated so the ending inventory is enough.
Goal seek is been used in a vba code to determine the requirement as there are so many material types. However for some reason the DOS sometimes restrict it to 30 days and below.
My question is if anyone can notice an issue in the formula used to calculate the DOS that is restricting the DOS from changing. sometimes the DOS will jump to 120 if the DOS is to be over 30 days. . it is hard trying to explain but i hope this helps

[piroro19]

i have added another material to the excel file. one of the materials i am having the issue with. if i try to use goal seek to solve for the DOS in November to over 30 days, it wont work. The DOS circles back and forth between 30 and 120 and can't come up with a solution to achieve any day over 30 days

[piroro19]

can you explain what you did there. i dont understand

[Richard Buttrey]

I think we need to clarify the basic understanding of the principles involved here.

You seem to be wanting the goal seek functionality to work across several columns to create the the same predetermined DOS for each period.

The Goal seek works by changing the value of one cell to create the result you want in another single cell, hence I don't see how a goal seek can achieve what you want.
More importantly the all important ending inventory for a month is a precedent for the next months opening inventory.
In order to then calculate the DOS for that month something needs adjusting, i.e either the requirement or the demand. Either or both COULD be changed but how should the system decide which, and if both then there are an infinite number of changes.

For instance,
Take July. The ending inventory is 3412. There are 31 days in August so 3412/31 = 110, i.e. > than the 40 days required
Then August. The ending inventory is 3518. There are 30 days in September so 3516/30 = 105, i.e. > than the 40 days required
Take September. The ending inventory is 749. There are 31 days in October so 3412/30 = 24, i.e. < than the 40 days required. So what should be altered? The requirement in Sept could be changed to 500, or the demand changed to 1909, both giving the 40 DOS required

But of course they could both be altered by the same amount, one up one down to keep the DOS at 40.

In summary, and I'm correct in my understanding of what you want to see I don't believe Goal Seek is what you want. And given that there are two independent variables it's not clear to me how you can do this without there being some more rules or restrictions. I'm not even sure SOLVER would offer a solution.

Perhaps a way forward is for you to manually enter ALL the values you expect to see from July onwards given the starting values in June with an explanation of why you've chosen the numbers you have. Then we might have a chance of deriving a solution.