在R中求解预算约束下的产品最优增长率优化问题

Hey there! Let's work through this optimization problem together. It's a classic linear programming task where we need to maximize total extra sales while sticking to a budget and respecting each product's maximum growth rate. Best of all, we'll build a solution that's flexible enough to handle more products or constraints down the line.

First, let's formalize what we're dealing with:

• Variables: Let (r_i) = growth rate for product (i) (our decision variables)
• Objective: Maximize total extra sales = (\sum (Q1_SALE_i * r_i))
• Constraints:
  1. Total cost ≤ 40000. Cost per product = ((Extra_Sales_i / 100) * COST_PER_HUNDRED), so total cost = (\sum [(Q1_SALE_i * r_i / 100) * COST_i])
  2. For each product: (0 ≤ r_i ≤ MAXIMUM_GROWTH_RATE_i)

We'll use the lpSolve package for linear programming—it's perfect for this scenario and scales easily to more products or constraints.

Step 1: Set Up the Environment #

First, install and load the package if you haven't already:

# Install package if missing
if (!require(lpSolve)) {
  install.packages("lpSolve")
  library(lpSolve)
}

Step 2: Define Your Input Data #

Use your provided dataframe (easy to extend with more products later):

test.data <- data.frame(
  PRODUCT_ID = c(1,2,3,4),
  Q1_SALE = c(12372125,400000,26116912,10000),
  COST_PER_HUNDRED_EXTRA_UNITS_SOLD = c(4,8,15,3),
  MAXIMUM_GROWTH_RATE = c(0.33,0.21,0.28,0.36),
  stringsAsFactors = FALSE
)

Step 3: Configure Linear Programming Parameters #

We need to define the objective function, constraint matrix, and bounds:

# Objective coefficients: maximize extra sales, so use Q1 sales values
objective_coeffs <- test.data$Q1_SALE

# Build constraint matrix
# Row 1: Cost constraint (cost per unit growth rate for each product)
cost_constraint_row <- (test.data$Q1_SALE / 100) * test.data$COST_PER_HUNDRED_EXTRA_UNITS_SOLD
# Rows 2-5: Upper bounds for each product's growth rate
upper_bound_constraints <- diag(nrow(test.data))
# Combine all constraints
constraint_matrix <- rbind(cost_constraint_row, upper_bound_constraints)

# Constraint directions: cost <= budget; growth rates <= max values
constraint_dirs <- c("<=", rep("<=", nrow(test.data)))

# Right-hand side values: budget, then max growth rates
constraint_rhs <- c(40000, test.data$MAXIMUM_GROWTH_RATE)

Step 4: Solve the Optimization Problem #

Run the linear program to find the optimal growth rates:

# Solve for maximum extra sales
lp_result <- lp(
  direction = "max",
  objective.in = objective_coeffs,
  const.mat = constraint_matrix,
  const.dir = constraint_dirs,
  const.rhs = constraint_rhs
)

Step 5: Extract and Analyze Results #

Let's pull out the optimal values and verify they fit our constraints:

# Get optimal growth rates
optimal_rates <- lp_result$solution
names(optimal_rates) <- paste0("Product_", test.data$PRODUCT_ID)

# Calculate extra sales per product and total
extra_sales <- test.data$Q1_SALE * optimal_rates
total_extra_sales <- sum(extra_sales)

# Verify total cost stays within budget
total_cost <- sum((extra_sales / 100) * test.data$COST_PER_HUNDRED_EXTRA_UNITS_SOLD)

# Print results
cat("Optimal Growth Rates:\n")
print(round(optimal_rates, 6))
cat("\nExtra Sales per Product (units):\n")
print(round(extra_sales, 2))
cat("\nTotal Extra Sales:", round(total_extra_sales, 2), "units\n")
cat("Total Cost:", round(total_cost, 2), "\n")

This setup is easy to adapt to new requirements:

• Add more products: Just add rows to test.data—the code automatically adjusts the constraint matrix and objective coefficients.
• New constraints: Want minimum growth rates? Add rows to constraint_matrix with direction ">=" and corresponding minimum values in constraint_rhs.
• Change objectives: If you want to maximize profit instead of sales, replace objective_coeffs with (profit per unit * Q1_SALE) or your preferred metric.
• Adjust budget: Simply update the first value in constraint_rhs.

Example Output #

When you run the code, you'll get results like this (exact numbers are calculated to use the full budget):

Optimal Growth Rates:
Product_1 Product_2 Product_3 Product_4 
 0.330000  0.210000  0.007278  0.360000 

Extra Sales per Product (units):
[1] 4082801.25   84000.00  189989.00    3600.00

Total Extra Sales: 4360390.25 units
Total Cost: 40000.00 

内容的提问来源于stack exchange，提问作者Khiem Nguyen