Cost of sales and gross profit template in Excel
Cost of sales and gross profit template in Excel
A business can sell more than it did last year and still run out of money. The reason is nearly always the same: sales grew, but so did the cost of replacing what had been sold, and nobody kept the two figures apart. When cost of sales is never worked out, the only number in view is the total that came through the till, and that total tells half the story.
This cost of sales and gross profit template in Excel shows, month by month and on a single sheet, how much a sale leaves behind once the goods sold have been replaced. It carries twelve months across the columns, with net sales, cost of sales and operating expenses as the figures you type in, and gross profit, gross margin, operating profit and operating margin calculated automatically. It is an ordinary workbook, with no macros and no passwords, ready to use the day you download it.
⬇ Download the template (Excel .xlsx)What cost of sales is and who this format is for
Cost of sales is what the goods that actually left the door cost you. It is not what you bought during the month, and it is not what sits in the storeroom: it is the part of that which has already been sold. If you bought a hundred units and sold sixty, cost of sales is the cost of those sixty, and the other forty are still inventory, not an expense of the period. That distinction, which sounds like a bookkeeper concern, prevents the most common mistake of all: believing a month was bad because a lot was bought, or good because nothing was.
Gross profit is a simple subtraction: net sales minus cost of sales. It is the money left to carry the operation and, if anything remains, to earn. Its constant companion is gross margin, the same figure expressed as a share of sales; it does not tell you how much you made in cash, it tells you how efficient each unit of sales was. A business can post a high gross profit and a thin gross margin, which means it sells a great deal with very little air between price and cost. That is the earliest warning signal there is, and it usually shows up before any cash problem does.
It is meant for businesses that buy and resell, transform materials or provide services, and want to know whether the operation leaves anything before administration is deducted. It suits a neighbourhood shop, a distributor, a restaurant, a garment workshop, a hardware store and a service business that bills by the hour. It also suits anyone who has never built an income statement: this is the first block, the simplest one, and the one that answers the most questions with the fewest columns.
It is worth saying what this sheet does not do. It does not replace the full income statement or the cash flow statement: net profit is not calculated here, debt is not reviewed and cash is not projected. It does one thing, and does it well: it shows what a sale leaves behind once the goods sold have been replaced.
What the template includes
The file holds a twelve month matrix ready to fill in, with the calculations already written into every line that depends on the figures you type. This is what you find when you open it:
| Section | What it holds and what it is for |
|---|---|
| Net sales | An input row for every month, with the effective sales of the period after returns and discounts. |
| Cost of sales | An input row with what the goods sold during that same month cost you, not what you purchased. |
| Operating expenses | An input row with administration, payroll, rent, utilities and the other expenses of the period. |
| Gross profit, automatic | Subtracts cost of sales from net sales for the month, with nothing for you to write. |
| Gross margin, automatic | Expresses gross profit as a share of the net sales of the month. |
| Operating result, automatic | Deducts operating expenses from gross profit and shows both the profit and its margin. |
| Monthly totals | Closes each monthly column and summarises that month across the lines of the matrix. |
| Year summary | The year total column adds sales, cost, expenses and profits so you can read the accumulated figure without opening other sheets. |
The sheet imposes no accounting categories and no complicated chart of accounts: three input lines and four result lines. If your business runs several lines of trade, you can duplicate the block and add the totals in a separate row; if you want to see cost by product family, keep that detail on another sheet and bring only the monthly total here.
The rows, the columns and how the sheet is laid out
The matrix has three parts: the column of concepts, the twelve monthly columns and the year total column. Every line plays a different role, and it is worth being clear about it before typing the first figure:
| Row or column | What you write or what it calculates |
|---|---|
| Concept | The name of each line: net sales, cost of sales, operating expenses and the four result lines. |
| Jan to Dec | Twelve columns, one per month. Only the three input lines are typed; everything else is calculated from them. |
| Net sales | What was effectively invoiced in the month, once customer returns and discounts granted are taken off. |
| Cost of sales | What the goods sold during that month cost, worked out from the units that left. |
| Operating expenses | Administration and selling costs of the period: payroll, rent, utilities, transport, stationery. |
| Gross profit | Automatic: net sales minus cost of sales. The first proof that the operation leaves something. |
| Gross margin | Automatic: gross profit divided by net sales. Compares months of different size on equal terms. |
| Operating profit | Automatic: gross profit minus operating expenses. Measures what the operation leaves before financing matters. |
| Operating margin | Automatic: operating profit divided by the net sales of the month. |
| Year total | A column that adds each row across the twelve months so the accumulated figure can be read at a glance. |
The four automatic lines sit in cells with a visible formula: if a number looks odd, a glance at the formula bar shows where it came from. That transparency is deliberate, because a template nobody can audit ends up abandoned.
How the calculation works, with an example
Take a month with net sales of 6,000,000 and cost of sales of 3,400,000, plus operating expenses of 2,200,000. The full run looks like this:
| Concept | Value or result |
|---|---|
| Net sales | 6,000,000 |
| Cost of sales | 3,400,000 |
| Gross profit | 2,600,000 |
| Gross margin | Close to forty three out of every hundred |
| Operating expenses | 2,200,000 |
| Operating profit | 400,000 |
| Operating margin | Close to seven out of every hundred |
The 2,600,000 of gross profit is the air a sale leaves before administration, and a gross margin close to forty three out of every hundred means that of every hundred sold, about forty three are left to carry the operation. With 2,200,000 of operating expenses the profit settles at 400,000: the business earns, but on a thin cushion. Expenses need only rise, or cost move by a few points, for that result to disappear. That is where the matrix earns its keep: the same figure set against previous months and against the year total, to see whether the margin holds or is quietly deflating.
How to fill it in, step by step
Order matters, because each result line rests on the one above it. This is the recommended flow:
- Write the net sales of the month in its row, with returns and discounts already taken off. If the month is still running, use only what was invoiced up to the cut off date and note it.
- Write the cost of sales for the same period, taken from inventory movements and not from total purchases. It is the figure most often got wrong, and the one that moves the result most.
- Write the operating expenses of the month. If an account is doubtful, ask whether it would exist even if you had sold nothing; if the answer is yes, it is an expense of the period.
- Check the four automatic lines against the previous months. A margin that falls two months in a row deserves a review of prices or of costs.
- Complete the month before looking at the year total. An accumulated figure built from half filled months is no basis for a decision.
- Save the sheet with the same cut off date every month and keep a copy per period. That way you can go back and understand what changed when a result surprises you.
Tips and common mistakes when measuring gross profit
Most gross profit figures that come out wrong fail not through the formula but through the quality of the data that goes in. These are the stumbles that repeat most often:
- Confusing purchases with cost of sales. Buying a lot fills the storeroom, not the income statement. Goods become cost only when they leave.
- Forgetting returns and credit notes. If they are not taken off, net sales are inflated and gross profit looks better than it is.
- Working out cost by eye. Cost of sales comes from inventory, not from a fixed share and not from the memory of whoever did the buying.
- Looking at profit and not at margin. A high profit on huge sales can hide a margin too thin to carry the operation.
- Pushing expenses into cost of sales. Rent, advertising and administrative payroll are expenses of the period, even in a month with weak sales.
- Not separating cost by product. An acceptable overall margin can be carried by two strong lines while hiding several that sell below their cost.
When to move to inventory and sales software
The template works well while cost of sales can be gathered by hand once a month. The trouble is that the figure depends on somebody pulling it out of inventory and typing it, and once there are hundreds of items, several storerooms, daily purchases, returns and sales, that task becomes the most fragile link in the operation. A system such as Kardex Tauro works out cost of sales from inventory itself: every issue of goods carries its cost, every sale takes units off stock and the gross profit report builds itself, with nobody transcribing anything.
Stated plainly: if you handle few items and review the month once, the Excel sheet is enough, and it is the right first step towards some order. Software is justified when you want the figure up to date without hunting for it, when inventory moves every day, or when you need the margin per product and not only for the whole business. At that point the template stops being the system and becomes the check that confirms the numbers still add up.
Gross profit is the first proof that a business works: if the sale does not even cover replacing what was sold, no amount of administration will fix it. Keep the count month by month, watch the margin rather than the total, and read the sheet before changing prices or signing a large order. Download the template free and start today with the figure that puts every other figure in order.
⬇ Download the template (Excel .xlsx)
