Vaibhav - 1 year ago 271
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\$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')

Profit constraint
xt<- (dataset\$Profit_1hr.Projected)
xt

#No.of items to be kept
xt<- (dataset\$keep_flag)
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.

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download