Create a Robust Household Budget in Less Than an Hour

Tracey Louis, Staff Editor

In one of my recent articles, I wrote about how my grandparents taught me some “old school” techniques to help bolster my personal finances. One of the most important things I learned from them is how to budget and save for retirement. In this article, I’m going to cover some basic budgeting principles, but more importantly, I’ll show you how to create a simple budget in less than an hour using FREE tools available to you right now!

Google Sheets — Your Friendly Budgeting Friend

Can someone please create a “Google Appreciation Day” holiday? If you don’t know already, Google has created a monster of a company through its popular online search portal. But if you aren’t tech-savvy, or just don’t keep up with Google’s contributions the way I do, you probably don’t realize that they provide an entire suite of productivity tools that are free for everyday use. And although I’d love to, I’m not here to throw heaps of praise on Google (we can do that on Google Appreciation Day). Instead, I’d like to talk about one tool that I use almost daily - Google Sheets.

Google Sheets is very similar to Microsoft Excel, but it’s free and it can be accessed from anywhere you have an Internet connection (including your smartphone). If you don’t know what a spreadsheet is, it’s simply a tool that allows you to create, manipulate, and maintain data sets. In a lot of cases, those sets of data can even be automated!

So, what kind of data does a household budget require? There are 4 points you need to consider:

• Total Monthly Income
• Total Monthly Bills
• Total Monthly Expenses
• Surplus and Deficits

Depending on your financial situation, it could get a lot more complex, especially when investments and secondary incomes come into play. For now, let’s keep it simple, shall we?

If you don’t have a Google account already, you’ll need one before proceeding. Click here to register for a free account. Once registered, or if you already have a Google account, sign in and go to your Google Drive.

Once you’re in Google Drive, you’ll see the main work area. This is where you can create documents and folders, move items to the trash, share files, and much more. Since we’re only focusing on Google Sheets for now, we’ll go ahead and create a new spreadsheet.

Step-by-step instructions below:

1. Click on the red “New” button near the top left of the work area.
2. You should now see a dropdown list of items. Click on “Google Sheets”.
3. You’re now working on a new spreadsheet. Click on “Untitled spreadsheet” near the top left of the page and rename your spreadsheet by typing in “Monthly Household Budget” in the highlighted area.
4. Near the bottom left of the spreadsheet, you’ll see a small tab that says “Sheet1”. Double-click that tab with your mouse and rename it to the current month and year. For example: “May 2016”. This allows you to keep track of each of your monthly budgets all in individual sheets within your main spreadsheet.

You should now see a blank spreadsheet in front of you with the title of “Monthly Budget” and a sheet title of “May 2016”.

The way a spreadsheet works is by displaying and manipulating any data you enter so that it’s easy to digest. It’s a perfect tool for budgeting, because it allows you the freedom to create formulas using cells, rows, and columns, so you can calculate your monthly expenses, surpluses, and shortfalls.

Here are some definitions before we get started:

Cell — The single point where an individual column or row intersects.

Column — The vertical axis of a spreadsheet, defined by the letters across the top.

Row — The horizontal axis of a spreadsheet, defined by the numbers on the left.

These three items are the very basic building blocks of a spreadsheet. Now that you know some of the terminology, let’s dig right in!

First, we’re going to create a column for our monthly income. Because we’re keeping it simple, we’ll only use an example of a single-earner income:

1. At the very top left of the spreadsheet, click in the A:1 cell. The “A:1” designation tells you that the cell is in Column A, Row 1.
2. Double-click your mouse inside the cell and type “Monthly Income”. Press Enter.
3. To add styling to the Monthly Income cell, click on it once more, and then click the “B” icon in the toolbar near the top of the page.
4. Since we all like income, let’s make a background color of green. Click the little “paint bucket” icon in the same toolbar and select “light green 2”. For those of you who are colorblind (like me), you can simply hover over the individual colors in the toolbar to see the name of that particular color.
5. You can now add categories of income inside this column. Click on the A:3 cell and type “Work Income”.

For this example, let’s use $40,000 as our annual salary. Conceptually, we want to break that number down monthly, so we’ll divide by 12. So…$40,000/12, which gives us: $3333.33. If you live in a region where your income gets taxed, you should insert that into the equation. Let’s say $3333.33 x 0.18 (18% income tax). That gives us a total of around $600. In other words, $600 gets taken out of your monthly income for tax purposes, which leaves us with a total of $2733.33.

That’s a lot of mumbo jumbo! So how does this translate in our spreadsheet?

In order to work this equation into our spreadsheet, we need to create a formula. Now, there are countless functions you can use to create formulas in Google Sheets, but we’re gonna use just one for now: The SUM function. This function allows us to add, subtract, multiply and divide. And that’s all we really need for this particular spreadsheet.

Since we’re not doing an in-depth tutorial on equations and formulas, we’ll simply give you the formula you should type into the cell below:

In cell B:3, type (or copy and paste) the following formula and press Enter:

=SUM(40000/12)-(40000/12*0.18)

You should now see the number 2733.333333 in cell B:3.

Every SUM function starts with an “=” sign. The actual formula gets placed between parentheses. As you can see from this formula, we divide our annual salary by 12 in the first set of parentheses. In the second set of parentheses, we multiply the amount we’re taxed by our annual salary. Then, that total gets subtracted from the first sum, giving us our total.

At this point, we still don’t have a dollar amount. Instead of placing a currency symbol in front of every number, we can do it for all cells within a row or column.

1. Click on the letter “B” in the top of the column heading. This should highlight the entire column.
2. Click on the “$” symbol in the toolbar. You should now see a dollar sign automatically placed next to each number within that column, with proper currency formatting.

Next, let’s make another column dedicated to our bills.

1. In column D:1, type “Bills”.
2. Bold the word “Bills” like we did in the previous column.
3. Fill the background with the color “dark red 1”.
4. Because black text doesn’t show up on a dark red background, let’s make the text white. With the word “Bills” highlighted, click on the “A” icon in the toolbar and select “white”.
5. For some sample bills, type in “Mortgage” in cell D:3, “Electric” in cell D:4, “Phone” in cell D:5, and “School Loan” in cell D:6.
6. We’re gonna use some example amounts for each bill. So, in E:3, type “900”, in E:4, type “100”, in E:5, type “50”, and in E:6, type “75”.
7. Now, instead of adding all of those totals by hand, or using a calculator, we’ll simply create a formula to do all the work for us. In cell D:8, type in “Total”.
8. To make things easy, so we don’t have to do a lot of typing, click on cell E:8 and type =SUM(
9. From here, click on cell E:3. This should highlight cell E:3 with a dotted border.
10. Next, hold down the Shift button on your keyboard. As you hold this button down, click on cell E:6. You should now see cells E:3 through E:6 highlighted with a dotted border. Press Enter.
11. You should now see the combined total of your mortgage, electric, phone, and school loan in cell E:8! To give each of the numbers in column E a currency format, simply click on the “E” at the top of the column and click the “$” icon in the toolbar.

Now that you know how to create formulas using the SUM function, repeat the same steps above for an “Expenses” column. Highlight the cell in “light yellow 1”, or any color of your choosing.

1. In cell G:3, type “Gas”. In cell G:4, type “Groceries”. In cell G:5, type “Appointments”.
2. For our sample amounts, type “80” in cell H:3, “200” in cell H:4, and “100” in cell H:5.
3. In cell G:8, type “Total”.
4. Add the cells using the same method from above in cell H:8.
5. You should now see “380” in that cell. Give the entire column a currency format, just like the example from above.

You should now see three columns representing your monthly income, bills and expenses. Now, we’re going to create a Surplus/Deficit column. This column will allow us to take a quick look to see if we have a little left over each month for savings, or if we need to cut down on things like entertainment, eating out, etc.

1. In cell J:1, type “Surplus/Deficit”, bold it, give it a background of blue and format the column for currency.
2. In cell J:3, type “Surplus or Deficit?”. This might make the words overlap the column. If that’s the case, you can resize the column to conform to the data by simply placing your mouse between the top of columns J and K until your mouse turns into a double-arrow, and then double-click.
3. In cell K:3, type the following formula and press Enter:
   =SUM(B3-E8-H8)

You should now see a total of $1,228.33 displayed in cell K:3.

At this point, we’re gonna create a feature called “conditional formatting” in cell K:3. Conditional formatting allows you to format a cell (or cells) according to the data that’s displayed. For our example, we’re going to create a background color of red if there’s a deficit in your budget, and a background color of green if there’s a surplus.

1. Click on cell K:3 and click “Format” in the main menu near the top of the sheet.
2. Click on “Conditional formatting…” near the bottom of the sub-menu.
3. At this point, you should see a toolbar that pops up near the right-hand side of your spreadsheet. This is where you can set the rules for your conditional formatting.
4. Within that toolbar, under the section titled “Format cells if…”, select “Greater than”.
5. In the area titled “Value or formula”, type in “0”.
6. Under “Formatting style”, click the “B” icon, and then click the fill icon. Choose “light green 1”.
7. Click “Done”.
8. You should now see a bolded $1,228.33 with a green background on cell K:3. Click the cell and go to the Conditional formatting toolbar once again.
9. Click “Add new rule”.
10. Repeat the steps above, but this time, change the formatting option to “Less than” and give it a background color of red.
11. Change the text color to white.
12. Click “Done”.

At this point, nothing should have changed. However, if any of your amounts under Monthly Income, Bills, or Expenses changes, you might get a chance to see your conditional formatting in action. In fact, type 5,000 for your mortgage payment and see what happens. Did cell K:3 turn red?

If you’d like to add additional rows to your sheet, without breaking the formulas, simply right-click on a number in the very left-hand side of a row, and select “Insert 1 below” or “Insert 1 above”. This will allow you to add additional items to your sheet as you see fit.

If you want to add additional months to your Household Budget, click the little “+” at the very bottom left of your sheet. You can rename the additional sheets whatever you want.

If you’d like to see more advanced ways to manage your household budget...well, you’re just gonna have to wait until next time!