Adding Formulas

Alright, let's enter some formulas (aka functions) to add up our income sources, expense sources, and then subtract total expenses from total income to produce a balance.

Select cell B5, which will hold the total available income for pay period 1. The available income is derived from carryover, net pay, and other deposits. Once you've selected cell B5, click "Insert" on main menu and then select "Function" from the menu that appears.



After you select function the "Function wizard" will appear as depicted below. Scroll down the list of functions and select SUM. Once you've selected SUM, click the "Next" button to continue.

Excel Note

The insertion of formulas is slightly different with Microsoft Excel but it is pretty intuitive. Just click Insert; then function; and then follow the screen directions. Whether you are in Calc or Excel, both programs want to know which cells you want added together and both abbreviate strings of cells to be added like this: =SUM(B6:B15). This means SUM everything in cells B6 through B15. All formulas must start with an = sign and the following signs are used in both Calc and Excel: plus is +, minus is -, multiply is *, and divide is /.



Once you've clicked next in Calc you'll be presented with the following screen where you'll be asked which cells to add together. Enter cells B2, B3 and B4 as the cells to add and then click "OK". The formula =SUM(B2;B3;B4) has now been applied to the B5 cell.



Recall earlier when Calc magically auto-populated our pay period column headers with the numbers 2, 3, 4, ..., 26? Well, what we need to do now is have it auto-populate our addition formula into cells C5 through AA5. Calc can automatically update the formula for each new cell it applies the formula to so that the correct cells in that particular column are added together.

Select cell B5 so that it has a thick border around it with the small black box in the bottom right corner. Click on the black box in the bottom right corner and drag your mouse right all the way to cell AA5 as depicted in the graphic below.



Your addition formula for adding up available income has now been personalized for each of the columns. By personalized, I mean B5 will add B2, B3, and B4; C5 will add C2, C3, and C4; D5 will add D2, D3, and D4, etc.

Now let's move on to cell B16, which will store total expenses for pay-period 1. Select cell B16, click Insert from the main menu, and then select Function to start the function wizard. From within the function wizard select SUM, click "Next" and then enter B6 through B15 to be added up. When you're finished, click OK.

Alright, once you've added the formula to cell B16 you need to have the formula auto-populated into cells C16 through AA16. To do this, select cell B16 and then drag the small black box in the bottom right corner of the cell to cell AA16 just as you did above but for the total income cells. Once you're finished your screen should look like the graphic below.



Now we're ready to work on the B17 cell, which will hold the balance left over at the end of each pay period (total available income during the pay period minus total expenses of the pay period). Click on B17 and enter the formula =B5-B16 into the formula bar immediately above the actual spreadsheet itself (the formula bar is another way to enter formulas). Once you've entered the formula click the check mark. Always click the check mark or hit the enter key when adding formulas via the formula bar so they'll be registered.

• =B5-B16 is total income - total expenses.

Then, select B17 again and drag it out to cell AA17. This will insert the formula and personalize it appropriately in every column. In other words, the B column balance will write to the C column carryover; C to D; D to E, and so on all the way through column AA.



You can check the formula in every cell in which you have added a formula. Just click on the cell and then look at the formula bar. It will display the formula for that particular cell. If you have populated a formula throughout a whole row as we have done, you can then click in the cell next to it in the row and see how the formula has been altered and "fitted" especially for that cell.

Adding the "Carryover" Formula

At this point we have to specify how the "carryover" should be handled. Carryover is the cash we started the budget with which we carried over from prior year; in our case cash we carried over from 2008. It is also the cash we have left over after paying our bills each pay period. This amount needs to be inserted into the next pay period's carryover cell so it can be added to the pay we receive that pay period and reflect the total cash we have on hand to address the bills of the new pay period.

To deal with carryover, click on cell C2; then enter =B17 in the formula bar above the spreadsheet. This tells Calc that you want the balance left over from pay period 1 entered into the carryover cell in pay period 2. In other words, you want cell C2 to equal the value in cell B17. Once you've entered the formula click the check mark to the left of the formula bar.

Now, click and hold down the little black box in the bottom right corner of cell C2 and drag it to cell AA2. This places the formula into all the remaining column's carryover cells. It means the balance in B17 (whether positive or negative) will be written to C2; the balance in C17 will be written to D2, etc.

Formatting

Before we begin adding data to our budget, let's do a little more formatting. It's good practice to do the formatting early on, but it isn't too late for us to do it now.

Let's format our data cells so they are centered. This makes for a neater budget and one that is nicer to look at. We also need to format the data cells so they use dollars and cents.

Centering Data

Click in cell B2, and while holding down your left mouse button, drag your mouse right and down until you reach cell AB17. Once you've selected all of those cells, click the "center align" button on the toolbar like you did at the beginning of this tutorial for the pay period column header numbers.

Note: when you've finished, leave the cells selected.

Using Dollars and Cents

Now, with the cells still selected click "Format" on the main program menu, then select "Cells" and ensure the "Numbers" tab is selected in the Format Cells wizard that appears. Once inside the wizard, select 2 decimal places and 0 leading zeros (we don't need leading zeros like 0.45 or 00.98) as depicted in the graphic below and then click OK.



This is what you should get. Notice our zeros are now displayed as ".00".

Entering the Data

The budget below is built from an imaginary, but not so unrealistic, set of income and expense data. The person who has this budget has a gross annual income of about $40,252.00 US. His or her net, or take-home pay, for the year is about $28,177.00.



Notice in pay period 8 below, this person begins to run into the red. His or her expenses finally exceed the available income leaving a balance of -$44.40. And, the problem continues to the end of the year, resulting in a deficit of -$955.11. Our imaginary person will have to cut some corners early on in the year to eliminate these deficits.

Defining the "Breakdown" Column

In the breakdown column, we can compute the percentage of our income we spend on each expense category. We'll compute this percentage for one expense category to show you how it's done.

First, we need to calculate our total available 2009 income which includes the carryover from last year with which we started this year; net pay each pay period; and any other deposits we may have made.

Click on cell AB4 and then enter =SUM(B2+SUM(B3:AA3)+SUM(B4:AA4)) in the formula bar. After entering the formula, click the check mark to register it.

This adds B2 (the carryover we brought into this year's budget from 2008); B3 through AA3 which is our combined net pay for this year; and B4 through AA4 which is the total of other deposits made. This person's total 2009 available, take-home income is therefore $29,389.46. Notice in the formula, we have the sum of net pay grouped in parentheses; the sum of added deposits grouped in parentheses; and then these two sums plus carryover grouped in giant parentheses. This is one of the proper ways to group these operations so Calc and Excel will properly interpret and perform our computation instructions.



Now, let's figure a sample expense, say cash withdrawals, and see what percentage it is of our total available income (which we just computed) for the year. We'll add this value into cell AB13 (the Cash WD #1 row). Before we enter the formula, however, we need to change the cell formatting of AB13 to a percent. To do this, click cell AB13, select Format from the main menu and then select Cells. When the format cells wizard appears select "Percent" in the categories menu, then select 0 for decimal places and leading zeros. Finally, click OK to apply the formatting.

Alright, now go to cell AB13 and enter =SUM(B13:AA13)*2/AB4 in the formula bar, then click the check mark. If your budget has no data in it yet, you'll see something along the lines of #DIV/0! appear in the cell. This will remain until you add data to your budget, and then the percentage will appear.

This formula sums all the cash withdrawals in row 13; then multiples that total times 2 because there are two rows of cash withdrawals for $200 in each cell; then we divide that sum, the total of both rows of cash withdrawals by AB4, our available total income for the year. The answer we get is .35 in decimal, which is 35%. Our imaginary person spends 35% of his or her available yearly income on living expenses like groceries, eating out, personal and household supplies, gasoline for the car, etc.



Well, I hope you enjoyed this intro to Calc and Excel via creating a personal budget. It has certainly been fun and rewarding to write. Also, don't forget about the weekly, bi-weekly and monthly "template" budgets available for download with this article.