Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

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.

Key Terms

Session 2.1

absolute reference

argument

Auto Fill

AVERAGE function

Date function

destination cell

destination range

F4 key

fill handle

MAX function

MIN function

mixed reference

NOW() function

optional argument

relative reference

source cell

source range

SUM function

syntax

TODAY() function

WEEKDAY function

Session 2.2

comparison operator

Financial function

IF function

Logical function

PMT function

Review Assignments

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

Practice the skills you

learned in Session 2.1

using the same case

scenario.

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

the range.)

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.