How to use a spreadsheet to create a simple budget

Creating a budget is of one the simplest and most useful tasks you can do using a spreadsheet. By using a spreadsheet, any changes you make to your budget are instantly updated across the whole budget, with totals calculated for you.

This tutorial shows you how to create a budget using Microsoft Excel 2013. If you’re using an earlier version of Excel or a spreadsheet from another company, you’ll find the principles and general techniques carry over, even though the menu commands differ.

I’ll show you how to create a simple budget summary for a small organization we’ll call Bleeding Heart Do-Gooders. You can use the same approach to create a household budget, a budget for a trip or a specific event, and so on

Adding data and formulae

Here’s how to create the budget:

1. Open Excel and create a new blank worksheet.
2. No matter what sort of spreadsheet I am creating—a budget, timesheet, whatever—I usually find it’s best to leave a couple of rows at the top and a couple of columns at the left empty when I start setting up the spreadsheet. This makes it easy to insert a heading or to reorganize the design—it’s amazing how often you discover you should have allowed for another column or row, and while it’s easy to insert these later it’s even easier to allow a little space to start with.

So, click in cell C4 (that is, column 4 row C) and type ‘Income’ (don’t include the quotation marks).

3. Now we’re going to start listing our income categories and the amounts budgeted for each, so:
   1. Click in cell D5;
   2. Type ‘Donations’;
   3. Press the Tab key;
   4. Type ‘50,000’;
   5. Press Enter;
   6. Type Grants;
   7. Press the Tab key;
   8. Type ‘75,000’;
   9. Press Enter.

Do you notice how you press the Tab key to move between columns and the Enter key to move down to the next row starting in the first data entry column? It’s quite different from how things work in most other programs, but you’ll find it’s a consistent feature of spreadsheet programs.

4. Let’s add one more entry in the Income category:
   1. Type ‘Miscellaneous’ and press the Tab key;
   2. Type ‘1000’.

That’s it for the income categories, so let’s total them up.

5. Click in cell C8 and type ‘Total Income’.
6. We want Excel to add up our three income categories and provide the result, so click and drag from cell E5 down through cell E7 to highlight the three cells containing the income amounts, then click the AutoSum button on the toolbar. (It’s a sigma symbol and it looks like this: ∑.)

When you click the AutoSum button it automatically adds up the values in the selected cells and places the result in the cell immediately below. You can use the same technique on a horizontal row of values and AutoSum will place the result in the next cell to the right of the selected numbers.

This illustrates how cells in your spreadsheet can contain data—numbers, text, images and so on—or formulae. If you want to know the formula Excel has used to provide a total for your income categories, click in cell E8 and then look at the expression displayed in the formula bar immediately above the data entry area. You’ll see:

=SUM(E5:E7)

That is, ‘display the sum of the values in cells E5 through E7’. If you click in cell E5, E6 or E7 the formula bar displays the same value you see in each of these cells, because each of those cells contains data. Cell E7, on the other hand, displays the result of a calculation based on data in the other cells.

Let’s continue creating the budget.

7. Click in cell C9, type ‘Expenses’.
8. Add the data for the expenses. To start doing so, click in cell D10 and:
   1. Type ‘Accounting’, press tab, type ‘400’, press Enter;
   2. Type ‘Bank Charges’, press tab, type ‘250’, press Enter;
   3. Type ‘Board Expense’, press tab, type ‘400’, press Enter.

Follow the same procedure to add the rest of the expenses:

To finish entering the budget information, we need to add up the expenses and then figure out our net income by subtracting the expense total from our total income:

9. Click in cell C25, type ‘Total Expenses’.
10. Select all the expense amounts (click and drag from cell E10 to E24) and click the AutoSum button to place the total in cell E25.
11. Click in cell B26 and type ‘Net Income’.
12. To determine our net income, we need to use a formula to subtract the total expenses (in E25) from the total income (in E8). So, click in cell E26 and type:

=(E8-E25)

and press Enter. You’ll see ‘5200’ displayed as the net income.

Making things look good

Figure 1. (Click to see a larger image)

By this stage, you have a working budget summary which should look like Figure 1.

As I said, it’s workable, but it’s also bland and not particularly readable. We can fix that by adding a header and by formatting both the text and the numbers.

Here’s how:

1. Click in cell A1and type ‘Bleeding Heart Do-Gooders’.
2. Click in cell A2 and type ‘Budget Summary July 2013 – June 2014’. Allowing room for these headers is one reason we left some space at the top and left when we first created the spreadsheet.
3. Let’s make all the headings bold. To do that:
   1. Click cell A1.
   2. Hold down the Ctrl key while you click each of the other cells that contains a heading: A2, C4 (Income), C8 (Total Income), C9 (Expenses), C25 (Total Expenses) and B26 (Net Income). Note how Ctrl-clicking allows you to select non-adjacent cells—a very useful technique to know.
   3. Click B in the Font section of the ribbon to bold the content of each of these cells.

As we’re dealing with dollar amounts, let’s format the numbers as currency as opposed to just plain ol’ numbers. So, click the column header of Column E to highlight the list of numbers then click Format -> Format Cells in the Cells section of the ribbon. The Format Cells dialog box will open.

4. Click the Number tab and then click Currency in the Category list. Make sure to select 2 decimal places and choose whichever method of displaying negative numbers you prefer, then click OK.

Things are looking a lot more readable now, but we can improve things a little more by highlighting the three most important values, total income, total expenses and net income. We could simply bold those numbers as we did with the headings or you could choose any of a number of other options to highlight the values. Let’s use some lines to offset these totals:

5. Click cell E8 (total income).
6. In the Font section of the ribbon click the arrow on the right of the Borders button and select Top Border from the list. This places a line between the income amounts and the income total.
7. Repeat the process for the total expense value (cell E25).
8. Doll up the net income by applying some double borders. Click cell E26, click the down arrow beside the Borders button and select Top and Bottom Double Border.

9. Finally, take a look at the tab at the bottom of your worksheet labelled ‘Sheet1’. Let’s rename that to more accurately reflect the contents of the worksheet. To do so, right-click the tab, select Rename from the pop-up menu, type ‘Budget Summary’ and press Enter. One reason to do this is that a workbook can contain multiple worksheets. At some point in the future, you may wish to add monthly budget figures or other information on separate sheets within this BHDG Budget workbook. Having descriptive names on your tabs not only makes it easy to quickly move between worksheets, it can also help if you get into developing more complex formulae which use values from multiple worksheets.

Your spreadsheet should now look like Figure 2. It’s appreciably more readable, with important information highlighted. You can make it look much smarter if you want, but this is a good start.

Figure 2. (Click to see a larger image)

Checking that it works

So the budget looks good, but does it produce the correct results? You can double-check that the formulae you’ve entered for total income, total expenses and net income are correct by doing a little addition for yourself. You should also check that the formulae work by changing some of the amounts for income and expenses. For example change the Grants amount to $80,000—just click in the cell, type ‘80,000’ (you don’t need to type the dollar sign or cents; Excel will add those for you) and press Enter. You should immediately see the total income rise to $131,000 and the net income jump to $10,400. Now increase the Travel expenses to $925. You’ll see total expenses as $120,875 and net income as $5,125.

In a spreadsheet as simple as this, it’s easy to check that your formulae are working. If you get into creating more complex spreadsheets, you’ll find Excel has a range of tools to help you create, check and troubleshoot your formulae and spreadsheet design.

[hr color=”black”]

© 2013 Rose Vines