Gradestructions Browl 2018 Proje EX16_XL_CH07_GRADER_CAP_AS - Sales Data 1.8 Project Description: Your assistant created a spreadsheet that its names, hire dates, quarterly sales and total sales for 2018. You will calculate the number of years each representative has worked and each representative's bonus. In addition, you will create a nested lookup function to look up a person's name and particular quarter to identify the sales for that person Nextyou will perform advanced database hering and use summary database functions. Finally, you will complete an amortization table to build a small addition to your office complex Steps to Perform: Step Points Possible Download and open the file named exploring 07 grader at Sales xlsx, and then save the fie as exploring 07 grader at Sales LastFirst, replacing Lasist with your name 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 On the Sales worksheet, enter a nested function in cells of the Bonus column to display the bonus amount of the employee sold $200,000 or more collJ2) AND is International foot 14). he or she cams a 5% (ool J4) bonus on his or her total annual sales; otherwise, the representative cams 3 cod J3) of his or her total annual sales. Use relative and mixed for absolute) references correctly in the nested function Copy the function from JB to the range J9:J20 Enter a nosted lookup function in cell 4 that uses the cells E2 and 3 to return a specife sales record. For example, using the current data you want the function to return the third quarter sales for Erica Click the Database worksheet tab and enter conditions in the Criteria Range for Domestic sales reps that made 240000 or more in sales. Perform an advanced based on the criteria range. Set the site to copy the new data to the range A22 G22 In call J enter a database function to calculate the number of rope meting the criteria In cell enter a database function to calculate the highest total sales for records meeting the criteria In cell J, enter a database function to calculate the average sales for records meeting the criteria Click the Addition worksheet tab, and then insert a formula in coll 2 to calculate the loan amount based on the loan parameters incall Esenter a function to colate the monthly payment. Modify the action to ensure the result is a positive number incel E nter a conto coate the total interest paid her tve ts Modify the function to ensure that the result is a positive number Fo States ader - Instructions Excel 2016 Project Step Instructions Points Possible 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.015. 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 In cell E11, enter the financial function to calculate the principal payment for the first payment period. The result should be a positive value 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 D12F15. 18 Save the file making sure the worksheets are in the following order: Sales, Database, and Addition. Close Excel Submit the file as directed Total Points 100 Focl ne Insert Draw Page Layout Formulas Data Review View Analytic Solver X Calibri (Body) 11 P AA BOVEN CUAL Conditional Formatting Format as Table Cell Styles Alignment Number Report Date 2/1/18 Erica $ 200,000 Lookup Sales rep name Quarter Amount sold Bonus Info Threshold Domestic International 5% 7 Sales Rep Ron 9 Nick 10 Sally 11 Susan 12 Bob 13 Mark Hire Date Years World 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 3/25/14 8/15/11 2018 Sales Total By Quarter 03 Te Location 29,911 S 92,249 S 46.475 $ 33,947 S 202,582 International 32,752 $ 30,222 $ 43,997$ 93,277 $ 200,208 internation 36,991 S 54,102 $ 63,914 S 42.642S 197 649 Domestic 50,087 25.179 S 54,912 68.875 $ 209,053 Domestic 52.923S 62, 673 S 63.635 $ 57,410 S 236,641 Domestic 59,678 S 70,934 S 410 $ 32,994 S 242,016 Domestic 66,385 74270S 36,165 76.5 S 253.368 Domestic 66.9365 72.838S9.479S631 2 6357 Domestic 74,507 S 94,178 S 41,391 5 2 7,235 237,311 Domestic 76.889S 49,2665 64,225 55,410 245,790 Domestic 90.515$ 29,230 S 30,9735 32.1455 182,871 Domestic 92.4265 01.051 S 26.122 S 1,191 S 250.000 Domestic 98. IS47.39S 0.55 IS 40446 IS266.693 linternation SA Swahi 15 Mike Sales Database Addition 2013 bu ZU Alignment Number Format as Table v Cell Styles Editing 1 x v fx Criteria Range A Criteria Range Sales Rep 01 02 03 04 Total 0 Database 5 Sales Rep 7 Ron S & Nick $ Total 202,582 Inter 200,248 Internationa 197,649 Do 209,053 Domestic 236,641 Domestic 242,016 Domestic Number of Reps Meeting Criteria Highest Annual Sales for Criteria Average Annual Sales for Criteria 11 Bob 12 Mark Swathi 1 02 03 04 29,911 S 92.249 S 46.475 S 33,947 $ 32,752 $ 30,222S 43.997 $ 93277 S 54.102 S 53,9141S 50, 07 $ 25,179S 4,912 $ 68,875 $ 52,923 $ 62,673 $ 63,635 $ 57,410 $ 59,678 $ 70,936 S 78.410 S 32,994 $ 66,3855 74.270 S 35,165 S 76,54 S 72,838 $ 60,4795 63320 S 74,507594170S41 1915 27.235 26.8 IS 492665 64225S 55410 90.5155 29,23851097352,145 97,426 43,0615 26.1225 83,391$ 98,094 S 47,398 S 80,755 $ 40.446 S 1$ $ $ S 15 Rick $ 263,577 Domestic 237,311 Domestic 245,750 Domestic 182,871 Dame S SUNNNNNNNNNBEEREEEE 21 Output Range Sales Rep 01 02 03 04 Total Sales Database Addition + 1001109_101RTO A X v fx Input Area A1 AB 1 Input Area 2 Facility Costs $ 720,000 3 Down Payment $ 250,000 4 # of Pmts per Year 12 5 Years 6 APR 5.25% 7 1st Payment Date 4/9/18 Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Payment Total Interest Paid 360 0.44% Payment # Payment Date Beginning Balance Interest Paid Principal Payment Ending Balance Sales Database Addition Gradestructions Browl 2018 Proje EX16_XL_CH07_GRADER_CAP_AS - Sales Data 1.8 Project Description: Your assistant created a spreadsheet that its names, hire dates, quarterly sales and total sales for 2018. You will calculate the number of years each representative has worked and each representative's bonus. In addition, you will create a nested lookup function to look up a person's name and particular quarter to identify the sales for that person Nextyou will perform advanced database hering and use summary database functions. Finally, you will complete an amortization table to build a small addition to your office complex Steps to Perform: Step Points Possible Download and open the file named exploring 07 grader at Sales xlsx, and then save the fie as exploring 07 grader at Sales LastFirst, replacing Lasist with your name 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 On the Sales worksheet, enter a nested function in cells of the Bonus column to display the bonus amount of the employee sold $200,000 or more collJ2) AND is International foot 14). he or she cams a 5% (ool J4) bonus on his or her total annual sales; otherwise, the representative cams 3 cod J3) of his or her total annual sales. Use relative and mixed for absolute) references correctly in the nested function Copy the function from JB to the range J9:J20 Enter a nosted lookup function in cell 4 that uses the cells E2 and 3 to return a specife sales record. For example, using the current data you want the function to return the third quarter sales for Erica Click the Database worksheet tab and enter conditions in the Criteria Range for Domestic sales reps that made 240000 or more in sales. Perform an advanced based on the criteria range. Set the site to copy the new data to the range A22 G22 In call J enter a database function to calculate the number of rope meting the criteria In cell enter a database function to calculate the highest total sales for records meeting the criteria In cell J, enter a database function to calculate the average sales for records meeting the criteria Click the Addition worksheet tab, and then insert a formula in coll 2 to calculate the loan amount based on the loan parameters incall Esenter a function to colate the monthly payment. Modify the action to ensure the result is a positive number incel E nter a conto coate the total interest paid her tve ts Modify the function to ensure that the result is a positive number Fo States ader - Instructions Excel 2016 Project Step Instructions Points Possible 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.015. 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 In cell E11, enter the financial function to calculate the principal payment for the first payment period. The result should be a positive value 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 D12F15. 18 Save the file making sure the worksheets are in the following order: Sales, Database, and Addition. Close Excel Submit the file as directed Total Points 100 Focl ne Insert Draw Page Layout Formulas Data Review View Analytic Solver X Calibri (Body) 11 P AA BOVEN CUAL Conditional Formatting Format as Table Cell Styles Alignment Number Report Date 2/1/18 Erica $ 200,000 Lookup Sales rep name Quarter Amount sold Bonus Info Threshold Domestic International 5% 7 Sales Rep Ron 9 Nick 10 Sally 11 Susan 12 Bob 13 Mark Hire Date Years World 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 3/25/14 8/15/11 2018 Sales Total By Quarter 03 Te Location 29,911 S 92,249 S 46.475 $ 33,947 S 202,582 International 32,752 $ 30,222 $ 43,997$ 93,277 $ 200,208 internation 36,991 S 54,102 $ 63,914 S 42.642S 197 649 Domestic 50,087 25.179 S 54,912 68.875 $ 209,053 Domestic 52.923S 62, 673 S 63.635 $ 57,410 S 236,641 Domestic 59,678 S 70,934 S 410 $ 32,994 S 242,016 Domestic 66,385 74270S 36,165 76.5 S 253.368 Domestic 66.9365 72.838S9.479S631 2 6357 Domestic 74,507 S 94,178 S 41,391 5 2 7,235 237,311 Domestic 76.889S 49,2665 64,225 55,410 245,790 Domestic 90.515$ 29,230 S 30,9735 32.1455 182,871 Domestic 92.4265 01.051 S 26.122 S 1,191 S 250.000 Domestic 98. IS47.39S 0.55 IS 40446 IS266.693 linternation SA Swahi 15 Mike Sales Database Addition 2013 bu ZU Alignment Number Format as Table v Cell Styles Editing 1 x v fx Criteria Range A Criteria Range Sales Rep 01 02 03 04 Total 0 Database 5 Sales Rep 7 Ron S & Nick $ Total 202,582 Inter 200,248 Internationa 197,649 Do 209,053 Domestic 236,641 Domestic 242,016 Domestic Number of Reps Meeting Criteria Highest Annual Sales for Criteria Average Annual Sales for Criteria 11 Bob 12 Mark Swathi 1 02 03 04 29,911 S 92.249 S 46.475 S 33,947 $ 32,752 $ 30,222S 43.997 $ 93277 S 54.102 S 53,9141S 50, 07 $ 25,179S 4,912 $ 68,875 $ 52,923 $ 62,673 $ 63,635 $ 57,410 $ 59,678 $ 70,936 S 78.410 S 32,994 $ 66,3855 74.270 S 35,165 S 76,54 S 72,838 $ 60,4795 63320 S 74,507594170S41 1915 27.235 26.8 IS 492665 64225S 55410 90.5155 29,23851097352,145 97,426 43,0615 26.1225 83,391$ 98,094 S 47,398 S 80,755 $ 40.446 S 1$ $ $ S 15 Rick $ 263,577 Domestic 237,311 Domestic 245,750 Domestic 182,871 Dame S SUNNNNNNNNNBEEREEEE 21 Output Range Sales Rep 01 02 03 04 Total Sales Database Addition + 1001109_101RTO A X v fx Input Area A1 AB 1 Input Area 2 Facility Costs $ 720,000 3 Down Payment $ 250,000 4 # of Pmts per Year 12 5 Years 6 APR 5.25% 7 1st Payment Date 4/9/18 Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Payment Total Interest Paid 360 0.44% Payment # Payment Date Beginning Balance Interest Paid Principal Payment Ending Balance Sales Database Addition