lunedì 30 dicembre 2013

How to make a household budget in Excel

In EXCEL you can build a family budget.

To obtain a family budget in EXCEL, proceed as follows:

1) Prepare a table with all the data that we want to manage our family budget, for example, we can decide what types of expenses we want to manage, then let's make a list (in green boxes), for example with Rent, Condominium, Water, Telephone etc.. , and I leave some empty for any new items to be added.
Also in the table input (yellow boxes), we define Type of expense, Description, Date, Month, Revenue and Expenses.

2) Now we define the column "B", the DATA VALIDATION that we have listed in the green boxes.
Basically here we can choose only one of the data in the selection drop down (see HERE how you are using DATA VALIDATION .

3) Now we can fill the table with all the data, then the first thing indicating the type of expenditure, choice drop-down list, then an additional description (if you want), then the date, the month (number 1.2.3 etc.), then the amount in Euros or the entry or exit.

4) Now our family budget is completed, and if we can apply the FILTERto see and filter costs.

5) But we can do something better, such as having a total that automatically updates as you write the data and makes us understand each month if you are on budget this month, or we're going in the red.
So we make a mirror, where we show all 12 months and we do withSOMMA.SE the totals of the receipts and expenditure of each month.
The formula will be = SOMMA.SE ($ E $ 6: $ G $ 985, K6, $ F $ 6: $ F $ 985), in sections of the column L (Revenue).
and it will be = SOMMA.SE ($ E $ 6: $ G $ 985, K6, $ G $ 6: $ G $ 985). boxes in the column M (outputs), then in the boxes in the column N, we will do the subtraction Revenue-Expenses ( L6-M6).

6) In this way, thanks to the formulas SOMMA.SE we have for each month the balance between revenue and expenses, and see right away if we went to red.

HERE I made ​​another blog you might be interested

Clearly it is possible to adjust the table, to our needs, and use the other particulars, but the logic is to get the data aggregated by month or filterable by type of expenditure, so that we can better understand and manage our expenses.