# ETL
library(tidyverse)
library(lpSolve)48 Simple Charge Capacity problem
As usual, we start by uploading the libraries that we’re going to use.
49 Defining the Problem
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 are now going to illustrate a problem related to a production.
we have 6 SKUs (syrups) that we manufacture.
each SKU has its own :
production rate.
opening stock.
maximum stock target.
We want to find the optimized production hours (and related quantities) for each item, considering 2 constraints :
the final stock must be 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 :
# create dimensions
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(20000, 15000, 25000, 30000, 35000, 45000)
opening_stock <- c(5000, 5000, 5000, 5000, 5000, 5000)
# create dataframe
initial_data <- data.frame(sku,
production_rate,
max_stock,
opening_stock)
# display
initial_data sku production_rate max_stock opening_stock
1 Syrup074 3200 20000 5000
2 Syrup011 4800 15000 5000
3 Syrup060 3750 25000 5000
4 Syrup057 4000 30000 5000
5 Syrup051 3509 35000 5000
6 Syrup056 4000 45000 5000Let’s create a variable num_skus which represents the number of SKUs.
# Number of SKUs
num_skus <- length(sku)
# display
num_skus[1] 6Now we can define the 3 elements to express the problem we want to solve :
- objective function | constraint matrix | constraint direction and RHS.
49.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.
# create objective function
objective.function.coefficients <- rep(1, num_skus) # Maximize Production hours
# display
objective.function.coefficients[1] 1 1 1 1 1 149.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 2 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.
Note :
To define constraints that require a variable to be both greater than or equal to one value and less than or equal to another, we need to set up two separate constraints for each condition.
In linear programming, each condition is represented by a separate row in the constraint matrix, and the corresponding direction is specified in the
constraint.directionsvector.
# create constraint matrix
constraint.matrix <- rbind(
rep(1, num_skus),
diag(production_rate)
)
# 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 400049.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)
)
# display
constraint.directions[1] "<=" "<=" "<=" "<=" "<=" "<=" "<="49.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.
# create right-hand side
constraint.rhs <- c(35, max_stock - opening_stock)
# display
constraint.rhs[1] 35 15000 10000 20000 25000 30000 4000050 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)
# display
resultSuccess: the objective function is 35 We already can notice that the sum of the production hours reached our objective of 35 hours, which is good. Now let’s look at the results more in details.
51 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.
51.1 Optimal Values of Decision Variables
It’s the optimum number of production hours for each SKU.
result$solution[1] 4.687500 2.083333 5.333333 6.250000 8.549444 8.096389We 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> 4.687500, 2.083333, 5.333333, 6.250000, 8.549444, 8.09638951.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 maximum stocks targets.
x1 ,…, x6 being the optimum values of Decision Variables.
result$objval[1] 3551.3 Status of the Solution
A value of 0 indicates an optimal solution.
result$status[1] 052 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 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_constraint <- if_else(df1$max_stock >= df1$ending_stock, "ok", "not")
# display
df1 sku production_rate max_stock opening_stock nb_hours production_qty
1 Syrup074 3200 20000 5000 4.687500 15000.00
2 Syrup011 4800 15000 5000 2.083333 10000.00
3 Syrup060 3750 25000 5000 5.333333 20000.00
4 Syrup057 4000 30000 5000 6.250000 25000.00
5 Syrup051 3509 35000 5000 8.549444 30000.00
6 Syrup056 4000 45000 5000 8.096389 32385.56
ending_stock check_ending_stock_constraint
1 20000 ok
2 15000 ok
3 25000 ok
4 30000 ok
5 35000 ok
6 37385 okWe can validate that both constraints are fully respected, and that total number of hours is maximized, reaching 35 hours.
# check total [nb_hours]
sum(df1$nb_hours)[1] 35