excelwork

Excel Lesson 4

Overview

 Watch the Excel Lesson 4 Video

 Using the L4_Titan_Property Excel file, complete the worksheet using the instructions below saving it as “firstName_lastName_L4_Titan_Property”.

 Complete the Lesson 4 Quiz using the completed worksheet.

 Upload the competed “firstName_LastName_L4_Titan_Property” to the “Lesson 4 Excel Submission Link”.

Objectives

1. Use the PMT financial function 2. Create a two-variable data table 3. Perform Goal Seeking 4. Referencing with a worksheet

Lesson Instructions

Background: Titan Off-Campus Shops is investigating the feasibility of purchasing some commercial real

estate for their shop. You will be completing a Loan Analysis worksheet to help them make their decision. The

worksheet you will use is the L4_Titan_Property file. Open it and complete the following instructions. (Note:

when no specific cell reference is given, you are to use your best judgment based upon how the worksheet is

set up for you to decide what cells are involved or where a result should be placed.)

1. Re-save the file to either your desktop or other storage device using the name

“firstName_LastName_L4_Titan_Property”. (Note firstName and LastName are your own first and last

names).

2. The upper left area of the worksheet is a Payment Calculator. Use a function to calculate the monthly

payment (D7) using the data provided.

3. In the Payment Calculator, use “Goal Seek” to keep the payment per month at $3,000 by increasing the

down payment.

4. Copy the info from B4:B8 and paste to cells B10:B14. Copy D4:D7 and paste to D10:D13. Note:

Remember to use “Paste Special” and use Values and Number Formats.

5. Reset the value in B6 back to $75,000.

6. Perform a second “Goal Seek” in the Payment Calculator again keeping the payment per month at

$3,000 this time by lowering the property cost.

7. Copy the info from B4:B8 and paste to cells B16:B20. Copy D4:D7 and paste to D16:D19. See note in

step 4.

8. Reset the value in B5 back to $750,000.

9. The upper right area of the worksheet will display the Payment Options. Create a two-variable Data

Table to show the monthly payment for each of the interest rates by the number of monthly payments.

(Note: all values in each cell will be different. If they aren’t, you can erase the table by highlighting

cells H6: L20 and deleting them).

10. Format the numbers in your Data Table so the that top row is Accounting Style with 0 decimal places

and the remaining numbers are Comma Style with 0 decimal places.

Lesson 4, P a g e | 2

Project Complete.

 Take Lesson 4 Quiz.  Close and upload completed workbook to Lesson 4 Excel Submission Link.