Microsoft Office Tutorials and References
In Depth Information
7. In the range C21:F23, enter a formula to calculate the total, average, minimum, and
maximum values of the two incomes.
8. In the range C25:F30, enter a formula to calculate the total, average, minimum, and
maximum values of each expense category.
9. In the range C32:F32, enter a formula to calculate the total, average, minimum, and
maximum values for net income.
10. Print the contents of the Budget worksheet, and save the changes you have made. If
you are not continuing with the remaining steps, close the workbook.
( Note: The following steps are optional . You should attempt them only if you have
completed Session 2.2 in the tutorial.)
Practice the skills you
learned in Session 2.2
using the same case
11. Save the workbook as Family3 to the Tutorial.02\Review folder.
12. Add a worksheet named “Loan Analysis” to the end of the workbook, and then enter
the text “Loan Analysis” in cell A1 of the worksheet.
13. Switch to the Budget worksheet, copy the nonadjacent range A21:B32;D21:D32,
switch to the Loan Analysis worksheet, and then paste the values, but not the formu-
las, into range A3:C14, using the Paste Special option. Increase the width of columns A
and C to 12 characters (89 pixels) each, and column B to 15 characters (110 pixels).
Edit the entries in cells C5, C12, and C14 so they contain formulas that calculate the
total income, total expense, and net income.
14. Enter the following labels in the cells as indicated:
• Cell E3: Loan Conditions
• Cell E4: Loan Amount
• Cell E5: Length of Loan
• Cell E6: Annual Interest Rate
• Cell E8: Payment Conditions
• Cell E9: Payments per Year
• Cell E10: Total Payments
• Cell E11: Payment Amount
15. Widen column E to 21 characters (152 pixels).
16. In the range F4:F9, enter values for the following loan and payment conditions:
• Loan Amount = –300,000
• Years = 15
• Annual Interest Rate = 6%
• Payments per Year = 12
17. In cell F10, enter a formula to calculate the total number of payments. In cell F11,
enter a formula using the PMT function to calculate the monthly loan payment.
18. In cell C8, enter the formula to make the mortgage expense equal to the result of the
calculation in cell F11.
19. Enter the following labels in the cells as indicated:
• Cell E13: Is the loan affordable?
• Cell E14: Minimum Loan Payment
• Cell E15: Conclusion
20. The family does not want a monthly loan payment greater than $2,500. Enter this
value into cell F14, and then in cell F15 enter a formula using the IF function to dis-
play the text string “Yes” if the monthly payment is less than or equal to the value you
entered in cell F14, and “No” if otherwise. Is the loan affordable under the loan con-
ditions you have entered?
21. Print the contents of the Loan Analysis worksheet.