# Excel Solver: Min Std Dev and changing variable cells by a defined increments ?

1. ## Excel Solver: Min Std Dev and changing variable cells by a defined increments ?

I am wanting to use excel to do some high level production planning and I am stuck on an issue. My goals of the solver are to change the production numbers while respecting the following:
• Ensure the days on hand falls within the minimum and maximum amounts (min 10, max 70)
• Minimize the amount of variation from week to week. In general, Operations will want the most steady production
• Be able to control the increments in which the solver uses to solve the production quantity. For example, want to run in increments of 1000

In my attached example it keeps saying it cannot solve the problem but I seem to be able to do it manually with the exception of the increments piece as I have not figured that out yet nor know if it is possible. I am using Excel 2010. My set objective is to minimize the standard deviation of the production numbers. But that was just my best guess on how to achieve this and am open to other suggestions.

Any advice?

2. ## Re: Excel Solver: Min Std Dev and changing variable cells by a defined increments ?

I do not fully understand your solver model and spreadsheet. One observation that seems to be at least part of the problem. Two of your constraints are telling Solver to make D4:K4 between 10 and 70 (D11:K12). I notice that K10=7, and I cannot find a combination of values that will change this value. I am guessing that Solver cannot, either. Because it cannot force K10 to be between K11 and K12, it gives up. I also note that, by changing K11 to be 5 (or some other value less than 7, since 7 seems to be the value this cell wants to be), Solver can run and find a solution.

I'm not sure what your final solution should be, but I would suggest that you need to examine your constraints (either as defined in Solver or as computed in the spreadsheet) more carefully to make sure they accurately reflect the desired Solver model.

On edit: I also note that just about any value above 20340 (I even tried 40000) in D4:K4 seems to satisfy the specified criteria, so the overall model may not be very specific. If it is adequate, then great, but you may find that you sometimes get very different results for the same set of conditions.

3. ## Re: Excel Solver: Min Std Dev and changing variable cells by a defined increments ?

I do not see where anything is trying to change D4:K4, that should be a static row that contains the demand. D5:K5 are the ones that are set to change as these represent the production or the supply. The constraints are trying to make the D10:K10 between the values in D11:K11 and D12:K12. In other words, adjust the production quantity but ensure that the Days on Hand values remain within the range of 10 days and 70 days.

Your comment about K10 makes sense, when I scrubbed the spreadsheet to get rid of sensitive information I should not have taken off so many columns. See attached where I extended the data into the future but left the solve part alone. Row 10 is a formulat that calculates how long the value in row 7 (Projected Inventory) will last against the Forecasted Sales (row 4). So when it says 14 in cell D10, that means that the 44,681 will be enough inventory to last 14 days (or 2 weeks) against the forecast of 15,319 and 16,709.

Hope this helps clarify the problem.

On a side note, keying in 25,000 into each of D5 to K5 should meet all of the constraints. All of D10:K10 fall between 10 and 70 and the standard deviation is 0. I don't understand what I am missing to get the solver to do this.

Thanks
Matt

4. ## Re: Excel Solver: Min Std Dev and changing variable cells by a defined increments ?

As it stands now, there is nothing in D5:K5 (my mistake on the row #), which means the stdev() function returns an error, which Solver cannot work with. You need to enter something into D5:K5 as a starting value. When I tried it, that gave Solver something to work with (larger than expected, say start at 40000), and it was able to find a solution.

Another observation: I can enter an insanely large number into production and never get more than about 56 DOH, If I extend the OFFSET() function so that it is possible to get more than 70 DOH, I quickly (manually) find that any value for production between 20500 and 43500 appears to satisfy the constraints given. For a different approach to the analysis, you might determine the minimum production that meets the criteria and the maximum production, then report that anything within that range meets the criteria.

Another observation. The minimum in the stdev() will always be when all values in row 5 are equal. Unless there is something else in your real spreadsheet that changes that conclusion, this seems like almost a trivial solution. I am not in a position to understand how this fits into your strategizing, so I'm not sure I can suggest a better model. It seems to me that, minimizing the stdev() is not the most interesting variable for an objective function in this case.

5. ## Re: Excel Solver: Min Std Dev and changing variable cells by a defined increments ?

Try adding the constraint "D7:K7" >= 0 and select "GRG Nonlinear" as the solving methode before running solver.

Alf

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1