Table of expenses and incomes of the family budget in Excel

The analysis of incomes and expenses is carried out by each person who is not indifferent to the financial well-being of his family. If you do not know how much money you have, you do not have them. The budget management allows stay always in the black, to prevent unnecessary waste, realize goals and dreams.

There are a lot of ready-made programs for recording incomes and expenses. But to find the best program that would fulfill all the requests of a particular family is difficult. After all, the requests are different. We propose to create a family budget in Excel and adapt it for your needs.



The personal income and expenditure budget in Excel

There are several templates are built into the Microsoft Office suite for solving those or other tasks. Open Excel - click «FILE» - «Personal» - «Personal monthly budget» - «Create» - OK.

If for some reason you do not have this template or you could not find it, you can:

download the personal budget in Excel.

The simplest template, where you can enter planned, actual indicators, will open. To distribute incomes and expenses for articles, to count amounts automatically.

We can adapt the ready template of the family budget to our needs:

• add/delete articles;
• change conditional formatting parameters;
• row names;
• fill colors, etc.

We have the summary statement of incomes and expenses. Perhaps, for some families it will be convenient. But we propose to detail the tables.



The table of Family Budget in Excel

For example, there is a separate budget in the family. It is important who brings in the house how much. It is necessary to take into account the monthly earnings of a husband and a wife. And the money comes unevenly. In one day – is the salary, in a week later - is advance payment. A couple of days later - interests on the deposit. And plus to periodic part-time work.

For detailing to the incomes, on a separate sheet we create the Excel spreadsheet of incomes and expenses of the family. Give a name for it.

Denote the graphs: «Date», «Income item», «Amount». Below - «Total». In this cell, we put to the formula for calculating the sum.

We return to the summary list. Distinguish the cell above which we want to insert a row. Right mouse button – is «Insert». We sign: «The family budget for a month».

You need to make now, that the total amount from the detailed report is automatically transferred in the report. Select the empty cell where the numbers should appear. We enter «equal». We go over to the sheet with a detailed report. And we press the total amount of income for the month. Input:

We have attached the detailed report to the main consolidated statement. You can make changes to the detail sheet as many times as you need during a month now. The sums in the report will be recalculated automatically.

The Budget Expenditure Detailing

Money is spent almost every day: food, fuel, travel tickets are bought. To optimize the management of the family budget, it is recommended pay the costs immediately. You are spent – you are recorded.

For convenience we will create drill sheets for all expenditure. On each - is the Excel detailed table of expenditures of the family budget. «Date» - «Item of expenditure» - «Amount». It is enough to do it once. And then copying and pasting.

For giving the name of the detail sheet, we click the right mouse button on its designation. «Rename».

Do not forget to write down the sum formula in the «Total» line.

We are connecting the reports with expenses to the consolidated statement now. The principle of connection is the same.

Try to allocate to the cell with the total amount!

Data protection in the cell from changes

Costs and incomes are introduced in a hurry often. Because of this, you can make an error by entering an invalid value. In the result is an incorrect summary data. And remember at the end of the month, where exactly was the inaccuracy, sometimes is impossible.

How to protect the cell from changes in Excel:

1. From entering of incorrect values. DATA – Data Validation - Settings. Select the data type «Whole number» from the pop-up list. OK.

2. To forbid changing of values in the cell. «REVIEW» – «Protect Sheet». In the opened tab you need to enter the password, which will disable the protection. In the pop-up list, you can choose what is prohibited and what is allowed. In the example, we left the ability to a user to allocate to the cells.

3. Selectively to protect the cells. Family budget can be used by a few people at once. For each of them may organize the access in a certain area of the sheet. «REVIEW» - «Allow Users to Edit Ranges» - «New». Enter the name of the range, the addresses of the cells (which will be accessed) and the password for access.

For protecting to the whole book, on the «REVIEW» tab, click the appropriate button.

Working with formulas in the personal finance table

When in the table with incomes and expenses you put the formula («multiply» throughout the all column), there is a danger of dislocating the link. A reference to the cell in the formula should be attached.

In the formula bar allocate the link (relative), that you need to fix (make absolute):

Press F4. Before the name of the column and the name of the line the sign $ appears:

Repeated pressing F4 will lead to this kind reference: C$17 (mixed absolute reference). The line only is fixed. The column can be moved. Click again - $C17 (the column is fixed). If you enter $C$17 (absolute reference), the values relative to the row and column are fixed.

The family budget is disciplined. It helps to develop financial behavior, avoid unnecessary money spending. And Excel allows you to take into account the characteristics of a particular family.