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.
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.
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.
5	Click the Database worksheet tab and enter conditions in the Criteria Range for Domestic sales reps that made 240000 or more in sales.
6	Perform an advanced filter based on the criteria range. Set the filter to copy the new data to the range A22:G22.
7	In cell J7, enter a database function to calculate the number of reps meeting the criteria.
8	In cell J8, enter a database function to calculate the highest total sales for records meeting the criteria.
9	In cell J9, enter a database function to calculate the average sales for records meeting the criteria.
10	Click the Addition worksheet tab, and then insert a formula in cell E2 to calculate the loan amount based on the loan parameters.
11	In cell E5, enter a function to calculate the monthly payment. Modify the function to ensure that the result is a positive number.
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.
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 F11and copy the formula to the range C13:C15.
14	In cell B12, insert a function to enter the payment date for the next month. Copy the function to the range B13:B15.
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.
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.
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.
18	Save the file making sure the worksheets are in the following order: Sales, Database, and Addition. Close Excel. Submit the file as directed.

Sharp_exploring_e07_grader_al_Sales Q- Search Sheet = Page Layout Formulas Data Calibri (Body) - 11 -A- A D D Home Insert A, Xcut Copy Format 2+ Share Review = = View = der Ep Wrap Text General F D D - 3*, Autosum AS, Paste BI UNIA Merge & Center $ % ) 000 Insert Delete Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter Check for Updates * Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. A1 x V fx Report Date A B C D E F G H I 1 Report Date Look up Bonus Info 2 /1/18 Sales rep name Erica Threshold $ 200,000 Quarter 03 Domestic L3% Amount sold International L 5% M N O P Q R S 2 Bonus 7 Sales Rep 8 Ron 9 Nick 10 Sally 11 Susan 12 Bob 13 Mark 14 Swathi 15 Mike 16 Rick 17 Jill 18 Rich 19 Daryl 20 Erica Hire Date Years Worked 3/1/16 5/12/14 3/14/17 7/20/17 $ 10/6/10 11/3/09 $ 8/4/10 $ 6/9/09 $ 4/16/12 $ 1/7/09 IS 3/25/14 $ 8/15/11 8/15/11 IS 3/15/13 $ 2018 Sales Total By Quarter 01 Q2 Q3 Q4 29,911 $ 92,249 $ 46,475 $ 33,947 $ 32,752 $ 30,222 $ 43,997 $ 93,277 $ 36,991 $ 54,102 $ 63,914 $ 42,642 $ 50,087 $ 25,179 $ 64,912 $ 68,875 $ 52,923 $ 62,673 $ 63,635 $ 57,410 $ 59,678 $ 70,934 $ 78,410 $ 32,994 $ 66,385 $ 74,270 $ 36,165 $ 76,548$ 66,936 $ 72,838 $ 60,479 $ 63,324 $ 74,507 $ 94,178 $ 41,391 $ 27,235 $ 76,889 $ 49,266 $ 64,225 $ 55,410 $ 90,515 $ 29,238$ 30,973 $ 32,145 $ 97,426 97420 $ 43,061 $ 26,122 $ 83,391 $ 98,094 $ 47,398 $ 80,755 $ 40,446 $ Total Location 202,582 International 200,248 International 197,649 Domestic 209,053 Domestic 236,641 Domestic 242,016 Domestic 253,368 Domestic 263,577 Domestic 237,311 Domestic 245,790 Domestic 182,871 Domestic 250,000 Domestic 266,693 International 41 Sales Database Addition + Ready 0 - - + 100% Sharp_exploring_e07_grader_al_Sales Q- Search Sheet = Page Layout Formulas Data Calibri (Body) - 11 -A- A D D Home Insert A, Xcut Copy Format 2+ Share Review = = View = der Ep Wrap Text General F D D - 3*, Autosum AS, Paste BI UNIA Merge & Center $ % ) 000 Insert Delete Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter Check for Updates * Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. A1 x V fx Report Date A B C D E F G H I 1 Report Date Look up Bonus Info 2 /1/18 Sales rep name Erica Threshold $ 200,000 Quarter 03 Domestic L3% Amount sold International L 5% M N O P Q R S 2 Bonus 7 Sales Rep 8 Ron 9 Nick 10 Sally 11 Susan 12 Bob 13 Mark 14 Swathi 15 Mike 16 Rick 17 Jill 18 Rich 19 Daryl 20 Erica Hire Date Years Worked 3/1/16 5/12/14 3/14/17 7/20/17 $ 10/6/10 11/3/09 $ 8/4/10 $ 6/9/09 $ 4/16/12 $ 1/7/09 IS 3/25/14 $ 8/15/11 8/15/11 IS 3/15/13 $ 2018 Sales Total By Quarter 01 Q2 Q3 Q4 29,911 $ 92,249 $ 46,475 $ 33,947 $ 32,752 $ 30,222 $ 43,997 $ 93,277 $ 36,991 $ 54,102 $ 63,914 $ 42,642 $ 50,087 $ 25,179 $ 64,912 $ 68,875 $ 52,923 $ 62,673 $ 63,635 $ 57,410 $ 59,678 $ 70,934 $ 78,410 $ 32,994 $ 66,385 $ 74,270 $ 36,165 $ 76,548$ 66,936 $ 72,838 $ 60,479 $ 63,324 $ 74,507 $ 94,178 $ 41,391 $ 27,235 $ 76,889 $ 49,266 $ 64,225 $ 55,410 $ 90,515 $ 29,238$ 30,973 $ 32,145 $ 97,426 97420 $ 43,061 $ 26,122 $ 83,391 $ 98,094 $ 47,398 $ 80,755 $ 40,446 $ Total Location 202,582 International 200,248 International 197,649 Domestic 209,053 Domestic 236,641 Domestic 242,016 Domestic 253,368 Domestic 263,577 Domestic 237,311 Domestic 245,790 Domestic 182,871 Domestic 250,000 Domestic 266,693 International 41 Sales Database Addition + Ready 0 - - + 100%