Microsoft Office Tutorials and References

In Depth Information

**Case Problems**

12.
In cell F32, determine the shipping charge by entering an IF function that tests

whether cell C15 equals “standard”. If it does, return the value in cell F9; otherwise,

return the value in cell F10.

13. In cell G32, display the value of cell C15.

14. In cell F34, calculate the total of the after discount value, the sales tax, and the

shipping fee.

15. Reduce the quantity of Mountain Rockets boxes from 2 to
, and then verify that the
1

discount is changed to 0 for the order.

16. Change the shipping option from overnight to
standard
, and then verify that the

shipping fee is changed to the fee for standard shipping.

17. Save and close the workbook, and then submit the ﬁ nished workbook to your

instructor, either in printed or electronic form, as requested.

Case Problem 3

Explore how

to use relative

and absolute

references and

the PMT function

to create a

loan table.

Data File needed for this Case Problem: Loan.xlsx

Eason Financial Services
Jesse Buchmann is a ﬁ nance ofﬁ cer at Eason Financial

Services in Meridian, Idaho. She works with people who are looking for home

mortgages. Most clients want mortgages they can afford, and affordability is determined by

the size of the monthly payment. The monthly payment is determined by the interest

rate, the total number of payments, and the size of the home loan. Jesse can’t change

the interest rate, but homebuyers can reduce their monthly payments by increasing the

number of years to repay the loan. Jesse wants to give her clients a grid that displays

combinations of loan amounts and payment periods so that they can select a loan that

best meets their needs and budget. Jesse already entered much of the layout and

formatting for the worksheet containing the loan payment grid. You will enter the PMT function.

Complete the following:

1. Open the
Loan
workbook located in the Excel3\Case3 folder included with your

Data Files, and then save the workbook as
Loan Grid
.

2. In the Documentation sheet, enter your name and the date.

3. In the Loan Calculation worksheet, in cell E3, enter a monthly payment of
$1,750
.

4. In cell E5, enter the annual interest rate of
5.75%
. In cell E6, enter
to indicate
12

that the interest payment is compounded 12 times a year, or monthly.

5. In the range C10:C20, use AutoFill to enter the currency values
$250,000
through

$350,000
in increments of $10,000. In the range D9:H9, use AutoFill to enter the

year values
through
in increments of 5 years.
35

6.
In cell D10, use the PMT function to calculate the monthly payment required to

repay a
$250,000
loan in
years at
5.75%
interest compounded monthly. Use
15

absolute references to cells E5 and E6 to enter the annual interest rate and number

of payments per year. Use the mixed references D$9 and $C10 to cells D9 and C10,

respectively, to reference the number of years to repay the loan and the loan amount.

Place a minus sign before the PMT function so that the value returned by the

function is positive rather than negative.

7.
Using AutoFill, copy the formula in cell D10 into the range E10:H10, and then copy

that range of formulas into the range D11:H20.

8.
Conditionally format the range D10:H20 to highlight all of the values in the range

that are less than the value in cell E3 in a dark green font on a green ﬁ ll.

9. Add a second conditional format to the range D10:H20 to highlight all of the values

in the range that are greater than the value in cell E3 in a dark red font on a red ﬁ ll.