Monthly income statement template in Excel

Monthly income statement template in Excel

The month closes, the sales moved, the bank account shows a balance that does not match what you expected and nobody can say with precision whether the business made money or lost it. In many small businesses that question gets answered by looking at the bank balance or by remembering how much was sold, and neither of those two things is profit. The income statement exists to answer it with ordered numbers: how much came in from sales, what the goods that were sold cost, what was spent to operate during the month and what was left at the end.

The most expensive confusion is believing that profit and cash are the same thing. A month can close with profit and still have nothing to pay payroll on the fifteenth, because the money sits in receivables, in inventory or in a sale that will be collected later. Profit is a result of the business; cash is a fact of the bank account. Profit does not pay payroll: cash does. That is why both statements are worth having and reading together, without replacing one with the other.

This file is an Excel workbook with the sheet Income statement, where each row is a line item and each column is a month of the year, plus an Instructions sheet that explains what goes in each line. The formulas for totals, gross profit, operating profit and margin come already set up, and so does the year summary with the average margin. There are no macros and no hidden data, and it opens in any recent version of Excel or in LibreOffice.

What an income statement is and who this template is for

The income statement is the table that orders the income and the expenses of a period to show, at the end, what was left. It reads from top to bottom: first what was sold, then what the goods that were sold cost, then what was spent to operate and finally the profit. That sequence matters, because each line answers a different question and mixing them up is what ends up making many spreadsheets useless for decisions. The margin appears as the relationship between operating profit and income, and it is the number that lets you compare a good month with a weak one without being impressed by sales volume.

The difference with cash flow is worth repeating, because it is the source of almost every scare. Cash flow follows the money that came into and left the accounts, whatever month the sale belongs to. The income statement recognises the income and the costs of the month, even when payment happens later or earlier. A business that sells a lot on credit can show profit in this table and a tight bank account; another that collected old receivables can show a weak month in the statement and a comfortable bank balance. Both tables are true at the same time and they tell different stories.

This template is meant for small businesses that need to see their result without setting up full accounting software: shops, workshops, restaurants, distributors, service providers and, in general, any operation that sells products or services and wants to know whether the month left anything. It also helps anyone who already has an accountant and wants to understand, month by month, the numbers they receive at year end. It is filled in once a month, in about twenty minutes, and over time it becomes the best X-ray of the business: the month-to-month comparison is where price changes, expenses that grew unnoticed and the months that truly carry the year all show up.

What the template includes

The file is ready to work with from the first time you open it: one sheet for the numbers, one sheet with the instructions and the formulas already set in the rows that are better left untouched. This is what you find inside:

The columns of the sheet

The sheet has a single horizontal structure and it is always the same: the concepts on the left and the months to the right. That layout lets you read the whole year without changing screens and compare the same concept at twelve different moments.

How profit is calculated: a worked example

The logic of the table is a chain of subtractions and one final division. First the income of the month is added up, then the cost of what was sold is subtracted and gross profit appears; after that the operating expenses are subtracted and operating profit appears; at the end that profit is divided by income to get the margin. Any given month of the table would look like this:

With those numbers, gross profit stands at 2,800,000, the expenses of the month add up to 2,400,000 and operating profit ends at 400,000. The margin comes from dividing 400,000 by 6,200,000, that is, a margin near seven points over sales. That is the figure worth writing down every month: if the margin drops two months in a row, the problem is not selling little but some cost or expense line that grew faster than sales.

Now the part that prevents misunderstandings: this table does not say how much money is in the bank. If the 6,200,000 was collected in full during the month, the bank account took in that money and profit matches the cash movement, apart from inventory purchases and pending payments. If half of it was invoiced on credit, profit stays the same and the account receives less. That is why the healthy habit is to close the income statement and then check the cash flow for the same month: one explains the result, the other explains what the business lived on.

How to use the template step by step

The first time takes a few minutes; after that, filling in a month is a matter of minutes. The recommended order is this:

1. Write the business name, the year and the currency in the header, so the statement is identified and can be filed without confusion.
2. Record the income of the month: sales on their line and, if you have them, other income from services, rents or interest.
3. Write the cost of the goods that were sold during the month, not the cost of the goods that were bought. That difference is what makes gross profit meaningful.
4. Complete the operating expenses of the month under their seven items: payroll, rent, utilities, transport, marketing, professional fees and other expenses.
5. Write nothing in the total, gross profit, operating profit or margin rows: those rows are formulas and they calculate on their own.
6. Compare the margin of the month with previous months and with the average margin of the year; that is where you see whether the business improved or only sold more.

Tips and common mistakes

Almost every problem with this form comes from mixing lines up or from leaving it half done. The mistakes that repeat the most are these:

• Mixing cost of sales with monthly expenses. Cost of sales depends on what was sold; monthly expenses do not. Putting rent inside cost of sales inflates gross profit and hides the real expense.
• Recording the purchase of goods in the month it was paid. If you bought inventory in December and sold it in January, the cost belongs to January, next to the sale that generated it.
• Leaving months blank. A table with gaps cannot be compared, and the comparison between months is exactly where the value of the form lies.
• Forgetting other income. If the business receives money from services, rents or interest, that money is also part of the result and should be recorded separately from sales.
• Watching only profit and not the margin. A month with high sales and a thin margin can leave less than a calmer month with a better margin; the margin is what tells you whether each sale is worth the effort.
• Using it as a cash flow. This table does not replace cash control. Profit does not pay payroll; cash does, and that is why the two forms complement each other.

When to move to software

The template handles the result of a small business well while the operations can be counted on your fingers. When volume grows, several locations appear, invoicing runs through several channels and inventory moves every day, Excel starts asking for double work: every sale has to be typed into the monthly table and then entered again in the sales record. That is where a system changes the task. Kardex Tauro is an inventory and sales software built for small businesses that records sales and costs at the moment they happen, so the income statement for the month is assembled from the information already saved in the operation, with no re-typing.

That does not mean the form loses value. While the business is at the stage where the owner knows every movement, Excel is the fastest and cheapest tool: it does not depend on the internet, it can be printed and anyone understands how to fill it in. The sign that it is time to move on is simple: when closing the income statement takes more than an afternoon, when two people keep different versions of the same file or when the inventory in the system and the inventory in the table no longer agree, manual control has become the problem.

Download the template, fill it in with the movements of the month that just closed and read the margin calmly. Separating the cost of what was sold from the expenses of the month, and reading the margin next to the cash flow, is the difference between knowing that the business works and knowing whether the business earns.