A shop counts what it bought. A manufacturer has to count the same goods at three stages: raw materials waiting to be used, work in progress that's partly made, and finished goods ready to sell. Packaging makes a fourth group. A manufacturing inventory spreadsheet puts all of them on one sheet, with a column saying which stage each row is in, and values each at what it has cost so far. Raw material is worth what you paid. Work in progress is worth its materials plus the work done to it. Finished goods are worth what they cost to make, never what they sell for. The stage column is what lets you answer the accountant's question and your own: where is the money sitting? Below is a sheet for a made-up hot sauce maker, how value moves through it, and the file.
The short version
- One sheet, four stages: raw material, work in progress, packaging, finished goods.
- Raw material at what you paid. Finished goods at what they cost to make.
- Work in progress counts. Value it at materials and work so far.
- Reorder levels for what you buy. Make-more levels for what you sell.
- When a batch is made, raw goes down and the next stage goes up, in the same entry.
- Add each stage up: that's where the cash is.
- TaroStack moves stock between stages when you record a batch. More below.
An example sheet
| Item | Stage | Unit | On hand | Unit cost | Value | Status |
|---|---|---|---|---|---|---|
| Red jalapeños | Raw material | lb | 260 | $1.85 | $481.00 | OK |
| White vinegar, 5% | Raw material | gal | 40 | $3.20 | $128.00 | OK |
| Pepper mash, fermenting | Work in progress | gal | 55 | $9.40 | $517.00 | |
| Sauce, cooked, in the holding tank | Work in progress | gal | 18 | $12.10 | $217.80 | |
| Bottle, 5 oz, with cap | Packaging | each | 3,100 | $0.34 | $1,054.00 | OK |
| Hot sauce, original, case of 12 | Finished goods | case | 96 | $15.80 | $1,516.80 | OK |
| Hot sauce, garlic, case of 12 | Finished goods | case | 41 | $16.40 | $672.40 | REORDER |
Ten rows in all, $4,959.45 at cost. One flag: garlic sauce is at 41 cases against a level of 60, which for a finished product means schedule a batch.
Where the money is
| Stage | Value | Share |
|---|---|---|
| Raw material | $729.45 | 15% |
| Work in progress | $734.80 | 15% |
| Packaging | $1,306.00 | 26% |
| Finished goods | $2,189.20 | 44% |
| All stock | $4,959.45 |
The two barrels of mash and the tank of cooked sauce are $734.80. Left off the count, the inventory would have been understated by 15%. Work in progress is easy to miss because it isn't on a shelf: it's in a barrel, a tank or an oven. Walk the production floor with the count sheet, not only the store room.
Finished goods are 44%. That's 137 cases made and not yet sold. Whether that's too much depends on how fast they sell: at 60 cases a week it's a little over two weeks of stock.
Valuing each stage
Raw material is what you paid, freight included.
Work in progress is its materials plus the work so far. This maker's mash is $9.40 a gallon: the peppers, salt and garlic that went into the barrel, and the hours of washing and grinding.
Finished goods are what a case cost to make. Follow one bottling run:
| 38 cases of original, from 18 gallons of cooked sauce | |
|---|---|
| Cooked sauce, 18 gal at $12.10 | $217.80 |
| Bottles with caps, 456 at $0.34 | $155.04 |
| Labels, 456 at $0.06 | $27.36 |
| Bottling labor and overhead | $200.20 |
| 38 cases | $600.40 |
| A case | $15.80 |
When that run is recorded, three rows go down and one goes up, together: sauce by 18 gallons, bottles by 456, labels by 456, and finished cases up by 38. If only the finished row is updated, the sheet shows sauce you no longer have, and the total is too high by the $400 of materials counted twice.
A case sells to shops for more than $15.80, and that's exactly why it isn't valued at its price. Stock is recorded at cost, and the profit appears when it's sold. The formula for the finished stage is in finished goods inventory formula, work in progress in WIP tracking Excel template, and the raw stage in raw materials inventory example.
Lots at every stage
| Date | Item | Lot | Quantity | Note |
|---|---|---|---|---|
| Nov 3 | Red jalapeños | P-1103 | 400 lb | 260 lb left |
| Oct 20 | Pepper mash, fermenting | M-1020 | 55 gal | Ready to cook from Dec 1 |
| Nov 5 | Hot sauce, original | HS-1105 | 40 cases | |
| Oct 22 | Hot sauce, garlic | HG-1022 | 41 cases |
Each stage's lot should name the lot before it: mash M-1020 was made from particular peppers, and a case of HS-1105 from a particular batch of sauce. That chain is what a trace follows. More in what is a traceability system.
Get the spreadsheet
Download the manufacturing inventory spreadsheet. Four sheets: inventory with a stage for each row, value and REORDER; lots with dates; a count sheet that works out each difference and what it's worth; and a read-me. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
Every batch is three or four rows changing at once, in different units, with a new unit cost to work out for what was made. Do that by hand twice a week and the sheet is wrong by the end of the month, in a way nobody can find, because the error is a subtraction somebody forgot in week two.
How TaroStack does it
TaroStack makes those entries when you record the batch.
Each product is a recipe, with sub-recipes. Mash is a sub-recipe of sauce, and sauce of the bottled case.
Record a batch and its inputs come off stock and its output goes on, in one step. The batch notes which lots it used, automatically.
Cost follows the materials: a batch is costed from what its inputs cost, freight included, with actual cost beside the standard.
Stock value by site and by month, at cost.
"What could I make right now" says how many cases the mash, bottles and labels on hand allow.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| Raw down, finished up | Four rows by hand | Recorded with the batch |
| What a case cost to make | A sum for each run | From the batch's inputs |
| Stock value for the accountant | Add up the stages | Stock value, by month |
| Which mash a case came from | A note, if written | The lots on each batch |
TaroStack doesn't cost your labor: add that yourself if your accountant wants it in the case's value. Recipes, batches and stock value are on every plan, from $9.99 a month (5 recipes) or $39 a month (50 recipes). The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.
