Guide
Income and expenses in Excel: keep track of your business money (free template)
Knowing each month how much came in, how much went out and how much is left is the basis for running a small business without nasty surprises. You don’t need accounting software: a cash book kept up to date is enough. Below you’ll find how to set it up and a free, ready-made Excel template.
Why keep an income and expenses record
- You always know how much money you really have, not just how much you took in: rent, suppliers and bills arrive in the slow months too.
- You see where the money goes. One expense often creeps up without you noticing.
- You compare months and see whether business is better or worse than last year, and when the quiet periods come.
- You reach your accountant with the numbers in order, instead of hunting for receipts at the last minute.
How to set it up: the columns you need
A simple cash book has six columns. Each movement goes on a new row, in the order it happens:
- Date of the movement.
- Description, short and recognisable: “Rent March”, “Supplier invoice no. 18”, “Sales of the day”.
- Category, chosen from a fixed list (see below).
- Income, for money coming in.
- Expense, for money going out. Fill in only one of the two on each row.
- Balance, calculated automatically: opening balance plus all income minus all expenses up to that row.
Before you start, write the opening balance: what you have at that moment in the bank and in cash. From then on the balance updates by itself and you can compare it with your bank statement.
A few categories, always the same
Categories show where money comes from and goes to, so they should be few and stable: eight to twelve are almost always enough. For example:
- Income: sales, services, other income.
- Expenses: rent, suppliers, utilities, staff, taxes and contributions, advertising, other expenses.
Tip: if an expense often ends up in “other expenses”, it probably deserves its own category. But don’t create a new one every month, or comparing months stops making sense.
How often to update it
The best rhythm is once a week, always on the same day: ten minutes with your receipts and bank statement are enough. Once a month, check that the balance in the cash book matches your bank account plus cash. If it doesn’t, usually a movement is missing or one was written twice.
Reading the monthly summary
The summary adds up income and expenses for each month. The Difference column tells you whether you gained or lost cash that month. Three things to look at:
- Months in the red: normal at certain times of year, but they need planning so you don’t run out of money.
- Fixed costs (rent, utilities, subscriptions): the ones you must cover even in the worst months.
- The trend compared with previous months, more than any single number.
The most common mistakes
- Mixing personal and business expenses. If you pay for something personal from the business account, record it separately or the numbers stop meaning anything.
- Forgetting cash. Cash takings and cash expenses belong in the cash book too.
- Counting transfers twice. Moving money from the till to the bank is neither income nor an expense.
- Confusing takings with profit. Part of what comes in will be needed for taxes: ask your accountant how much to set aside.
- Falling months behind. Rebuilding everything at the end is slow and leads to more mistakes.
What it is not
An income and expenses record is for keeping an eye on your money. It doesn’t replace official bookkeeping, invoicing or tax returns, which depend on your tax situation: always check those with your accountant.
When you need more
The free template is enough to get started. If you also want totals by category with two charts, to see which customers you earn most from and which suppliers you spend most with, and to keep several years in the same file, there is the full version:
Run a shop? The Shop Kit includes it together with stock, gift vouchers and social media templates. To manage your goods too, see the guide on managing stock in Excel.