EX16_XL_CH07_GRADER_CAP_AS - Sales Data 1.6

This is an excel grader project. Please include a detailed explanation of steps and multiple screenshots of the excel spreadsheets. That would be amazing and would definitely give you a thumbs up!! I am currently using Excel 2016 so that is the preference. I thank you a bunch in advance :]]]

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 representatives bonus. In addition, you will create a nested lookup function to look up a persons 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	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

1115 e Copy Ped Copy 11 u -A Merge & Cente Conditional Format as Cell Insert Delete Form Formatting Table Styles . % , 5 -, Format Painter Clipboard Font Number Styles A1 ixf Report Date Look up Sales rep name Erica Bonus Info Threshold 200,000 Domestic International 03 Amount sold 5% 2018 Sales Total By Quarter 01 Hire Date Years Worke Q3 Q4 Total Location Bonus S 29,911 $ 92,249 46,475 33,947 $ 202,582 International $ 32,752 $ 30,222 s 43,997 s 93,277 S 200,248 International 36,991 $ 54,102 $ 63,914$ 42,642$ 197,649 Domestic 3/1/2016 9 5/12/2014 10 3/14/2017 17/20/2017 12 10/6/2010 13 11/3/2009 4 8/4/2010 6/9/2009 6 4/16/2012 17: 1/7/2009 3/25/2014 19 8/15/2011 20 3/15/2013 8 S 50,087 25,179$ 64,912S 68,875 $ 209,053 Domestic 52,923 62,673S 63,635 57,410 236,641 Domestic 59,678 S 70,934 S 78410 32,994 $ 242,016 Domestic 66,385 74,270 36,165 S 76,548 S 253,368 Domestic 76,889 S 49,266 64,225 55,410 S 245,790 Domestic 0,515 S 29,238 $ 30,973 S 32,145 182,871 Domestic 98,094|$ 47, 398l s 80,755|$ 40,446|$ 266,693 Internationa S 97,426 S 43,061S 26,122 83,391S 250,000 Domestic Sales Database Addition Font Alignm Styles A1 Criteria Range Criterial Range Sales Rep Q1 02 Q3 Total Location s Database Sales Rep 7 Ron 8 Nick Q1 02 03 Total Location Summary Number of Reps Meeting Criteria Highest Annual Sales for Criteria Average Annual Sales for Criteria 04 $ 29,911 92,249$46,475 $ 33,947 $ 202,582 International 32,752 $ 30,222 $ 43,997 $ 93,277S 200,248 International 9 Sally$ 36,991$ 54,102$ 63,914 S 42,642 S 197,649 Domestic Susan 50,087 25,179 64,912$ 68,875 209,053 Domestic S 52923 62,673 63,635 $ 57410 236,641 Domestic 59,678S 70,934 78,410 32,994 242,016 Domestic a Swathi S 66,385 S 74,270 $ 36,165 S 76,548 S 253,368 Domestic 14 Mike 66,936 72,838S 60,479 63,324 S 263,577 Domestic 74,507 S 94,178 S 41,391 S 27,235S 237,311 Domestic 76,889 $ 49,266 S 64,225 55,410 245,790 Domestic 90,515 $ 29,238 30,973 s 32,145 S 182,871 Domestic 97,426 43,061 26,122 $ 83,391 250,000 Domestic 11 Bob 12 Mark 15 Rick 16 Jill 7 Rich 98,094 s 47,398 $ 80,755 40,446 $ 266,693 International 9 Erica 20 Output Range Sales Database Addition O Type here to search Format Painter i B r u ..- Merge & . Formatting Table Style Styles Clipboard Font Alignment Number Al xInput Area Input Area 2 Facility Costs Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Paymen Total Interest Paid 720,000 3 Down Payment s 250,000 4 t of Pmts per Year 5 Years 6 APR 7 1st Payment Date 360 12 30 5.25% 4/9/2018 0.44% Payment Beginning Balance Principal Pa Ending Balance Payment # Interest Paid ent 12 2 15 18 Sales Database Addition Ready