Non-linear optimization from excel to R

Question

Problem: Find optimal discount for each product such that spend budget is fully utilized. In simpler terms, I need to maximize sales by changing discount with the following constraints:

• min discount <= discount <= max discount
• spend_value <= 100 #spend_budget

Formula used(relation between diff variables): (details shared, at the end in the section, where I have used excel to solve the problem.)

• sales_value = discount discount_coef + constant [Excel formula = F2G2 + H2]

• spend_value = (sales_value/(mrp-discount))*discount [Excel formula = (E2/(B2-G2))*G2]

Work done: with naive knowledge on optimization, and extreme googling/ checking various SOs post, I managed to find some relevant post related to my problem here, which suggested use of NlcOptim::solnl. and code as follows:

Input data

structure(list(product = c("A", "B", "C", "D", "E", "F", "G", 
"H", "I", "J", "K", "L", "M", "N"), mrp = c(159, 180, 180, 230, 
230, 500, 500, 310, 288, 310, 500, 425, 425, 465), discount_coef = c(0.301594884229324, 
0.614829352312733, 0.149146787052132, 0.248723558155458, 0.138769169527518, 
0.330703149210594, 0.335917219291645, 0.296582160231912, 0.357483743973616, 
0.24978922074796, 0.334178652809571, 0.292011550773066, 0.157611497322651, 
0.357562105368776), min_discount = c(14.31, 25.2, 25.2, 29.9, 
29.9, 100, 100, 71.3, 66.24, 71.3, 100, 51, 51, 51.15), max_discount = c(39.75, 
30.6, 30.6, 39.1, 39.1, 200, 200, 179.8, 155.52, 179.8, 200, 
174.25, 174.25, 190.65)), row.names = c(NA, 14L), class = "data.frame") -> optim_data

code

library("NlcOptim")

(coeff <- optim_data$discount_coef)
#>  [1] 0.3015949 0.6148294 0.1491468 0.2487236 0.1387692 0.3307031 0.3359172
#>  [8] 0.2965822 0.3574837 0.2497892 0.3341787 0.2920116 0.1576115 0.3575621
(min_discount <- optim_data$min_discount)
#>  [1]  14.31  25.20  25.20  29.90  29.90 100.00 100.00  71.30  66.24  71.30
#> [11] 100.00  51.00  51.00  51.15
(max_discount <- optim_data$max_discount)
#>  [1]  39.75  30.60  30.60  39.10  39.10 200.00 200.00 179.80 155.52 179.80
#> [11] 200.00 174.25 174.25 190.65
(mrp <- optim_data$mrp)
#>  [1] 159 180 180 230 230 500 500 310 288 310 500 425 425 465
(discount <- numeric(length = 14L))
#>  [1] 0 0 0 0 0 0 0 0 0 0 0 0 0 0

## objective function
obj <- function(discount) {
  sales_value <- (discount/mrp) * coeff
  return(sum(sales_value))
}

## constraint
con <- function(discount) {
  sales <- (discount/mrp)*coeff
  spend <- (sales/(mrp-discount))*discount
  f = NULL
  f = rbind(f, sum(spend)-100) # 100 is spend budget
  return(list(ceq = f, c = NULL))
}

## optimize 
result <- solnl(X = discount, objfun = obj, confun = con, 
                lb = min_discount, ub = max_discount)
#> Error in solnl(X = discount, objfun = obj, confun = con, lb = min_discount, : object 'lambda' not found

^{Created on 2020-07-03 by the reprex package (v0.3.0)}

Issue:

1. It constantly throwing error message "object 'lambda' not found" and I am clueless on how to solve the issue.
2. How to solve non-linearity optimization problem shared in R?? Is there other way to solve the problem??

Details: Excel solution

• pre-setup(input data) in excel

• post setup (after running excel solver)

Answer 1

I have never dealt with NlcOptim, but I do have some experience with nloptr(link). Here is how to setup the problem (pay attention to comments below):

optim_data$constant <- c(30,60,-10,34,-23,54,-34,-56,23,45,-71,19,29,39)

# this is minimized, therefore "-", max_budget is a dummy variable
sales_value <- function(discount, discount_coef, mrp, max_budget, constant){
  -sum(discount * discount_coef + constant)
}  

# g(x) <= 0
constraint <- function(discount, discount_coef, mrp, max_budget, constant){
  sum((discount * discount_coef + constant)/(mrp-discount)*discount) - max_budget
}


# mean of the bounds as an initial guess
init_guess <- rowMeans(optim_data[,4:5])

sol <- nloptr(x0 = init_guess,
              eval_f = sales_value,
              lb = optim_data$min_discount, # lower bound
              ub = optim_data$max_discount, # upper bound
              eval_g_ineq = constraint, # g <= 0
              opts = list("algorithm" = "NLOPT_LN_COBYLA", "print_level" = 2, "maxeval" = 2000),
              discount_coef = optim_data$discount_coef,
              mrp = optim_data$mrp,
              max_budget = 100,
              constant = optim_data$constant)

which gives the following solution after 1010 iterations (matches Excel):

  > sol$solution
 [1]  14.31000  25.20000  30.60000  29.90000  39.10000 100.00000 164.94972
 [8] 164.57111  66.24000  71.30000 200.00000  82.56430  51.00000  77.32753

I use the mean of the boundary values as an initial guess. Hope this helps.

Answer 2

This finds the same result as the Xl Solver :

optim_data$constant <- c(30,60,-10,34,-23,54,-34,-56,23,45,-71,19,29,39)

obj <- function(discount) {
  sales_value <- (discount * optim_data$discount_coef) + optim_data$constant
  return(-sum(sales_value)) # looking for minimum
}

con <- function(discount) {
  sales_value <- (discount * optim_data$discount_coef) + optim_data$constant
  spend_value = (sales_value/(optim_data$mrp-discount))*discount
  return(list(ceq = NULL, c = sum(spend_value)-100))
}

library(NlcOptim)
solnl(X= optim_data$min_discount, objfun = obj, confun  =con , lb = optim_data$min_discount , ub = optim_data$max_discount )
#> $par
#>            [,1]
#>  [1,]  14.31000
#>  [2,]  25.20000
#>  [3,]  30.60000
#>  [4,]  29.90000
#>  [5,]  39.10000
#>  [6,] 100.00000
#>  [7,] 164.95480
#>  [8,] 164.56988
#>  [9,]  66.24000
#> [10,]  71.30000
#> [11,] 200.00000
#> [12,]  82.55170
#> [13,]  51.00000
#> [14,]  77.33407
#> 
#> $fn
#> [1] -481.6475

Note that the objective function should be negative as solnl is looking for a minimum.
The constraint should be c instead of ceq as it is an inequality : spend-100<0