COGS Calculation for Multi-SKU Stores: Solved with Google Sheets

## Why Multi-SKU COGS Is a Headache For e‑commerce stores with hundreds of SKUs, manually tracking the cost of goods sold (COGS) per product is error‑prone and time‑consuming. Each sale involves a specific SKU, and you must multiply the sold quantity by that SKU’s unit cost. Doing this ad hoc means lost profitability insights, delayed decisions, and potential tax miscalculations. Google Sheets offers a free, flexible way to automate the process. With a few well‑designed formulas, you can transform raw sales data into a live SKU‑level profit dashboard, without any complex inventory software. ## Step 1: Build Your SKU Master Sheet Create a sheet named “SKUs”. It will hold static product data: **SKU code**, **product name**, **unit cost**, and optionally **selling price**. For example: | SKU | Product | Unit Cost | |-----|---------|----------| | ABC123 | Widget A | 5.50 | | XYZ789 | Widget B | 12.00 | Later, if your cost changes, update this sheet—all linked calculations will instantly reflect the new value. Keep it clean; one row per SKU, no duplicates. ## Step 2: Import or Log Sales Transactions On a separate sheet named “Sales”, record every transaction: **date**, **SKU**, **quantity sold**, and **revenue per unit** (the price you actually charged). You can paste CSV exports from your platform (Shopify, Amazon, etc.), or use a Google Form for manual entry. The sheet might look like: | Date | SKU | Qty | Unit Price | |------|-----|-----|------------| | 2025-01-01 | ABC123 | 2 | 9.99 | | 2025-01-02 | XYZ789 | 1 | 19.99 | ## Step 3: Pull Unit Cost with VLOOKUP In the Sales sheet, add a column **Unit Cost**. Use `VLOOKUP` to fetch the cost from the SKUs sheet: `=VLOOKUP(B2, SKUs!A:C, 3, FALSE)` This searches for the SKU in column B of Sales in the SKUs sheet range A:C and returns the third column (Unit Cost). Copy this formula down the entire column. Now every row has both selling price and cost. ## Step 4: Calculate COGS and Gross Margin per Transaction Add two more columns: - **COGS** = `=C2 * D2` (Qty × Unit Cost) - **Gross Profit** = `= (C2 * E2) - F2` (Total Revenue - COGS), where E2 is Unit Price and F2 is COGS. Your Sales sheet now automatically shows profitability for each line item. ## Step 5: Aggregate by SKU Using QUERY or Pivot Table To see total profit per SKU, you need to sum revenue and COGS grouped by SKU. The `QUERY` function is ideal: `=QUERY(Sales!A:G, "SELECT B, SUM(C), SUM(C*E), SUM(F), SUM(C*E)-SUM(F) WHERE B IS NOT NULL GROUP BY B LABEL SUM(C) 'Total Qty', SUM(C*E) 'Total Revenue', SUM(F) 'Total COGS', SUM(C*E)-SUM(F) 'Total Profit'")` Place this formula on a new sheet; it will output a clean summary table. Alternatively, create a Pivot Table (Data > Pivot table) with SKU as rows and custom calculated fields for revenue, COGS, and profit. ## Step 6: Visualize Profitability Add a column for **margin %** in your summary: `=IFERROR(Total Profit/Total Revenue, 0)`. Apply conditional formatting to highlight high‑margin or loss‑making SKUs. For quick decision‑making, build a chart (bar chart) that compares profit across SKUs. ## Pro Tips for Real‑World Use - **Handling cost changes:** If you switch suppliers, don’t overwrite historic costs. Instead, use a separate “Cost History” sheet with effective dates, or calculate average cost. For simplicity, many small stores just update the unit cost and accept that past margins are recalculated at the new cost—useful for forward‑looking analysis. - **Returns and refunds:** Record negative quantities in Sales and ensure your formulas handle them (COGS becomes negative credit). - **FIFO/LIFO:** Google Sheets can’t natively do advanced inventory valuation, but you can approximate it by adding layers. For most e‑com sellers, weighted average or periodic update is sufficient. - **Automation:** Use Apps Script to automatically import sales data from emails or e‑commerce APIs, so your profit dashboard stays up‑to‑date with zero manual entry. ## Template Access To jumpstart your setup, make a copy of a pre‑built Google Sheets template (link in description). Once tailored to your SKU list, you’ll have a living report that tracks profitability at a glance, empowering you to cut underperformers and double down on winners.