Information Technology Management - Using Excel's Functions for Data Analysis 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_Macys DeptStore.xlsx (and is available for download on Blackboard - under the assignment course link) and looks similar to the image below. mont Dome san 11 12 AU Maryam e Ty Tran 11 20 Rey more DY . SMUL Info won Men SA NOT Chres Sense 11 Conti 23420162 . . 123 Cennis 36 20 Barte | Carrie hobe Como Man Now TAM Me England Sandeepen Med 13 14 12 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. Mr. McDougal has identified two ways that he would like the analysis of each week presented. Each part is detailed below. 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. Rank 52 Gross Pay Salesperson Santana, Dorian , Hendrix, Lucian Wright, Maryam Department Juniors Shoes Infants Juniors Weekly Productivity Report Hours Sales Target sales of Target Worked Base Pay Commission $4,058,62 $2,975.00 1365 17 S255.00 $ 101.475 356.47 $3,431.16 $2.975.00 17 S 255.00 $ 85.78 $ 340.78 $9,200,22 $6,400.00 144N 32 $752.00 $ 276.01 $ 1,025,01 $3.615.60 $3,700.00 98% 37 5388.50 $ $ 388.50 1155 AM PTI Notice the report includes the name in the format of Last, First. You will need to use some text functions to display the concatenated data. Also- note the sales column ("E") in the raw data is a text (string) value that will need to be converted to a number. - Using Excel's Functions for Data Analysis 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. A table of values is displayed below: Hourly Wages & Quotas Sales Hrly Sales Commission Rank Sales Title Hourly Wage Quota Rate AM Assistant Manager $ 23.50 $ 200.00 3.0% PT1 Sales Assistant $ 10.50 $ 100.00 1.0% PT2 Sales Partner S 11.75 $ 125.00 1.5% S1 Sales Associate $ 13.50 $ 150.00 2.0% S2 Sales Consultant $ 15.00 $ 175.00 2.5% . Target Sales is calculated by multiplying the hours worked times the hourly sales quota of the designated Sales Rank. % 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 . Gross Pay is the sum of Base pay and Commission The report should be sorted to make it easy to identify the "best" salesperson - defined by the % of target column. Part 2: Mr. McDougal would also like a summary report that will display the counts, sales and quotas (targets) by Rank Rank. Rank AM PTI PT2 51 S2 Totals Summary By Rank Count by Sales Title Rank Total Target Total Sales Assistant Manager 17 $ 91,600.00 $ 119,051.48 Sales Assistant 18 $ 46,500.00 $ 57,628.19 Sales Partner 20 S 68,250.00 $ 88,190.74 Sales Associate 23 S 92,850.00 $ 115,451.41 Sales Consultant 22 $ 100,100.00 $ 114,276.30 100 $ 399,300.00 $ 494,598.12 % of Target 130% 124% 129% 124% 114% 124% N - Using Excel's Functions for Data Analysis Put the Excel skills taught thus far to produce the requested information for Mr. McDougal. Write (and leave) formulas on the Raw Data worksheet to convert data, as needed. Copy and paste (AS VALUES) the data from the Raw Data worksheet onto your Reporting worksheet. You may need to seek out additional functions (not covered) to manipulate the downloaded text data and produce the information in the requested arrangement. Insert worksheets within the workbook supplied to build your solution and present the required information. Use absolute addressing, VLookup, IF, Count CountIF, SumlF, 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 Assignment Deliverables: Submit your Excel workbook to the Turn in a hardcopy of both reports in class on the due date. Use a reasonable "Print to Fit layout so that your reports are easily read. Include, as cover page, a printed version of the final page of this assignment document. Wwwww hours 17 17 32 37 13 25 22 A B 1 empFirst emplast rank 2 Dorian Santana S2 S2 Ryan 3 Quynh 4 Lucian Hendrix AM PT1 5 Maryam Wright 6. Florence Terry S2 7 Grant Ballard PT1 8 Zelda Riley PT1 9 Dacey Gilmore PT1 10 Sacha Short S1 11 Burke Carrillo PT2 12 Phoebe Combs PT2 13 Lacey Rosario S2 14 Ori Sargent AM 15 Maggie England PT1 16 Sandra George PT1 Meadows S2 18 Jameson Salinas PT2 19 Gretchen Diaz S1 20 Ursula Mann 21 Laith Klein PTZ Garrison AM Doyle PT1 24 Suki Durham S2 25 Paula Cruz 52 D departme sales Juniors $4058.62 Shoes $3431.16 Infants $9200.22 Juniors $3615.60 Mens $4811.53 Mens $2907.98 Childrens $4123.00 Womens $1700.83 Mens $4840.11 Cosmetics $5071.97 Shoes $4208.62 Juniors $5082.76 Housewar $5741.27 Childrens $2621.92 Cosmetics $1556.65 Housewar $5536.79 Cosmetics $4191.60 Mens $5387.26 Mens $4127.78 Mens $5107.61 Shoes $9966.55 Womens $2604.76 Juniors $6670.23 $6144.88 16 28 34 28 31 15 16 16 31 17 Fay PT1 22 Freya 23 Vanna 26 Chelsea Mccormic si 27 Neve Todd S1 28 Tarik Bond Si 29 Blythe Arnold SI 30 Luke Pierce PT1 31 Tasha Robles PT2 32 Sylvia PT2 33 Nigel Giles AM 34 Elton Velez PTI Sherman PT2 36 Joseph Jordan P12 37 Emma Melton PT2 38 Indigo Rivers S2 39 Candice Jarvis AM 40 Nell Mdaughli S2 41 Curran Atkins 51 Page 30 36 22 31 36 25 37 34 39 13 31 21 37 38 23 33 21 32 26 22 18 28 14 25 Shoes Childrens $5371.48 Childrens $2560.45 Childrens $5932.33 Mens $3854.82 Womens $5661.14 Mens $4778.29 Housewar $3866,30 Mens $7607.25 Cosmetic $2852.76 Mens $4720.20 Womens $4774.93 Housewar $5183.39 Childrens $3682.74 Mens $7666.56 Childrens $5331.56 Childrens $5152.47 CLADO A 35 Tyler RawData GS A A B F H 33 34 43 Cadman Quinn S1 44 Clara Ellison PT1 45 Keaton David PTZ 46 Merrill Herring S1 47 Thor Workman S2 48 Leo Rich AM 49 Mari Noel S1 50 Xenos Rosario S1 51 Virginia Benson PT2 52 Buckminst Riggs S1 53 Brianna William AM 54 Octavia Saunders S1 55 Deacon Winters PT2 56 Sheila Rush S2 57 Jessica Dunn AM 58 Jolie Valencia PTZ 59 Remedios Kirkland AM 60 Ocean Logan S2 61 Hermione Fry s1 62 Violet Hewitt AM 63 Cedric Riddle S2 64 Wyoming Hanson S2 65 David Reese S2 66 Shea Bartlett S2 67 Aretha Lowe 51 68 Constance Battle PTI D E Housewar $9429.93 Mens $2419.39 Juniors $3001.77 Shoes $7139.76 Infants $3903.66 Shoes $13949.09 Juniors $4289.02 Infants $5105.77 Womens $5563.78 Womens $3198.20 Infants $6500.71 Cosmetics $8327.29 Childrens $4019.32 Mens $3139.49 Housewar $5820.31 Infants $2670.70 Juniors $5770.51 Shoes Womens $5899.14 Shoes 36 27 22 40 19 34 13 18 20 38 23 12 34 40 12 21 38 32 39 $4011.84 39 29 $7064.45 Shoes $8203.00 Cosmetics $10301.98 Shoes $5371.07 Childrens $2492.48 Juniors $5118.50 Shoes $3651.89 Womens $5500.42 Mens $5468.32 Cosmetics $4933.21 Infants $3931.11 Infants $1905.60 Housewar $2677.63 Infants $3867.03 Housewar $2714.73 Cosmetics $4129.54 Cosmetics $5891.39 Cosmetics S5143.18 Mens $9547.88 Shoes $3181.29 Childrens $3617.98 Housewar $525.62 17 19 32 27 25 31 23 69 Addison Vasquez PT2 70 Kane Shaw 51 71. Naomi Morgan SI 72 Kenneth Oliver PT2 73 Claire Campbell PT2 74 Randall Hahn S1 75 Ferdinand Valdez 52 76 Molly Potts PTI 77 Pearl Woods AM 78 Frederick Finch AM 79 Stone Gibbs 52 80 Marsden Weeks S1 81 Evan Rush PT2 82 Timon Castillo Si Church AM 15 13 37 27 16 21 37 37 16 15 az 83 Kyle RawData o GS fr F H si 15 37 23 33 21 23 39 B C 82. Timon Castillo 83 Kyle Church AM 84 Uriel Rosales PT2 85 Evelyn Montoya PTZ 86 Finn Daugherty S2 87 Justine Giles PT1 88 Todd Leonard S2 89 Quintessa Vaughan S2 90 Kelly Goodwin 51 91 Martin Moses $1 92 Kaitlin Chan AM 93 Keefe Charles S1 94 Imani Mendez PT2 95 Camille Rollins AM 96 Neve Daniels S2 97 Willow Fitzgerald AM 98 Alan 1 99 Joan Massey AM 100 Eleanor Stanton PTI Salas PTI 102 103 104 105 D E Childrens $3617.98 Housewar $8525.62 Womens S4668.16 Womens $5572.33 Juniors $4817.61 Juniors $558.71 Womens $8023.37 Childrens $5151.93 Housewar $505.97 Infants $3212.78 Childrens $3865.65 Mens $7881.11 Cosmetics $6273.35 Cosmetics $7665.89 Cosmetics $5094.39 Shoes $3669.70 Infants $2964.82 Housewar $6016.77 Infants $4521.55 Infants $4739.80 20 25 19 24 32 33 26 27 Bray 20 22 32 30 38 101 Macy 106 107 108 109 110 111 115 116 117 118 119 120 122 RawData Information Technology Management - Using Excel's Functions for Data Analysis 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_Macys DeptStore.xlsx (and is available for download on Blackboard - under the assignment course link) and looks similar to the image below. mont Dome san 11 12 AU Maryam e Ty Tran 11 20 Rey more DY . SMUL Info won Men SA NOT Chres Sense 11 Conti 23420162 . . 123 Cennis 36 20 Barte | Carrie hobe Como Man Now TAM Me England Sandeepen Med 13 14 12 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. Mr. McDougal has identified two ways that he would like the analysis of each week presented. Each part is detailed below. 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. Rank 52 Gross Pay Salesperson Santana, Dorian , Hendrix, Lucian Wright, Maryam Department Juniors Shoes Infants Juniors Weekly Productivity Report Hours Sales Target sales of Target Worked Base Pay Commission $4,058,62 $2,975.00 1365 17 S255.00 $ 101.475 356.47 $3,431.16 $2.975.00 17 S 255.00 $ 85.78 $ 340.78 $9,200,22 $6,400.00 144N 32 $752.00 $ 276.01 $ 1,025,01 $3.615.60 $3,700.00 98% 37 5388.50 $ $ 388.50 1155 AM PTI Notice the report includes the name in the format of Last, First. You will need to use some text functions to display the concatenated data. Also- note the sales column ("E") in the raw data is a text (string) value that will need to be converted to a number. - Using Excel's Functions for Data Analysis 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. A table of values is displayed below: Hourly Wages & Quotas Sales Hrly Sales Commission Rank Sales Title Hourly Wage Quota Rate AM Assistant Manager $ 23.50 $ 200.00 3.0% PT1 Sales Assistant $ 10.50 $ 100.00 1.0% PT2 Sales Partner S 11.75 $ 125.00 1.5% S1 Sales Associate $ 13.50 $ 150.00 2.0% S2 Sales Consultant $ 15.00 $ 175.00 2.5% . Target Sales is calculated by multiplying the hours worked times the hourly sales quota of the designated Sales Rank. % 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 . Gross Pay is the sum of Base pay and Commission The report should be sorted to make it easy to identify the "best" salesperson - defined by the % of target column. Part 2: Mr. McDougal would also like a summary report that will display the counts, sales and quotas (targets) by Rank Rank. Rank AM PTI PT2 51 S2 Totals Summary By Rank Count by Sales Title Rank Total Target Total Sales Assistant Manager 17 $ 91,600.00 $ 119,051.48 Sales Assistant 18 $ 46,500.00 $ 57,628.19 Sales Partner 20 S 68,250.00 $ 88,190.74 Sales Associate 23 S 92,850.00 $ 115,451.41 Sales Consultant 22 $ 100,100.00 $ 114,276.30 100 $ 399,300.00 $ 494,598.12 % of Target 130% 124% 129% 124% 114% 124% N - Using Excel's Functions for Data Analysis Put the Excel skills taught thus far to produce the requested information for Mr. McDougal. Write (and leave) formulas on the Raw Data worksheet to convert data, as needed. Copy and paste (AS VALUES) the data from the Raw Data worksheet onto your Reporting worksheet. You may need to seek out additional functions (not covered) to manipulate the downloaded text data and produce the information in the requested arrangement. Insert worksheets within the workbook supplied to build your solution and present the required information. Use absolute addressing, VLookup, IF, Count CountIF, SumlF, 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 Assignment Deliverables: Submit your Excel workbook to the Turn in a hardcopy of both reports in class on the due date. Use a reasonable "Print to Fit layout so that your reports are easily read. Include, as cover page, a printed version of the final page of this assignment document. Wwwww hours 17 17 32 37 13 25 22 A B 1 empFirst emplast rank 2 Dorian Santana S2 S2 Ryan 3 Quynh 4 Lucian Hendrix AM PT1 5 Maryam Wright 6. Florence Terry S2 7 Grant Ballard PT1 8 Zelda Riley PT1 9 Dacey Gilmore PT1 10 Sacha Short S1 11 Burke Carrillo PT2 12 Phoebe Combs PT2 13 Lacey Rosario S2 14 Ori Sargent AM 15 Maggie England PT1 16 Sandra George PT1 Meadows S2 18 Jameson Salinas PT2 19 Gretchen Diaz S1 20 Ursula Mann 21 Laith Klein PTZ Garrison AM Doyle PT1 24 Suki Durham S2 25 Paula Cruz 52 D departme sales Juniors $4058.62 Shoes $3431.16 Infants $9200.22 Juniors $3615.60 Mens $4811.53 Mens $2907.98 Childrens $4123.00 Womens $1700.83 Mens $4840.11 Cosmetics $5071.97 Shoes $4208.62 Juniors $5082.76 Housewar $5741.27 Childrens $2621.92 Cosmetics $1556.65 Housewar $5536.79 Cosmetics $4191.60 Mens $5387.26 Mens $4127.78 Mens $5107.61 Shoes $9966.55 Womens $2604.76 Juniors $6670.23 $6144.88 16 28 34 28 31 15 16 16 31 17 Fay PT1 22 Freya 23 Vanna 26 Chelsea Mccormic si 27 Neve Todd S1 28 Tarik Bond Si 29 Blythe Arnold SI 30 Luke Pierce PT1 31 Tasha Robles PT2 32 Sylvia PT2 33 Nigel Giles AM 34 Elton Velez PTI Sherman PT2 36 Joseph Jordan P12 37 Emma Melton PT2 38 Indigo Rivers S2 39 Candice Jarvis AM 40 Nell Mdaughli S2 41 Curran Atkins 51 Page 30 36 22 31 36 25 37 34 39 13 31 21 37 38 23 33 21 32 26 22 18 28 14 25 Shoes Childrens $5371.48 Childrens $2560.45 Childrens $5932.33 Mens $3854.82 Womens $5661.14 Mens $4778.29 Housewar $3866,30 Mens $7607.25 Cosmetic $2852.76 Mens $4720.20 Womens $4774.93 Housewar $5183.39 Childrens $3682.74 Mens $7666.56 Childrens $5331.56 Childrens $5152.47 CLADO A 35 Tyler RawData GS A A B F H 33 34 43 Cadman Quinn S1 44 Clara Ellison PT1 45 Keaton David PTZ 46 Merrill Herring S1 47 Thor Workman S2 48 Leo Rich AM 49 Mari Noel S1 50 Xenos Rosario S1 51 Virginia Benson PT2 52 Buckminst Riggs S1 53 Brianna William AM 54 Octavia Saunders S1 55 Deacon Winters PT2 56 Sheila Rush S2 57 Jessica Dunn AM 58 Jolie Valencia PTZ 59 Remedios Kirkland AM 60 Ocean Logan S2 61 Hermione Fry s1 62 Violet Hewitt AM 63 Cedric Riddle S2 64 Wyoming Hanson S2 65 David Reese S2 66 Shea Bartlett S2 67 Aretha Lowe 51 68 Constance Battle PTI D E Housewar $9429.93 Mens $2419.39 Juniors $3001.77 Shoes $7139.76 Infants $3903.66 Shoes $13949.09 Juniors $4289.02 Infants $5105.77 Womens $5563.78 Womens $3198.20 Infants $6500.71 Cosmetics $8327.29 Childrens $4019.32 Mens $3139.49 Housewar $5820.31 Infants $2670.70 Juniors $5770.51 Shoes Womens $5899.14 Shoes 36 27 22 40 19 34 13 18 20 38 23 12 34 40 12 21 38 32 39 $4011.84 39 29 $7064.45 Shoes $8203.00 Cosmetics $10301.98 Shoes $5371.07 Childrens $2492.48 Juniors $5118.50 Shoes $3651.89 Womens $5500.42 Mens $5468.32 Cosmetics $4933.21 Infants $3931.11 Infants $1905.60 Housewar $2677.63 Infants $3867.03 Housewar $2714.73 Cosmetics $4129.54 Cosmetics $5891.39 Cosmetics S5143.18 Mens $9547.88 Shoes $3181.29 Childrens $3617.98 Housewar $525.62 17 19 32 27 25 31 23 69 Addison Vasquez PT2 70 Kane Shaw 51 71. Naomi Morgan SI 72 Kenneth Oliver PT2 73 Claire Campbell PT2 74 Randall Hahn S1 75 Ferdinand Valdez 52 76 Molly Potts PTI 77 Pearl Woods AM 78 Frederick Finch AM 79 Stone Gibbs 52 80 Marsden Weeks S1 81 Evan Rush PT2 82 Timon Castillo Si Church AM 15 13 37 27 16 21 37 37 16 15 az 83 Kyle RawData o GS fr F H si 15 37 23 33 21 23 39 B C 82. Timon Castillo 83 Kyle Church AM 84 Uriel Rosales PT2 85 Evelyn Montoya PTZ 86 Finn Daugherty S2 87 Justine Giles PT1 88 Todd Leonard S2 89 Quintessa Vaughan S2 90 Kelly Goodwin 51 91 Martin Moses $1 92 Kaitlin Chan AM 93 Keefe Charles S1 94 Imani Mendez PT2 95 Camille Rollins AM 96 Neve Daniels S2 97 Willow Fitzgerald AM 98 Alan 1 99 Joan Massey AM 100 Eleanor Stanton PTI Salas PTI 102 103 104 105 D E Childrens $3617.98 Housewar $8525.62 Womens S4668.16 Womens $5572.33 Juniors $4817.61 Juniors $558.71 Womens $8023.37 Childrens $5151.93 Housewar $505.97 Infants $3212.78 Childrens $3865.65 Mens $7881.11 Cosmetics $6273.35 Cosmetics $7665.89 Cosmetics $5094.39 Shoes $3669.70 Infants $2964.82 Housewar $6016.77 Infants $4521.55 Infants $4739.80 20 25 19 24 32 33 26 27 Bray 20 22 32 30 38 101 Macy 106 107 108 109 110 111 115 116 117 118 119 120 122 RawData