Excel Homework 5 --- Fall 2017

Project Description:

Project Description: In this project, you will apply the Excel skills you learned in Excel chapters 1 and 2. More specifically, you will be applying your problem-solving skills by writing formulas and using Excel's VLOOKUP and PMT functions.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	NOTE: To minimize chances for confusion and misinterpretation of the instructions, when working with only the Instructions documents downloaded from MyITLab, either do NOT enable editing (do NOT click the Enable Editing button at the top) or always do the following: 1. Click the File tab, and then click Options 2. Click Proofing 3. Scroll down to locate the label Exceptions for: 4. Under the Exceptions for: label, check the last two checkboxes: Hide spelling errors in this document only checkbox Hide grammar errors in this document only checkbox 5. Click the OK button to close the Word Options dialog box NOTE: As a general rule, do NOT set/change any properties/parameters/settings unless asked specifically and explicitly by specific instructions. NOTE: As a general rule, any text/content between [ and ] characters must be typed literally and exactly as given. When typing text/content between [ and ] characters, do NOT type the [ and ] characters. NOTE: For those steps requiring use of the PMT function, always assume payments are made at the end of each period. NOTE: If and when asked to save a file/document as _YourFirstName_YourLastName , always replace the YourFirstName part with your real first name and the YourLastName part with your real last name.	0.000
2	Download and open the file named Excel_Homework_5.xlsx. Save the file, changing the file name to [ Excel_Homework_5_YourFirstName_YourLastName.xlsx ].	0.000
3	Switch to the Commission worksheet.	0.000
4	Assign the name [ Commissions_Table ] to the range E7:G13.	5.000
5	Use the VLOOKUP function in cell C2 to calculate the commission amount (dollar amount of commission) for the first salesperson based on the information in cells E7:G13. Hint: You need to use the VLOOKUP function and perform some calculations too.	20.000
6	Copy the function/formula in cell C2 to cells C3 through C7.	1.000
7	Switch to the 15-Year Loan worksheet.	0.000
8	Monthly Scenario: Write a formula in cell B12 to calculate the dollar amount of loan based on the information in cells B5 and B6. NOTE: You must write your own formula. Do NOT use functions.	7.000
9	Monthly Scenario: Use the PMT function in cell B13 to calculate required monthly payments based on the information in cells B7:B9 and B12. Note: You must use the proper function. Do NOT write your own formula. Note: Payments are made at the end of each period.	20.000
10	Monthly Scenario: Write a formula in cell B14 to calculate the total dollar amount to repay the loan (loan plus the interest). NOTE: You must write your own formula. Do NOT use functions.	10.000
11	Monthly Scenario: Write a formula in cell B15 to calculate the total dollar amount of interest paid. NOTE: You must write your own formula. Do NOT use functions.	10.000
12	Quarterly Scenario: Repeat steps 8, 9, 10 and 11 above and write formulas and/or use the PMT function in cells G12:G15 based on the information for the Quarterly Scenario in cells G5:G9.	12.000
13	Create a copy of the 15-Year Loan worksheet. Rename the copy to [ 30-Year Loan ]. Place the 30-Year Loan worksheet before the 15-Year Loan worksheet and after the Commission worksheet.	7.000
14	Switch to 30-Year Loan worksheet. Type [ 30 ] in cells B8 and G8 of the 30-Year Loan worksheet and press the Enter key. Although the monthly payment is reduced when you pay the loan over 30 years, compare the total loan repayment and the total interest paid with the same values in the 15-Year Loan worksheet. Which loan repayment schedule would you select?	8.000