Streamlining Production: Calculating 'Available to Build' from Component Stock in Google Sheets

For many ecommerce businesses that assemble or manufacture their own products, accurately knowing how many finished goods can be produced at any given moment is critical. This 'available to build' metric directly impacts sales forecasting, production scheduling, and customer satisfaction. While seemingly complex, determining this quantity can be effectively managed within Google Sheets, transforming raw component inventory into actionable production insights.

The Foundation: Bill of Materials (BOM) in Google Sheets

At its core, this challenge revolves around the Bill of Materials (BOM) – a comprehensive list of the raw materials, components, and sub-assemblies required to construct a product. To calculate 'available to build,' you need two primary pieces of information:

1. The specific components and their quantities needed for each finished product.
2. The current stock levels of each of those individual components.

Consider a product, 'Item X,' that requires 1 unit of 'Part A,' 2 units of 'Part B,' and 4 units of 'Part C.' If you have 20 units of Part A, 30 units of Part B, and 10 units of Part C in stock, the goal is to determine the maximum number of 'Item X' units you can assemble.

Structuring Your Data for Success

An organized Google Sheet setup is paramount. Typically, this involves at least two to three distinct sheets or named ranges:

1. Product List: A list of all finished products you sell, often with unique identifiers.

   PRODUCT Table
   Stock Number | Product
   -------------|----------
   1122         | Widget 1122
   2233         | Widget 2233
   3344         | Widget 3344

2. Component Requirements (BOM): A mapping of each finished product to its required components and their respective quantities.

   COMPONENT Table
   Stock Number | Component ID | Quantity
   -------------|--------------|---------
   1122         | AB           | 3
   1122         | BC           | 1
   1122         | CD           | 4
   2233         | AB           | 2
   2233         | DD           | 1

3. Component Inventory: Your current stock levels for each individual component.

   INVENTORY Table
   Component ID | OnHand
   -------------|--------
   AB           | 8
   BC           | 16
   CD           | 2
   DD           | 10

Basic Calculation Principle: The Limiting Factor

For a single product, the calculation is straightforward: for each required component, divide its current 'OnHand' quantity by the 'Quantity' needed for one unit of the finished product. The smallest result across all components for that product is your 'available to build' quantity.

• For 'Item X' (requiring 1 Part A, 2 Part B, 4 Part C):
• Part A: 20 OnHand / 1 Required = 20 units
• Part B: 30 OnHand / 2 Required = 15 units
• Part C: 10 OnHand / 4 Required = 2.5 units (floor to 2)

In this scenario, Part C is the limiting factor, meaning you can only build 2 units of 'Item X.'

Advanced Solutions for Dynamic Calculations

While simple formulas can work for a few products, managing this across a large catalog with varying BOMs becomes unwieldy. This is where more robust, dynamic solutions come into play, treating your Google Sheets data more like a relational database.

Leveraging SQL-like Functionality in Google Sheets

An elegant approach involves using a custom Google Apps Script function that enables SQL-like queries directly within your spreadsheet. This allows you to perform joins, aggregations, and complex calculations that would be difficult or impossible with standard formulas alone.

Implementing a Custom SQL Function (gsSQL)

One such custom function, like 'gsSQL,' allows you to execute SQL SELECT statements against named ranges in your Google Sheet. After installing the custom function (typically by copying the script into your Google Sheets Apps Script editor), you can define named ranges for your data tables (e.g., 'PRODUCT', 'COMPONENT', 'INVENTORY') as shown above.

With the tables defined, the following formula can be used:

=gsSQL(
  "select stock_number as 'Stock Number', floor(MIN(inventory.OnHand/component.quantity)) as 'Build Units' 
   from product 
   join component on product.stock_number = component.stock_number 
   join inventory on component.comp 
   group by stock_number", 
  "product", Product, 
  "component", Component, 
  "inventory", Inventory
)

This powerful query does the following:

• JOIN operations: It links your PRODUCT, COMPONENT, and INVENTORY tables based on common identifiers (stock_number and component_id).
• inventory.OnHand/component.quantity: For each component required by a product, it calculates how many units of that product could be built based solely on that component's stock.
• MIN(...): For each product, it finds the minimum of these ratios across all its required components, identifying the true limiting factor.
• FLOOR(...): Ensures only whole, buildable units are counted.
• GROUP BY stock_number: Aggregates these minimums for each unique product.

The result is a dynamic table showing your buildable units:

Stock Number | Build Units
-------------|------------
1122         | 2
2233         | 4
3344         | 4

The Nuance of Shared Components: A Critical Consideration

It's important to note that while the SQL approach above elegantly solves the 'available to build' for each product independently, it doesn't account for components shared across multiple products simultaneously. For example, if 'Part AB' is needed for both 'Widget 1122' and 'Widget 2233,' the calculation for 'Widget 1122' will assume all 'Part AB' stock is available, and similarly for 'Widget 2233.' If you build 'Widget 1122,' those 'Part AB' units are consumed, reducing the actual build capacity for 'Widget 2233.'

For scenarios where components are heavily shared and you need to optimize for total production across your entire catalog, a more sophisticated optimization algorithm or a system with advanced production planning capabilities might be necessary. This often involves defining prioritization rules (e.g., build highest-margin products first, or fulfill backorders). However, for many businesses, understanding the independent build capacity for each product is a valuable first step.

Beyond the Basics: Continuous Inventory Management

Accurate 'available to build' numbers are only as good as your inventory data. Regularly updating component stock levels is crucial. This can be done manually, through integrations with POS systems, or via automated processes. By maintaining precise component inventory and leveraging structured data, businesses can make informed production decisions, minimize waste, and ensure they meet customer demand effectively.

Managing complex inventory and production data in Google Sheets is a powerful way to gain operational clarity. For businesses looking to ensure their ecommerce storefront reflects accurate product availability, syncing this 'available to build' data from Google Sheets directly to platforms like Shopify or WooCommerce is essential. Tools like Sheet2Cart (sheet2cart.com) facilitate this by connecting your Google Sheets with your store, allowing you to set schedules so your products, inventory, and prices stay in sync, preventing oversells and streamlining your catalog management.