EX16_XL_CH02_GRADER_ML2_HW -Car Calculator 1.5 Project Description: As a finavncal cansutant you work with a famly that plans to purchase a $35,000 car, You want to create a worksheet containing vaniable data (the price of the car,don payment, date of the irst payment, and borrowers creit 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 warksheet needs to perform various calculations In cell F6, multiply the regotiabed cost of the vehide by the sales tax rate to determine the sales tax. 10 5.000 In cell F10, add the minimum down payment located in cl FS with the addtonal down 5.000 payment located in cel CS to determine the total down payment. In cal F11, enter a formula to datermine the difference betwoen the negotiatad cost of the chicle and the total down payment. 5.000 For the purpose of grading the project you are required to perform the following tasks 13Incell F12, use the PMT function to determine the pericdic Ican payment. Format the resuits 15.000 to appear as a pestve number 1 Open the downloaded file explaring.e02 grader h ex. 0.000 14 Incll F13, enter a formula to calculate the monthly sas tax based on the total ses ta S.000 ocated in cel FG and the total number of payments (C12 C11) Type Auto Loan Calculator in cel A1, and then merge and center the te on the first row in the range A1:FL Apply bold, 18 gg fontsize, and Gold, Accert4, Darker 25% font color. 5.000 Enter a formula in cel F14 to determine the total monthly payment by adding the monthly peyment in cel F12 and the monthiy sales tax in cell F13 15 10.000 3 Insert a functicn in call C6 to display the current datc. 5.000 Irsert a footer with your name on the left side, the sheet name in the center, and the file nerne code on the right side of the sheet 5.000 Use the Fomat Painter to copy the formatting from the range A3 C3 to the ranges AS C9 5.000 Save and clase the workbook. Submit the file as cirected 0.000 Apply Percent Sty'e with no decimal points to the range B15:B18. Apply Percent Style with two decimals points to the range C15:C18. 5.000 Total Points 100.000 Type APR Based on Credit Rating in coll E4, Min Down Payment Required in cell ES, and Sales Tax in ccll E6. 5.000 Total Down Payment in cell E10 Amount of the Loan in cell E11 Monthly Payment (P&I) in cell 12 5.000 Sales Tax in cell E13. ghoud0.000 In cel F4, enter a Lookup function to display the interest rate of the loan. The function shouk reference the borrower's credt rating and the table array in range A15:C18. Indude the acce lookun argument to ensure an eact match In cel F5, ingert a lookup function that uoes the credit rating to determine the minimum down 9 payment baoed on the table array A1S Ci8. Indlude the rag Jookup argument to ensure an 10.000 exact match Mutply the function result by the negotiated cot of the vehicle
