Tab 1: Linear Program: 50 points
Consider the Linear Program:
Max 2A + 3B
s.t.
1A + 2B <= 6
5A + 3B <= 15
A, B >= 0
Use the Solver function of MS Excel to determine the optimal solution for this problem. What is the value of the objective function?
Value object function 9,857143
Correct, with A=12/7 and B=15/7
To determine the optimal solution for the given linear program using the Solver function in MS Excel, follow these steps:
Step 1: Set up the problem in a spreadsheet. Arrange the linear program in a tabular format.
Objective Function:
Max 2A + 3B
Constraints:
1A + 2B <= 6
5A + 3B <= 15
A, B >= 0
Step 2: Open the Solver function in Excel. Solver is an add-in feature that may need to be activated if not already done. You can find it under the "Data" tab in Excel.
Step 3: Set the objective.
Set the objective cell: Enter the objective function cell, which is the cell containing the value 2A + 3B, as the target cell to maximize.
Step 4: Set the decision variables.
Set the decision variable cells: Enter the cell references for A and B as the decision variable cells. These should be the cells where you want to enter the values for A and B.
Step 5: Set the constraints.
Add constraints: Enter the left-hand side of the constraints in the "Constraint" area. For the first constraint (1A + 2B <= 6), enter the cell references for 1A and 2B, followed by <=6. Similarly, for the second constraint (5A + 3B <= 15), enter the cell references for 5A and 3B, followed by <=15. Also, make sure to specify that both A and B should be non-negative (A, B >= 0).
Step 6: Specify the solution method.
Choose the "Simplex LP" solver method for linear programming problems.
Step 7: Solve the linear program.
Click the "Solve" button to find the optimal solution for the given linear program.
Step 8: Evaluate the result.
Once Solver has found the optimal solution for the linear program, check the "Value of" section in the Solver Results dialog box. The value displayed should be the numerical value of the objective function at the optimal solution.
To find the optimal solution for the given linear program using the Solver function in MS Excel, follow these steps:
Step 1: Open MS Excel and create a new worksheet.
Step 2: Enter the linear program in the worksheet. The objective function and the constraints should be entered in separate cells.
In this case, you should enter the objective function "2A + 3B" in a cell and the constraints "1A + 2B <= 6" and "5A + 3B <= 15" in separate cells.
Step 3: Once you have entered the linear program, go to the "Data" tab in Excel's ribbon.
Step 4: In the "Data" tab, find the "Solver" button, which is usually located in the "Analysis" section. Click on the "Solver" button.
Step 5: In the Solver window that opens, you need to set the optimization parameters.
- Set the objective cell to the cell containing the objective function that you want to maximize or minimize.
- Set the "By Changing Variable Cells" to the cells containing the variables A and B.
- Set the "Subject to the Constraints" section by adding each constraint and selecting the appropriate cell ranges for each.
- Optionally, you can set bounds for the variables A and B by specifying their minimum and maximum values (in this case, A and B are already set as non-negative).
Step 6: Select the "Max" or "Min" option depending on whether you want to maximize or minimize the objective function.
Step 7: Click on the "Solve" button in the Solver window.
Step 8: If Solver finds a feasible solution, it will display "Solver found a solution." Click on the "OK" button to close the Solver window.
Step 9: Excel will update the variable cells with the optimal values for A and B, and it will also display the optimal value of the objective function in the objective cell.
In this case, the value of the objective function "2A + 3B" is the optimal solution, so you need to check the cell that contains the objective function to find its value.
Following these steps, you should be able to determine the optimal solution for the given linear program and find the value of the objective function.