Documents
User Generated
Resources
Learning Center

# IA – Answers to excercise set 1

VIEWS: 11 PAGES: 3

• 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

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

1H. Build a user interface in Excel and Visual Basic for the solution of exercise 3 of the previous set. If
 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 ->
Define)
 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
quantities.
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
parameters.
 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.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Kill paramfile      ' delete old parameter file

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

On Error GoTo err_opt
Shell optimizer, vbNormalFocus

Exit Sub

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

mypath = ActiveWorkbook.Path
resultfile = mypath + "\params.txt"

Workbooks.OpenText Filename:=resultfile, DataType:=xlDelimited, Tab:=True
Selection.SpecialCells(xlCellTypeLastCell).Select
Range(Selection, Cells(1)).Copy
ActiveWindow.Close
Range("Results").Select
Application.DisplayAlerts = False ' if copy and paste areas of different size
ActiveSheet.Paste
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
s.t.
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