fill in the excel table and create a vlookup table based on the excel workbook. Use the other photos for guidence and direction.

Although you are free to work with the design of your workbook, each workshect should have a consistent, professional appearance. You should use appropriate formatting for the cells and worksheets. Test Your Design After creating the Productivily workbook described in the case scenario, you should test your design. Perform the following steps. 7. Which salespersons received more than $180 in commissions last week? Use filtering to find the answer. Take a screen shot of the answer and paste it in a word document. 8. Based on past performance, what would happen (total commission paid, number of people who miss their quota) if Mr. Doo increased the sales quota for each position by SAS per hour? You have to ereate scenarios for this. 9. What would happen (total amount of commission paid) if Mr. Doo increased each sales position's commission by a quarter of a percent (.0025) ? Create scenario summary report to get the answer and then tspe your answer in word document. PROJDCT DMIVIRABLSS In order to satisfactorily completo this project, you should build the workbook as deseribed above and then prepare a written report. Submit the following deliverables. 1. A copy of your workbook should be left in the drop box. (Type full names of your group members in the comment box when you leave your workbook in the drop box.) 2. Hard copy of your type written answers to questions posed above. 3. Hard copy of your entire workbook. 4. Make sure that your workbook has a title shect. Figure 1: Hourly Wages and Quotas EXCEL Project 20 points Department store Scooby Doo, owner of Snazzy department store, understands the importance of a department store's sales force, especially when it comes to the sales force of a premiere, upscale department store like Snazzy. Customers have long enjoyed the expert knowledge, attention to detail, and service provided by Snazzy's sales staff. Maintaining the quality sales force is a daily job for Mr. Doo. He carefully evaluates the performance of his sales staff and adjusts when needed. Currently, Mr. Doo monitors the performance of his sales staff by reading weekly sales productivity reports. The problem is that these reports are prepared on a word processor. Mr. Doo has no efficient way of analyzing the data in detail. As a new intem at Snazzy's, you have impressed Mr. Doo with your work. Mr. Doo asks you to prepare a Productivity workbook for him. You will prepare the weekly productivity worksheet, analyze the sales data by using the PivotTable and AutoFilter tools, and generate several charts. Case scenario Snazzy's Department Store is a prestigious, upscale department store located in one of the metropolitan area malls. The store has speciaily departments for women, men, children, cosmetics, cologne, linen, fumiture, and housewares. One of the reasons why Snazry's has such a fine reputation is because of its highly-trained sales staff. Snazzy's management believes in rewarding its sales staff for its hard work, so in addition to an hourly wage, sales representatives are paid a commission on sales above an established quota. Sales representatives are ranked and paid according to their experience and tenure with the company. Sales representatives are paid a commission on all sales exceeding their established quota. As members of the sales staff may work a different number of hours during a given week, the sales quota is based on the hours worked. For instance, if a sales representative has an hourly quota of S100 in sales and he works 15 hours during a week, then his weekly sales quota for that week is $1500. For any sales for that week above the $1500 quota, the sales representative receives a commission. Figure 1 summarizes the hourly wages and established quotas. Design Specifications Each week, Mr. Doo will enter each salesperson's sales and hours into a Weekly Productivity worksheet. He then expects the workshect to determine each salesperson's base pay, commission, and gross pay. While the gross pay involves adding the base pay to the commission, calculating the base pay and commission requires referencing values in a lookup table. The names of employees, their title, and department information is available in a worksheet named "Storexis". The base pay and commission are dependent upon the salesperson's rank in the company. For instance, an assistant manager is paid $25 per hour and receives a 3 percent commission. In contrast, a sales associate is paid $14.50 per hour and receives a 2 pereent commission. You use the VLOOKUP function to build this logic into the base pay; use the IF function to caleulate the Extra sold; and VLOOKUP function to calculate the commission. The base pay formula uses the salesperson's rank to retrieve the correet hourly wage and then multiplies the hourly wage by the number of hours. Information Specifications Prepare a pie chart shffiwing the total commissions by department. You may create a pivot table first and then create a pie chart using the pivot table. In addition to the information requirements specified above, Mr. Doo wants answers to the following questions. Using the PivotTable, and AutoFilter tools, provide Mr. Doo with answers to these questions. 1. Which 6 sales representatives received the highest commissions last week? Use filtering to get the answer. Take a sereen shot of the answer and paste it in a word doeument. 2. Which sales staff did not make quota last week? Use filtering to get the answer. Take a sereen shot of the answer and paste it in a word document. 3. For each department, which salesperson had the highest sales? Use DMAX function and advanced filtering to determine this. Take a sereen shot of the answer and paste it in a word document. 4. What percentage of sales staff that worked last week made their quota? Use COUNTIF finction to calculate this. 5. What are the total commissions and total sales for each sales position by department? Create a pivot table report for this. 6. What is the average base pay, average commission, and average gross pay for each sales position? Use DAVERAGB finction to compute this. Implementation Concerns Although you are free to work with the design of your workbook, each workshect should have a consistent, professional appearance. You should use appropriate formatting for the cells and worksheets. Test Your Design After creating the Productivily workbook described in the case scenario, you should test your design. Perform the following steps. 7. Which salespersons received more than $180 in commissions last week? Use filtering to find the answer. Take a screen shot of the answer and paste it in a word document. 8. Based on past performance, what would happen (total commission paid, number of people who miss their quota) if Mr. Doo increased the sales quota for each position by SAS per hour? You have to ereate scenarios for this. 9. What would happen (total amount of commission paid) if Mr. Doo increased each sales position's commission by a quarter of a percent (.0025) ? Create scenario summary report to get the answer and then tspe your answer in word document. PROJDCT DMIVIRABLSS In order to satisfactorily completo this project, you should build the workbook as deseribed above and then prepare a written report. Submit the following deliverables. 1. A copy of your workbook should be left in the drop box. (Type full names of your group members in the comment box when you leave your workbook in the drop box.) 2. Hard copy of your type written answers to questions posed above. 3. Hard copy of your entire workbook. 4. Make sure that your workbook has a title shect. Figure 1: Hourly Wages and Quotas EXCEL Project 20 points Department store Scooby Doo, owner of Snazzy department store, understands the importance of a department store's sales force, especially when it comes to the sales force of a premiere, upscale department store like Snazzy. Customers have long enjoyed the expert knowledge, attention to detail, and service provided by Snazzy's sales staff. Maintaining the quality sales force is a daily job for Mr. Doo. He carefully evaluates the performance of his sales staff and adjusts when needed. Currently, Mr. Doo monitors the performance of his sales staff by reading weekly sales productivity reports. The problem is that these reports are prepared on a word processor. Mr. Doo has no efficient way of analyzing the data in detail. As a new intem at Snazzy's, you have impressed Mr. Doo with your work. Mr. Doo asks you to prepare a Productivity workbook for him. You will prepare the weekly productivity worksheet, analyze the sales data by using the PivotTable and AutoFilter tools, and generate several charts. Case scenario Snazzy's Department Store is a prestigious, upscale department store located in one of the metropolitan area malls. The store has speciaily departments for women, men, children, cosmetics, cologne, linen, fumiture, and housewares. One of the reasons why Snazry's has such a fine reputation is because of its highly-trained sales staff. Snazzy's management believes in rewarding its sales staff for its hard work, so in addition to an hourly wage, sales representatives are paid a commission on sales above an established quota. Sales representatives are ranked and paid according to their experience and tenure with the company. Sales representatives are paid a commission on all sales exceeding their established quota. As members of the sales staff may work a different number of hours during a given week, the sales quota is based on the hours worked. For instance, if a sales representative has an hourly quota of S100 in sales and he works 15 hours during a week, then his weekly sales quota for that week is $1500. For any sales for that week above the $1500 quota, the sales representative receives a commission. Figure 1 summarizes the hourly wages and established quotas. Design Specifications Each week, Mr. Doo will enter each salesperson's sales and hours into a Weekly Productivity worksheet. He then expects the workshect to determine each salesperson's base pay, commission, and gross pay. While the gross pay involves adding the base pay to the commission, calculating the base pay and commission requires referencing values in a lookup table. The names of employees, their title, and department information is available in a worksheet named "Storexis". The base pay and commission are dependent upon the salesperson's rank in the company. For instance, an assistant manager is paid $25 per hour and receives a 3 percent commission. In contrast, a sales associate is paid $14.50 per hour and receives a 2 pereent commission. You use the VLOOKUP function to build this logic into the base pay; use the IF function to caleulate the Extra sold; and VLOOKUP function to calculate the commission. The base pay formula uses the salesperson's rank to retrieve the correet hourly wage and then multiplies the hourly wage by the number of hours. Information Specifications Prepare a pie chart shffiwing the total commissions by department. You may create a pivot table first and then create a pie chart using the pivot table. In addition to the information requirements specified above, Mr. Doo wants answers to the following questions. Using the PivotTable, and AutoFilter tools, provide Mr. Doo with answers to these questions. 1. Which 6 sales representatives received the highest commissions last week? Use filtering to get the answer. Take a sereen shot of the answer and paste it in a word doeument. 2. Which sales staff did not make quota last week? Use filtering to get the answer. Take a sereen shot of the answer and paste it in a word document. 3. For each department, which salesperson had the highest sales? Use DMAX function and advanced filtering to determine this. Take a sereen shot of the answer and paste it in a word document. 4. What percentage of sales staff that worked last week made their quota? Use COUNTIF finction to calculate this. 5. What are the total commissions and total sales for each sales position by department? Create a pivot table report for this. 6. What is the average base pay, average commission, and average gross pay for each sales position? Use DAVERAGB finction to compute this. Implementation Concerns