Ceci est une ancienne révision du document !
In part 8 of this series, we began our work in Calc, and created and formatted an income section for a budget worksheet. Last month we began our discussion of functions and formulas. In this part, we will continue working on our budget worksheet, and use formulas and functions to do calculations in it.
Setting Up the Assets Section
If you recall, we had set up a section that contained all of our income items in columns A and B. We will begin our Assets section in cell D1. Move to that cell and enter “Assets” in it. Select cells D1-G1, and merge and center the cells (Format > Merge Cells > Merge and Center Cells). In cell D2, enter “Type”. Merge and center this cell with E2. Enter “Beginning” and “Ending” in cells F2 and G2 respectively. In cell D3, enter “Period Income Total,” and merge it with cell E3 (Format > Merge Cells > Merge Cells). In rows D4-D9, enter the following text in order: Checking Balance, Savings Transfer, Total Expendable, Savings Balance, Retirement Balance, Gross Assets. After entering the text in the cells, go back and merge these cells with their adjacent cell in column E (i.e. merge D4 with E4, D5 with E5…).
Initial Calculations for Asset Section
In cell F1, we will place our first calculation using the SUM() function. For this cell we need to total the numbers of column B in our Income section. Enter this formula in cell F3:
=SUM(B3:B6)
This formula uses the SUM() function to total the numbers entered in cells B3, B4, B5, and B6. If your Income section contains more or less income items, adjust the cell reference given to the SUM() function. If you pull out a calculator and add up the numbers, you will find you get the same number as the total calculated by LibreOffice Calc.
In cells F4 and F5, enter amounts for the Checking Balance and Savings Transfer. In cell F6, we will need to sum the three amounts above for the Total Expendable item, but this time, we will use a different method. Select cell F6 and click on the sum button (shown left) in the formula toolbar. You will notice that LibreOffice automatically finds the three amounts above and creates a SUM() function formula with the range inside. Press Enter to accept the range.
Enter amounts for the Savings Balance and Retirement Balance in cells F7 and F8. Select cell F9, and click the sum button in the formula toolbar. Notice that, once again, Calc has selected the two numbers above it, but for an accurate total of the Gross Assets, we need to include the Total Expendable amount. Left click and drag to select the three cells. You should now see the corrected range in the SUM() function. Press Enter to accept.
Setting Up the Expenses Section
We will come back to the Assets section to do some more calculations once we get the Expenses section set up. In cell A11, enter “Expenses”. Merge and center the cells A11-G11. In cell A12-E12, enter the following text in the cells: Type, Due, Amt Due, Amt Pay, and Notes. Merge and center the cells E12-G12. This becomes our header row for this section.
In cell A13, enter “Savings”, and in cell A14 enter “Retirement”. These two expenses will represent deposits into these two accounts, and we will use them in our final calculations in the Assets section. Beginning with cell A15, and going down the A column, enter other expenses like Food, Fuel, Mortgage, Phone, etc. In columns B and C, enter due dates and due amounts for all the items you entered in the Expenses section. Select a few of the items and put payment amounts in the D column. Merge the E, F, and G rows for each of the items.
Formulas in the Expenses Section
In the cell below the last item (column A), enter “Total Expenses:”, and merge it with column B on that row. Select the cell in column C for that row. Click the sum button in the formula toolbar. Calc should select all the amounts in the Amt Due column. Press Enter. In the D column of the same row, type “Total Payments” and merge it with column E on the same row. Select column F of the same row and click the sum button on the formula toolbar. This time Calc jumps all the way up to the last amount in our Assets section, but this is not what we want. This happens because this is the first number Calc found in the column. Select the numbers in the Amt Pay column of the section to change the range. Press Enter.
Final Calculations for the Asset Section
Going back to the Assets section, we will use some of the numbers in the Expenses section to do a few more calculations. Select cell G6. This is the total for our expendable income after all payments have been made. Enter the formula:
=F6-F##
where ## is the row number where the total payments is calculated.
For the ending balance on our savings account, we need to take the beginning total, add the deposit, and subtract the transfer. If you put the Savings as your first item in the Expenses section, the formula will look like this:
=F7+D13-F5
For the Retirement ending balance, we just need to add the deposit. If you added Retirement as the second item in the Expenses section, the formula will be:
=F8+D14
Finally, we will make the final adjustment to the Gross Assets by summing the three numbers in the G column. Select G9, and click the sum button in the formula toolbar. Calc should select the three numbers we need. Press Enter.
Our budget spreadsheet is now complete, but it doesn't look very pretty. Next time, we will format our spreadsheet using styles.