如何用R实现产品变体生产配额分配以匹配预测比例

Got it, let's tackle this production allocation problem step by step. The goal is to split the new production quotas (100 units for Product 1, 200 for Product 2) between variants so that the final stock ratios are as close as possible to the forecast ratios—with the key constraint that we can't produce more of Product 1's B variant since its current stock already exceeds the forecast sales.

First, let's start with your existing data:

# Forecast data
dat_forecast <- data.frame(
  product = c(1,1,2,2),
  variation = c("A", "B", "A", "B"),
  forecast_sales = c(612,238,741,455),
  ratio = c(0.72,0.28,0.6195652,0.3804348)
)

# Current stock data
dat_stock <- data.frame(
  product = c(1,1,2,2),
  variation = c("A", "B", "A", "B"),
  current_stock = c(400,268,341,155),
  ratio = c(0.5988024,0.4011976,0.6875,0.3125)
)

Step 1: Combine Data & Define Constraints #

We'll merge the forecast and stock data, then flag which variants are eligible for production (Product 1's B gets excluded):

library(dplyr)

dat_combined <- dat_forecast %>%
  left_join(dat_stock, by = c("product", "variation"), suffix = c("_forecast", "_stock")) %>%
  # Mark variants that can receive production
  mutate(allow_production = case_when(
    product == 1 & variation == "B" ~ FALSE,
    TRUE ~ TRUE
  ))

Step 2: Adjust Forecast Ratios for Eligible Variants #

Since we can't produce for Product 1's B, we need to re-normalize the forecast ratios for the eligible variants of each product. This ensures we allocate the full quota only to variants that can receive production:

dat_combined <- dat_combined %>%
  group_by(product) %>%
  mutate(
    # Sum of forecast ratios for eligible variants
    total_allowed_ratio = sum(ratio_forecast[allow_production]),
    # Adjusted ratio (normalized to 100% for eligible variants)
    adjusted_ratio = ifelse(allow_production, ratio_forecast / total_allowed_ratio, 0)
  ) %>%
  ungroup()

Step 3: Allocate Production Quotas (With Integer Handling) #

Production units are whole numbers, so we'll first allocate based on the adjusted ratios, then distribute any leftover units to the variant with the highest adjusted ratio to minimize deviation from the target:

# Define total production quotas per product
production_quota <- tibble(product = c(1, 2), total_production = c(100, 200))

dat_combined <- dat_combined %>%
  left_join(production_quota, by = "product") %>%
  # Calculate initial floating-point allocation
  mutate(production_allocated = adjusted_ratio * total_production) %>%
  # Take integer part of the allocation
  mutate(production_int = floor(production_allocated)) %>%
  group_by(product) %>%
  # Calculate remaining units to allocate after taking integers
  mutate(remaining = total_production - sum(production_int)) %>%
  # Distribute remaining units to the variant with the highest adjusted ratio
  mutate(production_int = ifelse(row_number(desc(adjusted_ratio)) <= remaining, production_int + 1, production_int)) %>%
  ungroup() %>%
  # Ensure no production for restricted variants
  mutate(production_int = ifelse(!allow_production, 0, production_int))

Step 4: Calculate Final Stock & Ratios #

Let's compute the final stock levels and their ratios to verify how close we are to the forecast targets:

dat_result <- dat_combined %>%
  mutate(final_stock = current_stock + production_int) %>%
  group_by(product) %>%
  mutate(final_ratio = final_stock / sum(final_stock)) %>%
  ungroup() %>%
  # Select relevant columns for clarity
  select(product, variation, current_stock, production_int, final_stock, ratio_forecast, final_ratio)

# View the result
print(dat_result)

Sample Output & Explanation #

When you run the code, you'll get a result like this:

product variation current_stock production_int final_stock ratio_forecast final_ratio
1       1         A           400            100         500          0.720    0.6510417
2       1         B           268              0         268          0.280    0.3489583
3       2         A           341            124         465          0.6196    0.6681034
4       2         B           155             76         231          0.3804    0.3318966

• Product 1: Since B can't receive production, all 100 units go to A. The final ratio (0.65) is as close as possible to the forecast ratio (0.72) given the constraint.
• Product 2: We split 200 units between A (124) and B (76) based on the forecast ratio. The final ratio (0.668) moves closer to the forecast ratio (0.6196) from the original stock ratio (0.6875), which is exactly what we wanted.

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