Cogs Tracking Allocation

COGS Tracking and Allocation

Overview

Tracking Cost of Goods Sold (COGS) tells you the actual gross margin on every order — revenue minus what you paid for the products you sold. Most platforms let you enter a cost price per product, and accounting integrations (QuickBooks, Xero) use that to generate COGS entries automatically. Landed cost allocation (freight, customs, duties) requires either a dedicated app or manual allocation in your accounting software. Only build a custom COGS system if your platform cannot meet your costing method requirements (FIFO, weighted average).

When to Use This Skill

• When your income statement shows revenue but you cannot compute gross margin because unit costs are not tracked
• When importing goods internationally and needing to allocate freight, customs, and duties into the landed cost of each SKU
• When switching from periodic (year-end count) to perpetual (real-time) cost tracking
• When building variance analysis reports to identify SKUs where actual purchase costs are drifting from standard costs
• When an ERP integration (QuickBooks, NetSuite, Xero) requires COGS journal entries at time of sale

Core Instructions

Step 1: Determine platform and choose the right approach

---

Step 2: Enter cost prices per product

Before any COGS reporting is possible, every product variant needs a cost price.

Shopify

1. Go to Admin → Products → [Product] → [Variant] 2. Under Pricing, enter the Cost per item field 3. Shopify's Analytics → Finances → Product sales by variant report will then show profit per product

For bulk cost updates: use Matrixify (App Store) to import a CSV with cost per SKU — the column is Variant Cost.

WooCommerce

1. Go to WooCommerce → Products → [Product] → Inventory tab 2. Enter the value in the Purchase cost field (requires WooCommerce Cost of Goods plugin or the field added by your theme/plugin) 3. For reporting: install Metorik or connect QuickBooks for WooCommerce — both read the cost field and generate gross margin reports

Popular COGS plugins for WooCommerce:

• WooCommerce Cost of Goods (SkyVerge) — adds cost field, generates margin reports
• Metorik — analytics dashboard showing profit per product, order, and channel

BigCommerce

1. Go to Products → [Product] → Pricing 2. Enter the value in the Cost Price field 3. Connect QuickBooks Online via Apps → BigCommerce for QuickBooks — it maps cost price to COGS entries automatically

---

Step 3: Connect accounting software for COGS journal entries

The cost price field alone only enables reporting inside the platform. For your P&L and balance sheet, connect an accounting integration that posts COGS when orders are fulfilled.

Shopify → QuickBooks / Xero

• QuickBooks Online: Install QuickBooks Connector by OneSaas from the Shopify App Store. Configure: Sales → Fulfilled orders post to COGS account; map products to QuickBooks items
• Xero: Install Xero by Amaka or A2X from the Shopify App Store. A2X is the most widely used — it batches daily Shopify sales into summarized Xero journal entries including COGS

A2X is strongly recommended for Shopify + Xero — it handles currency conversion, refunds, fees, and COGS automatically.

WooCommerce → QuickBooks / Xero

• QuickBooks: Install QuickBooks Commerce or MyWorks Sync (most reliable WooCommerce ↔ QuickBooks sync)
• Xero: Install Xero for WooCommerce by Zapier or Xero Bridge by Parex for direct sync

BigCommerce → QuickBooks

• Use the built-in BigCommerce for QuickBooks app from the Apps marketplace
• Configure COGS mapping under Settings → Accounting → Cost of Goods Sold account

---

Step 4: Set up landed cost allocation

Landed costs (freight, insurance, customs, duties) must be added to the product cost before the first sale to get accurate COGS.

For most merchants (manual method): 1. Receive your shipment and get the freight/customs invoice 2. Calculate the landed cost per unit: Total landed cost ÷ Total units received 3. Update the cost price in your platform to include the landed cost component 4. For allocation by value: (Product value / Total shipment value) × Total landed cost ÷ Units received

For Shopify merchants with frequent imports:

• Install Shopify Shipping Costs or LandedCost.app from the App Store
• These apps let you enter a shipment's landed costs and allocate them across received SKUs automatically

For accounting software users:

• In QuickBooks or Xero, use a landed cost allocation journal entry to add freight/duties to inventory asset value before goods are sold

---

Step 5: Run COGS and margin reports

Once cost prices are entered and accounting is connected:

Shopify

• Analytics → Finances → Profit by product — shows revenue, cost, and margin per variant
• Analytics → Reports → Product sales — filter by date range, includes units sold and profit

WooCommerce + Metorik

• Metorik → Reports → Profitability — gross margin by product, order, and time period
• Metorik also shows cost per order and margin %

QuickBooks / Xero

• P&L report — COGS line shows total cost of goods sold for the period
• Inventory valuation summary — current on-hand value per SKU

---

Custom / Headless

For headless storefronts needing perpetual inventory costing with FIFO, build a cost ledger:

// Cost layer model — one row per purchase order receipt
interface InventoryCostLayer {
  variantId: string;
  receiptDate: Date;
  quantityReceived: number;
  quantityRemaining: number;  // Decrements as units are sold
  unitCostCents: number;      // Purchase price per unit
  landedCostCents: number;    // Allocated freight/duties per unit
}

// FIFO cost assignment when an order is fulfilled
async function assignCogsFifo(variantId: string, locationId: string, quantity: number, orderId: string) {
  let remaining = quantity;
  let totalCostCents = 0;

  return db.transaction(async tx => {
    // Consume oldest cost layers first (FIFO)
    const layers = await tx.inventoryCostLayers.findAll({
      where: { variantId, locationId, quantityRemaining: { gt: 0 } },
      orderBy: { receiptDate: 'asc' },
    });

    for (const layer of layers) {
      if (remaining <= 0) break;
      const units = Math.min(remaining, layer.quantityRemaining);
      const totalUnitCost = layer.unitCostCents + layer.landedCostCents;

      await tx.inventoryCostLayers.update(layer.id, {
        quantityRemaining: layer.quantityRemaining - units,
      });
      await tx.cogsEntries.create({
        orderId, variantId, quantity: units,
        unitCostCents: totalUnitCost,
        costingMethod: 'fifo',
      });

      totalCostCents += units * totalUnitCost;
      remaining -= units;
    }

    if (remaining > 0) throw new Error(`Insufficient cost layers for ${variantId}`);
    return { totalCostCents };
  });
}

// Allocate landed costs to a received shipment (by value)
async function allocateLandedCostsByValue(shipmentId: string, totalLandedCostCents: number) {
  const layers = await db.inventoryCostLayers.findByShipment(shipmentId);
  const totalValue = layers.reduce((s, l) => s + l.unitCostCents * l.quantityReceived, 0);

  for (const layer of layers) {
    const share = (layer.unitCostCents * layer.quantityReceived) / totalValue;
    const perUnitLanded = Math.round((share * totalLandedCostCents) / layer.quantityReceived);
    await db.inventoryCostLayers.update(layer.id, { landedCostCents: perUnitLanded });
  }
}

Best Practices

• Enter cost prices before your first import or purchase order receipt — COGS is only accurate for orders after costs are set; retroactive fixes require manual adjustments
• Include landed costs in unit cost before the first sale — freight and duties paid after goods are received but before sale must be allocated; otherwise COGS understates the true cost
• Choose one costing method (FIFO vs. weighted average) per product category and stick to it — switching mid-year requires a revaluation journal entry
• Reconcile platform profit reports with your accounting software monthly — discrepancies usually mean cost prices are missing for some variants
• Set a standard cost at the start of each fiscal year for variance reporting — compare actual purchase prices to standard to catch cost drift early

Common Pitfalls

Related Skills

• @inventory-tracking
• @multi-warehouse
• @catalog-import-export

Perpetual Inventory Cost Tracking — Database Schema

Problem/Feature Description

A mid-sized online retailer is replacing their end-of-year spreadsheet inventory count with a real-time perpetual inventory cost system. They sell hundreds of SKUs across multiple product variants and warehouse locations. Some goods are imported internationally, so the total cost of a unit includes not just the purchase price but also freight, insurance, customs, and duties that arrive on separate invoices.

The finance team has three requirements. First, they want to be able to trace which purchase lots were consumed when any order ships, using the costing method of their choice (FIFO, LIFO, or weighted average). Second, they need to record the landed costs associated with each inbound shipment and spread those costs across the received inventory. Third, they want to run variance reports each month comparing what units actually cost against the budgeted standard costs they set at the start of the year.

Your job is to produce the SQL migration that creates the full schema for this system. The schema must support all these use cases and enforce valid states through the database itself.

Output Specification

Produce a single SQL file named schema.sql containing all table definitions. Include comments explaining the purpose of key columns where the naming alone might be ambiguous. Do not include seed data or application logic — just the table definitions.

Implement COGS Cost Assignment Functions

Problem/Feature Description

A warehouse management system already has an inventory_cost_layers table that records purchase lots (each row is a received batch of a given product variant at a given location, with a unit cost and remaining quantity). When an order ships, the system needs to post COGS — it must determine which purchase lots were consumed, record the cost, and reduce the available inventory in those lots.

The company runs two types of products: fast-moving consumables tracked with weighted average cost, and high-value electronics tracked with FIFO. Both methods need to be implemented. The COGS functions will be called inside a larger order fulfillment workflow, so correctness and data integrity are critical — any silent failures or stale cost data will cause the income statement to be wrong.

The engineering team lead has flagged two specific concerns from past incidents: (1) a prior implementation cached a running average per SKU and never recomputed it, causing margin reports to drift significantly after large receipts; and (2) another service used to silently skip COGS posting when inventory data was missing, hiding the problem for weeks. The new implementation must not repeat either mistake.

Output Specification

Write the COGS assignment logic in a file called cogs.ts. The file should contain:

• A function for FIFO cost assignment (assignCogsFifo or similar)
• A function for weighted average cost assignment (assignCogsWeightedAvg or similar)

Assume a db object is available with methods db.transaction(), db.raw(), and ORM-style finders/updaters on each table. You do not need to make the code runnable — focus on the logic being correct. Add a brief comment in the file explaining any key design decisions.

Landed Cost Allocation and Standard Cost Variance Report

Problem/Feature Description

A global electronics distributor imports components from three suppliers in Southeast Asia. Each inbound shipment carries freight, insurance, and customs charges that are invoiced separately from the goods — sometimes arriving weeks after the components themselves have been received and entered into the inventory system. The finance team needs a way to record these shipment-level charges and distribute them proportionally across the individual purchase lots that were included in each shipment.

Separately, the CFO has asked for a monthly report that compares what units actually cost (based on recorded COGS entries) against what the finance team budgeted they would cost (standard costs). The report should highlight the products drifting furthest from budget so the pricing team can act. The company sets new standard costs at the beginning of each fiscal year, and some SKUs have had their standards revised mid-year when a major supplier changed pricing — so the system must correctly match each COGS entry to whichever standard cost was active at the time of the sale.

Output Specification

Write the implementation in a file called cost-management.ts. It should contain:

• A function to allocate a shipment's landed costs across inventory cost layers (allocateLandedCosts or similar)
• A function or SQL query that computes standard cost variance per product variant for a given date range (computeVarianceReport or similar)

Assume the database tables from the schema (inventory_cost_layers, landed_cost_shipments, landed_cost_allocations, standard_costs, cogs_entries, product_variants) are available via a db object. You do not need to make the code runnable. Add a brief comment explaining any important decisions.