COGS formula: put purchasing and inventory data into the gross-margin conversation
Calculate cost of goods sold from beginning inventory, purchases, and ending inventory, then trace changes to supplier prices, freight, credits, waste, and stock records.
Bottom line
COGS is useful when the inputs can be traced. Start with beginning inventory plus purchases and other included costs, then subtract ending inventory. A companywide total can hide supplier price changes, freight, waste, or an inventory-count error in one product line.
Is it right for you?
- Lock the beginning and ending inventory dates
- Reconcile purchases to supplier invoices and credits
- Apply the policy for freight, labor, overhead, and write-downs consistently
- Review COGS by product or category where the data supports it
Start with the inventory bridge
A common periodic formula is beginning inventory plus purchases and other included costs, minus ending inventory. The result is cost of goods sold for the period. The exact included costs and inventory method depend on the business's accounting policy.
The formula connects warehouse records to AP. Supplier invoices, freight bills, credits, returns, and cutoff dates can all change the purchase input before the closing inventory count is considered.
Test the formula with an example
| Input | Illustrative amount |
|---|---|
| Beginning inventory | $90,000 |
| Purchases and included costs | $310,000 |
| Ending inventory | ($110,000) |
| COGS | $290,000 |
If revenue was $500,000, the illustrative gross profit would be $210,000 before operating expenses. A changed inventory count can move both COGS and gross profit, so the count support matters as much as the arithmetic.
Find the purchasing cause behind a change
Split the movement into price, volume, mix, freight, supplier credits, damage, shrinkage, and cutoff. A higher COGS percentage may come from a supplier increase, but it can also come from selling more low-margin products or failing to record a credit.
AP should reconcile large purchase accounts, unmatched receipts, supplier credits, and invoices received after close. Operations should explain count adjustments and unusable stock.
Frequently asked questions
Is COGS the same as all business expenses? No. COGS covers costs assigned to goods or services sold under the business's policy; operating expenses are reported separately.
Why does AP affect COGS? Invoices from suppliers, along with credits, freight, and purchase cutoffs, provide much of the source data.
Can ending inventory be estimated? Some businesses use estimates between counts, but the method and reconciliation should be approved and applied consistently.
Should COGS be reviewed by product? Yes, when reliable product-level data is available. The company total can hide a weak category.
Are COGS in gross profit? Yes, COGS is the number subtracted from revenue to get gross profit; the two are directly linked, not separate categories. In the worked example above, $500,000 in revenue minus $290,000 in COGS leaves $210,000 of gross profit still to absorb operating costs. A COGS error shows up twice: once in the cost figure itself and again in the gross profit it feeds into.
How do I calculate COGS in Excel? Set up three cells, the opening stock count, everything bought and added to inventory cost during the period, and the closing stock count, then use a fourth cell that adds the first two and subtracts the third. Keep each input tied to its own supporting schedule, an inventory count, a purchase ledger, rather than typing a number directly into the formula cell, so a reviewer can trace the total back to source records the way the opening walkthrough above describes.
See General ledger if that's closer to what you're evaluating.