Learning Center
Plans & pricing Sign in
Sign Out

IA – Answers to excercise set 1


  • pg 1
									IA – Exercise set 4
The two last final assignments are a little more complicated than the previous assignments. They
should be (at least partly) completed to pass the course. If you have any problems, do not hesitate to
ask the lecturer for advice by email.

Grading will be based on the amount of points collected the previous assignments and the last

1H. Build a user interface in Excel and Visual Basic for the solution of exercise 3 of the previous set. If
your answer did not work properly, you can download the model answer from the course web page.
        Make a user interface sheet with a nice layout for the parameters and optimization results.
        Use Excel formulas to collect the parameters into the format that the optimizer wants. Name
           this area “Parameters”. (Paint the area and then use the command Insert -> Name ->
        Reserve an area named “Results” for the optimization results in the format that the
           optimizer produces. Place this area either directly on your user interface or place it
           elsewhere and use formulas to bring the results to the user interface.
        Insert a button to run a Visual Basic macro that saves the parameters to a parameter file and
           runs the optimizer. The macro should write the contents of the “Parameters” area to a text
           file. (You can use Tools -> Macro -> Record command to do this and then edit the macro a
           little). Below is a sample macro that does this.
        Insert a button to read the optimizer results from a tab-delimited text file and copy them to
           the “Results” area. Below is a sample Visual Basic macro that does this.

2H. The additional material (available in the locker outside the lecture room) describes how to solve
   the raw material mix problem in the stochastic case, where yield coefficients are stochastic
   Consider a much simplified single batch optimization case. Extend the previous assignment so that
   you can analyze what happens when the element concentrations parameters are independent
   stochastic normally distributed parameters.
        Enter standard deviations for the concentrations
        Enter formulas to compute normally distributed random concentrations. Use the
           deterministic concentrations as the expected value.
        Enter formulas to simulate the outcome of the raw material mix with the given random
        How often does the product mix “succeed”, i.e. satisfy product standard requirements? How
           often can it be “saved” in a second alloying phase by adding some pure raw materials?
        Add “safety margins” to the upper concentration bounds and see if you can produce more
           robust mixtures. This is the second approach for stochastic linear programming as described
           at the end of this exercise set.
Sub Optimize()
  mypath = ActiveWorkbook.Path
  paramfile = mypath + "\params.txt"
  optimizer = mypath + "\lpdemo.exe"

  On Error GoTo err_para

  Application.Goto Reference:="Parameters"

  On Error Resume Next ' no error handling

  Selection.PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False

  Kill paramfile      ' delete old parameter file

  On Error GoTo err_save
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs Filename:=paramfile, FileFormat:=xlText, CreateBackup:=False

  On Error GoTo err_opt
  Shell optimizer, vbNormalFocus

  Exit Sub

   MsgBox ("Parameters area not defined")
   Exit Sub
   MsgBox ("Saving parameter file failed")
   Exit Sub
   MsgBox ("Running optimization failed")
   Exit Sub
End Sub

Sub ReadResults()
  mypath = ActiveWorkbook.Path
  resultfile = mypath + "\params.txt"

  Workbooks.OpenText Filename:=resultfile, DataType:=xlDelimited, Tab:=True
  Range(Selection, Cells(1)).Copy
  Application.DisplayAlerts = False ' if copy and paste areas of different size
End Sub
Stochastic Linear Programming
In a stochastic optimisation some (or all) of the parameters are stochastic. In particular, a stochastic
linear programming problem can be written in the form

                                          min (max) z = cx
                                               Ax = b
                                            0  x  xmax

where the parameters c, and A are stochastic. There is no need for b and xmax to be stochastic, because
the problem can always be converted to a form these parameters are included in the A-matrix.

In the general case, the stochastic parameters may be dependent, have thus a joint distribution function
f(A). Sometimes the stochastic parameters are independent, and have their own distributions fij(aij). The
latter case is much simpler to handle.

There are several approaches, how a stochastic problem could be solved.

       1. The naïve way is to replace all stochastic parameters with their expected values and then to
          solve the resulting deterministic problem. With certain types of problems, this is a suitable
          approach. With other problems, such as the charge optimisation problem, this may results into
          infeasible solutions in real life.

       2. The so-called chance constrained formulation, that works only with inequality constraints.
          Instead of requiring that all constraints Axb must be satisfied, these constraints are relaxed to
          P(Axb)1-, where  the maximum allowed failure rate. Stochastic c-parameters are replaced
          by their expected values. This can be implemented by strengthening the inequality constraints in
          a suitable way. For example Axb can be converted into Axb-b, where b is a non-negative
          vector of coefficients.

       3. The problem is written as an unconstrained optimisation problem by including all non-
          deterministic constraints to the objective function as penalty terms. This means writing the
          problem as max z = cx – g(Ax-b) (or min z = cx + g(Ax-b)), where g() is some function that
          produces large penalty values when Ax-b>0. Then the expected value E(z) is maximised
          (minimised). This approach may easily lead to non-linear optimization problems.

To top