Vaibhav Vaibhav - 3 months ago 25
R Question

Is there any alternative for Excel solver in R?

We have the below code for solving an optimization problem where we want to maximize sales by applying constraint on profit and no. of items.
We want to apply this profit threshold as a percentage of Revenue generated by 200 items only.
We have done it by applying a formula on profit using changing variable in Excel Solver using GRGE non-linear algorithm. We want a similar alternative for R.
Is there any way to assign changing variable in R?

Dataset

item sales profit
A 1200 120
B 5600 45
C 450 00
D 990 -90
E 1000 80
F 560 120
G 500 23
H 2000 350


Code

library(lpSolveAPI)
dataset<-read.csv("Dataset.csv",header=T,na.strings='NA',stringsAsFactors =F)
dataset$keep_flag <-1

**all the func in LPsolve API**
ls("package:lpSolveAPI")
summary(dataset)

**Passing the parameters**
ncol <- nrow(dataset)

**you have eight rows that can be picked or dropped from the solution set**
lp_rowpicker <- make.lp(ncol=ncol)
set.type(lp_rowpicker, columns=1:ncol, type = c("binary"))

**checking the model**
lp_rowpicker

**setting objective**
obj_vals <- dataset$Revenue_1hr.Projected
#obj_vals<- dataset[, 2]
obj_vals
set.objfn(lp_rowpicker, obj_vals)
lp.control(lp_rowpicker,sense='max')

**Adding contraints**

Profit constraint
xt<- (dataset$Profit_1hr.Projected)
add.constraint(lp_rowpicker, xt, ">=", 100)
xt

#No.of items to be kept
xt<- (dataset$keep_flag)
add.constraint(lp_rowpicker, xt, "=", 4)
xt

#model check
lp_rowpicker

#solving equation
solve(lp_rowpicker)

#Maximised revenue
get.objective(lp_rowpicker)

#The one with binary as 1 is our item
dataset$keep_flag<- get.variables(lp_rowpicker)
dataset$keep_flag <- as.data.frame(dataset$keep_flag)
sum(dataset$keep_flag)



final_set <- cbind(dataset,final_flag)
final_set <- final_set[which(final_set$final_flag==1),]

final_set$keep_flag <- NULL
final_set$final_flag<- NULL


This code snippet applies the profit threshold on total no. of items rather than applying it on selected items.

Edit

This is the model that got created when I ran @Karsten W. code:

C1 C2 C3 C4 C5 C6 C7 C8
Maximize 1200 5600 450 990 1000 560 500 2000
R1 120 45 0 -90 80 120 23 350 >= 100
R2 1 1 1 1 1 1 1 1 = 4
Kind Std Std Std Std Std Std Std Std
Type Int Int Int Int Int Int Int Int
Upper 1 1 1 1 1 1 1 1
Lower 0 0 0 0 0 0 0 0


And the output obtained is:

item sales profit
1 A 1200 120
1.1 A 1200 120
1.2 A 1200 120
1.3 A 1200 120


The same item is returned four times. I want 4 unique items. Plus I want to apply constraint of profit as a percentage of Sales generated by those 4 items.
By the way, we kept 'keep_flag' for the similar function to what your 'nitems' is doing. It is a changing variable that takes binary value.

Answer

Your code seems ok to me, except for that the variable names do not fit to the dataset you provided. In particular it is not clear to me what keep_flag stands for, is that some sort of preselection?

The profit constraint in your code is applied only the four from the solver selected variabes.

Here is your code, a bit cleaned up.

library(lpSolveAPI)

dataset <- data.frame(item=LETTERS[1:8], sales=c(1200, 5600, 450, 990, 1000, 560, 500, 2000), profit=c(120, 45, 0, -90, 80, 120, 23, 350))
nitems <- nrow(dataset)

# make lp   
lprec <- make.lp(0, ncol=nitems)
set.type(lprec, columns=seq.int(nitems), type="binary")

# set objective
invisible(lp.control(lprec, sense="max")) # sense defaults to "min" 
set.objfn(lprec, obj=dataset[, "sales"]) 

# constraints
min_rel_profit <- 0.10 # min. 10% profit
add.constraint(lprec, dataset[, "profit"]-min_rel_profit*dataset[,"sales"], ">=", 0) # required profit
add.constraint(lprec, rep(1, nitems), "=", 4)  # four products

print(lprec)
solve(lprec)
dataset[get.variables(lprec)==1,]

The profit constraint is derived as follows (p is the vector of profits, s is the vector of sales, x is the decision variable 0/1, all of length nitems, minp is the minimum relative profit):

  • sum(profit) / sum(sales) >= minprofit translates to p'x/s'x >= minp
  • this is equivalent to (p - minp s)'x >= 0

Hence the minimum profit has to appear as part of the coefficients on the LHS.

Comments