Two Kits, One Shared Part: The Stock Your Sheet Counts Twice

A Google Sheets inventory template gives each SKU one row and one number: quantity on hand. When listing two bundles, Desk Set A contains a body and a black cap, while Desk Set B contains the same body and a grey cap. With five bodies and five black caps, the sheet adds two more rows, both displaying five. However, combining these numbers is incorrect.

The storefront offers ten desk sets, but only five bodies are available. While this issue seems like a bug, it functions consistently, and the formulas still add up correctly. The oversell only becomes apparent a week later through a cancelled order and an apology.

The mistake lies in assigning a quantity column to a kit, which represents something to be built rather than something owned. Each kit's availability depends on the smallest number of whole kits required to cover each component, not an average or whichever component appears first in the list. The function `kitAvailability` calculates this by iterating through components, dividing the on-hand quantity by the per number (ensuring whole numbers), and finding the smallest sets value. If on-hand goes negative, it indicates real stock that cannot be sold as part of that kit.

Three lines in the code are there due to a test requirement. The `Math.floor(have / per)` ensures a kit needing two caps out of five is counted as two kits and one orphan, not two and a half. The remainder, which is real stock, cannot be sold as part of that kit. The `best` value is set to zero if it is reached during calculations, preventing negative numbers from appearing on product pages.

A missing component in the inventory sheet throws an error, highlighting the importance of accurate data entry. Adding a missing component to the map would create a recipe for a part no longer carried, leading to a negative stock count and indirectly causing a kit to reach zero. Walking through stock rows, not commitments, keeps faults visible and prevents creating components with negative stock.

When an order takes a kit, no parts are claimed against the kit itself, as there is nothing to claim. Instead, the claim distributes across every component. The `commitKit` function handles this by iterating through components of the kit and reducing the on-hand quantity accordingly. This approach prevents the two numbers from lying to each other, ensuring accurate stock management for kits.