Microsoft Office Tutorials and References
In Depth Information
In Session 2.2, you learned about the PMT function, which is a Financial function sup-
ported by Excel. You used the PMT function to calculate the monthly payment required to
pay off a specified mortgage. You also learned about one of Excel’s most commonly used
Logical functions, the IF function. You used the IF function to display a text string indicat-
ing whether a loan was affordable.
Data File needed for the Review Assignments: Family1.xls
Amanda appreciates the work you did on her family budget. Her friends Ken and Ava
Giles have examined the workbook you created and have asked you to create a similar
workbook for their budget.
Practice the skills you
learned in Session 2.1
using the same case
Once you have completed a budget worksheet for the Giles family, they may want you to
help them determine if they can afford to purchase their dream house in the country. The
mortgage would be substantially higher than the family’s current mortgage, but with Ava
now working full time, the couple feels that they may be able to afford the higher mort-
gage. They would like you to create a workbook that will help them to determine if purchas-
ing the house is possible.
To complete this task:
1. Open the Family1 workbook located in the Tutorial.02\Review folder included with
your Data Files, and then save the workbook as Family2 in the same folder.
2. In the Documentation sheet, enter your name in cell B3, and then enter the current
date in cell B4 using the TODAY() function.
3. Switch to the Budget worksheet, and then enter the formulas in the ranges C7:N7 and
C14:N14 to calculate the total income and expenses, respectively, for each month.
( Hint : Enter the formula in cells C7 and C14 first, and then copy and paste the formu-
las to the other cells in the ranges.)
4. In the range C16:N16, enter a formula to calculate the family’s net income. ( Hint : Enter
the formula in cell C16 first, and then copy and paste the formula to the other cells in
5. In the range D17:N17, enter a formula using the SUM function to calculate the run-
ning total for net income from February through December. ( Hint : Use an absolute
reference for the appropriate cell reference.)
6. In the range C4:N4, use Auto Fill to fill in the month names January, February, March,
and so forth.