Multi-solver

[Haantje]

Hi,
I am developing an excel worksheet that helps with inventory problems

For helping to solve the problems I use the solver tool.
Every row represents a product and the thing is to find an inventor
level that suits a target service level.
In excel terms cell C2 is the target cell that has to take value 95 b
changing cell A2. To execute the solver tool for one product(=one row
is no problem, but I like to execute this for over 1000 product
(=rows). Is there a way to do this all very fast or do I have t
execute the solver tool more than 1000 times.

thanks in advance
Joost Hanegraaf
The Netherland

 

[R Grinde]

This sounds like something Goal Seek can handle, as long as you're
changing only one cell and you're trying to have the target cell achieve
a specific value.

To execute Goal Seek (or Solver) repeatedly would require some VBA code.

Alternately, can you algebraically solve the equation for the inventory
level (for a specific service level), and then use that directly?

HTH,
Roger

 

[Tushar Mehta]

What is the relationship between A2 and C2? Is the optimization
something that can be done algebraically?

You could create a single larger problem that encompasses multiple
products. If you are using the version of Solver that is included with
XL, the limit on the number of variables is 200. So, in some cell, say
H1, enter the formula =SUM(G1:G200). In Solver, set the optimization
criterion to be H1 value equals 190 (which is 200*0.95). Add the
constraint G1:G200>=0.95. In the Options dialog box check 'Assume non
negative' and assuming your model is indeed linear check 'Assume linear
model.'

Solving 200 variables with a dummy problem I created took about a
second or 2.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Haantje]

Thanks,

VBA is still an option but the solution Tusher gave works fine.

I already did some research to solve the problem algebraically, bu
experts in that field said it wasn't possible.

Thanks for the tips. BTW, great site Tusher.

Regards,
Joos