Level 3 ? Creating a Loan Application and Amortization Schedule for CKG Auto

CKG Auto?s Financial Department has asked you to finish developing an Excel workbook that it plans to use to use to analyze automotive credit application data stored in individual XML documents. The workbook is based on an existing credit analysis spreadsheet. The work book will be used to calculate loan payment information and create a loan payment information and create a loan authorization schedule for customers purchasing new and used vehicles from dealerships that provide financing options through CKG auto. The Smith.xml file contains simple data that you will import into the spreadsheet to test the workbook.

Complete the following

• Open the Credit.xlsx file from the Chapter 7 folder, and the save it as Automotive Credit Application.xlsx.
• Use the XML Source task pane to create an XML source map using the Smith.xml file in the Chapter 7 folder
• Map the elements in the XML Source task pane to the appropriate cells in the worksheet by dragging them from the XML source task pane and dropping them into the appropriate cells in the worksheet.
• Use the Developer tab to map and import the appropriate elements from the Smith.xml document to the blank cells for the Application Information, Loan Details, and Vehicle information sections in the Automotive Credit Application worksheet.
• Insert the appropriate function and /or formula to calculate the monthly car payment.
• Prepare an amortization schedule based on the information imported from the XML document.
• Save and close the Automotive Credit Application.xlsx workbook.

Automotive Credit Application Applicant Information Applicant Name Application ID Request Date Loan Details Product ID Amount Financed Term Interest Rate Calculated Payment Create formula here Vehicle Information Status Year Make Model VIN Stock Number Trim Doors Body Style Interior Color Exterior Color Vehicle Price Mileage Amortization Schedule Month Payment Principal Interest 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 n Schedule $ Balance - 2013-04-13 2348484 New 2013 Jones Motors FJ-40 CF49595GGH904 WE339494-697970-33 EX 2 SUV Cashmere Salsa 49995 14 Sally Smith s_smith@xyz.com 812-555-1234 012-56-2290 1967-08-13

1213 Oak St. Smithville Indiana 47425

XYZ Corporation 120000 CEO Engineer Consulting S812-555-8985 3345 45000 60 0.0385 >