Cost of production and sales statement template in Excel

Cost of production and sales statement template in Excel

When a business buys and resells, the cost of what it sold can almost be read off the supplier invoice. When it turns raw materials into something else, it cannot: what was bought is not what was used, because part of it stayed in the warehouse, and what was made is not what was sold, because finished goods are still waiting for a buyer. The cost of production and sales statement lives in that gap.

This cost of production and sales statement template in Excel builds the whole path on a single sheet of fixed rows: raw materials, direct labour, manufacturing overhead, cost of production for the period and cost of production and sales, with the calculated cells already solved and a final check against the cost of sales in the income statement.

What the cost of production and sales statement is

It is the statement that explains how the cost of what was sold during the period was formed. It is not a stock report or a list of purchases: it is the bridge between what came into the warehouse, what was transformed on the shop floor and what went out to be sold. Its last total is a single figure, and that figure has one exact destination: the cost of sales line of the income statement.

Any business that manufactures or transforms needs it: bakeries, garment workshops, furniture makers, food plants, print shops. So does any trader that packs or mixes products before selling them, because there buying and selling are no longer the same movement. It serves three practical purposes: valuing closing inventory properly, knowing whether the selling price covers the cost of making the product, and spotting differences between the warehouse and the accounting records early.

The path of the cost, from raw materials to what was sold

The statement is read from top to bottom. First raw materials: opening inventory plus purchases minus closing inventory give the materials used, that is, what was really consumed. Then direct labour and overhead, which are added to that figure to reach the cost of production for the period. And finally finished goods: what was already made at the start, plus what was made, minus what stayed unsold, give the cost of production and sales.

In one line: raw materials up to materials used, materials used up to cost of production, and cost of production up to what was sold. Each step deducts what stayed in the warehouse, which is why the statement does not end at what was bought or what was made, but at what left.

What the sheet includes

The item goes in column A and the amount in column C. Some rows are typed in and others calculate themselves. This is the full list, in the order in which it appears.

The four large figures of the statement are not typed in: they are calculated. The only entries are the inventory balances, purchases, labour, the four overhead lines and finished goods.

The worked example, step by step

This is the solved case the template ships with, with the figures in the same order as the statement.

The check balances because the last total of the statement and the cost of sales already recorded are the same figure. When they differ, the sheet says so and the difference line shows how much is still unexplained; that gap almost always comes from half-finished inventory counts or from warehouse movements without support.

Why closing inventory is deducted

This is the most common confusion. If 2,400,000 of raw materials were bought and 600,000 were still in the warehouse at closing, those 600,000 were not consumed: they are still there, ready for the next period. Charging them as cost of sales would load the month with an expense that has not happened yet. That is why closing inventory is deducted: what was not consumed stayed in the warehouse and becomes a cost of the period in which it is used.

Opening inventory, by contrast, is added: that material was paid for earlier and is being consumed now.

Why goods that stayed unsold are also deducted

The month ends and some finished goods were not bought by anyone. They already cost raw materials, labour and services, but they have not produced revenue yet. Leaving them inside cost of sales would show the income statement a cost without its revenue and would sink the margin for no reason. That is why closing finished goods are deducted from the cost of production: what stayed in the warehouse is set aside and becomes a cost of the period in which it is sold.

Both deductions are the same idea applied twice: once in the raw materials warehouse and once in the finished goods warehouse. What did not leave is not charged against the month's sales.

From this statement to the income statement

The last total of this statement feeds the cost of sales line of the income statement, and that line turns sales revenue into gross profit. If the total is wrong, the margin is wrong too.

The check against cost of sales

The last line of the sheet compares the calculated total with the cost of sales already recorded in the accounts. If the two figures agree, the statement balances. If they do not, the sheet says so and shows the difference, which is where inventory problems usually appear: goods received and not recorded, warehouse issues without support and counts that were never posted to accounting. That warning does not solve the problem, but it says how much it is worth and where to look.

Labour and overhead

Direct labour is the cost of the people who work directly on the product: the production line, the machine operator, the person who assembles and the person who packs. If those hours are charged to administrative expenses, the cost of production comes out incomplete and gross profit looks better than it is.

Manufacturing overhead is everything needed to produce without being part of the product itself. The template has four lines: indirect materials, indirect labour, rent and utilities, and depreciation and others. In the example they total 1,200,000, and that total enters the cost of production together with materials used and labour.

How to fill it in, step by step

1. Do the physical count of both warehouses before opening the file: the statement does not guess inventory, it receives it.
2. Type the opening raw materials inventory exactly as it closed in the previous period, so the two periods join without gaps.
3. Record the purchases of the period, including the ones that arrived on the last day and had not been invoiced yet.
4. Type direct labour and the four overhead lines, each in its own row, without forcing them together.
5. Load opening and closing finished goods, the two balances that close the path, and review the cost of production for the period.
6. Carry the last total to the check line and let the sheet say whether it agrees with cost of sales. If it does not, fix the inventory or the support before going on.

Tips and common mistakes

• Do not mix purchases with consumption. The month's purchases can be double the consumption if a good price was taken, and that does not double the cost of what was sold.
• Count both warehouses on the same day: a raw materials count on the 28th and a finished goods count on the 3rd do not describe the same period.
• Keep the support for every warehouse issue and separate shrinkage from what was sold: damaged goods are charged as a loss, not as cost of sales.
• Do not adjust the total by hand to make it agree with accounting. If the check does not balance, something needs recording, and hiding it today means finding it twice tomorrow.
• Review the basis you use to spread overhead and apply the same one every month: changing it halfway through the year makes costs jump with nothing real behind it.
• Be clear that this statement is an internal control and cost analysis tool: it does not replace the income statement, it does not replace an official document and it does not replace the accountant's judgement.

When to move to software

The template works very well while the business makes a few products and one person feeds the cost. Once the catalogue grows, once several production orders are open at the same time or once the warehouse moves goods every day, the sheet falls short: inventory is typed by hand and nobody sees the cost as it happens.

The natural step is to move the cost into a system that calculates it as inventory moves. Kardex Tauro handles a valued stock card, production orders and cost of sales linked together, so materials used and finished goods update with every movement. Even so, it is worth starting with the template: understanding the path of the cost is the step before configuring any system properly.

Download the statement, build it with the verified example in this guide and compare the result with the cost of sales you already have on record. This statement leans on the cost of sales template and on the inventory template, where the counts that become figures here are recorded.