How I make a personal budget with openofffice calc
Posted: Tue Dec 14, 2010 1:30 pmSomeone asked at the meeting about how to make a budget with openoffice.org calc. Well, I do exactly that, and here is how I do it.
First download the ods file I created when making this post.
I do it on three sheets, expenses, income, and balance. I break it down week by week. In a nutshell, each expense or income item gets entered with a date. A spreadsheet function calculates the week that the item occours in. The balance sheet sums up all expenses and incomes for a given week and subtracts that from the previous weeks balance.
Expenses
Columns
enter this in the expenses sheet and expand it down a bunch of rows. (click the A1 cell and drag down the box on the bottom right) You should see alot of -6s which is fine.
Fill in some expenses
Just ignore the errors in column A. If you want you can even hide the column.
Notice that I get fancy on the "every other tuesday" section, in column B we just add 14 days to the above row to get a date 2 weeks ahead. In column C we just sum the values in D and E.
You can easily enter any kind of payment schedule this way
Income
The Income sheet is similar to the expenses sheet.
The only difference is that we move the incomes into the week AFTER we get the income. This is to reflect the fact that we are usually paid at the end of the week and the bank may take a few days to post our deposit so it most likely will not be available until next week.
Our column A function is
Balance
Now we pull it all together into a balance sheet.
Heres the Income SUMIF function that lives in column B
It says look for all rows in income column A that match A2 in the current sheet and then sum up the values in column C. We have to put dollar signs on the ranges so we when we fill the function down into more rows the references stay the same.
The expenses SUMIF is the same we just look at the expenses sheet instead of income
So our final balance sheet looks like this
This is how I make sure I won't run out of money in any week. I bet there are better ways, (like maybe gnucash) but this is pretty simple and flexible.
I can explain this a little bit better in person at the next meeting if anyone wants me to
First download the ods file I created when making this post.
budget_example.ods- (10.15 KiB) Downloaded 1800 times
I do it on three sheets, expenses, income, and balance. I break it down week by week. In a nutshell, each expense or income item gets entered with a date. A spreadsheet function calculates the week that the item occours in. The balance sheet sums up all expenses and incomes for a given week and subtracts that from the previous weeks balance.
Expenses
Columns
- A - First day of the week (computed automatically by spreadhseet)
- B - Date expense is due
- C - Expense amount
- Code: Select all
B1-(WEEKDAY(B1)-1)
enter this in the expenses sheet and expand it down a bunch of rows. (click the A1 cell and drag down the box on the bottom right) You should see alot of -6s which is fine.
Fill in some expenses
- exp1.png (36.94 KiB) Viewed 11176 times
Just ignore the errors in column A. If you want you can even hide the column.
Notice that I get fancy on the "every other tuesday" section, in column B we just add 14 days to the above row to get a date 2 weeks ahead. In column C we just sum the values in D and E.
You can easily enter any kind of payment schedule this way
Income
The Income sheet is similar to the expenses sheet.
The only difference is that we move the incomes into the week AFTER we get the income. This is to reflect the fact that we are usually paid at the end of the week and the bank may take a few days to post our deposit so it most likely will not be available until next week.
Our column A function is
- Code: Select all
=B1-(WEEKDAY(B1)-8)
- pay2.png (33.76 KiB) Viewed 11176 times
Balance
Now we pull it all together into a balance sheet.
- In Column A we fill in our week dates (in this example it is sunday), remember to just fill in the first date and add 7 to it in subsequent rows.
- Column B finds the rows in the income sheet for the given week and sums them up.
- Column C finds the rows in the expenses sheet for the given week and sums them up.
- Column D takes balance from the previous week, adds incomes and subtracts expense to get the current balance
Heres the Income SUMIF function that lives in column B
- Code: Select all
=SUMIF(Income.$A$1:$A$12;A2;Income.$C$1:$C$12)
It says look for all rows in income column A that match A2 in the current sheet and then sum up the values in column C. We have to put dollar signs on the ranges so we when we fill the function down into more rows the references stay the same.
The expenses SUMIF is the same we just look at the expenses sheet instead of income
So our final balance sheet looks like this
- balance.png (37.73 KiB) Viewed 11176 times
This is how I make sure I won't run out of money in any week. I bet there are better ways, (like maybe gnucash) but this is pretty simple and flexible.
I can explain this a little bit better in person at the next meeting if anyone wants me to