Microsoft excel..

Office 2016 – myitlab:grader – Instructions Excel Project

EX16_XL_CH02_GRADER_ML2_HW - Car Calculator 1.5

Project Description: As a financial consultant, you work with a family that plans to purchase a $35,000 car. You want to create a worksheet containing variable data (the price of the car, down payment, date of the first payment, and borrower’s credit rating) and constants (sales tax rate, years, and number of payments in one year). Borrowers pay 5% sales tax on the purchase price of the vehicle and their credit rating determines the required down payment percentage and APR. Your worksheet needs to perform various calculations.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Open the downloaded file exploring_e02_grader_h3.xlsx. 0.000 2 Type Auto Loan Calculator in cell A1, and then merge and center the title on the first row in the range A1:F1. Apply bold, 18 pt font size, and Gold, Accent 4, Darker 25% font color. 5.000 3 Insert a function in cell C6 to display the current date. 5.000 4 Use the Format Painter to copy the formatting from the range A3:C3 to the ranges A9:C9, E3:F3, E9:F9, A14:C14. 5.000 5 Apply Percent Style with no decimal points to the range B15:B18. Apply Percent Style with two decimals points to the range C15:C18. 5.000 6 Type APR Based on Credit Rating in cell E4, Min Down Payment Required in cell E5, and Sales Tax in cell E6. 5.000 7 Enter the following labels: Total Down Payment in cell E10. Amount of the Loan in cell E11. Monthly Payment (P&I) in cell E12. Monthly Sales Tax in cell E13. 5.000 8 In cell F4, enter a Lookup function to display the interest rate of the loan. The function should reference the borrower’s credit rating and the table array in range A15:C18. Include the range_lookup argument to ensure an exact match. 10.000 9 In cell F5, insert a lookup function that uses the credit rating to determine the minimum down payment based on the table array A15:C18. Include the range_lookup argument to ensure an exact match. Multiply the function result by the negotiated cost of the vehicle. 10.000 10 In cell F6, multiply the negotiated cost of the vehicle by the sales tax rate to determine the sales tax. 5.000 11 In cell F10, add the minimum down payment located in cell F5 with the additional down payment located in cell C5 to determine the total down payment. 5.000 12 In cell F11, enter a formula to determine the difference between the negotiated cost of the vehicle and the total down payment. 5.000 13 In cell F12, use the PMT function to determine the periodic loan payment. Format the results to appear as a positive number. 15.000 14 In cell F13, enter a formula to calculate the monthly sales tax based on the total sales tax located in cell F6 and the total number of payments (C12*C11). 5.000 15 Enter a formula in cell F14 to determine the total monthly payment by adding the monthly payment in cell F12 and the monthly sales tax in cell F13. 10.000 16 Insert a footer with your name on the left side, the sheet name in the center, and the file name code on the right side of the sheet. 5.000 17 Save and close the workbook. Submit the file as directed. 0.000 Total Points 100.000

Updated: 08/30/2017 1 Current_Instruction.docx