Using Excel's Functions for Data Analysis Due: Check Blackboard Course Site for Due Date Case Background: You have been hired by John McDougal, sales manager of Macy's Franklin Park store, to help him better understand the productivity of his sales force. Mr. McDougal has provided you with a download from the POS system that contains the employee name, rank, department, sales and hours worked. That data is provided to you in the file named A1_MacysDeptStore.xlsx (and is available for download on Blackboard) and looks similar to the image below. mind emplastra Dorian Santana 12 Cyn Byan Saman Maryam Wright Pr AM Hrnce Terry Q Gra Kalle T Bey PTS GPT 2 Decay Sache u Bole Can P12 Phone Combs p Lacey 12 agent AM Mage England Pr San George T Rank 52 Salesperson Santana, Dorian Department Juniors $2 Shoes Ryan, Quyn Hendrix, Lucian AM Infants Wright, Maryam PT1 Juniors 14 On de SAD 11.1 0022 Jun 145.40 Mani SALM Meny S Child 34121.00 Womens Mans Shopp Using the file given (Do NOT copy into another Excel file), Mr. McDougal would like you to produce a series of reports that will let him know how well the sales force is performing - where performance is defined by how many employees meet their targeted sales quotas and the overall percentage of the sales quota obtained by the cumulative efforts of all salesmen. Weekly Productivity Report Hours Sales Target Sales % of Target Worked $4,058.62 $2,975.00 136% 17 53,431.16 $2,975.00 115% 17 $9,200.22 $6,400.00 144% 32 53,615.60 $3,700.00 96% 37 hours 17 17 32 37 31 25 22 10 20 34 28 Mr. McDougal has identified two ways that he would like the analysis of each week presented. Each part is detailed below. S441 C07137 Shoes SAA 02. 37677 11 Ch 121.32 14 Comen 44 14 Part 1: Mr. McDougal would like a weekly recap of sales by salesman that displays information regarding weekly sales, the salesman's sales quota, the percent of the quota met, number of hours worked, base pay earned, commission due and the salesman's gross pay for the week under review. He put together a sample of how he would like the report to look - use this layout - or your best judgement to produce a well laid out and easy to understand report. Base Pay Commission Gross Pay $ 255.00 $ 101.47 $ 356.47 $255.00 $ 85.78 $ 340.78 5 752.00 $ 276.01 $1,028.01 $388.50 $ $ 388.50 Notice the report includes the name in the format of Last, First. You will need to use some text functions to display the name concatenating the emplast to the empFirst with a comma and space between (", "). The other formulas required to produce this detail report will need to pull information from a table of hourly wages and quotas that Mr. McDougal has provided to you. Each employee is assigned a "Rank" within the sales force. Macy's has 5 different ranks. The employee's base pay and commission rate is dependent upon the rank assignment. For example, a rank of AM indicates an Assistant Manager who is paid $23.50 per hour, is expected to produce $200.00 of sales for each hour that he/she works and is paid a commission on his sales, if he/she meets the hourly sales quota for the week. The table of values displayed below should be used in your workbook (it has been replicated in your rawData sheet): Sales Rank AM PT1 PT2 $1 $2 Using Excel's Functions for Data Analysis Sales Title Assistant Manager Sales Assistant Sales Partner Sales Associate Sales Consultant Do not sort the report! Rank AM PT1 PT2 51 Hourly Wages & Quotas 52 Totals Hourly Wage 23.50 10.50 11.75 13.50 15.00 $ $ $ $ $ Target Sales is calculated by multiplying the hours worked times the hourly sales quota of the designated Sales Rank. Sales Title Assistant Manager Sales Assistant Sales Partner Sales Associate Sales Consultant % of Target is calculated by dividing Sales by Target Sales. Base Pay is calculated by multiplying the hours worked times the hourly wage. Commission is calculated for employees whose Sales exceeds their Target Sales - by multiplying the weekly sales amount times the commission rate that corresponds to the employee's Sales Rank. Gross Pay is the sum of Base pay and Commission. Part 2: Mr. McDougal would also like a summary report that will display the counts, sales and quotas (targets) by Rank. (The % of Target column in this summary report is calculated as Total Sales divided by Total Target.) Hrly Sales Quota $ 200.00 12229 $ 100.00 $ 125.00 Summary By Rank Count by Rank 17 $ $ 18 $ 150.00 $ 175.00 20 $ 23 Commission Rate 3.0% 1.0% 1.5% 2.0% 2.5% % of Target Total Target Total Sales 91,600.00 $ 119,051.48 46,500.00 $ 68,250.00 $ 130% 57,628.19 124% 88,190.74 129% 115,451.41 124% $ 92,850.00 $ $ 100,100.00 $ 114,276.30 114% 494,598.12 124% 100 $ 399,300.00 $ Using Excel's Functions for Data Analysis Put the Excel skills taught thus far to produce the requested information for Mr. McDougal. Copy and paste (AS VALUES) the data from the Raw Data worksheet onto your Reporting worksheet. Insert worksheets within the workbook supplied to build your report and summary solutions to present the required information. Use absolute addressing, VLookup, IF, Count, CountIF, SumIF, and various text functions to create your solution. Create a solution that would work with this or another similar data set. Do NOT go into the data and modify it by hand-and do not use Pivot Tables in this assignment. Create formulas to lookup, calculate and display the requested information. Pay attention to formatting, column widths, headings, and other visual aids to produce easily consumed information. Attention: No late work accepted. Assignment Deliverables: Submit your Excel workbook to the Blackboard Your work must be done on the file posted on Blackboard if you want any credit! empFirst emplast rank Dorian Santana S2 Quyn Ryan $2 Lucian Hendrix AM Maryam Wright PT1 Florence Terry S2 Grant Ballard PT1 Zelda Riley PT1 Dacey Gilmore PT1 Sacha Short $1 Burke Carrillo PT2 Phoebe Combs PT2 Lacey Rosario S2 Sargent AM Ori England PT1 Maggie Sandra George PT1 Paul Atriedes S2 Jameson Salinas PT2 $1 Gretchen Diaz Ursula Mann PT1 Laith Klein PT2 Freya Garrison AM Vanna Doyle PT1 Suki Durham S2 Paula Cruz $2 Chelsea Mccormic S1 Neve Todd S1 Tarik Bond $1 Arnold S1 Blythe Luke Tasha Robles PT2 Pierce PT1 Sylvia Page PT2 Nigel Giles AM Elton Velez PT1 Tyler Sherman PT2 Joseph Jordan PT2 Emma Melton PT2 Indigo Rivers S2 Candice Jarvis AM Nell Mclaughli $2 Curran Atkins $1 Aubrey Bryers PT1 Cadman Quinn $1 Ciara Ellison PT1 Keaton David PT2 Merrill Herring S1 Thor Workman S2 Len Rich AM departme sales hours. Juniors $4,058.62 Shoes $3,431.16 Infants $9,200.22 Juniors $3,615.60 $2,907.98 Mens Mens Childrens $4,123.00 Womens $1,700.83 28 Mens $4,840.11 Cosmetics $5,071.97 34 Shoes $4,208.62 28 $5,082.76 31 Juniors Housewar $5,741.27 15 16 Childrens $2,621.92 Cosmetics $1,556.65 16 Housewar $5,536.79 31 Cosmetics $4,191.60 30 $5,387.26 36 $4,127.78 22 $5,107.61 31 $9,966.55 36 $2,604.76 25 Mens Shoes Mens Mens Womens Juniors Shoes 17 17 32 37 $4,811.53 13 25 22 16 $6,670.23 37 Childrens $5,371.48 $6,144.88 34 39 Childrens $2,560.45 13 Childrens $5,932.33 31 Mens Womens $3,854.82 21 $5,661.14 37 Mens $4,778.29 38 Housewar $3,866.30 23 Mens $7,607.25 33 Cosmetics $2,852.76 21 Mens $4,720.20 32 Womens $4,774.93 26 Housewar Childrens : $5,183.39 22 $3,682.74 18 $7,666.56 28 Mens Childrens $5,331.56 14 Childrens $5,152.47 28 Cosmetics $4,284.88 22 Housewar $9,429.93 33 Mens $2,419.39 34 Juniors $3,001.77 36 Shoes $7,139.76 27 Infants $3,903.66 22 $13.949.09 Shoes 40 Sales Rank AM PT1 PT2 $1 S2 Hourly Wages & Quotas Sales Title Assistant Manager Sales Assistant Sales Partner Sales Associate Sales Consultant Hourly Hrly Sales Commission Wage Quota Rate $23.50 $200.00 $10.50 $100.00 $11.75 $125.00 $13.50 $150.00 $15.00 $175.00 3.00% 1.00% 1.50% 2.00% 2.50%