90.2K

Verified Solution

Link Copied!

plz answer these in excel

XYZ SALES COMPANY \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline \multicolumn{10}{|c|}{ Annual Bonus } \\ \hline & & & & & & & & & \\ \hline Agent & Years at XYZ & Sales Goal" & Actual Sales & Individual BC & sc Team Bonus fate & eoal Achieved & Individual Bonu & UTeam Bonus Amol & Total Bonus A \\ \hline Carey, Richard & 12 & 3375000 & 3200000 & 0.01 & 0.025 & yes & paid & & 32000 \\ \hline Ortiz, David & 12 & 3375000 & 3500000 & 001 & 0.025 & 5 No & 70 & & 35000 \\ \hline Calafato, Ryan & 10 & 2375000 & 2700000 & 0.01 & 0.025 & yes & paid & & 27000 \\ \hline Akers, kim & 5 & 2500000 & 2600000 & 0.013 & 0.02 & No & 0 & & 19000 \\ \hline Carson, Nicole & 4 & 1500000 & 1224000 & 0.015 & 0.015 & yes & paid & & 18360 \\ \hline Moschell, Linda & 1 & 800000 & 925000 & 0.02 & 0.01 & No & 0 & & 18500 \\ \hline Nash, Nichael & 1 & 300000 & 220000 & 0.02 & 0.01 & yes & paid & & 4400 \\ \hline Totals & & 14725000 & 14369000 & & & yes & paid & & \\ \hline \end{tabular} \begin{tabular}{|r|r|r|} \hline Yearsat XYZ & \multicolumn{3}{|c|}{ Individual Bon Team Bonus. } \\ \hline 1 & 0.01 & 0.02 \\ \hline 2 & 0.015 & 0.015 \\ 5 & 0.02 & 0.015 \\ 10 & 0.025 & 0.01 \\ 15 & 0.01 & 0.01 \\ \hline \end{tabular} Your task is to populate the COMMISSION table for XYZ Sales Company using formulas. Step: 1 Name your bonus lookup range (A15:C20) 2 Calculate the individual bonus rate (column E) based on Years at XYZ Use a VLOOKUP and refer to the bonus table you named in step 1 3 Calculate the team bonus rate (column F) based on Years at XYZ Use a VLOOKUP and refer to the bonus table you named in step 1 4 In column G, indicate whether each individual's sales goal was achieved (YES/NO) if actual sales is greater than or equal to the sales goal, then then sales goal was achieved You should also indicate whether the total/team (G12) goal was achieved 5 Individual bonus (column H ) is paid only if the individual goal is met use your indicator in column G to calculate an amount (or 0 ) in column H 6 Team bonus is paid only if the team goal is met (cell G12) use your indicator in cell G12 to populate an amount (or 0) in column I 7 Calculate the Total Bonus Amount 8 Format appropriately to draw attention to what you think matters most 9 Format your page setup appropriately using the tips we have discussed in class. 10 Print to PDF (this is scenario 1) 11 Richard Carey just made a last minute sale of $700,000 12 Adjust his actual sales 13 Print to PDF (this is scenario 2) 14 Upload your Excel file and 2 PDF files \begin{tabular}{|l|l|} \hline Program & Fee \\ \hline Anthropology & $23.00 \\ \hline Apparel Merchandising & $25.00 \\ \hline Biology & $27.00 \\ \hline Business & $86.00 \\ \hline Communication & $21.00 \\ \hline Criminal Justice & $60.00 \\ \hline East Asian Language and Culture & $58.00 \\ \hline Education & $74.00 \\ \hline English & $26.00 \\ \hline Fine Arts & $49.00 \\ \hline Geology & $36.00 \\ \hline Health & $58.00 \\ \hline Informatics & $85.00 \\ \hline Journalism & $81.00 \\ \hline Law & $68.00 \\ \hline Mathematics & $42.00 \\ \hline Music & $46.00 \\ \hline Optometry & $75.00 \\ \hline Political Science & $20.00 \\ \hline Psychology & $24.00 \\ \hline Public and Environmental Affairs & $35.00 \\ \hline Telecommunications & $41.00 \\ \hline Theatre & $64.00 \end{tabular} A1 f On the Students worksheet, in cell B2, a V A B C On the Students worksheet, in cell B2, create a VLOOKUP function that will return a student's full name when a University ID is entered in B2. Insert a column to the left of Column E. In Column E, ese LEFT and CONCAT to create the data field showing first initial, period, space, last name. On the Students worksheet, in cell B3, create a XLOOKUP function that will return a student's First Intial and Last Name when a University ID is entered in 4 B2. Create a VLOOKUP or XLOOKUP function that will return the amount of fees (column H ) a student owes based on their major. When the fees have been added, format column H as currency. Hint: you will need to work with both the Students and Fees worksheets. In cell B4, Create a VLOOKUP or XLOOKUP function that will find a student's residence hall. Add the heading, Residence Hall, in cell A4. On the TestScores worksheet, create a XLOOKUP function to find the letter grade for each student. On the Students worksheet, create a VLOOKUP function to add the students' grades to the Students worksheet. Column L has been designated for this. Not all students in the Students worksheet will have a grade. Use a filter to see only the students with grades. UPLOAD YOUR EXCEL FILE TO SUBMIT XYZ SALES COMPANY \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline \multicolumn{10}{|c|}{ Annual Bonus } \\ \hline & & & & & & & & & \\ \hline Agent & Years at XYZ & Sales Goal" & Actual Sales & Individual BC & sc Team Bonus fate & eoal Achieved & Individual Bonu & UTeam Bonus Amol & Total Bonus A \\ \hline Carey, Richard & 12 & 3375000 & 3200000 & 0.01 & 0.025 & yes & paid & & 32000 \\ \hline Ortiz, David & 12 & 3375000 & 3500000 & 001 & 0.025 & 5 No & 70 & & 35000 \\ \hline Calafato, Ryan & 10 & 2375000 & 2700000 & 0.01 & 0.025 & yes & paid & & 27000 \\ \hline Akers, kim & 5 & 2500000 & 2600000 & 0.013 & 0.02 & No & 0 & & 19000 \\ \hline Carson, Nicole & 4 & 1500000 & 1224000 & 0.015 & 0.015 & yes & paid & & 18360 \\ \hline Moschell, Linda & 1 & 800000 & 925000 & 0.02 & 0.01 & No & 0 & & 18500 \\ \hline Nash, Nichael & 1 & 300000 & 220000 & 0.02 & 0.01 & yes & paid & & 4400 \\ \hline Totals & & 14725000 & 14369000 & & & yes & paid & & \\ \hline \end{tabular} \begin{tabular}{|r|r|r|} \hline Yearsat XYZ & \multicolumn{3}{|c|}{ Individual Bon Team Bonus. } \\ \hline 1 & 0.01 & 0.02 \\ \hline 2 & 0.015 & 0.015 \\ 5 & 0.02 & 0.015 \\ 10 & 0.025 & 0.01 \\ 15 & 0.01 & 0.01 \\ \hline \end{tabular} Your task is to populate the COMMISSION table for XYZ Sales Company using formulas. Step: 1 Name your bonus lookup range (A15:C20) 2 Calculate the individual bonus rate (column E) based on Years at XYZ Use a VLOOKUP and refer to the bonus table you named in step 1 3 Calculate the team bonus rate (column F) based on Years at XYZ Use a VLOOKUP and refer to the bonus table you named in step 1 4 In column G, indicate whether each individual's sales goal was achieved (YES/NO) if actual sales is greater than or equal to the sales goal, then then sales goal was achieved You should also indicate whether the total/team (G12) goal was achieved 5 Individual bonus (column H ) is paid only if the individual goal is met use your indicator in column G to calculate an amount (or 0 ) in column H 6 Team bonus is paid only if the team goal is met (cell G12) use your indicator in cell G12 to populate an amount (or 0) in column I 7 Calculate the Total Bonus Amount 8 Format appropriately to draw attention to what you think matters most 9 Format your page setup appropriately using the tips we have discussed in class. 10 Print to PDF (this is scenario 1) 11 Richard Carey just made a last minute sale of $700,000 12 Adjust his actual sales 13 Print to PDF (this is scenario 2) 14 Upload your Excel file and 2 PDF files \begin{tabular}{|l|l|} \hline Program & Fee \\ \hline Anthropology & $23.00 \\ \hline Apparel Merchandising & $25.00 \\ \hline Biology & $27.00 \\ \hline Business & $86.00 \\ \hline Communication & $21.00 \\ \hline Criminal Justice & $60.00 \\ \hline East Asian Language and Culture & $58.00 \\ \hline Education & $74.00 \\ \hline English & $26.00 \\ \hline Fine Arts & $49.00 \\ \hline Geology & $36.00 \\ \hline Health & $58.00 \\ \hline Informatics & $85.00 \\ \hline Journalism & $81.00 \\ \hline Law & $68.00 \\ \hline Mathematics & $42.00 \\ \hline Music & $46.00 \\ \hline Optometry & $75.00 \\ \hline Political Science & $20.00 \\ \hline Psychology & $24.00 \\ \hline Public and Environmental Affairs & $35.00 \\ \hline Telecommunications & $41.00 \\ \hline Theatre & $64.00 \end{tabular} A1 f On the Students worksheet, in cell B2, a V A B C On the Students worksheet, in cell B2, create a VLOOKUP function that will return a student's full name when a University ID is entered in B2. Insert a column to the left of Column E. In Column E, ese LEFT and CONCAT to create the data field showing first initial, period, space, last name. On the Students worksheet, in cell B3, create a XLOOKUP function that will return a student's First Intial and Last Name when a University ID is entered in 4 B2. Create a VLOOKUP or XLOOKUP function that will return the amount of fees (column H ) a student owes based on their major. When the fees have been added, format column H as currency. Hint: you will need to work with both the Students and Fees worksheets. In cell B4, Create a VLOOKUP or XLOOKUP function that will find a student's residence hall. Add the heading, Residence Hall, in cell A4. On the TestScores worksheet, create a XLOOKUP function to find the letter grade for each student. On the Students worksheet, create a VLOOKUP function to add the students' grades to the Students worksheet. Column L has been designated for this. Not all students in the Students worksheet will have a grade. Use a filter to see only the students with grades. UPLOAD YOUR EXCEL FILE TO SUBMIT

Answer & Explanation Solved by verified expert

See Answer