How To Do Home Bookkeeping Via Excel Yourself

Table of contents:

How To Do Home Bookkeeping Via Excel Yourself
How To Do Home Bookkeeping Via Excel Yourself

Video: How To Do Home Bookkeeping Via Excel Yourself

Video: How To Do Home Bookkeeping Via Excel Yourself
Video: Create a Bookkeeping Spreadsheet using Microsoft Excel - Part 2 2024, March
Anonim

If you decide to do home bookkeeping, you can try different methods. One of them is the control of the family budget through MS Excel. This will allow not only to systematize the streams of expenses and income, but also significantly reduce the time for calculations. Let's consider family budget management using the example of a simple table with a weekly plan of income / expenses per person with a detailed explanation of the introduction of formulas for calculating data.

How to do home bookkeeping via Excel yourself
How to do home bookkeeping via Excel yourself

It is necessary

  • - PC, laptop, netbook, smartphone or tablet with the ability to edit MS Excel documents
  • - an installed MS Excel program or another program that allows you to create and edit files of this format

Instructions

Step 1

Create an Excel document and name the first sheet so that you can differentiate between different periods later. For example, 1.12.2014-7.12-2014. It is better to name the sheets by dates so as not to get confused. At the same time, in order to conduct accounting for a long period, you do not need to make entries only on one sheet. Divide the period into several parts, for example, a week or half a month. This will improve the perception of information from sight.

Image
Image

Step 2

Think about how you would like to design your future balance sheet. You can create a separate template each time by copying expense / income items, or create a table for a period at once. The table may look like a standard balance sheet, or all items will be arranged in a column. For the simplest table, a template for a period is suitable.

Image
Image

Step 3

Enter the items of expense and income in the appropriate column of the table. You can arrange in the order you want, but for the convenience of calculations, it is better to group income and expenses. You can write down the expense items in more detail so that later you can understand for which expense item is the highest, as well as whether these expenses are really important to you. Usually money flows away little by little for minor household items, snacks in a cafe, etc. Also add "Other" items for those expenses and incomes that you cannot remember or they are not included in a separate group.

Image
Image

Step 4

In the column of items of expense and income at the very end, add the columns "Total". In this line, you can track how much you spent or earned in the day. Also, you can add the "Balance" column, it will reflect the amount of money that you have left after deducting all expenses. If the balance is negative, you are in debt, and the money you have on hand is not yours.

Image
Image

Step 5

Insert below the table, or where convenient, a place for a comment. There you can write things that are important to you, determine what you spent your money on in the "Other" column, etc.

Image
Image

Step 6

Record all income and expenses for the day in the appropriate columns so that the data on the items of expense are recorded in the column "expenses" by the corresponding date. Do the same with the income data. To format numeric data, use the Home tab, Number section.

Image
Image

Step 7

Next, we draw up the Total and Balance items so that income and expenses are automatically summed up in them. To do this, select the Total / Consumption cell (1.dec) and put the "=" sign in it to enter the formula. A simple amount can be used here by typing "= SUM". Then highlight the data you want to total and press ENTER. The result of these actions should be the appearance in the cell of the amount of expenses for that day. Do the same with income.

Image
Image

Step 8

To insert a formula into the balance column, select the Balance / expense or Balance / income for 1.dec cell. Then put the "=" sign in it, select the Total / Income cell (the cell number will appear in the formula bar), put the “-” sign and select the Total / Income cell. Press ENTER. After that, your income will appear in the cell for 1 deck minus expenses. This is the kind of money you can use.

Image
Image

Step 9

In order not to perform the same operations for entering formulas into rows each time, select the cells Total / Expense and Total / Income, then stretch them to the end of the table. After that, the formulas will appear in the Total line. To check the correctness of copying, select any cell in the Total row and place the cursor in the formula bar - the summation area should be highlighted.

Image
Image

Step 10

Distribute the formula from the Balance cell in the same way. Select it and the cell next to it so that the formula is copied not into every cell, but one after another. Stretch the cell to the end of the table and check that the formula is copied correctly, as in the previous step.

Image
Image

Step 11

To understand how much money you have left at the end of the week, add up the Balance cells for the entire period. Data can be displayed on the right of the table. In the same way, you can sum up all items of income / expense and view these flows in detail. To sum up a row, click in the cell where you want to enter the data "= SUM" and highlight the row, press ENTER.

Recommended: