Level 3 Creating a Loan Application and Amortization Schedule for CKG Auto CKG Autos Financial Department has asked you to finish developing an Excel workbook that it plans 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 amortization schedule for customers purchasing new and used vehicles from dealerships that provide financing options through CKG Auto. The Smith.xml file contains sample data that you will import into the spreadsheet to test the workbook. Complete the following:

1. Open the Credit.xlsx file from the Chapter 7 folder, and then save it as Automotive Credit Application.xlsx.

2. Use the XML Source task pane to create an XML map using the Smith.xml file in the Chapter 7 folder.

3. Map the elements in the XML Source task pane to the appropriate cells in the work- sheet by dragging them from the XML Source task pane and dropping them into the appropriate cells in the worksheet.

4. 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.

5. Insert the appropriate function and/or formula to calculate the monthly car payment in cell B13.

6. Prepare an amortization schedule based on the information imported from the XML document.

7. Save and close the Automotive Credit Application.xlsx workboo

Automotive Credit Application Amortization Se hedule licant Infor mation Month Payment Principal Interest Balance pplicant Name lication ID Request Date Loan Details Product ID Amount Financed Interest Rate Calculated Payment Vehicle Information Status Make Model Stock Number Doors Body Stvle Interior Color Exterior Color Vehicle Price 34 46 60 Automotive Credit Application Sheet2 She