lpsolve with constraints in r

Question

I would like to use R to solve an optimization problem using the lpSolve which can perform processes similar to the solver add-in in excel. Below is a simple case where I would like to maximize npv value specifically using lpSolve.

df<-structure(list(id = c(1, 2, 3, 4, 5, 6, 7, 8), Revenue = c(109, 
111, 122, 139, 156, 140, 137, 167)), row.names = c(NA, 8L), class = "data.frame")

dcf <- function(x, r, t0=FALSE){
  # calculates discounted cash flows (DCF) given cash flow and discount rate
  #
  # x - cash flows vector
  # r - vector or discount rates, in decimals. Single values will be recycled
  # t0 - cash flow starts in year 0, default is FALSE, i.e. discount rate in first period is zero.
  if(length(r)==1){
    r <- rep(r, length(x))
    if(t0==TRUE){r[1]<-0}
  }
  x/cumprod(1+r)
}

npv <- function(x, r, t0=FALSE){
  # calculates net present value (NPV) given cash flow and discount rate
  #
  # x - cash flows vector
  # r - discount rate, in decimals
  # t0 - cash flow starts in year 0, default is FALSE
  sum(dcf(x, r, t0))
}
npv(df$Revenue,.2)
#Non optimized npv yields a value of 492.
#How can i use lpSolve to optimize my table? Said another way how can I rearrange the table to maximize npv using lpSolve?

More complicated problem involves a penalizing column with the following rule: Id's represent projects.

if Id project is not the starting period (row 1). Check to see if previous Id is within a delta of 2 (absolute value of subtracting row Id from other previous rows. If true, penalize Revenue by 20%. I think this problem still involved solving for the correct order. How can I optimize this function?

#Randomize order to give base npv. Now i need to optimize the order to find max value
df<- df%>%mutate(random_sort= sample(nrow(df)))

x=function(i){
  df_fcn<- i
  df_fcn<- df_fcn%>%mutate(Penalty= if_else(abs(random_sort-lag(random_sort))>2,1,.8))%>%mutate(Penalty=ifelse(is.na(Penalty),1,Penalty))
  df_fcn<- df_fcn%>%mutate(Revenue_Penalized= Revenue*Penalty)
  
  npv(df_fcn$Revenue_Penalized,.2)
  }

`npv(sort(df$Revenue, decreasing = TRUE), 0.2)`. I fail to understand what you want to do with lpSolve. — Rui Barradas, Aug 12 '21 at 07:41
Picking just the project with the largest NPV is not really optimization. It becomes more interesting if you can select different projects and have some budgets (per period). — Erwin Kalvelagen, Aug 12 '21 at 12:56
My actual problem involves scheduling a series of geospatial data based on value and distance to other data. I'm just trying to understand how lpSolve works so I can implement on my actual data. If needed i can share an example of actuals — jsimpsno, Aug 12 '21 at 13:03
The second problem is more representative of my data. This previous question might be of use to you to understand why i need lpSolve. https://stackoverflow.com/questions/68744583/optimize-schedule-revenue-based-on-two-parameters — jsimpsno, Aug 12 '21 at 13:05

score 0 · Accepted Answer · answered Aug 12 '21 at 18:37

Best I've come up with is to randomly rearrange the data and find the maximum value.

schedule_function=function(i){
  i<- i%>%mutate(random_sort=sample(random_sort))
  df_fcn<- i%>%mutate(Penalty= if_else(abs(random_sort-lag(random_sort))>2,1,.8))%>%mutate(Penalty=ifelse(is.na(Penalty),1,Penalty))
  df_fcn<- df_fcn%>%mutate(Revenue_Penalized= Revenue*Penalty)
  final_df<-print(df_fcn)
  npv(df_fcn$Revenue_Penalized,.2)
}


n <- 1:10000
MAX = -Inf    ## initialize maximum
for (i in 1:length(n)) {
  x <- schedule_function(df)
  if (x > MAX) MAX <- x

  }

lpsolve with constraints in r

1 Answers1