Office 2013 MyITLab MS-Excel Chapter 7 Assessment Project 1 Specialized Functions
In the following project, you will perform sales analysis, calculate summary data using database functions, and complete an amortization table.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
| Step | Instructions | Points Possible |
| 1 | Download and open the file named exploring_e07_grader_a1.xlsx, and then save the file as e02c1Sales_LastFirst, replacing LastFirst with your name. | 0 |
| 2 | Click the Sales Data by Agent worksheet and enter a nested function in cell H9 the Bonus column. If the employee is international and sold over $200,000 they receive 5% bonus, all other employees receive 3%. | 10 |
| 3 | Using the appropriate cell referencing, copy the function down the column. | 7 |
| 4 | Type Ron in cell B24. | 4 |
| 5 | Type Q1 in cell B25. | 4 |
| 6 | Enter a nested function in cell B26 that uses the cells B24 and B25 to return a specific sales record. | 10 |
| 7 | Click the Individual Awards worksheet and enter conditions in the Criteria Range for international sales reps that made $250,000 or more in sales. | 10 |
| 8 | Perform an advanced filter based on the criteria range. Set the filter to copy the new data into row 22. | 10 |
| 9 | Enter a database function to calculate the total number of international sales rep in cell J8. | 12 |
| 10 | Enter a database function to calculate the highest international sales dollar in cell J9. | 3 |
| 11 | Click the Acquisition worksheet and then insert a function in cell E2 to calculate the loan amount based on the loan parameters. | 4 |
| 12 | Enter a formula in cell E3 to calculate the total number of periods. | 2 |
| 13 | Enter a formula in cell E4 to calculate the periodic monthly rate. | 2 |
| 14 | Enter a function in cell E5 to calculate the monthly payment. Modify the function to ensure that the result is a positive number. | 2 |
| 15 | Enter a function in cell E6 to calculate the total interest paid after five payments. Modify the function to ensure that the result is a positive number. | 2 |
| 16 | Complete the loan amortization table for the first five payments only. In cell A11, enter 1. In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns. In cell F11, enter =C11-E11. In cell C12, create a relative reference to cell F11. Note: Be sure to only complete the table through row 15. | 18 |
| 17 | Save the file making sure the worksheets are in the following order: Sales Data By Agent, Individual Awards, and Acquisition. Close Excel. Submit the file as directed. | 0 |
- File Format: MS-Excel .xlsx
- Version: 2013