Question

Bonus Info Threshold Domestic Look up 5 200,000 3% 596 Sales rep nameErica 03 Amount sold 2018 Sales Total By Quarter Years Worked Total Sales Rep Hire Date 8 Ron 9 Nick 33,947 $ 202,582 Internationa 29,911 $92,249$46,475S 32,752 30,222 43,997 93,277$ 200,248 International 36,991 $ 54,102$63,914 42,642 197,649 Domestic 50,087$ 25,179 $64,912 68,875 $ 209,053 Domestic 52,923 62,673 $63,635 57.410$ 236,641 Domestic 5/12/14 11 Susan 12 Bob 13 Mark 14 Swathi 15 Mike 16 Rick 7/20/17 10/6/10 11/3/09 8/4/10 6/9/09 4/16/12 1/7/09 3/25/14 32,994$ 242,016 Domestic 74,27036,165$ 76,548 $ 253,368 Domestic 63,324$ 263,577 Domestic 27,235 $ 237,311 Domestic 66,385 76,889 49,266 64,225 55,410 245,790 Domestic 90,515 $ 29,238 $ 30,973 32,145 $ 182,871 Domestic 18 Rich 19 20 Erica 250,000 Domestic 266,693 International 98,094 $47.39880,755 40,446 24 25 26 28 29 31 36 37 38 47 SalesDatabase Addition+
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_Sales.xlsx, and then save the file as exploring_e07_grader_a1_Sales_LastFirst, replacing LastFirst with your name.	0
2	On the Sales worksheet, enter a date function in cell C8 to calculate the number of years the first representative has worked for your company. Copy the function to the range C9:C20.	7
3	On the Sales worksheet, enter a nested function in cell J8 of the Bonus column to display the bonus amount. If the employee sold $200,000 or more (cell J2) AND is International (cell I4), he or she earns a 5% (cell J4) bonus on his or her total annual sales; otherwise, the representative earns 3% (cell J3) of his or her total annual sales. Use relative and mixed (or absolute) references correctly in the nested function. Copy the function from J8 to the range J9:J20.	7
4	Enter a nested lookup function in cell E4 that uses the cells E2 and E3 to return a specific sales record. For example, using the current data, you want the function to return the third quarter sales for Erica.	7
5	Click the Database worksheet tab and enter conditions in the Criteria Range for Domestic sales reps that made 240000 or more in sales.	4
6	Perform an advanced filter based on the criteria range. Set the filter to copy the new data to the range A22:G22.	8
7	In cell J7, enter a database function to calculate the number of reps meeting the criteria.	7
8	In cell J8, enter a database function to calculate the highest total sales for records meeting the criteria.	7
9	In cell J9, enter a database function to calculate the average sales for records meeting the criteria.	7
10	Click the Addition worksheet tab, and then insert a formula in cell E2 to calculate the loan amount based on the loan parameters.	4
11	In cell E5, enter a function to calculate the monthly payment. Modify the function to ensure that the result is a positive number.	5
12	In cell E6, enter a function to calculate the total interest paid after five payments. Modify the function to ensure that the result is a positive number. The formula will result in an error until the loan amortization table is completed.	7
13	In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. In cell C12, enter a relative reference to cell F11 and copy the formula to the range C13:C15.	4
14	In cell B12, insert a function to enter the payment date for the next month. Copy the function to the range B13:B15.	7
15	In cell D11, enter the financial function to calculate the interest paid for the first payment period. The result should be a positive value.	7
16	In cell E11, enter the financial function to calculate the principal payment for the first payment period. The result should be a positive value.	7
17	In cell F11, subtract the principal payment in cell E11 from the beginning balance in cell C11. Copy the functions and formulas from the range D11:F11 to the range D12:F15.	5
18	Save the file making sure the worksheets are in the following order: Sales, Database, and Addition. Close Excel. Submit the file as directed.	0
	Total Points	100

Answer 1

2.

The formula for C8: =YEARFRAC(B8,TODAY()) or, =YEAR(TODAY())-YEAR(C4)? (if you don't want fraction)

Then it will be copied till C20

3.

The formula for H8: =IF(AND($J$2>=200000,I8=$I$4),$J$4*H8,$J$3*H8)

Then it will be copied till H20.

4.

The formula for E4: =VLOOKUP(E2,A8:H20,MATCH(E3,A7:G7,0),FALSE)

5.

Enter in G3: Domestic and in F3: 240000