Let’s test the capabilities of LP solve versus a very common method of linear programming, the Excel Solver solution. To begin, let’s look at the LP solving gradient matrix for a typical formulation of a calf starter. In this formula we have 15 ingredients including soy hulls, wheat midds, beet pulp, corn gluten feed and a number of other ingredients. The columns are organized by price or cost, then the minimum and maximums for each ingredient. We see here wheat middlings is set to have a maximum of 20%. The following columns are the nutrients, dry matter, protein, fat, ash, starch, the minerals, ME MP, etcetera. You’ll notice that the columns in blue are the ones that contain a minimum or maximum. The columns in Gray don’t contain either a minimum or a maximum. For the sake of this comparison, we’re only going to look at the columns that contain a min or a Max.

At the bottom of the grid we see the two rows, minimum and maximum. This is where the min or Max for each of the nutrients is included. These are dry matter, protein, fat, ash, starch, calcium, phosphorus, sodium and magnesium. Notice that we don’t have any maximum set in this formula.

Now let’s take a look at Excel. I created a spreadsheet that is set up exactly like the grid in Lpsolve. I copied all of the cells from Lpsolve into this spreadsheet and organized them in the same way we have. Columns ingredient, price, min, Max, etcetera. I’ve added one column called unit and I’ll explain why in just a moment.

Then we have the nutrients dry matter, protein, fat, ash, starch, calcium, phosphorus, sodium and magnesium. For the moment, all of the other columns that are available within LP Solve are not included because they’re not involved in the calculation of the lease cost formula. Since they don’t have a min or a Max.

I’ve added three new columns called Percent, lower prices and upper prices. These are presented by LP Solve after we find a feasible solution, so we need to include them here.

Below the minimum maximum rows. I’ve also included rows that are printed when we find a feasible solution within LP solve. These are the actual nutrient composition as well as the total price and then the shadow prices for the minimum and maximums. And we’ll look at these in more detail in a few moments.

The unit column is required by any linear program to perform the actual minimization or maximization. Each of these units has to be equal to 1 and then the total needs to be equal to 100. This forces the linear program to actually do the calculations and calculate the percentages provided here in column Q. All of the data above the actual line are included as simple numbers. There are no calculations in the percentage column. This will be calculated by the LP algorithm to calculate the total cost. The actual line is where the calculations occur. Let’s look at the minimum and maximum columns first. We have some entries here. So for example wheat middlings has a maximum of 20%. I’ll name this Cell. Max midds so that when we begin the calculations in the solver algorithm, it’s a little easier to understand. I’ll do the same for fat, molasses, canola and the premixed numbers min premix and Max premix. If I were to generalize this solver spreadsheet, I would probably name these a bit differently But for the sake of this simple demonstration, I’ll just include the names as they are fixed for the rows.

The actual price cell is the value that’s going to be minimized, and the and. I’ve named this tall column Total cost N 2. Calculate this value. We multiply the percentages in column Q by the prices in column D We do this using the formula sum product that allows Excel to do this calculation by row for each of the columns. It’s an easy way to do this quick calculation.

For each of the columns within the actual row. I do a similar calculation. The sum product of the column for each of the ingredients multiplied by the percentage. This will then calculate the actual percentage for each of the nutrients, dry matter, protein, fat, etcetera. This is an important calculation because this will actually do the calculation based on the formula. Which will set the minimums and maximums for each nutrient. So you can see here that I’ve set this sum product for each of the cells within row 21 to match the calculations for the percentage of nutrients based on the percent inclusion of each of the ingredients.

For the nutrient minimums in row 19, I’ve named each of the columns within the row by the nutrient. So I have min dry matter, min crude protein, min fat, min ash, etcetera. Again, this is just a way of making the calculations within the solver engine a little easier to understand. Cell G1 is the total formula and needs to be equal to 100 for the actual list in row 21. I’m also naming each cell to make it a little easier to understand. For example, cell H1 would be actual dry matter, actual protein is I-21, etcetera. This will allow us to understand more easily the calculations within the solver engine.

Now let’s take a look at the Solver calculations. Now, Solver is not always included within a basic Excel installation. You may need to install Solver, and there are a number of resources available online that can help you with that method of installation. If you don’t have Solver installed, go to. One of these resources and install the package.

Now that you’ve installed Solver, we’ll go to the Data tab and click on the Solver tab to bring up the Solver parameter window. These allow us to enter the inputs required to run the linear program. We’ll first set the objective as total cost, that is cell D21. This is the value we want to minimize. Remember, total cost is calculated as the sum product of the percentages by the cost of each of the ingredients. The values we’re going to change include the column percents. I’ve named this for each of the percentages for all of the ingredients within the formula. This is column Q. So we’ll change the percentages in order to minimize the total cost.

Next, we include the constraints, that is, the requirements we’ve indicated for both ingredients and nutrients. I’m going to begin by addressing the ingredient minimums and maximums. Remember I named each of the columns or cells. Remember, I named the cells to make it a little easier for us to understand. The first constraint will be that the actual. MIDDS percentage needs to be less than or equal to the maximum MIDDS percentage. We repeat this for all of the ingredient maximums.  Remember, in order to generalize this solver spreadsheet, I would need to make these numbers more general and include minimums as well.

Now we’ll set the minimum nutrient minimums and maximums. We add each constraint in turn. The actual protein needs to be greater than or equal to the minimum protein. The actual dry matter needs to be greater than or equal to the minimum dry matter and so forth. One constraint that’s critical to make the LP run is the total calculation, that is the percentage by the units. The units being ones that needs to equal to 100. This is the constraint that actually drives the formulation. 

Once I’ve included all the constraints and check them carefully, I’m ready to run the solver algorithm. I’ll check the box here that says to make unconstrained variables non negative, which makes sure that any inclusions would be greater than 0 or would be 0 or greater. Next we select the solving method and we’re going to use the simplex LP method that’s similar to the L55 algorithm that’s used within the LP solve software. Once we set our parameters, we’re ready to do the actual lease cost optimization.

Solver will do its calculations and if it finds a feasible solution, it will return the results as is shown here the solver report. The Solver results reports that Solver found the solution. All constraints and optimality conditions are satisfied, so we’re given the option of keeping the original Solver solution or returning to the original values. We also have the option of selecting any one of three reports. For our purposes, we’re going to select the sensitivity report because that gives us the information about the solution that we’ve generated. So I’ll click on reports, sensitivity analysis. And then click OK.

We see that Solver returns an optimal solution with a price of $148.82. The formula contains 36.5% soy hulls, 20% wheat midds, 35.06% barley, etcetera. All of the ingredients are included at less than or equal to the maximums that we’ve set. In this case, the maximum percent wheat mince is 20% and the solution includes 20% wheat mints. The vitamin mineral premix is set at a fixed amount of 0.25% with the minimum and maximum and that is also shown here.

The nutrient specifications have all been met. The minimum 17% protein. 3% fat, etcetera. Some of the requirements are binding and others are not. For example, the ash with a 4% minimum is actually 7.34%, which is greater than the minimum, but since we’ve set no maximum, this is allowed. Magnesium has a minimum percentage of 0.2, but we’ve included 0.28% within the formula. Umm.

Now we’ve set up this formula within Excel, let’s run the same scenario within LP solve. The conditions are all set as similar to the Excel spreadsheet. I’m ready to click the run button which will ask LP solve to do its LP. Linear optimization.

LP Solve also finds a feasible solution with a cost of $140.82, exactly the same as that of Solver. The percentages are also the same. 30.65% soy hulls, 20% wheat midds, etc. The nutrients are also identical, 17% protein, 3% fat, 7.34% ash, etcetera.

You’ll notice the LP solve output provides some additional information. The lower prices and upper price columns are shown to the right and under the actual column we see we have some additional shadow price information. Now Excel also provides us with this information. But it’s included here in the sensitivity report.

Excel provides us with this sensitivity report in a new worksheet that’s called Sensitivity Report One. The upper values are the value variable cells, which provides us with information about the prices of each ingredient. These values correspond to the lower and upper prices presented in the LP solve columns. The calculations are slightly different, but the actual numbers are identical. Below we see the constraints. Values correlate to the numbers within the LP solve shadow prices and in this case the shadow price minimum and upper and lower limits are calculated using these numbers as presented by Excel.

To generate the same values we need to do some calculations from Excel, but essentially the numbers once calculated are identical. To do the calculations and to understand the implications really require some knowledge of linear programming and how sensitivity reports can be calculated and interpreted. This is beyond the scope of this demonstration, but essentially the numbers that are presented in the sensitivity report are identical to those that are. Presented by the LP Solver results.

Let’s wrap up by making a few changes to the requirements and see if the results are still the same. I’ll change the maximum midds to 25%, increase the minimum protein to 18%. Set a 7% maximum ash and change the price of beet pulp from 145 to $130.00 per ton.  Note that when I make these changes within LP solve, each of the changes is highlighted. Makes it a little easier for me to remember what changes I’ve made within a formula. I’ll next do the same. Changes within Excel.

Now remember when I set up the Excel spreadsheet, I didn’t generalize, so in order to include maximum ash I need to add another constraint to the solver sets of constraints. So I’ll name the constraint and also the percent and add a maximum constraint to the solver matrix.

I’ll delete this sensitivity report one worksheet so that when Solver completes its evaluation, the Sensitivity report won’t be duplicated. So now that we’re ready, I’ll click on Solver and ask Solver to run its evaluation, and we’ll look at the results. Here we see the total cost and percentages.

We see the total cost between the two methods of calculation are still identical, as are the percentages included in the formula as well as the nutrient specifications. So you can see that within the parameters of a typical. Ration Formulation System Solver within Excel and LP Solve provide us with the same set of calculations and the same outputs.