Ending inventory = beginning inventory + purchases − cost of goods sold. It's what should be on the shelves at the end of a month or a year, at cost: what you started with, plus what went into stock, minus what left in sales. Turned round, beginning inventory = ending inventory + cost of goods sold − purchases, and the beginning of one period is the end of the last. A hot sauce maker that starts July with $8,400 of stock, puts $23,300 into it over the quarter and sells $18,400 of it at cost should end September with $13,300. The count says $12,850. Below is each formula, the quarter, what the $450 gap is, the same sum in bottles, and a free calculator.
The short version
- Ending inventory = beginning inventory + purchases − cost of goods sold, all at cost, not at selling price.
- Beginning inventory = last period's ending inventory, or ending + cost of goods sold − purchases.
- For a maker, purchases are what went into stock: materials bought, and labor put into batches if you cost it in.
- The formula says what should be there; a count says what is. The gap is waste, mistakes and loss.
- Work it in units too: units at the start + made − sold = units at the end, times the cost of each.
- TaroStack keeps stock up to date as batches and sales happen, and values it by month. More below.
The formulas
| To find | Formula |
|---|---|
| Ending inventory | Beginning inventory + purchases − cost of goods sold |
| Beginning inventory | Ending inventory + cost of goods sold − purchases |
| Cost of goods sold, from a count | Beginning inventory + purchases − ending inventory |
| Ending inventory, in units | Units at the start + units made or bought − units sold |
All three money formulas are the same sum rearranged, so knowing any three numbers gives the fourth. They're in dollars at cost: a bottle that cost $2.10 to make counts as $2.10, whatever it sells for. Which cost a unit carries when prices change, first in first out or an average, is in FIFO method formula.
An example: a hot sauce maker's quarter
Blue Ridge Hot Sauce, a made-up hot sauce maker in Roanoke, Virginia, I use in these examples, counts its stock at cost on July 1: $8,400 of peppers, pepper mash, vinegar, bottles, labels and finished sauce. It costs its batches with their labor, so the hours that go into a batch go into stock with it:
| Month | Beginning inventory | Materials bought | Labor into batches | Cost of goods sold | Ending inventory |
|---|---|---|---|---|---|
| July | $8,400 | $4,700 | $1,500 | $5,900 | $8,700 |
| August | $8,700 | $6,100 | $1,700 | $6,400 | $10,100 |
| September | $10,100 | $7,300 | $2,000 | $6,100 | $13,300 |
| The quarter | $8,400 | $18,100 | $5,200 | $18,400 | $13,300 |
Each month begins where the last ended. September's materials are high because that's when the pepper harvest is bought and set to ferment, so stock grows while sales hold steady.
What the count says
On September 30 the count, at cost, comes to $12,850, $450 less than the formula's $13,300. The formula only knows what was recorded; the $450 is what left without a sale: a case of habaneros that went soft before it was mashed, a few dozen bottles broken or labeled wrong, and the samples handed out at a tasting. Why counts and records part ways is in why inventory doesn't match.
The count wins. Worked backward, the quarter's real cost of goods sold is $8,400 + $23,300 − $12,850 = $18,850, and October begins at $12,850. If you only know a count at the end and want the beginning, the same sum works the other way: $12,850 + $18,850 − $23,300 = $8,400. How a bakery uses the same sum for its cost of goods sold is in how to calculate COGS for a bakery.
The same sum in bottles
For finished goods, the formula is easier to check in units first, then in dollars:
| Sauce, 5 oz | Bottles July 1 | Made | Sold | Bottles September 30 | Cost a bottle | Value |
|---|---|---|---|---|---|---|
| Smoked habanero | 600 | 2,500 | 2,400 | 700 | $2.10 | $1,470 |
| Original | 900 | 3,900 | 3,800 | 1,000 | $1.85 | $1,850 |
| Green chile | 500 | 1,500 | 1,400 | 600 | $1.95 | $1,170 |
| Ghost pepper | 300 | 750 | 700 | 350 | $2.60 | $910 |
| Mango habanero | 200 | 900 | 800 | 300 | $2.20 | $660 |
| All five | 2,500 | 9,550 | 9,100 | 2,950 | $6,060 |
The finished bottles are $6,060 of the $12,850 counted; the other $6,790 is peppers, mash fermenting in barrels, vinegar, empty bottles and labels. A count sheet that keeps those apart is in inventory count sheet example.
Get the calculator
Download the ending inventory calculator. Three sheets: ending inventory, one line a month, where you type the first beginning inventory and each month's materials, labor into batches and cost of goods sold, and ending inventory fills in, with a column for the count and the difference; beginning inventory, worked back from a count; and by units, where each product's bottles at the start, made and sold give the bottles at the end and their value. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
The formula is only as good as the three numbers in it. Cost of goods sold has to come from somewhere: a cost for every bottle sold, at the cost it was made at, across five sauces whose peppers cost more in March than in September. Worked out once a month from a pile of invoices and batch sheets, it arrives weeks late, and the gap with the count can't say which sauce it came from. The difference between keeping stock up to date as it moves and counting it now and then is in perpetual vs periodic inventory.
How TaroStack does it
TaroStack keeps stock up to date as batches and sales happen, and values it by month.
Stock that moves as you work. Receiving peppers adds them at what they cost, a batch of sauce takes its peppers, vinegar and bottles off stock and adds the finished bottles, and each sale takes them off again, so the ending inventory is there at any moment, not worked out at month end.
Cost from what you paid. Each batch is costed from what its materials cost, with first-in-first-out or a moving average, so the cost of goods sold carries the cost each bottle was made at. Stock value is reported by site and by month.
Counts with reasons. A count on a phone, even offline, records what's really there, and each difference is an adjustment with a reason, so the $450 shows as soft peppers, breakage and samples, by item, in the waste report.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| Ending inventory | Worked out at month end | Up to date as stock moves |
| Cost of goods sold | Added up from invoices | From each batch's cost |
| Stock value by month | A sheet for each month | Stock value by site and month |
| Why the count is short | Unknown | Adjustments with reasons |
Batches, costing, counts and stock value are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.