Google Docs v3 Family Budget
Happy New Year to all who can not on holidays without brain activity and without Habr.
I continue my favorite series of posts ( one , two ) about the theory and practice of maintaining a family budget in Google Docs.
I honestly kept my budget, written in Google docs a year ago, and gave birth to a new version and a new philosophy (for myself) of running a budget. What I am in a hurry to share.
I know that there are many specialized systems for the family budget, like 4 Envelopes or Easy Finance, but I like to do everything myself, because it allows you to do what you want and use your brainchild more pleasantly.
')
1. You can fill in from anywhere (laptops, phones, iPads) - using Google forms
2. You can develop the logic and visualization yourself.
3. Reliability and almost 100% fault tolerance provided by Evil Corporation :)
Why are we starting to keep a budget? Everyone has their own reasons: to understand why the money runs out 10 days before the salary; understand why credit card debts are only growing; understand what costs are better to cut or how to save money for a new car.
Many financial systems offer us to have a whole lot of budget items and write down our expenses in them without thinking about why we need all this. At the very beginning, I went the same way and faced the fact that it is almost impossible to analyze expenses, because some of them were obviously not constant (buying a laptop or a suit - obviously expenses “by necessity / desire”) and it is impossible to plan and analyze them.
Spherical budget in a vacuum - these are two articles: “arrival” and “expense”. Every day you enter expenses and incomes and see what happens. Then you have thoughts, and what do we want to analyze? Someone wants to see, and how much money is spent on cars (gasoline, insurance, taxes, repairs, washing), someone is interested in understanding how much he spends on entertainment (going to restaurants, movies, bars).
I advise you to divide the cost items by no more than 10-15 items based on what you want to analyze this year / quarter.
My expense items look like this:
And the most interesting is how to do it in the Gouglodox box with detailed instructions and formulas and examples - under the cut.
Create a spreadsheet in Google Docs.
And we make on it a FORM to fill -
As a result, the data from this form will fall on the sheet, which I called "F-0" - the type of raw data.
The data here is simple:
- date (affixed automatically, which is very nice, although you can change the pens)
- expense (amount)
- income (amount)
- comment
here we put the tag with the key symbol colon. Tags are set on the sheet PLAN. Tags allow us to break the cost of items. In theory, you can make a choice of tag from the list, but I decided that I would write them with my hands - 3-4 letters are not scary. After the colon, you can write any comment, so you do not forget or analyze, you should do for meaningful and large purchases.
- a source
For me personally, for analysis, it is important to understand the state of the cache and each of my credit cards, so I choose where I pay each amount from. With this, I equate debit card and cache with cache.
As a result, my form from iPhone looks like this:
Bought groceries - opened iphone - made a record. It's simple.
Above is a reminder of what tags I have, a link to the last 10 entries, a link to an analysis sheet — all of this is in the text below.
Further, these data are processed on the tab "F-1", but about it a little later. First, let's think and plan our expenses and revenues.
Here is another one of my know-how (well, at least I haven’t seen such an approach yet). How to plan, say, the cost of a car, where we want to include not only constant monthly expenses for gasoline and car wash, but also repair (MOT), insurance and taxes + inspection? Constant expenses are simple - 3,400 per month, but in March we have insurance for 32,000, in August and November there will be maintenance at 7K and 11K. It is clear that there are unforeseen expenses for the same car - but they are already unforeseen :), that they cannot be planned.
Solution: I made a table with coefficients for each month. Like this:
Those. I tried to single out the constant component so that in the maximum number of months there was a coefficient 1, and there, where there would most likely be “single” expenses in this article - I put the coefficients more (and somewhere else, less, for example, in November the vacation time is also by car I will not go half a month).
Of course, you can select insurance for a car in a hotel article, as well as TO-40,000 and TO-50,000 and another tax, but as I wrote above - I do not need to analyze the costs of maintenance, but I want to see the cost of cars in general. And you? Google docs will allow you to customize it yourself :)
Go ahead and before proceeding to the sly logic in the "F-1", let's see what we want in the end get.
The main page (sheet) for analyzing the current “financial well-being” is “A month”, which means Analysis of the month. That's so compact on one page, I see my current position and I can see the history by months.
1. From above, we select the year and month for analysis. (PS Year is only one and the choice does not work)
Recently, a list of
2. Below we have automatically put down the planned value of the flow "PLANNED" and the actual situation "FACT".
Everything is collected from the form "F-1" and when you add a record - in a second it is already updated.
Then comes the percentage of the plan - if the value goes to a critical 93%, tint in red.
(You can make a whole gradation - adding 3-4 rules: green, yellow, red, brown - like traffic jams on Yandex)
3. The lowest CORR cell is an adjustment.
Often needed for unplanned situations. Suppose you paid a card for a friend's TV, and he gave you cash. It seems the money from the card was spent, and at the same time the cash came. Or at the end of the month, they realized that you actually have 2,000 rubles more in your wallet than in the system — you need an adjustment.
By the way, such an article as an adjustment I rarely met in "specialized systems". I have it shows how much you have adjusted to plus, and how much to minus.
4. On the right (above) of 5 lines under the heading "Last 10 records" show the last 5 records.
Why five and not 10? Guess yourself :)
The algorithm is tricky here - it is sewn up on the “OTH” tab, where all the service info and calculations are dumped.
looking for the last entry (each is numbered in ascending order) - i.e. you need to find the maximum and compare it with a line in the table "F-1". Then make look 5 lines up from the last (bottom) entry.
Why do you need it? I share the “A month” sheet as a web page and a range of cells under the last 10 records - you can see quickly and cheaply in terms of traffic. Suppose you have forgotten, whether skates you bought yesterday or not entered into the budget - Last 5 records will help you.
5. Below on the right is my status by cards and cash.
It can be seen how much what bank I owe in rubles and interest. Every month I do a reconciliation and look at each map and availability — how much is in fact and how much is in the system — and I make an adjustment.
Well, that's all about the planning list - let's move on to the logic in “F-1”
It looks like this:
Let's sort by columns:
1. columns “B” through “F” stupidly duplicate records from the raw data sheet “F-0”. Although not entirely stupid. For some reason, Guglodox didn’t want to accept just a formula like "= A1! F-0" and shifted the entries in the F-1 sheet as soon as a new one was added to the F-0, so I had to fake the formula for the "stupid" data collection from the F- sheet 0 looks like this:
where in A2 stands ROW (), which returns the row number :) - so we bypassed the automatic shifting of the formula when adding a new record in the form.
2. In the “H” column I make my favorite feature in Excel - an auxiliary column,
which closes a hole in the Gugloboxes, that it does not have the SUMIFA formula, which allows one to sum up the values ​​given not one, but several criteria. As a result, in the “H” column I received unique values ​​for the aggregate “year”, “month”, “Tag”, which gives me the right to summarize it in the analysis sheet:
3. In the SUM column, simply summarize the income and expense in order to look at the same column in the analysis.
I wondered ... why would I need two different fields in the form of "income" & "expense" ... because each amount is tagged and it is clear that income or expense! Ok, we'll fix it later :)
4. In the column “REC No for Last 10” - the recording number is added according to a ridiculous formula:
= IF (C3 + D3> 0; J2 + 1; J2) She catches “empty” entries for me.
5. In the column “Test wrong category” - there is a real test for a fool - by entering the Tag just to be mistaken - it is important to wink with red if you entered the wrong tag.
6. Next comes the allocation of the amount to a particular source (maps, cache, envelopes) - the amount is simply put with a plus or minus sign and summarized on the Analysis sheet. By the way, the initial values ​​of the money at the start of the budget are also laid in separate cells in the plan sheet.
Here on the analysis sheet, the system first looks at the initial value on CITIBANK and then summarizes all the values ​​from the CITI column.
It seemed so long and difficult to think about all this and create a solution architecture - and it came out to describe in an hour. For a snack:
1. The shared file web page (unfortunately without formulas) spreadsheets.google.com/pub?key=0Aht4cFRJGkY7dEhHb3RqdzhUMTZCMXlPbzJ1T3kxZHc&output=html
2. copy of file in Excel - formulas are visible there - narod.ru/disk/2677466001/HABR%20Budget%202011.xls.html
By the way, for some reason, the IFERROR formula (IFERBY) - is not converted into Russian Excel - I had to interrupt with my hands :)
Probably forgot something or didn’t tell, but for this we have comments and UPD . in posts - therefore I will be glad to discuss and accept all your opinions on the account of personal finances.
I continue my favorite series of posts ( one , two ) about the theory and practice of maintaining a family budget in Google Docs.
I honestly kept my budget, written in Google docs a year ago, and gave birth to a new version and a new philosophy (for myself) of running a budget. What I am in a hurry to share.
Introduction
I know that there are many specialized systems for the family budget, like 4 Envelopes or Easy Finance, but I like to do everything myself, because it allows you to do what you want and use your brainchild more pleasantly.
')
The main advantages of maintaining a budget for Google Docs:
1. You can fill in from anywhere (laptops, phones, iPads) - using Google forms
2. You can develop the logic and visualization yourself.
3. Reliability and almost 100% fault tolerance provided by Evil Corporation :)
A bit of philosophy.
Why are we starting to keep a budget? Everyone has their own reasons: to understand why the money runs out 10 days before the salary; understand why credit card debts are only growing; understand what costs are better to cut or how to save money for a new car.
Many financial systems offer us to have a whole lot of budget items and write down our expenses in them without thinking about why we need all this. At the very beginning, I went the same way and faced the fact that it is almost impossible to analyze expenses, because some of them were obviously not constant (buying a laptop or a suit - obviously expenses “by necessity / desire”) and it is impossible to plan and analyze them.
Spherical budget in a vacuum - these are two articles: “arrival” and “expense”. Every day you enter expenses and incomes and see what happens. Then you have thoughts, and what do we want to analyze? Someone wants to see, and how much money is spent on cars (gasoline, insurance, taxes, repairs, washing), someone is interested in understanding how much he spends on entertainment (going to restaurants, movies, bars).
I advise you to divide the cost items by no more than 10-15 items based on what you want to analyze this year / quarter.
My expense items look like this:
And the most interesting is how to do it in the Gouglodox box with detailed instructions and formulas and examples - under the cut.
Let's start from the beginning.
Create a spreadsheet in Google Docs.
And we make on it a FORM to fill -
As a result, the data from this form will fall on the sheet, which I called "F-0" - the type of raw data.
The data here is simple:
- date (affixed automatically, which is very nice, although you can change the pens)
- expense (amount)
- income (amount)
- comment
here we put the tag with the key symbol colon. Tags are set on the sheet PLAN. Tags allow us to break the cost of items. In theory, you can make a choice of tag from the list, but I decided that I would write them with my hands - 3-4 letters are not scary. After the colon, you can write any comment, so you do not forget or analyze, you should do for meaningful and large purchases.
- a source
For me personally, for analysis, it is important to understand the state of the cache and each of my credit cards, so I choose where I pay each amount from. With this, I equate debit card and cache with cache.
As a result, my form from iPhone looks like this:
Bought groceries - opened iphone - made a record. It's simple.
Above is a reminder of what tags I have, a link to the last 10 entries, a link to an analysis sheet — all of this is in the text below.
Further, these data are processed on the tab "F-1", but about it a little later. First, let's think and plan our expenses and revenues.
Here is another one of my know-how (well, at least I haven’t seen such an approach yet). How to plan, say, the cost of a car, where we want to include not only constant monthly expenses for gasoline and car wash, but also repair (MOT), insurance and taxes + inspection? Constant expenses are simple - 3,400 per month, but in March we have insurance for 32,000, in August and November there will be maintenance at 7K and 11K. It is clear that there are unforeseen expenses for the same car - but they are already unforeseen :), that they cannot be planned.
Solution: I made a table with coefficients for each month. Like this:
Those. I tried to single out the constant component so that in the maximum number of months there was a coefficient 1, and there, where there would most likely be “single” expenses in this article - I put the coefficients more (and somewhere else, less, for example, in November the vacation time is also by car I will not go half a month).
Of course, you can select insurance for a car in a hotel article, as well as TO-40,000 and TO-50,000 and another tax, but as I wrote above - I do not need to analyze the costs of maintenance, but I want to see the cost of cars in general. And you? Google docs will allow you to customize it yourself :)
Go ahead and before proceeding to the sly logic in the "F-1", let's see what we want in the end get.
The main page (sheet) for analyzing the current “financial well-being” is “A month”, which means Analysis of the month. That's so compact on one page, I see my current position and I can see the history by months.
1. From above, we select the year and month for analysis. (PS Year is only one and the choice does not work)
Recently, a list of
2. Below we have automatically put down the planned value of the flow "PLANNED" and the actual situation "FACT".
Everything is collected from the form "F-1" and when you add a record - in a second it is already updated.
Then comes the percentage of the plan - if the value goes to a critical 93%, tint in red.
(You can make a whole gradation - adding 3-4 rules: green, yellow, red, brown - like traffic jams on Yandex)
3. The lowest CORR cell is an adjustment.
Often needed for unplanned situations. Suppose you paid a card for a friend's TV, and he gave you cash. It seems the money from the card was spent, and at the same time the cash came. Or at the end of the month, they realized that you actually have 2,000 rubles more in your wallet than in the system — you need an adjustment.
By the way, such an article as an adjustment I rarely met in "specialized systems". I have it shows how much you have adjusted to plus, and how much to minus.
4. On the right (above) of 5 lines under the heading "Last 10 records" show the last 5 records.
Why five and not 10? Guess yourself :)
The algorithm is tricky here - it is sewn up on the “OTH” tab, where all the service info and calculations are dumped.
looking for the last entry (each is numbered in ascending order) - i.e. you need to find the maximum and compare it with a line in the table "F-1". Then make look 5 lines up from the last (bottom) entry.
Why do you need it? I share the “A month” sheet as a web page and a range of cells under the last 10 records - you can see quickly and cheaply in terms of traffic. Suppose you have forgotten, whether skates you bought yesterday or not entered into the budget - Last 5 records will help you.
5. Below on the right is my status by cards and cash.
It can be seen how much what bank I owe in rubles and interest. Every month I do a reconciliation and look at each map and availability — how much is in fact and how much is in the system — and I make an adjustment.
Well, that's all about the planning list - let's move on to the logic in “F-1”
It looks like this:
Let's sort by columns:
1. columns “B” through “F” stupidly duplicate records from the raw data sheet “F-0”. Although not entirely stupid. For some reason, Guglodox didn’t want to accept just a formula like "= A1! F-0" and shifted the entries in the F-1 sheet as soon as a new one was added to the F-0, so I had to fake the formula for the "stupid" data collection from the F- sheet 0 looks like this:
where in A2 stands ROW (), which returns the row number :) - so we bypassed the automatic shifting of the formula when adding a new record in the form.
2. In the “H” column I make my favorite feature in Excel - an auxiliary column,
which closes a hole in the Gugloboxes, that it does not have the SUMIFA formula, which allows one to sum up the values ​​given not one, but several criteria. As a result, in the “H” column I received unique values ​​for the aggregate “year”, “month”, “Tag”, which gives me the right to summarize it in the analysis sheet:
3. In the SUM column, simply summarize the income and expense in order to look at the same column in the analysis.
I wondered ... why would I need two different fields in the form of "income" & "expense" ... because each amount is tagged and it is clear that income or expense! Ok, we'll fix it later :)
4. In the column “REC No for Last 10” - the recording number is added according to a ridiculous formula:
= IF (C3 + D3> 0; J2 + 1; J2) She catches “empty” entries for me.
5. In the column “Test wrong category” - there is a real test for a fool - by entering the Tag just to be mistaken - it is important to wink with red if you entered the wrong tag.
6. Next comes the allocation of the amount to a particular source (maps, cache, envelopes) - the amount is simply put with a plus or minus sign and summarized on the Analysis sheet. By the way, the initial values ​​of the money at the start of the budget are also laid in separate cells in the plan sheet.
Here on the analysis sheet, the system first looks at the initial value on CITIBANK and then summarizes all the values ​​from the CITI column.
It seemed so long and difficult to think about all this and create a solution architecture - and it came out to describe in an hour. For a snack:
1. The shared file web page (unfortunately without formulas) spreadsheets.google.com/pub?key=0Aht4cFRJGkY7dEhHb3RqdzhUMTZCMXlPbzJ1T3kxZHc&output=html
2. copy of file in Excel - formulas are visible there - narod.ru/disk/2677466001/HABR%20Budget%202011.xls.html
By the way, for some reason, the IFERROR formula (IFERBY) - is not converted into Russian Excel - I had to interrupt with my hands :)
Probably forgot something or didn’t tell, but for this we have comments and UPD . in posts - therefore I will be glad to discuss and accept all your opinions on the account of personal finances.
Source: https://habr.com/ru/post/111111/
All Articles