Any example implementations of solver used within an iteration by Yasaiw?

[Mark Neal]

Does anyone have a good working example of Solver being used either before or after Recalculation for each iteration with Yasiaw? 

I have implemented it and I'm getting an optimisation working in the first iteration, but not (as far as i can tell) in subsequent iterations.

I understand you would have to be careful with the recalculations. I'm not using any stochastic variables in the optimisation model, just using the parameter function to alter coefficients in the model that is optimised.

Cheers,

Mark

[Jonathan Eckstein]

I have never tried to do anything like this. It doesn't see like you
really need YASAI at all.

The following page looks useful:


https://msdn.microsoft.com/en-us/library/office/ff196600%28v=office.15%29.aspx

I would just write some VBA macro that calls the solver in a loop,
altering the data and using the above functions to start up the solver.

Another option would be to use YASAI with a sample size of 1, and have
your spreadsheet call some user-defined VBA function which in turn calls
some of the Solver VBA functions.

Prof. Eckstein

> --
> You received this message because you are subscribed to the Google
> Groups "yasai-simulation" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to yasai-simulati...@googlegroups.com
> <mailto:yasai-simulati...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Mark Neal]

Prof,

I could use VBA in a loop, but I'm trying to make it fit within Yasaiw to gain some interest from others in using it (by presenting some examples at a conference), and applying to a broader set of problems. 

The problem I have in mind is where you want to need to select two inputs (eg to a production process), one of which is fixed before the "state" is known (eg as defined by a given set of parameters). The other input is then optimised After the state is known (eg in an iteration). (The point being that Monte Carlo analysis doesn't usually allow for changing some inputs to improve results within a state/iteration (or at least by numerical optimisation techniques), being one of the main criticisms it receives around here.

So I have a simple problem in the attached file. It has a button linked to some VBA code in that worksheet (OptimseAtIteration) that I used to test the call to solver etc. That seems fine when the data is fixed (ie no yasai parameters involved). 

I have included a module with a sub (YASAIwBeforeRecalc) which calls solver code (OptiDuringIteration) basically identical to above. I use a Parameter to change states during the simulation (which it does by changing production function coefficients). With an Iteration of 1 (and just two scenarios/states), I hit the go button on Yasaiw, and optimisations occur, but because the parameter formula is being recalculated each iteration of solver, solver struggles (as you would expect with a moving target - ie objective function coefficients changing). 

If possible, we just want to either turn of the recalculation of parameter formulas (not possible?) or replace the parameter formulaes with their number while the solver is operating? I'm not good enough in VBA to know how to do either of theses options. Suggestions?

Cheers,

M

> <mailto:yasai-simulation+unsub...@googlegroups.com>.

[Jonathan Eckstein]

I'm sorry, I am finding this very hard to understand.

Prof. Eckstein

> > an email to yasai-simulati...@googlegroups.com <javascript:>
> > <mailto:yasai-simulati...@googlegroups.com
> <javascript:>>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "yasai-simulation" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to yasai-simulati...@googlegroups.com

> <mailto:yasai-simulati...@googlegroups.com>.