Data Smart, Ch 4, Linear Programming


Executive Summary

In Chapter 4 of the book Data Smart, by John Foreman, the author uses Excel’s linear programming tool, Solver, to solve an optimization problem, specifically, minimizing the raw materials cost for a commercial orange juice blend. Consistent with this series, here we use R to solve the same problem, specifically, we invoke R’s lpSolve package.


The problem posed is to optimize the allocation of orange juice orders among a pool of suppliers worldwide, to keep costs to a minimum, yet still meeting taste, color, acidity, supply and contractual constraints.

There are 11 potential suppliers for each of the three months (Jan – March) for which orders are placed. Our objective function has 33 variables, each variable representing an order quantity placed with each vendor for each of the three months. We identify and express 41 constraints for the problem.

The source data and Excel solutions are available at the book’s website.

The R code

# Read source data
objF <- read.csv("objF.csv") # contains the objective function
objF <- objF[["objF"]] # converts objF to a vector
supply <- read.csv("OJ.csv")
# Create constraint matrix
constraintMat <- as.matrix(supply[1:41,1:33]) # 41 rows, 33 variables
# Create constraintDir
constraintDir <- as.vector(supply[["Eqn"]]) # separates out the constraint function e.g. ">=", "<=", ...
# Create constraintRHS
constraintRHS <- as.vector(supply[["RHS"]]) # separates out the constraint values
# Run the linear programming solver. 
optBlend <- lp(direction = 'min', = objF, const.mat = constraintMat,
               const.dir = constraintDir, const.rhs = constraintRHS)
optBlend # returns the minimum of the objective function

Success: the objective function is $1,227,560,. This is the same value returned by Excel’s Solver.

optBlend$solution # returns the order quantities placed, 1000 gals/month, with each of the 11 suppliers, for 
 Jan ( 1-11), Feb (12-22) and Mar (23-33). 

[1] 0.0 13.5 240.0 0.0 0.0 60.9 0.0 0.0 35.0 174.6 76.0 0.0 0.0 240.0 0.0
[16] 75.5 12.4 0.0 114.2 132.3 0.0 25.5 0.0 0.0 280.0 111.2 8.5 0.0 0.0 53.8
[31] 132.6 35.4 78.5

Summary & Conclusion

It is convenient to have the ability to run linear programming optimizations in R. lpSolve does not specify a limit to the number of variables which can be presented to the solver, rather memory is cited as the limiting factor. By contrast, Excel’s Solver is reportedly limited to 200 or fewer variables for most installations.

Other tools are available and may be better suited for heavy duty optimization problems with high variable counts. I have used FICO XPRESS Optimization Suite in the past and found it to be excellent – powerful and easy to use. The author highlighted two good commercial solutions based on his experience. One is the cloud-based Gurobi and the other is the enterprise-focused CPLEX, from IBM.

Happy optimizing!

Leave a Reply

Your email address will not be published.