Styles O Create a data model using the data on the Customer, Sales, and Employee worksheets Get the data from the Customer, Sales and Employee tabs (one at a time) - using the from Table/Range option from the Data tab. Name each Query/Table corresponding to the data: Cust, Sales, and Emp Use the "Close and Load to" option to: De o Only Create the Connection and o Add this data to the Data Model . Once the connections are established, Manage the Data Model (Data Tab relationships to express the relationships that exist in the data: icon-and set the o One Customer produces Many Sales (based upon the Cust_code column in both tables) One Employee produces Many Sales (based upon the Emp_num column in both tables) o Your final data model should look like this: Sove CAT COOK NAME KUST START XUST.COM ne erwe PORT Insert a worksheet named PT1 as the last worksheet in the workbook. Create pivot tables to produce the following analysis To Insert a pivot table using the Data Model, make the appropriate selection from the Pivot Table Wizard: . Create a pivot table to aggregate Sales (invotal) by Cust. State and Year of the invoice date (inv date). Add a calculated coomn to display the % by State. Add any other formatting needed to produce an easy to read report. a Create a second pivot table that displays Sales (inv total) by Employee Last Name. Include the Emp_num column, but to allow for a better display, Select the Tabular Report Layout (from the Pivot Table Design ribbon) Editing Voice Query Settings 15 Sensitivity Review Mailings View Heip References Design Layout bri (Body) 11. A A Aa AE-ES- ENT IU- ab x, x A-D-A- Font 5 Paragraph D EMP NUM EMP FNAME 83509 FRANKLYN 83537 CLEO EMP LNAME STOVER EMP TITLE SENIOR SALES MANAGER SENIOR ASSOCIATE ENGLISH ABERNATHY SENIOR ASSOCIATE 83565 LOURDES 83593 ROSANNE NASH SENIOR ASSOCIATE 83621 FONDA SENIOR ASSOCIATE GONZALEZ JACOB 83649 DELMA SENIOR ASSOCIATE SENIOR ASSOCIATE 83677 HERB MANNING 83705 JOSE BARR SALES MANAGER 83734 INEZ ROCHA SENIOR ASSOCIATE 83763 JAIME FELTON SENIOR ASSOCIATE 83792 WALLY ANDERSEN ASSOCIATE 83820 TOM HOBSON ASSOCIATE DIRECTOR MILES ASSOCIATE 83850 RUSTY 83878 PAT MARIN ASSOCIATE 83906 DELLA SIMONS ASSOCIATE 83936 BRADFORD BRAY SALES MANAGER 83964 HAILEY ASSOCIATE SWEENEY CORTES 83993 SANG ASSOCIATE DICKINSON ASSOCIATE BRANDON SALES MANAGER ERWIN ASSOCIATE SAMUEL ASSOCIATE GARLAND ASSOCIATE EASLEY ASSOCIATE HOLBROOK ASSOCIATE ASSOCIATE 84021 JAROD 84049 LANE 84078 DIEGO 84106 FELICE 84134 ROSALIE 84163 GWEN 84191 ROXANA 84219 THURMAN 84248 DANICA 84276 ROSALIND 84306 ROWENA 84334 LINNIE 84364 LESLIE 84392 ALEJANDRA 84420 DOUG 83652 GEORGINA 83901 ZORA ASSOCIATE WILKINSON CASTLE VILLARREAL MEDINA ASSOCIATE ASSOCIATE GOLDMAN ASSOCIATE VARNER ASSOCIATE WHALEY ASSOCIATE CAUDILL ASSOCIATE EASON DRIVER WOODALL ASSOCIATE TedFunctions Newtop10 2 3 4 5 6 7 8 9 10 11 12 14 15 CERRAZASSAR* 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 NewTextWorksheet Top100 AaBbCcDr AaBbCcD. An 1 Normal 1 No Spac... Hea EMP COMM DEPT_NUM 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.04 200 0.04 200 0,04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.03 200 0.03 200 0.03 200 0.03 550 0.03 400 Employee CreatAces Customer Sales Uab x, x' A-2-A- Font Paragraph A D E 1 INV_NUM INV DATE CUST_CODE INV TOTAL EMP NUM 2 960 09-Sep-20 458 29.18 83792 3 961 09-Sep-20 163 40.77 84392 963 09-Sep-20 335 305,46 83820 964 09-Sep-20 427 85.98 84191 965 10-Sep-20 459 236.93 84078 10-Sep-20 460 155.96 84078 966 967 10-Sep-20 151 53.55 84306 969 10-Sep-20 400 35.97 84334 970 10-Sep-20 213 29.78 84392 971 10-Sep-20 423 270.88 84364 972 10-Sep-20 461 356.22 83964 973 11-Sep-20 206 183,29 83964 975 11-Sep-20 335 115.94 84049 976 11-Sep-20 382 60.17 84049 977 11-Sep-20 105 245.38 83763 978 11-Sep-20 462 97.82 84021 979 11-Sep-20 329 164.25 83936 981 11-Sep-20 305 286.46 84191 982 11-Sep-20 463 61.95 83878 983 12-Sep-20 424 293.34 83565 984 12-Sep-201 134 252.57 84163 986 12-Sep-20 159 221.85 83792 987 12-Sep-20 100 305.83 84276 988 12-Sep-20 377 161.07 84276 989 12-Sep-201 312 141.89 83763 990 12-Sep-20 302 127.73 84078 992 13-Sep-20 458 293.83 84078 207 472.35 83906 993 13-Sep-20 994 13-Sep-20 995 13-Sep-20 465 277.04 84049 466 23.59 84248 467 $09.04 996 13-Sep-20 998 13-Sep-20 81820 321 92.3 83677 415 999 13-Sep-20 1000 14-Sep-20 269.22 83509 153 114.95 83964 1001 14-Sep-20 335 127.95 84364 Teatfunctions NewTetworksheet NewTop100 Codes Cuenet Sales 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 R1932X 26 Top 100 A > H G > H T Normal Employe 1 No Spac... O Styles 2 3 4 3 1 7 4. 9. 10 11 2 33 24 RS 16 37 16 19 20 215 22 23 24 26 CUST CODE CUST NAME RUBY SERVICE & SALES 87 MICHEAL UMITED UNO AND SONS 9 MONICA AND SONS 90 HELEN INC. 91 ALEXANDRA AND SONS 92 CARLENE LIMITED 93 CIARA SERVICE & SALES 94 DOMINICK LIMITED 95 WILMA INC. 96 ROLANDA INC. 97 ERWIN AND SONS 98 VALENTIN INC. 99 MINERVA AND SONS 100 VELDA INC. 101 LEONORA AND SONS 102 EVA SERVICE & SALES 103 CORRINA SERVICE & SALES 104 THAD INC. 105 FRANCIS INC. 106 MELONIE INC. 107 RAY INC. 108 GONZALO INC 309 SAL AND SONS 110 CRISTINA SERVICE & SALES 111 JOSE AND SONS 112 LAN AND SONS 113 KORY SERVICE & SALES 114 MIRIAN INC. 115 STACIE LIMITED 116 STEPHAINE AND SONS 117 KARON INC. 118 JESSE AND SONS 119 MATTIE INC 120 BRADLEY INC. wetWork Top CUST STREET 1013 SNOWLINE ORIVE 1027 POLLOCK DRIVE 1201 BARNES AVENUE 697 ADAK CIRCLE 1823 HORIZON STREET 481 CHALLENGER CIRCLE 1162 CAMPBELL CREEK ROAD 1977 SEAWOU DRIVE 62 EAST 25TH COURT 1575 KING WAY DRIVE 1415 AUDUBON DRIVE 884 PLEASANT DRIVE 1377 RUSHWOOD CIRCLE 582 WINGATE ORCLE 145 CRESCENT DRIVE 2 EAST DOWLING ROAD 1084 LUNAR CIRCLE 1651 HIGHTOWER ROAD 966 VALARIAN STREET 1921 WEST 74TH AVENUE 848 DOWNEY FINCH LANE 303 TEEKOANA CIRCLE 1567 WALLACE MOUNTAIN BOAD 985 OSTOVIA OROL 904 PINNACLE CIRCLE 640 WEST 71ST COURT 367 LAKEVIEW DRIVE 1949 HUMPHREY DRIVE 1432 POTTER GLEN CIRCLE 704 WOODLAND DRIVE 1372 LOREN CIRCLE 1073 FERN CIRCLE 614 SOUTH ARPARK DRIV 548 FALLING WATER DRIVE 1153 TOBUE CIRCLE NewTa CHOREDI 5 Select- Editing D CUST CITY Fayetteville Greenback Alquina Loachapoka Catawba Heights Autor Escanaba Hope Upron Bevina Center Calverton Kingston White Plaies West Medford Tampa Reese Queen Hampden Pine Neelyton Fast Monkton Cliftonville Crescent Beach Harts Store Can Na D Wyane Petsburgh Cochecton NAM Ontle Saint Paris Bay Mate Bay Head Sebuy Philadelphia 326 6954C Dictate Sensitivity Voice Sensitivity Re CUST STATE NG CUSTOTIP 28305 37742 47331 MMS 2012 47001 49429 04647 17225 33740 11933 02881 30629 02156 33641 28683 16670 25423 12208 17239 05443 19739 34242 2012 47522 15695 15262 12726 16938 45742 43072 01060 08242 06092 NY At NY MA FL NO PA ww NY PA VT ***21*1885201 Styles O Create a data model using the data on the Customer, Sales, and Employee worksheets Get the data from the Customer, Sales and Employee tabs (one at a time) - using the from Table/Range option from the Data tab. Name each Query/Table corresponding to the data: Cust, Sales, and Emp Use the "Close and Load to" option to: De o Only Create the Connection and o Add this data to the Data Model . Once the connections are established, Manage the Data Model (Data Tab relationships to express the relationships that exist in the data: icon-and set the o One Customer produces Many Sales (based upon the Cust_code column in both tables) One Employee produces Many Sales (based upon the Emp_num column in both tables) o Your final data model should look like this: Sove CAT COOK NAME KUST START XUST.COM ne erwe PORT Insert a worksheet named PT1 as the last worksheet in the workbook. Create pivot tables to produce the following analysis To Insert a pivot table using the Data Model, make the appropriate selection from the Pivot Table Wizard: . Create a pivot table to aggregate Sales (invotal) by Cust. State and Year of the invoice date (inv date). Add a calculated coomn to display the % by State. Add any other formatting needed to produce an easy to read report. a Create a second pivot table that displays Sales (inv total) by Employee Last Name. Include the Emp_num column, but to allow for a better display, Select the Tabular Report Layout (from the Pivot Table Design ribbon) Editing Voice Query Settings 15 Sensitivity Review Mailings View Heip References Design Layout bri (Body) 11. A A Aa AE-ES- ENT IU- ab x, x A-D-A- Font 5 Paragraph D EMP NUM EMP FNAME 83509 FRANKLYN 83537 CLEO EMP LNAME STOVER EMP TITLE SENIOR SALES MANAGER SENIOR ASSOCIATE ENGLISH ABERNATHY SENIOR ASSOCIATE 83565 LOURDES 83593 ROSANNE NASH SENIOR ASSOCIATE 83621 FONDA SENIOR ASSOCIATE GONZALEZ JACOB 83649 DELMA SENIOR ASSOCIATE SENIOR ASSOCIATE 83677 HERB MANNING 83705 JOSE BARR SALES MANAGER 83734 INEZ ROCHA SENIOR ASSOCIATE 83763 JAIME FELTON SENIOR ASSOCIATE 83792 WALLY ANDERSEN ASSOCIATE 83820 TOM HOBSON ASSOCIATE DIRECTOR MILES ASSOCIATE 83850 RUSTY 83878 PAT MARIN ASSOCIATE 83906 DELLA SIMONS ASSOCIATE 83936 BRADFORD BRAY SALES MANAGER 83964 HAILEY ASSOCIATE SWEENEY CORTES 83993 SANG ASSOCIATE DICKINSON ASSOCIATE BRANDON SALES MANAGER ERWIN ASSOCIATE SAMUEL ASSOCIATE GARLAND ASSOCIATE EASLEY ASSOCIATE HOLBROOK ASSOCIATE ASSOCIATE 84021 JAROD 84049 LANE 84078 DIEGO 84106 FELICE 84134 ROSALIE 84163 GWEN 84191 ROXANA 84219 THURMAN 84248 DANICA 84276 ROSALIND 84306 ROWENA 84334 LINNIE 84364 LESLIE 84392 ALEJANDRA 84420 DOUG 83652 GEORGINA 83901 ZORA ASSOCIATE WILKINSON CASTLE VILLARREAL MEDINA ASSOCIATE ASSOCIATE GOLDMAN ASSOCIATE VARNER ASSOCIATE WHALEY ASSOCIATE CAUDILL ASSOCIATE EASON DRIVER WOODALL ASSOCIATE TedFunctions Newtop10 2 3 4 5 6 7 8 9 10 11 12 14 15 CERRAZASSAR* 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 NewTextWorksheet Top100 AaBbCcDr AaBbCcD. An 1 Normal 1 No Spac... Hea EMP COMM DEPT_NUM 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.05 200 0.04 200 0.04 200 0,04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.04 200 0.03 200 0.03 200 0.03 200 0.03 550 0.03 400 Employee CreatAces Customer Sales Uab x, x' A-2-A- Font Paragraph A D E 1 INV_NUM INV DATE CUST_CODE INV TOTAL EMP NUM 2 960 09-Sep-20 458 29.18 83792 3 961 09-Sep-20 163 40.77 84392 963 09-Sep-20 335 305,46 83820 964 09-Sep-20 427 85.98 84191 965 10-Sep-20 459 236.93 84078 10-Sep-20 460 155.96 84078 966 967 10-Sep-20 151 53.55 84306 969 10-Sep-20 400 35.97 84334 970 10-Sep-20 213 29.78 84392 971 10-Sep-20 423 270.88 84364 972 10-Sep-20 461 356.22 83964 973 11-Sep-20 206 183,29 83964 975 11-Sep-20 335 115.94 84049 976 11-Sep-20 382 60.17 84049 977 11-Sep-20 105 245.38 83763 978 11-Sep-20 462 97.82 84021 979 11-Sep-20 329 164.25 83936 981 11-Sep-20 305 286.46 84191 982 11-Sep-20 463 61.95 83878 983 12-Sep-20 424 293.34 83565 984 12-Sep-201 134 252.57 84163 986 12-Sep-20 159 221.85 83792 987 12-Sep-20 100 305.83 84276 988 12-Sep-20 377 161.07 84276 989 12-Sep-201 312 141.89 83763 990 12-Sep-20 302 127.73 84078 992 13-Sep-20 458 293.83 84078 207 472.35 83906 993 13-Sep-20 994 13-Sep-20 995 13-Sep-20 465 277.04 84049 466 23.59 84248 467 $09.04 996 13-Sep-20 998 13-Sep-20 81820 321 92.3 83677 415 999 13-Sep-20 1000 14-Sep-20 269.22 83509 153 114.95 83964 1001 14-Sep-20 335 127.95 84364 Teatfunctions NewTetworksheet NewTop100 Codes Cuenet Sales 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 R1932X 26 Top 100 A > H G > H T Normal Employe 1 No Spac... O Styles 2 3 4 3 1 7 4. 9. 10 11 2 33 24 RS 16 37 16 19 20 215 22 23 24 26 CUST CODE CUST NAME RUBY SERVICE & SALES 87 MICHEAL UMITED UNO AND SONS 9 MONICA AND SONS 90 HELEN INC. 91 ALEXANDRA AND SONS 92 CARLENE LIMITED 93 CIARA SERVICE & SALES 94 DOMINICK LIMITED 95 WILMA INC. 96 ROLANDA INC. 97 ERWIN AND SONS 98 VALENTIN INC. 99 MINERVA AND SONS 100 VELDA INC. 101 LEONORA AND SONS 102 EVA SERVICE & SALES 103 CORRINA SERVICE & SALES 104 THAD INC. 105 FRANCIS INC. 106 MELONIE INC. 107 RAY INC. 108 GONZALO INC 309 SAL AND SONS 110 CRISTINA SERVICE & SALES 111 JOSE AND SONS 112 LAN AND SONS 113 KORY SERVICE & SALES 114 MIRIAN INC. 115 STACIE LIMITED 116 STEPHAINE AND SONS 117 KARON INC. 118 JESSE AND SONS 119 MATTIE INC 120 BRADLEY INC. wetWork Top CUST STREET 1013 SNOWLINE ORIVE 1027 POLLOCK DRIVE 1201 BARNES AVENUE 697 ADAK CIRCLE 1823 HORIZON STREET 481 CHALLENGER CIRCLE 1162 CAMPBELL CREEK ROAD 1977 SEAWOU DRIVE 62 EAST 25TH COURT 1575 KING WAY DRIVE 1415 AUDUBON DRIVE 884 PLEASANT DRIVE 1377 RUSHWOOD CIRCLE 582 WINGATE ORCLE 145 CRESCENT DRIVE 2 EAST DOWLING ROAD 1084 LUNAR CIRCLE 1651 HIGHTOWER ROAD 966 VALARIAN STREET 1921 WEST 74TH AVENUE 848 DOWNEY FINCH LANE 303 TEEKOANA CIRCLE 1567 WALLACE MOUNTAIN BOAD 985 OSTOVIA OROL 904 PINNACLE CIRCLE 640 WEST 71ST COURT 367 LAKEVIEW DRIVE 1949 HUMPHREY DRIVE 1432 POTTER GLEN CIRCLE 704 WOODLAND DRIVE 1372 LOREN CIRCLE 1073 FERN CIRCLE 614 SOUTH ARPARK DRIV 548 FALLING WATER DRIVE 1153 TOBUE CIRCLE NewTa CHOREDI 5 Select- Editing D CUST CITY Fayetteville Greenback Alquina Loachapoka Catawba Heights Autor Escanaba Hope Upron Bevina Center Calverton Kingston White Plaies West Medford Tampa Reese Queen Hampden Pine Neelyton Fast Monkton Cliftonville Crescent Beach Harts Store Can Na D Wyane Petsburgh Cochecton NAM Ontle Saint Paris Bay Mate Bay Head Sebuy Philadelphia 326 6954C Dictate Sensitivity Voice Sensitivity Re CUST STATE NG CUSTOTIP 28305 37742 47331 MMS 2012 47001 49429 04647 17225 33740 11933 02881 30629 02156 33641 28683 16670 25423 12208 17239 05443 19739 34242 2012 47522 15695 15262 12726 16938 45742 43072 01060 08242 06092 NY At NY MA FL NO PA ww NY PA VT ***21*1885201