Balance sheet template in Excel: assets, liabilities and equity

Balance sheet template in Excel: assets, liabilities and equity

At some point in the month you have to answer a simple question: how much the business owns, how much it owes and how much is really the owners'. That answer, laid out on a sheet with the right columns, is the balance sheet. You do not need a large system to keep it at hand: a list of accounts, a group properly assigned to each one and two columns of balances are enough. The summary builds itself.

This balance sheet template in Excel is built for exactly that. It is a single worksheet: the list of accounts with their balances sits at the top, and below and apart, the summary of the balance sheet by large groups. The sheet does not ask for complicated formulas or a special order in the data; it asks for two things: that every account has its balance and that every account has its group. With those two things, the subtotals and the total appear without anyone typing them.

What the balance sheet is and what it is for

The balance sheet is the photograph of the business on a date. On one side sits the asset: everything the business owns and everything it expects a benefit from. There go the cash on hand, the bank balances, what customers owe, the inventory, the machinery, the furniture and the equipment. On the other side sit liabilities and equity: where all of that came from. What was bought with debt lives in liabilities; what was financed with the owners' resources and with the profits reinvested lives in equity.

It serves three specific purposes. The first is knowing whether the business can handle what it owes in the short term: what is in the bank and what is due to be collected is compared against what matures this year. The second is seeing how the business has grown: a balance sheet from today against one from last year shows whether what grew was the machinery or the debt. The third is answering whoever asks for it: the partner, the bank, the accountant or whoever reviews the accounts before a decision.

One single idea holds the whole format together: both sides tell the same story from different sides. Everything the business owns came from somewhere, from a creditor or from the owners. That is why assets must equal liabilities plus equity. When that happens, the balance sheet balances; when it does not, something is recorded wrong and the sheet itself shows it on its last line.

The seven columns of the sheet

The list of accounts fills the upper part and has seven columns. The first six are the ones you always see; the seventh is the one that keeps a strange figure from being left without an explanation.

Row 105 closes the list with the totals and the summary of the balance sheet starts on row 107. That space in between is not decorative: it separates the detail from the result and makes the summary read as what it is, a conclusion and not one more line of the list.

The group is chosen from the list and the subtotals come out of it

Every account carries a group, and that group is not typed by hand: it is chosen from the drop-down list the sheet brings. The list has five options and no more: current assets, non-current assets, current liabilities, non-current liabilities and equity. Choosing it from the list instead of typing it free is what holds everything else up; one extra letter, one extra space or a different capital letter and the subtotal for that account is lost without any warning appearing.

The subtotals come out of that group, one for each option. The sheet does not guess which group an account belongs to from its name or its code: it reads what the group column says and adds there. That is why the most important step when setting up the file is reviewing that column line by line before looking at the summary.

The split between current and non-current is the one that gets mistaken the most. The practical rule is simple: current is what is expected to turn into cash, to be collected or to be paid within the year; non-current is everything else. A loan paid in instalments over several years has a current part and a non-current part, and on the sheet that is solved with two lines.

The variation column compares against the previous period

The two balance columns tell a story that one alone cannot tell. The current balance says how much there is today; the previous balance says how much there was at the last close. The variation is the subtraction of the two and it calculates itself, so there is no need to type it or check it.

That column is what turns the balance sheet into a working tool and not a loose photograph. An inventory of 2,800,000 says nothing by itself; the same inventory with a variation of 400,000 upwards says that purchases grew faster than sales and that there is money sitting still there. Collecting an account that is getting old, stopping the pile-up of stock or understanding why a supplier went up are decisions that come from reading the variation and not the balance.

Contra accounts are written with a minus sign

Some accounts live inside a group but run against it. The clearest case is accumulated depreciation: it is a non-current asset account, but it does not add, it subtracts. It is written with a minus sign, for instance -1,200,000, and the group subtotal takes it with that sign.

Writing it as a positive is the mistake that throws the balance sheet out of balance without it being obvious at first glance, because total assets end up inflated by an amount that has in fact already been consumed. The same happens with other contra accounts, such as the allowance for doubtful accounts when it is handled separately or advances received inside liabilities. The rule is the same for all of them: if the account subtracts inside its group, it goes with a minus sign; if it adds, it goes as a positive. The sheet does not fix that sign on its own, so reviewing those cases is part of the work.

The full example: a balance sheet that balances

With the figures from the sample file the summary comes out like this. It is worth reading it in full before filling in your own, because it shows what a finished, balanced sheet looks like.

Below the summary sits the line that says whether the balance sheet balances: it compares total assets with total liabilities plus equity. Here both are 20,400,000 and the difference comes out as zero, so the balance sheet balances. That line is the format's own check and not an opinion: if it does not give zero, the difference is shown and the work is not finished.

As for the variation, cash was at 1,200,000 and goes up 300,000, so it closes at 1,500,000. That is the kind of reading the automatic column leaves in view: not one more figure, but the explanation of the change from one period to the next.

How to set it up, step by step

1. Copy the list of accounts from the chart of accounts and delete the ones that do not move during the period.
2. Leave one account per line and check that the code is the same as in the chart of accounts.
3. Choose the group for each account from the drop-down list; do not type it by hand.
4. Load the previous balance from last period's close and the current balance from today's.
5. Review the contra accounts and leave them with a minus sign.
6. Look at the summary: total assets must equal liabilities plus equity. If it does not match, look at the group column before looking at the figures.

Mistakes that repeat

• Typing the group by hand instead of choosing it from the list.
• Leaving accumulated depreciation as a positive.
• Loading the previous balance equal to the current one, so the variation comes out as zero and says nothing.
• Classifying as non-current something that is collected or paid within the year.
• Adding by hand on top of the summary: if the sheet balances, the subtotals are already taken care of.

Closing: internal control and where the accounts come from

The list of accounts on this sheet comes from the business's chart of accounts. If you do not have one set up yet, the blog entry on the chart of accounts shows how to build it and how to number it, and the download link for this template is below. An orderly chart of accounts makes the balance sheet take minutes to set up; with repeated accounts or different names for the same fact, the group column becomes a headache.

It is worth repeating because it is what gives the file its credibility: this balance sheet is a tool for internal control and daily work. It does not replace an official document or a filing, and it does not replace the signature of whoever reviews the accounts. Its value lies in the control: showing the state of the business clearly and leaving any difference in view instead of hiding it.

A balance sheet that balances is not a perfect balance sheet; it is a finished one. That is the difference the format helps you hold on to, day after day.