Cost Of Goods Sold Schedule

The Real Way to Build a COGS Schedule

Most people approach this completely wrong. They try to match every single transaction to inventory in real time and then wonder why their spreadsheet crashes or why their numbers never reconcile. The schedule itself is straightforward, but the way you assemble it matters enormously. Here is how I actually build one without losing my mind.

Start with your ending inventory before you touch anything else. I know that sounds backwards since the whole point is figuring out what sold, but if you do not know what you have left, you cannot derive what went out. Pull a current physical count or a reliable perpetual system report, then use that as your anchor. The formula is always the same: Beginning Inventory plus Purchases minus Ending Inventory equals Cost Of Goods Sold. It is mathem so simple it feels like a trick. The trick is getting the inputs right. A COGS schedule is essentially a detailed breakdown that traces every dollar of product cost from your opening balance through purchases, adjustments, and finally to the goods actually sold during a period. In practice it looks like a simple grid with columns for beginning inventory, purchases, freight-in, purchase returns and allowances, purchase discounts, goods available for sale, ending inventory, and the final COGS figure. The rows break down by inventory category, product line, or warehouse location depending on how your business operates. Here is where beginners consistently mess up. They forget to include freight-in. If your supplier charges shipping to bring inventory to your warehouse, that cost is part of your inventory value, not a separate expense. Excluding it understates COGS and overstates gross profit. I have seen this happen in multiple quarterly closes. The variance looked small at first but accumulated across product lines until it was a five-figure discrepancy that took three hours to trace.

Another thing nobody warns you about: purchase returns and discounts. When you return damaged goods to a vendor or take an early payment discount, those amounts reduce your effective cost of goods. Your schedule needs to account for them, or your ending inventory and COGS will both be wrong. The adjustment flows through automatically if your accounting system is set up correctly, but if you are building this manually or in a spreadsheet, you have to enter it yourself. I learned that the hard way during my first year working with a client who had a 2 percent early payment discount program they never tracked in their schedules. Their gross margin was off by about 1.3 percentage points every quarter. Let me walk through a concrete example. Say you start the month with $40,000 in inventory. During the month you purchase $60,000 in goods. Freight-in is $3,000. You returned $2,000 worth of defective items to suppliers. You took a $1,000 purchase discount. Your physical count at month-end shows $35,000 in ending inventory. Here is the schedule: Beginning Inventory: $40,000
Purchases: $60,000
Freight-In: $3,000
Purchase Returns: -$2,000
Purchase Discounts: -$1,000
Cost of Goods Available for Sale: $100,000
Ending Inventory: -$35,000
Cost Of Goods Sold: $65,000

That $65,000 is the number that goes on your income statement. The rest is just supporting detail for auditors and anyone who asks why your margins shifted. For larger operations with multiple SKUs, I recommend organizing the schedule by inventory category or product line. This gives you visibility into which segments are driving margin changes. A single consolidated number is fine for tax purposes but almost useless for operational decisions. I once analyzed a manufacturing client where the overall COGS looked stable, but when I broke it down by product line, one category had inflated by 18 percent due to a supplier price increase they had not updated in the system. We caught it because we were building a detailed schedule instead of just pulling a report. If you want a downloadable template, most accounting platforms offer a standard COGS schedule template. QuickBooks, Xero, and FreshBooks all have built-in inventory reports that export to CSV. For more control, you can find solid templates on the AICPA resource library or from major accounting textbook publishers. I typically build mine in Excel or Google Sheets with a structured format that mirrors the calculation above. I use data validation on the inventory categories and conditional formatting to flag any line item that deviates more than 10 percent from the prior period. That last part alone catches roughly half the errors I see before they become problems.

One limitation worth noting upfront: this method assumes a periodic inventory system or a reasonably accurate perpetual system. If you are running a high-volume retail operation with thousands of transactions per day and no barcode scanning, your perpetual numbers will drift. Physical counts become essential, and even then, shrinkage and spoilage can create material variances. In those cases, a standard COGS schedule will show you the numbers, but it will not tell you why. You need cycle counts and variance analysis on top of it. I switch to a more granular approach for clients doing over $2 million in annual inventory movement. The spreadsheet gets bigger but the accuracy improves significantly. The other common pitfall is mixing up LIFO, FIFO, and weighted average costing methods mid-period. Your COGS schedule needs to reflect the same method consistently. Switching methods changes your ending inventory valuation, which changes your COGS, which changes your taxable income. If you must change methods, document it clearly and calculate the cumulative effect. The IRS requires that for tax purposes, and your auditor will ask for it whether or not you are a public company. For a quick reference, here is a condensed checklist you can use when building your schedule each period: verify beginning inventory matches the prior period's ending balance, confirm all purchases are recorded gross before returns and discounts, include freight and insurance costs in the inventory value, subtract purchase returns and discounts, conduct or review the physical count, calculate goods available for sale, subtract ending inventory, and reconcile the final COGS to your general ledger. If every line checks out, you are done. If one does not, you have a hunt on your hands, and starting from the physical count backward is usually the fastest way to find the error.