I need someone to answer these so i can compare my spread sheet

E_CH07_EXPV2_IRCD_Instructions.docx

Office 2010 – myitlab:grader – Instructions Exploring Series Vol. 2, Chapter 7, IRCD

Motorcycle Purchase

Project Description: In this project, you will filter and analyze data based on multiple criteria, and then calculate the payments for a loan on a new motorcycle purchase.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Download, save, and open the Excel workbook named Exploring_e07_Grader_IRCD.xlsx. 0 2 In cell G4 of the Database worksheet, insert a nested function that will return the result Possibility if the first model listed was built after 2002 and has less than 30000 miles on it. Otherwise, the function should return No chance as the result. 10 3 Copy the function in G4 down through G14. 2 4 In cell E19, enter a function that will average the Sales Price for the range B3:G14 using the criteria in cells D19:D20. 10 5 In cell G19, enter a function that will find the lowest Sales Price over $7,000 for the range B3:G14 using the criteria in cells F19:F20. 10 6 Perform an advanced filter on the list in the range B3:G14 to find motorcycles built in 2007 or 2008. Use the criteria range C18:C20 and filter the data in-place. 10 7 Click the Payments sheet tab. You will purchase a 2008 Harley-Davidson for $7,200 and take out a loan for $5,760 for one year to pay for it. In cell B6, insert a PMT function to calculate the monthly payments using the information in B3:B5. 10 8 In cell D10, insert a formula that references the loan amount in cell B3. 3 9 In cell E10, enter an IPMT function that will return the monthly amount of interest as a positive value. Reference the loan information in B3:B5 and the payment period in B10. Set B5, B4, and B3 as absolute cell references. 10 10 In cell F10, enter a PPMT that will return the principal payment as a positive value. Reference the loan information in B3:B5 and the payment period in B10. Set B5, B4, and B3 as absolute cell references. 10 11 In cell G10, enter a formula that subtracts the first principal payment from the beginning balance. 4 12 In cell D11, enter a formula that references the ending balance in cell G10. Copy the formula in D11 down through D21. 5 13 To complete the loan amortization table, copy the functions in E10:G10 down through row 21. 6 14 In B7, enter a CUMIPMT function to determine how much interest you will pay for the entire length of the loan if interest is calculated at the end of each period. Reference the loan information in B3:B5 and the corresponding Payment number in the table for the start and end period arguments. 10 15 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Updated on: 11/19/2010 1 E_CH07_EXPV2_IRCD_Instructions.docx