How to make a budget spreadsheet
Make a budget and update it weekly. For the first few months, do that as a couple. It will help each of you understand why it is reasonable to cut back on certain expenses. When you see a total that is unexpectedly high, you may want to simply reduce spending in that category.
- Eliminate those 3 main causes of marital stress.
- You each try to treat each other with consideration & respect at all times.
- Then you can expect to have one amazing marriage.
Identify budget spreadsheet expenses
The first step in making a budget spreadsheet is identifying your expenses. Use 2 months of bank statements, credit card statements, cheque book and/or whatever to discover all of the categories of expenses that you may have.
Add them to a column in alphabetical order in your brand new budget spreadsheet.
Adding expense to each category
Take each of those expenses in the top section and add them in alphabetical order further down the page. You have to leave space for the number of expenses in that category that you think you may have.
E.g., you will likely need way more blank lines for groceries than you would for gasoline.
The animated GIF here illustrates how to add a column of numbers using the Apple Numbers spreadsheet. Here are the steps:
- Add an equal sign (=) in the cell which is to have the total.
- Type the word “sum” in that cell.
- Add a left-hand parenthesis.
- Click on the top number.
- Add a colon (:).
- Click on the bottom number.
- Add a right-hand parenthesis.
- Click on the green check mark.
Note that for amounts that come only once a month or 2 you would NOT have any totals in the lower section. For things such a car insurance, electric services and phone bills you would type the date that the bill was paid beside the description. Then you would add the amount paid into the totals column.
To add the totals at the bottom section of your spreadsheet to the top, use the methodology below.
- Click on the cell that is to have the total.
- Add an equal sign (=).
- Click the total that is to go into the top cell.
- Click on the green check circle.
- The total is reproduced in the top cell.
Adding opening cash to a new month
Let’s say that the very first month of your budget is April. A section below shows you how to copy and paste your April budget to help create your May budget.
Use the system in the above animated GIF to reproduce April’s closing cash in May’s opening cash.
What every month has in common
Going from top to bottom, these are what you would see in every month’s budget:
- Name of month
- Opening cash
- List of all expenses with a different expense on each line.
- All of those expenses totalled in a line called Total Expenses.
- List of all income categories.
- All of that income totalled in a line called Total Income.
- A line called Closing Cash. It is used so often for comparing the budget to a bank statement that the total should be bold, enlarged & coloured.
- Every expense item listed in the appropriate section with a date and dollar amount. Every expense category has a total at the bottom.
Paid in cash
If you do have an expense section for items that are paid in cash, enter it as a negative amount. Then enter the same number as a positive amount in the expense category to which it applies. That way these amounts will cancel each other out and they will not cause a conflict with the bank account total.
Calculating the budget spreadsheet closing cash
To calculate closing cash:
- Click on the space in which you wish to have the total show up.
- Click Opening Cash.
- Click on the minus sign (-).
- Click on Total Expenses.
- Click on a plus sign (+).
- Click on Total Income.
- Click on the green circle with the check mark.
- Format the Closing Cash to bold, larger font & a bright colour.
(Cmd C and other formulas & keyboard shortcuts on this page are for the Apple Numbers spreadsheet. If you use a Windows computer, go to this page to see the Excel equivalents.)
You then go to the top cell in the right-hand blank column and press Cmd V to paste the entire monthly budget to create the next month’s budget.
In the new month you change the date of the month. Then you delete the date and amount from the one-time lines from the top part of the budget. (By “one-time lines” I mean expenses that happen only once a month. You do NOT want to delete the amounts in the other lines because those cells contain the totals from sections below such as Gasoline or Groceries.)
You change the date to the current month. To get the Opening Cash, add an equal sign (=) to the cell that will have the amount. Then click on the Closing Cash amount of the previous month. That amount will now be the new Opening Cash.
In each expense group you will:
- Select all data except for the total.
- Press the Delete key.
Budget vs. actual
After a month or 2 you can reproduce the list of variable expenses near the bottom of the spreadsheet like the sample shown here. You will link each cell in the Actual column to the corresponding amount near the top of the spreadsheet. Using your historical data, you will create a Budget amount for each item.
At month end you select each amount that is greater than the Budget amount. Change it to bold red. Then you will calmly discuss each red number with your spouse and decide if that should have been reduced.
After we learned the above information about budget spreadsheets, we took our credit cards and cut them in two. We did not want to be tempted to run up credit card debt. We use debit cards instead.
Some companies such as car rental agencies or BC Ferries will not accept debit cards. In such cases we are dependent upon family or friends with credit cards to help us out. Alternatively, you can have only one fee-free credit card and use it for only one category of expenses such as gasoline. You make sure you pay it off every month.
If you insist on using credit cards, one of your expense items should be Non-mortgage Interest. Any time there is even one cent above zero at the end of the month, it should be made red bold.
It is too easy to get into a trap of borrowing using your credit card. Because of the curse of compound interest, the cost of borrowing can get way out of hand.
Watch the video below to see how credit card interest can get out of hand. In the example, a $10,000 debt increased to $20,000. This was in spite of the fact that the credit card holder was paying back $100 more than she was charging each month.