Linking Worksheets (Creating a Summary Worksheet)

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 9.3.1 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.

As shown in Figure 9.3.1, 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. The formula in the formula bar should read: =’Mortgage Payments’!B5*12
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.3.2 below shows the results of creating formulas that reference cell locations in the 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 or simply press the ENTER key to close the function. The total for all annual expenses now appears on the worksheet.
6. Click cell D7 on the Budget Summary worksheet. You will enter a formula to calculate Net Change in Cash in this cell.
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 9.3.3 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.

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.
10. Save the CH2 Personal Budget file.
11. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH2 Personal Budget workbook as directed by your instructor.
12. Close the CH2 Personal Budget file before moving on to 2.4 – Preparing to Print.

Figure 9.3.4 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.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.