VIEWS: 11 PAGES: 3 POSTED ON: 3/17/2010
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 assignments. 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 -> 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 Workbooks.Add 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 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 Sub ReadResults() 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 Axb must be satisfied, these constraints are relaxed to P(Axb)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 Axb can be converted into Axb-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.
Pages to are hidden for
"IA – Answers to excercise set 1"Please download to view full document