EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 19 Solver Re-Visited
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 19.1 Introduction
• 19.2 Review of Chapter 8
• 19.3 Object Oriented API in Risk Solver
  Platform
• 19.4 Application
• 19.5 Summary

3
Introduction

• Preparing an optimization problem to be solved by
  the Solver
• Preparing and running the Risk Solver Platform
  using ObjectOriented API
• Creating a dynamic optimization application using
  Object Oriented API

4
Review of Chapter 8

• Understanding the problem
• Preparing the spreadsheet
• Solving the Model

5
Review of Chapter 8

• In Chapter 8 we described how to transform a
  problem into a mathematical model and then use
  the Risk Solver Platform to solve it.
• We will review the main parts of a mathematical
  model and the Solver preparation steps.
• There are important steps which take place in the
  Excel spreadsheet before the Solver is used.
• Reading and Interpreting the Problem
• Preparing the Spreadsheet
• Solving the Model and Reviewing the Results

6
Understanding the Problem

• Mathematical models transform a word problem into
  a set of equations that clearly define the values
  you are seeking given the limitations of the
  problem.
• There are three main parts of a mathematical
  model.
• Decision variables
• Objective function
• Constraints

7
Decision Variables

• Decision variables are variables that are
  assigned to a quantity or response that you must
  determine in the problem.
• They can be defined as negative, non-negative, or
  unrestricted variables.
• An unrestricted variable can be either negative
  or non-negative.
• These variables are used to represent all other
  relationships in the model, including the
  objective function and constraints.

8
Objective Function

• The objective function is an equation that states
  the goal, or objective, of the model.
• Objective functions are either maximized or
  minimized.
• Most applications involve maximizing profit or
  minimizing cost.

9
Constraints

• The constraints are the limitations of the
  problem.
• In most realistic problems there are certain
  limitations, or constraints, which we must
  satisfy.
• Constraints can be a limited amount of resources,
  labor, or requirements for a particular demand.
• These constraints are also written as equations
  in terms of the decision variables.

10
Applying a Mathematical Model

• We saw some examples in Chapter 8 which involved
  production of different parts.
• The amount to produce of each part was considered
  the decision variables.
• Maximizing profit (given certain costs and
  revenues for each part) was the objective
  function.
• There were also constraints which limited the
  resources needed for each part and stated a
  minimum demand that had to be met.

11
Preparing the Spreadsheet

• We must translate and clearly define each part of
  our model in the spreadsheet.
• The Solver will then interpret our model
  according to how we have declared the decision
  variables, objective function, and constraints in
  the spreadsheet.
• We use referencing and formulas to mathematically
  represent the model in the spreadsheet cells.

12
Entering Decision Variables

• To enter the decision variables, we list them in
  individual cells with an empty cell next to each
  one.
• The Solver will place values in these cells for
  each decision variable as it solves the model.
• All other equations (for the objective function
  and constraints) will reference these cells.

13
Entering Objective Function

• To enter the objective function, we place our
  objective function equation in a cell with an
  adjacent description.
• This equation should be entered as a formula
  which references the decision variable cells.
• As the Solver changes the decision variable
  values in the decision variable cells, the
  objective function value will automatically be
  updated.

14
Entering Constraints

• To enter the constraints we list the equations
  separately with a description next to each
  constraint.
• The most important part of setting up the
  constraint table is expressing the left side of
  our equations as formulas.
• As each constraint is in terms of the decision
  variables, all of these formulas must be in terms
  of the decision variable cells that Solver uses.
• These equations should reference the decision
  variable cells so that as the Solver places
  values in these cells the constraint values will
  automatically be calculated.
• Another important consideration when laying out
  the constraints in preparation for Solver is that
  the RHS (right-hand side) values of each
  constraint should be in individual cells to the
  right of these equations.
• We should also place all inequality signs in
  their own cells.

15
Naming the Ranges

• Another advantageous way to keep our constraints
  organized as we use the Solver is to name our
  cells.
• Using the methods discussed in Chapter 3, we can
  name the ranges decision variables and the cell
  which holds the objective function equation.
• We can also name ranges of constraint equations
  which are in a similar category of constraints or
  which have similar inequality signs.
• This makes inserting these model parts into the
  Solver easier when using both Excel and VBA code .

16
Solver Example from Chapter 8

• A company produces six different types of
  products. They want to schedule their production
  to determine how much of each product type should
  be produced in order to maximize their profits.
  This is known as the Product Mix problem.
• Production of each product type requires labor
  and raw materials but the company is limited by
  the amount of resources available.
• There is also a limited demand for each product,
  and no more than this demand per product type can
  be produced. Input tables for the necessary
  resources and the demand are given.

17
Step 1

• Decision Variables The amount produced of each
  product type.
• x1, x2, x3, x4, x5, x6
• Objective Function Maximize Profit.
• z p1x1 p2x2 p3x3 p4x4 p5x5 p6x6
• Constraints There are two resource constraints
  labor, l, and raw material, r.
• Labor Constraint
• l1x1 l 2x2 l 3x3 l4x4 l 5x5 l 6x6
  lt available labor 4500
• Raw Material Constraint
• r1x1 r 2x2 r 3x3 r 4x4 r 5x5 r
  6x6 lt available raw material 1600
• There is also a constraint that all demand, D,
  must be met, and no extra amount can be produced.
  
• Demand Constraint
• xi lt Di for i 1 to 6

18
Figure 19.1

• The Spreadsheet layout for the Product Mix

19
Figure 19.2

• Risk Solver Task Pane
• Set the Objective to the location of the
  objective function formula.
• Set the Variables to the empty decision variable
  cells named PMDecVar.
• The Constraints show the left and right sides of
  the constraint equations with the corresponding
  inequalities.
• The labor and raw material constraints are listed
  as Normal constraints.
• Demand constraints are listed as the Bound
  constraints since they set an upper limit on the
  value that the decision variables can take.
• Set the Assume Non-Negative property of the model
  to true.

20
Figure 19.3

• The Results of the Solver are shown.
• All constraints are met.

21
Object-Oriented API in Risk Solver Platform

• Building a Problem Using Object-Oriented API
• Identifying the Solver Engine and Setting its
  Parameters
• Running the Solver
• Accessing Optimization Results

22
Object-Oriented API in Risk Solver Platform

• Use object-oriented API to create a problem
  initially, and then add the decision variables,
  objective function, and constraints. From Tools gt
  References list on the VBE select Risk Solver
  Platform xx Type Library.
• Use VBA code to manipulate Engine parameters, and
  optimize the problem using VBA commands.
• Access the results of the optimization, such as,
  objective function value, optimal solution, dual
  variables, etc.

23
Building a Problem Using Object-Oriented API

• We start by creating an instance of the Problem
  object. The Problem object represents the whole
  optimization problem. We declare this object
  variable using the Dim statement as follows
• Dim MyProb As New RSP.Problem
• Set the SolverType property of the Solver object
  to Maximize or Minimize to specify whether the
  optimization problem should be maximized or
  minimized.
• The values that the SolverType property takes
  are
• Solver_Type_FindFeas (find a feasible solution)
• Solver_Type_Maximize (maximize)
• Solver_Type_Minimize (minimize)
• Solver_Type_Simulate (simulate)

24
Adding New Decision Variables

• To add new decision variables to MyProb, we
  create a Variable object, initialize the object,
  set its properties, and then add this object to
  the Variable collection of the problem object.
• Use the Init method to specify the range which
  contains the decision variables.
• Use the Variables.Add method to add the decision
  variables to the problem object.
• Dim MyVar As New Variable
• MyVar.Init Range("DecisionVariables")
• MyVar.NonNegative
• MyProb.Variables.Add MyVar
• Set MyVar Nothing

25
Adding the Objective Function

• To add the objective function to MyProb, we
  create a Function object, initialize this object,
  set its properties, add it to the Functions
  collection of the problem, and finally set its
  value to nothing.
• Use the Init method to specify the range of the
  objective function.
• Use the FunctionType property to identify the
  type of the function that is being added to the
  problem.
• This property takes the value Function_Type_Object
  ive for the objective function, and
  Function_Type_Constraint for normal constraints.
• Dim MyObj As New RSP.Function
• MyObj.Init Range("ProdObjFunc")
• MyObj.FunctionType Function_Type_Object
  ive
• MyProb.Functions.Add MyObj
• Set MyObj Nothing

26
Adding Constraints

• Use a For Next loop to add each individual
  constraint.
• Create an array of Functions objects using the
  Dim statement.
• The size of this array is set to the total number
  of constraints.
• Use the Init method specifies the range which
  contains a constraint equation.
• The Relation method allows us to specify the
  relation (lt, , or gt) and the RHS value of the
  constraints..
• The constants Cons_Rel_EQ (), Cons_Rel_GE (gt)
  or Cons_Rel_LE (lt) are used to specify the
  relation.
• The Add method is used to add each individual
  constraint to the end of the Function collection
  of the problem object.

27
Adding Constraints (contd)

• Dim MyConstraints(NumCons) As New RSP.Function
• For i 1 To NumCons
• MyConstraints (i).Init Range("A1").Offset(i
  - 1, 0)
• MyConstraints (i).Relation Cons_Rel_GE,
  Range("B1").Offset(i - 1, 0).Value
• MyConstraints (i).FunctionType
  Function_Type_Constraint
• MyProb.Functions.Add MyConstraints (i)
• Set MyConstraints (i) Nothing
• Next
• The Remove method allows us to delete constraints
  from the problem formulation.
• The only parameter this function takes is the
  index of the constraint that will be removed. The
  following statement removes the last constraint
  of MyProb.
• MyProb.Functions.Remove NumCons

28
Identifying Solver Engine and Parameters

• Prior to solving an optimization problem, we
  should specify the appropriate Solver Engine to
  use.
• For an optimization problem, the Engine object
  represents the LP/Quadratic, Standard
  Evolutionary or the GRG Nonlinear solver
  depending on the type of the problem we are
  solving.
• When the Solver.Optimize method is then called,
  this engine will run.
• If the problem we are working with is a linear
  program, we will select the LP/Quadratic Solver
  using
• MyProb.Engine MyProb.Engines("Standard
  LP/Quadratic")
• Prior to modifying Solver Engine parameters you
  should reset all the parameters to their default
  value by using the ParamReset method.
• MyProb.Engine.ParamReset

29
Identifying Solver Engine and Parameters

• There are a large number of problem parameters
  for a particular Solver Engine.
• Use the Name property to identify the name and
  the index of a parameter of interest. Use this
  index to access and modify the corresponding
  parameter.
• For i 0 To MyProb.Engine.Params.Count - 1
• If MyProb.Engine.Params(i).Name
  "Iterations" Then
• MyProb.Engine.Params(i).Value
  100
• End If
• Next i
• MyProb.Engine.Params(AssumeNonneg).Value True

30
Running the Solver

• Use Solver.Optimize() method to run the Solver.
• This methods only argument, Solve_Type, takes
  two values
• Solve_Type_Analyze to perform a model analysis
  without actually solving,
• Solve_Type_Solve to solve the optimization
  problem.
• MyProb.Solver.Optimize (Solve_Type)
• The OptimizeStatus property of the Solver object
  returns an integer value classifying the result
  of the optimization.
• The values 0, 1, or 2 signify a successful run in
  which a solution has been found.
• The value 4 implies that there was no convergence
• The value 5 implies that no feasible solution
  could be found.

31
Running the Solver (contd)

• Dim result As Integer
• result MyProb.Solver.OptimizeStatus
• If result 5 Then
•    MsgBox Your problem was infeasible. Please
  modify your model.
• End If
• Solver.Optimize is the only command needed to run
  the Solver.
• If we have already set up the Solver in the
  spreadsheet or in some initial part of the VBA
  code, at execution time, we need to write only
  the Solver.Optimize command.

32
Accessing Optimization Results

• We can access the results of the optimization by
  using the Value property of Variable object, or
  FinalValue, DualValue and Slack properties of the
  Function object.
• VBA is also used to automatically generate the
  solution reports listed in the Report drop-down
  menu of the Ribbon.
• The Size property of VarDecision object
  identifies the total number of the decision
  variables in a problem.
• For i 0 To MyProb.VarDecision.Size - 1
• MsgBox MyProb.VarDecision.Value(i)
• Next i

33
Accessing Optimization Results (contd)

• The Count property of the Functions object
  identifies the total number of functions in the
  Problem object.
• MsgBox MyProb.Functions(MyProb.Functions.Count -
  1).Value(0)
• To print the dual variables for problem
  constraints, we would write
• For i 0 To MyProb.Functions.Count - 2
• MsgBox MyProb.Functions(i).DualValue(0)
• Next i
• If we decide to generate the reports, then we
  need to set the value of Bypass Solver Reports
  engine parameter to False.
• After the Solver has finished running, we can
  access the reports using the following statement.
• Solver.Report ReportName

34
Accessing Optimization Results (contd)

• ReportName is one of the following strings
  Answer, Feasibility, Linearity, Limits,
  Population, Sensitivity, Scaling, and
  Solution.
• The Report method generates a report in the form
  of an Excel worksheet and inserts it into the
  active workbook.

35
Other Solver Methods

• The Init method instantiates a problem using a
  named model or worksheet.
• This method creates the variable and function
  objects using an optimization problem already
  defined in the worksheet.
• The Save method is used to save model
  specifications in a cell range, or as a text
  string model name.
• The Load method is used to load model
  specifications.
• Format is one of the parameters required by this
  method. This parameter is a constant which takes
  one of the following two values
• File_Format_XLStd or File_Format_XLPSI.

36
Example Code 1

• Use this VBA code to
• Initiate a problem using a model already defined
  in an existing Excel worksheet.
• Save problem parameters.
• Solve the problem.
• Display the objective function value found from
  the optimization.
• Sub Init_Save_Prob_Methods()
• Dim prob As New RSP.Problem
• prob.Init Worksheets("Prob_Setup")
• prob.Save Worksheets("Prob_Save").Range(A1
  )
• prob.Solver.Optimize
•  
• MsgBox prob.Functions(prob.Functions.Count
  - 1).Value(0)
• End Sub

37
Example Code 2

• Using the following lines of code you can load
  the model you already saved, optimize the
  problem, and display the corresponding objective
  function value.
• Sub Load_Prob_Method()
• Dim prob As New RSP.Problem, Param As Range
• Set Param Worksheets("Prob_Save").Range(
  Range(A1),Range(A1). End(xlDown))
• prob.Load Param, File_Format_XLStd
• prob.Solver.Optimize
•  
• MsgBox prob.Functions(prob.Functions.Count
  - 1).Value(0)
• End Sub

38
Application

• Dynamic Production Problem

39
Description

• We consider a production problem in which we are
  trying to determine how much to produce of
  different items in order to maximize profit.
• Each item has a given weight, space requirement,
  profit value, quota to satisfy, and limit on
  production.
• Each item must meet its quota but be less than
  its limit.
• There is also a total weight requirement and
  space requirement for shipping which will limit
  the production.

40
Dynamic Solver

• We want this production problem to be dynamic.
• We want the user to decide how many items to
  consider in the problem and to provide the input
  for each item.
• We limit these dynamic options to five possible
  items and prepare the spreadsheet for the maximum
  number possible.
• To make this problem dynamic, we will develop a
  user interface.

41
Figure 19.4
42
Figure 19.5

• The Parameters form asks the users for the number
  of decision variables.

43
Figure 19.6

• The Input form is dynamic in that it allows the
  users to enter input values for the number of
  items they specified in the Parameters form.

44
Initial Code

• We will have a main procedure associated with the
  Solve Dynamic Problem button called
  SetParameters. In this procedure, we
• Initialize our range variables
• Call the ClearPrev procedure
• Show the users the first form (the second form
  will be shown from the first form code).
• Redefine our dynamic ranges by using the values
  for the number of decision variables (NumDV) and
  the number of constraints (NumCons) identified in
  the code of the first form.
• Call the SolveProb procedure procedure.

45
Figure 19.7

• We show the variable declarations and code for
  the SetParameters procedure and a ClearPrev
  procedure.

46
Figure 19.8

• Parameters Form code.

47
Figure 19.9

• Input Form code.

48
Figure 19.10

• After initializing the text box arrays, it is
  easy to set these default values simply by
  looping through each array

49
Figure 19.11

• This part of SolveProb procedure creates a
  problem instance and adds the corresponding
  decision variables, constraints and objective
  function.

50
Figure 19.12

• This part of SolveProb procedure selects an
  engine, sets engine parameters, solves the
  problem, and calls the ViewResults procedure.

51
Figure 19.13

• The ViewResults procedure displays the final
  value of the decision variables, the value of the
  dual variables, and the objective function value.
• The ClearProb procedure clears the memory we
  allocated to the problem, variable and function
  objects.

52
Application Conclusion

• The application is now complete.
• We can now solve this problem multiple times
  using the Solve Dynamic Problem button and
  varying the number of items for which the problem
  is solved.
• If the result is infeasible, we can simply modify
  the input values and solve it again.

53
Summary

• There are three main parts of an optimization
  model decision variables, objective function,
  and constraints.
• Using the Risk Solver Platform requires a short
  sequence of steps 1) reading and interpreting
  the problem, 2) preparing the spreadsheet, 3)
  solving the model and reviewing the results.
• We use object-oriented API in Risk Solver
  Platform to create an instance of an optimization
  problem, and add to this problem the
  corresponding decision variables, constraints,
  and objective function.
• To select an engine for solving the problem we
  use the Engine property of the problem object.
• To solve an optimization problem, we use the
  Solver.Optimize method. There is one argument for
  this method Solve_Type.
• We use the Solver.OptimizeStatus property of the
  problem object to keep or ignore the Solver
  results.
• We use the Solver.Report method of the problem
  object to generate solution reports. There is one
  argument for this method ReportName.
• We use the Init, Save, and Load methods of the
  problem object to initialize a problem instance,
  save, and load a optimization problem.

54
Additional Links

• (place links here)