Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

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

scenario.

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.