Microsoft Office Tutorials and References

In Depth Information

**Review Assignments**

Review Assignments

Practice the skills

you learned in

the tutorial using

the same case

scenario.

Data File needed for the Review Assignments: Timov.xlsx

Sergei and Ava Timov, friends of Diane and Glenn, ask you to create a similar workbook

for their family budget. The Timovs want to purchase a new home. They are

considering two houses with different mortgages. They want the budget worksheet you create to

display the impact of monthly mortgage payments on the couple’s cash ﬂ ow. They also

want to be able to quickly switch between one mortgage plan and another to observe the

impact of each plan on their budget. The couple has already designed the workbook and

entered estimates of their income and expenses for the upcoming year. You need to set

up the formulas. Complete the following:

1. Open the
Timov
workbook located in the Excel3\Review folder included with your

Data Files, and then save the workbook as
Timov Family Budget
.

2. In the Documentation sheet, enter your name in cell B3. Use the TODAY function to

display the current date in cell B4.

3. In the Family Budget worksheet, in the range C18:N18, use AutoFill to replace the

month numbers with the abbreviations
Jan
through
Dec
.

4. In the range C21:N21, calculate the couple’s total income. In the range C27:N27,

calculate the couple’s total monthly expenses. In the range C28:N28, calculate the

monthly net cash ﬂ ow (equal to the total income minus the total expenses).

5. In cell C7, enter a formula to calculate the sum of Sergei’s monthly income for the

entire year. In cell D7, calculate Sergei’s average monthly income. In cell E7,

calculate Sergei’s maximum monthly income. In cell F7, calculate Sergei’s minimum

monthly income.

6. Complete the Year-End Summary table by ﬁ rst selecting the range C7:F7. Use

AutoFill to copy the formula in the range C7:F7 into the range C7:F16. Use the Auto

Fill Options button to copy only the formulas into the selected range and not both

the formulas and the formats. (
Hint
: Because you haven’t yet entered any mortgage

payment values, cell D14 will show the value #DIV/0!, indicating that Excel cannot

calculate the average mortgage payment. You’ll correct that problem shortly.)

7. In the range K2:K7, enter the following loan and loan conditions of the ﬁ rst

mortgage:

a. In cell K2, enter
6.7%
as the annual interest rate.

b. In cell K3, enter
as the number of payments per year.
12

c. In cell K4, calculate the monthly interest rate.

d. In cell K5, enter
as the number of years in the mortgage.
30

e. In cell K6, calculate the total number of months to repay the loan.

f. In cell K7, enter
395,000
as the loan amount.

8. In cell K8, use the PMT function to calculate the monthly payment required to repay

this loan.

9. Edit the formula in cell K8, adding a minus sign directly before the PMT function to

make the value returned by the formula positive rather than negative.

10. In the range K10:K15, enter the following conditions of the second mortgage plan,

and calculate the monthly interest rate and the total number of months to repay

the loan:

• The annual interest rate is
6.7%
.

• The interest rate is compounded
times a year (or monthly).
12

• The mortgage will last
years.
20

• The loan amount (or value of the principal) is
$300,000
.