Grader - Instructions Excel 2016 Project EX16_XL_CH07_GRADER_CAP_AS - Sales Data 1.6 Project Description: Your assistant created a spreadsheet that lists 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. Next, you will perform advanced database filtering 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 Instructions 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 Created On: 11/06/2018 1 EX16_XL_CH07_GRADER_CAP_AS - Sales Data 1.6 Grader - Instructions Excel 2016 Project Points Possible Step Instructions 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 Created On: 11/06/2018 2 100 EX16_XL_CH07_GRADER_CAP_AS - Sales Data 1.6 Report Date 2/1/2018 Look up Sales rep name Quarter Amount sold Erica Q3 2018 Sales Total By Quarter Sales Rep Ron Nick Sally Susan Bob Mark Swathi Mike Rick Jill Rich Daryl Erica Hire Date Years Worked 3/1/2016 5/12/2014 3/14/2017 7/20/2017 10/6/2010 11/3/2009 8/4/2010 6/9/2009 4/16/2012 1/7/2009 3/25/2014 8/15/2011 3/15/2013 $ $ $ $ $ $ $ $ $ $ $ $ $ Q1 29,911 32,752 36,991 50,087 52,923 59,678 66,385 66,936 74,507 76,889 90,515 97,426 98,094 $ $ $ $ $ $ $ $ $ $ $ $ $ Q2 92,249 30,222 54,102 25,179 62,673 70,934 74,270 72,838 94,178 49,266 29,238 43,061 47,398 $ $ $ $ $ $ $ $ $ $ $ $ $ Q3 46,475 43,997 63,914 64,912 63,635 78,410 36,165 60,479 41,391 64,225 30,973 26,122 80,755 $ $ $ $ $ $ $ $ $ $ $ $ $ Q4 33,947 93,277 42,642 68,875 57,410 32,994 76,548 63,324 27,235 55,410 32,145 83,391 40,446 Bonus Info Threshold Domestic International $ 200,000 3% 5% rter $ $ $ $ $ $ $ $ $ $ $ $ $ Total 202,582 200,248 197,649 209,053 236,641 242,016 253,368 263,577 237,311 245,790 182,871 250,000 266,693 Location International International Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic International Bonus Criteria Range Sales Rep Q1 Q2 Q3 Q4 Total Location Q1 29,911 32,752 36,991 50,087 52,923 59,678 66,385 66,936 74,507 76,889 90,515 97,426 98,094 Q2 92,249 30,222 54,102 25,179 62,673 70,934 74,270 72,838 94,178 49,266 29,238 43,061 47,398 Q3 46,475 43,997 63,914 64,912 63,635 78,410 36,165 60,479 41,391 64,225 30,973 26,122 80,755 Q4 33,947 93,277 42,642 68,875 57,410 32,994 76,548 63,324 27,235 55,410 32,145 83,391 40,446 Total 202,582 200,248 197,649 209,053 236,641 242,016 253,368 263,577 237,311 245,790 182,871 250,000 266,693 Location International International Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic International Total Location Database Sales Rep Ron Nick Sally Susan Bob Mark Swathi Mike Rick Jill Rich Daryl Erica $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Output Range Sales Rep Q1 Q2 Q3 Q4 Summary Number of Reps Meeting Criteria Highest Annual Sales for Criteria Average Annual Sales for Criteria Input Area Facility Costs Down Payment # of Pmts per Year Years APR 1st Payment Date Payment # 1 2 3 4 5 $ $ Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Payment Total Interest Paid 720,000 250,000 12 30 5.25% 4/9/2018 Payment Date Beginning Balance Interest Paid 360 0.44% Principal Payment Ending Balance
Purchase answer to see full attachment