Microsoft Excel contains a Solver that can solve linear programming problems. The previous problem can be solved with the following starting values, which are provided as output of the "LPSolves" program.
(XLS omitted)
Spreadsheet values before running Solver:
(
values omitted)
Spreadsheet formulas before running Solver:
(
formulas omitted)
One way to run the Solver for the previous problem is as follows.
Select "Tools" "Solver".
Set target cell: "D8".
Equal to: "Max".
By changing cells: "B7:C7".
Subject to the constraints: "D11:D13 <= E11:E13"
Options... "Assume linear model" "OK".
Solve. Create reports.
These hints are provided by "LPSolves". The copied worksheet output from "LPSolves" can be pasted into an Excel worksheet, and the "Kithara", "LPSolves" menu option can be selected from the "Kithara-enhanced" Excel spreadsheet to automatically perform these actions. The reports, however, must be created manually.
Note that the Microsoft Excel Solver uses the following terminology.
The target cell is the cell that contains the objective function of the model.
The changing cells are the cells that contain the decision variables of the model.
The constraint cells are the cells that contain the LHS (Left Hand Side) of the constraints of the model.
(XLS omitted)
Spreadsheet values after running Solver:
(
values omitted)
Spreadsheet formulas after running Solver:
(
formulas omitted)
One way to create the reports is as follows.
Run Solver on the problem.
From the "Solver Results" dialog box,
Select one of the reports,
Hold the Shift key down while clicking on the other reports. This selects all of the reports.
Select "OK".
The reports will be created as customized sheets within the spreadsheet. Cut, paste, and/or link these worksheets in the usual manner.
Answer report values:
(
values omitted)
Sensitivity report values:
(
values omitted)
Limits report values:
(
values omitted)
Require: Linear programming
Use "
LPSolves", or other approved linear programming software, to solve a problem specified by the instructor. Answer and/or do the following. Where appropriate, do not type in the answer. Instead, copy-paste from the program/output.
a. Formulate the problem as an LP primal problem in "LPSolves" form. (text omitted)
b. What are the independent variable(s)? What are the dependent variable(s)? Is the objective function a linear function?
c. Do a graphical solution to the LP primal problem by hand and include the graphical solution as the last page of your assignment.
d. Create a chart that displays the associated lines for each of the constraints. Use the Excel drawing tools to outline and label the feasible region. (chart omitted)
e. Identify all of the points that form the feasible region of the convex polygon. Calculate the objective function z-value for each of those points. Specify each point and z-value in the following form.
z = f(x1, x2) =
f. What is the optimal value for the objective function? Include units with your answer.
g. Create a 3-D surface plot of the objective function over a relevant range for the independent variables. What is the maximum value of the objective function on your 3-D surface plot? Include units with your answer. (chart omitted)
h. Use LPSolves to solve the primal problem. (text omitted)
i. Use the Microsoft Excel Solver to solve the same primal problem. Create "Answer", "Sensitivity", and "Limits" reports. ( omitted)
j. Create a Lindo program for the same problem. (text omitted)
k. Create an LPSolves program that represents the dual problem. (text omitted)
l. Use LPSolves to solve the dual problem. (text omitted)
m. Is the answer to the primal problem the same as the answer to the dual problem? Why is it advantageous to have two seemingly different ways to formulate and solve the same problem? Explain.
