PRDV006: Spreadsheets II: Formatting and Functions

So far we have used cell references in formulas and functions, which allow Excel to produce new outputs when the values in the cell references are changed. Cell references can also be used to display values or the outputs of formulas and functions in cell locations on other worksheets. This is how data will be displayed on the Budget Summary worksheet in the Personal Budget workbook. Outputs from the formulas and functions that were entered into the Budget Detail, Mortgage Payments, and Car Lease Payments worksheets will be displayed on the Budget Summary worksheet through the use of cell references. The following steps explain how this is accomplished:

1. Click cell C3 in the Budget Summary worksheet.
2. Type an equal sign: =
3. Click the Budget Detail worksheet tab.
4. Click cell D12 on the Budget Detail worksheet.
5. Press the ENTER key on your keyboard. The output of the SUM function in cell D12 on the Budget Detail worksheet will be displayed in cell C3 on the Budget Summary worksheet.

Figure 8 Cell Reference Showing the Total Expenses in the Budget Summary Worksheet shows how the cell reference appears in the Budget Summary worksheet. Notice that the cell reference D12 is preceded by the Budget Detail worksheet name enclosed in apostrophes followed by an exclamation point (‘Budget Detail’!) This indicates that the value displayed in the cell is referencing a cell location in the Budget Detail worksheet.

Figure 8 Cell Reference Showing the Total Expenses in the Budget Summary Worksheet

As shown in Figure 8 Cell Reference Showing the Total Expenses in the Budget Summary Worksheet, the Budget Summary worksheet is designed to show the expense budget for the mortgage payments and the auto lease payments. However, you will recall that we used the PMT function to calculate the monthly payments. In the Budget Summary worksheet, we need to show the total annual payments. As a result, we will create a formula that references cell locations in the Mortgage Payments and Car Lease Payments worksheets. The following steps explain how this is accomplished:

1. Click cell C4 in the Budget Summary worksheet.
2. Type an equal sign: =
3. Click the Mortgage Payments worksheet tab.
4. Click cell B5 in the Mortgage Payments worksheet.
5. Type an asterisk (*) for multiplication.
6. Type the number 12. This multiplies the monthly payments by 12 to calculate the total payments required for the year.
7. Press the ENTER key on your keyboard. The value of multiplying the monthly mortgage payments by 12 is now displayed on the Budget Summary worksheet.
8. Click cell C5 on the Budget Summary worksheet.
9. Type an equal sign: =
10. Click the Car Lease Payments worksheet tab.
11. Click cell B6 in the Car Lease Payments worksheet.
12. Type an asterisk (*) for multiplication.
13. Type the number 12. This multiplies the monthly lease payments by 12 to calculate the total payments required for the year.
14. Press the ENTER key on your keyboard. The value of multiplying the monthly lease payments by 12 is now displayed on the Budget Summary worksheet.

Figure 9 Formulas Referencing Cells in Mortgage Payments and Car Lease Payments Worksheets shows the results of creating formulas that reference cell locations in the Mortgage Payments and Car Lease Payments worksheets.

Figure 9 Formulas Referencing Cells in Mortgage Payments and Car Lease Payments Worksheets

We can now add other formulas and functions to the Budget Summary worksheet that can calculate the difference between the total spend dollars vs. the total net income in cell D2. The following steps explain how this is accomplished:

1. Click cell D6 in the Budget Summary worksheet.
2. Type an equal sign: =
3. Type the function name SUM followed by an open parenthesis: (
4. Highlight the range C3:C5.
5. Type a closing parenthesis: ) and press the ENTER key on your keyboard. The total for all annual expenses now appears on the worksheet.
6. Click cell D7 on the Budget Summary worksheet.
7. Type an equal sign: =
8. Click cell D2.
9. Type a minus sign (−) and then click cell D6.
10. Press the ENTER key on your keyboard. This formula produces an output of $1,942, indicating our income is greater than our total expenses.

Figure 10 Formulas Added to Show Income Is Greater Than Expenses shows the results of the formulas that were added to the Budget Summary worksheet. The output for the formula in cell D7 shows that the net income exceeds total planned expenses by $1,942. Overall, having your income exceed your total expenses is a good thing because it allows you to save money for future spending needs or unexpected events.

Figure 10 Formulas Added to Show Income Is Greater Than Expenses

We can now add a few formulas that calculate both the spending rate and the savings rate as a percentage of net income. These formulas require the use of absolute references, which we covered earlier in this chapter. The following steps explain how to add these formulas:

1. Click cell E6 in the Budget Summary worksheet.
2. Type an equal sign: =
3. Click cell D6.
4. Type a forward slash ( / ) for division and then click D2.
5. Press the F4 key on your keyboard. This adds an absolute reference to cell D2.
6. Press the ENTER key. The result of the formula shows that total expenses consume 94.1% of our net income.
7. Click cell E6.
8. Place the mouse pointer over the Auto Fill Handle.
9. When the mouse pointer turns to a black plus sign, left click and drag down to cell E7. This copies and pastes the formula into cell E7.

Figure 11 Calculating the Savings Rate shows the output of the formulas calculating the spending rate and savings rate as a percentage of net income. The absolute reference shown for cell D2 prevents the cell from changing when the formula is copied from cell E6 and pasted into cell E7. The results of the formula show that our current budget allows for a savings rate of 5.9%. This is a fairly good savings rate. In the next section we will discuss how these savings can grow over time by exploring the time value of money concepts.

Figure 11 Calculating the Savings Rate