Statement of changes in equity template in Excel

Statement of changes in equity template in Excel

Equity never stands still. Over the year the owners put money in, profits are distributed, reserves are set aside, and the period leaves a result that has not been shared out yet. At closing time those movements have to be explained in a single statement instead of being scattered between the balance sheet and a loose appendix. That statement is the statement of changes in equity, and this template builds it as a matrix: the equity lines down the rows and the movements across the columns.

The worksheet ships with seven columns and leaves the heavy work to formulas. You type the opening balance of each line, the contributions made during the period, the distributions, the result for the year and the other movements; the closing balance of each row is calculated for you. At the bottom, a check line reconciles the total of the statement with the equity shown on the balance sheet, so nobody hands over a statement that does not tie out.

What the statement of changes in equity is and what it is for

The statement of changes in equity is the financial statement that explains what happened to the owners' money during the period. The balance sheet shows equity as a single figure at closing, but it does not say why it changed: whether it rose because the owners contributed, because the business earned, or because someone distributed. That detail is exactly what this statement adds. It is the bridge between last year's equity and this year's.

It is useful for three concrete things. First, to be accountable: whoever put capital in wants to see whether their money grew through their own contribution or through the result of the business. Second, to separate what was earned from what was distributed: a business may have earned and still seen its equity fall if it distributed more than it earned. Third, for internal control: if the total of the statement does not match the equity on the balance sheet, a movement was left unrecorded.

The matrix: equity lines down the rows, movements across the columns

Each row is an equity line and each column is a type of movement. These are the seven columns of the file.

There are five rows. Capital gathers what the owners have contributed. Reserves are the portions of profit the business decides not to distribute. Retained earnings are the profits of earlier years that stay inside equity. The result for the year is this period's profit and is shown on its own line until it is distributed. And the other row collects whatever does not fit the four above it. Underneath sits the totals row, which adds up column by column.

A worked example: where each line comes from

Using the same business as the other statements in the series, this is the full picture for the period. Capital starts at ten million and receives two million in contributions, so it closes at twelve million. Reserves came in at five hundred thousand and receive three hundred thousand through other movements, up to eight hundred thousand. Retained earnings came in at one million two hundred thousand and distribute four hundred thousand, so they settle at eight hundred thousand. The result for the year is one million eight hundred thousand, and the other row stays at zero.

The totals row tells the story at a glance: equity started at eleven million seven hundred thousand, took in two million in contributions, paid out four hundred thousand, added one million eight hundred thousand of result and three hundred thousand of other movements, and closed at fifteen million four hundred thousand. With that, the statement does not merely say how much equity changed, it says where every unit of it came from.

Contributions add, distributions subtract

The rule of the matrix is simple: contributions and the result add up, distributions subtract. The closing balance of each line is its opening balance plus what came in minus what went out. Capital almost always rises, because contributions increase it and capital is not returned in normal operations. Retained earnings are the line that moves most often downward, because that is where the payouts to the owners come from.

It is worth separating two ideas that sound alike. Earning is when the period leaves a profit. Distributing is deciding to hand that profit, or the profits of earlier years, to the owners. A business can earn one million and distribute two million: equity falls even though the year was good, because the payout took more than the period produced. This template shows that difference without a speech: it keeps the result for the year in its own column and the distributions in theirs, so the reader sees the two things apart.

The check against the equity on the balance sheet

The statement and the balance sheet look at the same equity from two sides. The balance sheet shows it at closing as a single figure; the statement explains how it got there. That is why the total of the statement has to match the equity on the balance sheet. The sheet carries a check that subtracts the two values and flags the line when the difference is not zero. When they do not match, a movement is almost always missing from the records: a contribution that was never posted, a payout left in draft, or a reserve that was approved and never entered.

In the example, the equity on the balance sheet is fifteen million four hundred thousand and the total of the statement gives the same, so the check ties out. That last line is what turns the sheet into a control tool rather than decoration. A statement that agrees with the balance sheet lets you trust both figures; one that does not forces you to review the movements before the financial statements are issued.

Common mistakes when building it in Excel

Most of the mismatches in this statement come from three slips. The first is mixing the result for the year with retained earnings: the profit of the period goes on its own line and in its own column, and only moves into retained earnings once its destination is decided. The second is leaving contributions outside the matrix and adding them at the end by hand, which makes the opening and closing balances stop tying out. The third is entering distributions with a plus sign; in the matrix they go in as positive and the formula is what subtracts them.

• Write the opening balance of each line before touching the movement columns.
• Keep the result for the year on its own row and column, without repeating it in retained earnings.
• Type contributions and distributions as positive numbers: the closing-balance formula does the subtracting.
• Review the check against the balance sheet before issuing the statement.
• Explain in a note every movement in the other column.

How to read the sheet in five minutes

Reading this statement always follows the same order. Start with the opening balance column to see how much equity you began with. Then the contributions column: if it is empty, the owners put no new money in during the period and any increase in equity came from the business itself. Then the result for the year column, which is what the period produced, and finally the distributions column, which is what went out to the owners.

Those four looks already tell you almost everything. Equity that rises with a loaded contributions column grew by the owners' decision; equity that rises through the result grew through the performance of the business; equity that falls even with a positive result is paying out more than it earns. The totals row sums up all three stories at once, and the check confirms that none of them was told halfway. It is a short sheet that answers the question most often asked at year end: where the business's equity came from and where it went.

Closing: two ways of seeing the same thing

This statement and the balance sheet tell the same story from two sides. The balance sheet says how much equity there is; the statement says how it changed. Seen next to the income statement, the picture comes full circle: the result for the year shown in its column comes from the income statement, and the total comes from the equity on the balance sheet. The three pieces hold each other up.

This template is a working tool and an internal control aid. It does not replace an official document or a filing, and it is not submitted to any authority on its own: it is there to build the statement, make it tie out and support the conversation with the accountant. Kardex Tauro ships it so the period close leaves equity explained line by line, with no hidden movements and the check at zero.