# ETL
library(tidyverse)
library(lpSolve)49 Charge Capacity 3 constraints
As usual, we start by uploading the libraries that we’re going to use.
50 Defining the Problem
As we saw previously that to solve a linear programming problem, we need to define 3 elements :
the objective function.
constraints and their directions.
the right-hand side values.
We continue with the previous example, and are now going to illustrate another problem related to a production. This time we add one more constraint : a minimum stocks targets by SKU.
we have 6 SKUs (syrups) that we manufacture.
each SKU has its own :
production rate.
opening stock.
minimum and maximum stocks targets.
We want to find the optimized production hours (and related quantities) for each item, considering 2 constraints :
the final stock must be :
above the minimum stock target.
below the maximum stock target.
the total number of production hours must be equal to 35 hours.
Here is a data frame initial_data containing those inputs :
sku <- c("Syrup074", "Syrup011", "Syrup060", "Syrup057", "Syrup051", "Syrup056")
production_rate <- c(3200, 4800, 3750, 4000, 3509, 4000)
# Adjusted stock limits for feasibility
max_stock <- c(50000, 50000, 50000, 30000, 35000, 45000)
min_stock <- c(6000, 6000, 6000, 10000, 6000, 20000)
opening_stock <- c(5000, 5000, 5000, 5000, 5000, 5000)
# create dataframe
initial_data <- data.frame(sku,
production_rate,
max_stock,
min_stock,
opening_stock)
# display
initial_data sku production_rate max_stock min_stock opening_stock
1 Syrup074 3200 50000 6000 5000
2 Syrup011 4800 50000 6000 5000
3 Syrup060 3750 50000 6000 5000
4 Syrup057 4000 30000 10000 5000
5 Syrup051 3509 35000 6000 5000
6 Syrup056 4000 45000 20000 5000Let’s create a variable num_skus which represents the number of SKUs.
# Number of SKUs
num_skus <- length(sku)Now we can define the 3 elements to express the problem we want to solve :
- objective function | constraint matrix | constraint direction and RHS.
50.1 Objective Function
It represents the coefficients of our objective function as a numeric vector.
We set those coefficients to 1, because the sum of each one of them will be equal to our target of 35 hours.
objective.function.coefficients <- rep(1, num_skus) # Maximize Production hours
objective.function.coefficients[1] 1 1 1 1 1 150.2 Constraint Matrix
We create a matrix representing the coefficients of our decision variables in the constraints. Each row corresponds to a constraint.
In our case here we have 3 constraints :
Total Production hours constraint : related to the sum of the production hours of each SKU.
- captured under a single row.
Maximum Stock : for each SKU individually.
- captured under different rows, one for each SKU.
Minimum Stock : for each SKU individually.
- captured under different rows, one for each SKU.
# create constraint matrix
constraint.matrix <- rbind(
rep(1, num_skus), # Total Production hours constraint
diag(production_rate), # [Production Qty] + [opening_stock] <= max_stock constraint
diag(production_rate) # [Production Qty] + [opening_stock] >= min_stock constraint
)
# display constraint matrix
constraint.matrix [,1] [,2] [,3] [,4] [,5] [,6]
[1,] 1 1 1 1 1 1
[2,] 3200 0 0 0 0 0
[3,] 0 4800 0 0 0 0
[4,] 0 0 3750 0 0 0
[5,] 0 0 0 4000 0 0
[6,] 0 0 0 0 3509 0
[7,] 0 0 0 0 0 4000
[8,] 3200 0 0 0 0 0
[9,] 0 4800 0 0 0 0
[10,] 0 0 3750 0 0 0
[11,] 0 0 0 4000 0 0
[12,] 0 0 0 0 3509 0
[13,] 0 0 0 0 0 400050.3 Constraint Directions
A character vector indicating the direction of each constraint (e.g., “<=”, “>=”, “=”).
Those constraints are related to each row of the constraint matrix.
# create constraint
constraint.directions <- c("<=",
rep("<=", num_skus),
rep(">=", num_skus)
)
# display
constraint.directions [1] "<=" "<=" "<=" "<=" "<=" "<=" "<=" ">=" ">=" ">=" ">=" ">=" ">="50.4 Right-Hand Side (RHS) Values
A numeric vector containing the right-hand side values of the constraints.
Here :
the sum of each production hours must be equal to 35 hours.
the quantity produced for each item, added to the opening stocks, cannot exceed the maximum stock target defined for each SKU.
the quantity produced for each item, added to the opening stocks, cannot be lower than the minimum stock target defined for each SKU.
constraint.rhs <- c(35,
max_stock - opening_stock,
min_stock - opening_stock
)
constraint.rhs [1] 35 45000 45000 45000 25000 30000 40000 1000 1000 1000 5000 1000
[13] 1500051 Solving the Problem
Now, we use the lp() function to solve the linear programming problem.
We need to specify whether we are minimizing or maximizing.
# For maximization
result <- lp("max",
objective.function.coefficients,
constraint.matrix,
constraint.directions,
constraint.rhs)
# For minimization
# result <- lp("min",
# objective.function.coefficients,
# constraint.matrix,
# constraint.directions,
# constraint.rhs)
resultSuccess: the objective function is 35 52 Interpreting the Results
The result object contains various information about the solution, especially 3 :
- Optimal Values of Decision Variables.
- Optimal objective value (based on the optimal values of the Decision Variables).
- Status of the solution.
Let’s look at those 3.
52.1 Optimal Values of Decision Variables
It’s the optimum number of production hours for each SKU.
result$solution[1] 14.0625000 9.3750000 6.2775185 1.2500000 0.2849815 3.7500000We also can notice that the sum of those production hours is equal to 35 hours.
It was one of the constraints of the variable constraint.rhs .
sum(result$solution)[1] 35Get Results as data frame
Let’s get those results, the calculated optimum decisions variables, creating a data frame.
# format as data frame
df1 <- as.data.frame(result$solution)
# rename
df1 <- df1 |> rename(nb_hours = `result$solution`)
# keep results
result_data <- df1
glimpse(df1)Rows: 6
Columns: 1
$ nb_hours <dbl> 14.0625000, 9.3750000, 6.2775185, 1.2500000, 0.2849815, 3.750…52.2 Optimal Objective Value
It indicates the final score (maximum or minimum) of the solution that we found.
In our example, we wanted to maximize the function : 1*x1 + 1*x2 + 1*x3 + 1*x4 + 1*x5 + 1*x6
bringing it as <= 35 hours.
respecting the minimum and maximum stocks targets.
x1 ,…, x6 being the optimum values of Decision Variables.
result$objval[1] 3552.3 Status of the Solution
A value of 0 indicates an optimal solution
result$status[1] 053 Get & Check Results
Now, let’s check whether the decisions variables that we obtained are well aligned with our constraints.
First, we calculate the production quantity and ending stocks of each SKU, using the optimum decisions variables.
Then we check :
whether the ending stock is :
above the minimum stock target.
below the maximum stock target.
if the total of the production hours of each SKU is inferior or equal to 35 hours.
#---------------
# Calculate using the optimum decisions variables
#---------------
# combine
df1 <- cbind(initial_data, result_data)
# calculate production_qty
df1$production_qty <- df1$production_rate * df1$nb_hours
# calculate ending inventories
df1$ending_stock <- df1$opening_stock + df1$production_qty
# formatting
df1$ending_stock <- floor(df1$ending_stock)
#---------------
# Check constraints
#---------------
# constraint [ending_stock] <= [max_stock]
df1$check_ending_stock_max_constraint <- if_else(df1$max_stock >= df1$ending_stock, "ok", "not")
# constraint [ending_stock] >= [min_stock]
df1$check_ending_stock_min_constraint <- if_else(df1$min_stock <= df1$ending_stock, "ok", "not")
glimpse(df1)Rows: 6
Columns: 10
$ sku <chr> "Syrup074", "Syrup011", "Syrup060", …
$ production_rate <dbl> 3200, 4800, 3750, 4000, 3509, 4000
$ max_stock <dbl> 50000, 50000, 50000, 30000, 35000, 4…
$ min_stock <dbl> 6000, 6000, 6000, 10000, 6000, 20000
$ opening_stock <dbl> 5000, 5000, 5000, 5000, 5000, 5000
$ nb_hours <dbl> 14.0625000, 9.3750000, 6.2775185, 1.…
$ production_qty <dbl> 45000.00, 45000.00, 23540.69, 5000.0…
$ ending_stock <dbl> 50000, 50000, 28540, 10000, 6000, 20…
$ check_ending_stock_max_constraint <chr> "ok", "ok", "ok", "ok", "ok", "ok"
$ check_ending_stock_min_constraint <chr> "ok", "ok", "ok", "ok", "ok", "ok"Let’s check the total number of hours :
# check total [nb_hours]
sum(df1$nb_hours)[1] 35