Lab Handout 3: Solving Linear Programming Problems in MS Excel
The objective of this week’s lab exercise is to become familiar with the procedure for setting up and solving Linear Programming (LP) problems in MS Excel. In lab, you will work through a complete example by filling in information and solving the spreadsheet. In the homework assignment, you will be required to input a model into a blank spreadsheet, solve for an LP solution, and answer questions.
Checking to see if Solver is installed
Open the spreadsheet provided at the link with this handout. We first need to check your computer’s installation of MS Excel to see if Solver has been activated. To do this click on the ‘Data’ tab at the top of the Excel screen. If Solver is installed it will appear at the right of the screen as part of the ‘Analysis’ group of tools. If Solver does not appear there, we will need to add it to your installation of MS Excel.
Installing Solver into Excel (only required if it is not present)
To install Solver complete the following steps:
1)Click on the Office button at the top left of the Excel program screen.
2)Click on Excel Options at the bottom of the Office button box that appears.
3)Choose Add-Ins in the left hand side of the Excel Options page
4)Highlight ‘Analysis ToolPak’ in the Add-ins list, then click the ‘Go…’ button at the bottom of the page
5)In the Add-Ins choices that appear, click the box next to ‘Solver Add-in’ and click OK
Setting up the problem in MS Excel
The setup of a linear program in a spreadsheet is very similar to the algebraic form we have been using as well as the tableau setup that we saw in lecture. Each variable of the problem will have its own column. The first cell in that column will initially be zero but will change as we try different values for the variable in search of its optimal setting. We call this the ‘Activity Level.’ Directly beneath the ‘Activity Level’ we will place the objective equation coefficient for the variable. This completes the objective equation part of the problem and allows us to start writing the constraints. The algebraic form of the problem is given below. Note that this is the same problem that we applied the Simplex method to in lecture.
In the above linear program C = acres of corn planted and B = acres of soybean planted. P is the total net profit earned and represents the objective criterion of the decision maker. The constraint coefficients (numbers in front of the variables) indicate the usage of a particular resource per acre of crop planted. The RHS (right hand side of the constraints) represents the fixed limits of resources available to the decision maker.
MS Excel does not have a mechanism for writing equations directly as you would see in many computer programming environments. Instead, it has a large list of formulas that combines the values of other cells into a new numerical value in a new cell. As such, equations in Excel must be setup so that a cell calculates the LHS (left hand side) of an equation or inequality while a different cell calculates the RHS (right hand side) of an equation. We will go through the Excel setup of the farm problem to demonstrate.
A screenshot of the lab spreadsheet depicting the farm problem is given above and you should save and open a copy of the spreadsheet in order to follow along. This will allow you to look at the formulas input into any cells directly.
First note that the variables are labeled at the top of the columns and their values (activity levels) are directly below in cells B3 and C3. The coefficients for these variables in the objective equation are given in B4 and C4. To the right of those coefficients, we calculate the objective variable of profits. Open the formula in this cell (D4) and you will see that we use the sumproduct formula. The sumproduct formula takes two rows of data and first multiplies the column elements then adds the products from each column. In the case of the objective cell the sumproduct formula will return the same solution as entering the formula as =B3*B4 + C3*C4.
Moving to row 6 of the spreadsheet we have a row of headings for the constraint inequalities. In column A, each of the constraints is named for the resource the inequality will track. In columns B and C, we input the coefficients for Corn and Soybean usage of the different resources. Columns D through F represent the equation or inequality section. Here we calculate the LHS of the inequality using the sumproduct formula in column D, indicate the sign that determines the relationship between LHS and RHS in column E, and input the fixed RHS value for each resource in column F.
Note again how the sumproduct formula is used to multiply the constraint coefficients by the activity levels in the LHS cell formulas. For the land constraint, the sumproduct formula used is equivalent to inputting the formula =B3*B7 + C3*C7. One way to look at this is the LHS column keeping track of usage of resources by the different activity combinations we might try and the RHS giving the upper limits that determine feasibility. Also note that we are not entering non-negativity constraints into the problem, we will see why in a moment.
Solving the LP in MS Excel
We are now ready to solve this linear program using the Solver tool. With the spreadsheet open, click on the Data tab and then click on Solver in the Analysis group. The figure below shows the Solver Parameters box that should appear. (Note that if you are using MS Excel 2010 the layout will look a little bit different.
The first thing we need to do is input the objective variable into Solver. This goes in the ‘Set Target Cell’ box at the top of the Solver Parameters box. You can click the red arrow to the right of the input field and Solver will take you to the spreadsheet you are working in and allow you to highlight the cell you want as the objective cell rather than having to type it in.
**The objective cell we need referenced in ‘Set target cell’ is D4. (not N4 as in the picture).
The next line in the Solve Parameters box allows you to choose whether you want to find a maximum, minimum, or target value. We want the Max button clicked here. We will only use the ‘Value of’ box for input if we are trying to target an exact predefined value of profits.
The next area of the Solver Parameters box is labeled ‘By Changing Cells.’ This is where the decision variables of the problem are input, meaning these are the cells that Solver is allowed to change as it searches for the maximum profit combination. You can again use the red arrow at the right of the input field to switch to the spreadsheet and highlight the cells for variables (we labeled them activity levels).
The ‘By Changing Cells’ with our variable values are found in B3 and C3. This can be typed in as B3,C3 or B3:C3. If you use the red arrow to highlight these cells it will be the latter version with the colon separation and will also add dollar signs to the cell references. Don’t worry about the dollar signs for now.
The last field of the Solve Parameters box is titled ‘Subject to Constraints’ and is the location where we define the feasible space of the problem. To activate this field you will need to click the ‘Add’ button to the right of the field. Once you click on this the ‘Add Constraint’ box should appear. The cell reference on the left is where we input the LHS cell for each constraint. Recall that the formulas in these cells keep track of usage when corn and soybean levels change. The middle field asks for the sign between LHS and RHS. This box is a drop-down that lets you choose among several options defining the constraint types. The right-hand field of the ‘Add Constraint’ box is the RHS value we entered into our spreadsheet. You can input both the LHS and RHS values in the ‘Add Constraint’ box using the red arrow mechanism.
When you have input the first constraint for land, you can click on ‘Add’ at the bottom of the ‘Add Constraint’ box and input the next constraint. After you have entered the last constraint from the spreadsheet, click OK.
When you complete the constraint entry, your ‘Solver Parameters’ box should look like the screenshot below.
If you are using Excel 2010, you will also need to check the box underneath the constraint field requiring variables to be non-negative. If you are using an older version of Excel you will need to click first on ‘Options’ and then choose ‘Assume non-negative.’
Click Solve in the Solve Parameters box.
When Solver completes it will give you several options on dealing with the solution. For now, just ignore all of those options and click on OK.
Your spreadsheet should now look like the screen capture below.
This completes the lab assignment. Write answers to the following questions and submit them at the start of class on Wednesday. Write neatly and use a clean sheet of paper with no fringe.
Before each question below, set the activity level cells to a value of zero.
1)Reset the RHS for land to 321 and resolve the model. Do profits go up or down? What would be the maximum the decision maker could pay to rent an acre of land?
Change the RHS for land back to 320 before question 2.
2)Double all of the RHS values in your spreadsheet and resolve the model. How does the new solution compare to the original solution? Offer an explanation for any differences (or lack of differences) between this result and the original solution in the figure above.
Change the RHS for all resources back to their original values before question 3.
3)Change the profit per acre of corn to 90 and resolve the model. How does this change the solution? What is the new solution? Explain any changes you see from the original solution in the figure above.