How do I actually calculate the "perfect" order amount so I'm not wasting money on storage?

When you're managing stock, you're essentially playing a tug-of-war between two costs: the cost of placing an order (shipping, admin, setup) and the cost of holding that item in a warehouse (insurance, space, depreciation). If you order too often in small batches, your ordering costs skyrocket. If you order huge bulks, your holding costs eat your margin.

The classic way to solve this is the Economic Order Quantity (EOQ) model. It's a simple formula, but in R, I prefer wrapping it in a function so you can run it across a whole product catalog without losing your mind. Let's imagine we're managing a shop that sells high-end mechanical keyboards.

# EOQ Function: sqrt((2 * Annual Demand * Order Cost) / Holding Cost)
calc_eoq <- function(demand, order_cost, holding_cost) {
  return(sqrt((2 * demand * order_cost) / holding_cost))
}

# Example: A "Custom Mechanical Keyboard" kit
# Demand: 1,200 units/year, Order Cost: $50 per order, Holding Cost: $10/unit/year
eoq_result <- calc_eoq(demand = 1200, order_cost = 50, holding_cost = 10)
print(paste("Optimal Order Quantity:", round(eoq_result)))
# Result: ~110 units per order

I'll be honest: EOQ assumes demand is constant, which it almost never is in the real world. But it gives you a baseline. If your calculated EOQ is 110 but your supplier only sells in crates of 50, you now know that ordering 100 units is a very efficient compromise.

How do I figure out how much "buffer" stock I need to handle random spikes in demand?

The EOQ tells you how much to buy, but not how much to keep as a "just in case" pile. That's your safety stock. The amount of safety stock you need depends on your "service level"—basically, how okay are you with telling a customer "sorry, we're out of stock"?

To calculate this, you need the standard deviation of your demand and the lead time from your supplier. We use qnorm() in R to find the Z-score associated with your desired service level (e.g., 95%).

# Safety Stock = Z-score * std_dev_of_demand * sqrt(lead_time)
calc_safety_stock <- function(service_level, std_dev, lead_time_days) {
  z_score <- qnorm(service_level)
  return(z_score * std_dev * sqrt(lead_time_days))
}

# Let's say demand fluctuates by 5 units/day, lead time is 7 days, 
# and we want a 95% service level.
safety_stock <- calc_safety_stock(0.95, 5, 7)
print(paste("Safety Stock needed:", round(safety_stock)))
# Result: ~22 units

If you're selling a mission-critical part, you might bump that service level to 99%. Just be aware that the jump from 95% to 99% often requires a disproportionately larger amount of safety stock. It's a diminishing return on your investment.

Can I put this all together into a system that tells me exactly when to hit the 'buy' button?

Yes, and this is where it gets useful. You want to calculate the Reorder Point (ROP). The ROP is the inventory level that triggers a new order. It's simply the demand during the lead time plus your safety stock.

In a professional setting, you wouldn't do this for one item; you'd do it for a whole data frame of SKUs. Here is how I'd structure that using a tidy approach.

library(dplyr)

inventory_data <- data.frame(
  sku = c("KB-01", "KB-02", "KB-03"),
  avg_daily_demand = c(3.3, 1.5, 5.0),
  std_dev_demand = c(1.1, 0.4, 2.1),
  lead_time_days = c(7, 14, 5),
  current_stock = c(45, 10, 30)
)

# Applying the logic across the dataset
optimized_stock <- inventory_data %>%
  mutate(
    safety_stock = qnorm(0.95) * std_dev_demand * sqrt(lead_time_days),
    reorder_point = (avg_daily_demand * lead_time_days) + safety_stock,
    status = if_else(current_stock <= reorder_point, "ORDER NOW", "OK")
  )

print(optimized_stock)

Now you have a dashboard-ready table. Instead of guessing, you have a mathematical trigger. If current_stock drops below the reorder_point, the status flips to "ORDER NOW". It's clean, reproducible, and far more reliable than a spreadsheet someone manually updated three months ago.

📋 Practical Task

Exercise: Developing a Reorder Trigger for Custom Keycap Sets

You have been handed a dataset of three different keycap sets with varying demand patterns. Your goal is to build a script that identifies which items need to be reordered immediately to maintain a 98% service level.

Dataset:

• Set A: Avg Daily Demand: 2.1, Std Dev: 0.8, Lead Time: 10 days, Current Stock: 25
• Set B: Avg Daily Demand: 0.5, Std Dev: 0.2, Lead Time: 20 days, Current Stock: 15
• Set C: Avg Daily Demand: 4.2, Std Dev: 1.5, Lead Time: 4 days, Current Stock: 20

Requirements:

1. Create a data frame with the values provided above.
2. Calculate the safety_stock using a 98% service level (qnorm(0.98)).
3. Calculate the reorder_point.
4. Add a logical column should_order that returns TRUE if current stock is less than or equal to the reorder point, and FALSE otherwise.
5. Print the final table to the console.