Exploring - Excel Chapter 7: Assessment Project 1

Exploring - Excel Chapter 7: Assessment Project 1

Specialized Functions
Project Description:
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

Points
Possible

Instructions

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.

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

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

Updated: 07/17/2013

1

0

10

E_CH07_EXPV2_A1_Instructions.docx

Office 2013 – myitlab:grader – Instructions

Step

Exploring - Excel Chapter 7: Assessment Project 1

Points
Possible

Instructions

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.

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.
Total Points

Updated: 07/17/2013

2

18

0

100

E_CH07_EXPV2_A1_Instructions.docx