Required Create an automated process that can be used over and over again 1 Stream 1 Extract the data from the "Sales Report" sheet. Transform the data to the correct values as found on the Region table 2 Stream 2 Extract the data from the "Products" sheet Review the data, join to Stream 1 and verify the number of rows 3 Stream 3 Extract the data from the "Salesperson" sheet Review the data, join to combined Stream 1 and Stream 2 and verify the number of rows 4 Stream 4 Extract the data from the "Shipping Costs" sheet Review the data, create required fields and join to the previously combined streams. Verify that the number of rows has not changed 5 Create the number of units sold (units sold sales/seling price per unit) and the shipping cost (shipping cost-unds sold x shipping cost per unit) for each invoice For your project total sales are $11,440,836.00, units sold are 498,060 and total shipping costs are $1,022,715.12. 6 As illustrated below, create a report that show the number of units sold and sales in dollars by product 7 As illustrated below, create a report that show the number of units sold, sales in dollars and shipping costs by salesperson 8 As ilustrated below, create a cross-tab report that show the shipping costs by product and region 9 These three reports are to be saved in this Excel file. Sample reports Product Stuffed teac Stuffed Elephant Stuffed Frog Stuffed Koale Muffed Fanda Mufied Unicom Tuta By Product of units Sales 66936 $1,673,400.00 125,016 $2.625336.00 79248 51,745 45.00 33.240 97977 40.296 42.132 566868 926,808.00 842,040 00 18609,400.00 Salesperson Ulysses Grant George Washington James Monege Jahn Tyler by Salesperson of units James Madiso James Polk Sales 36,636 $18,052 00 58,284 $1,299,193 00 92,152 97,162,436.00 78,924 $1,751.352.00 38.308 5854412.00 77484 $1,725.956.00 305.368 58 600 400.00 Shipping Costs 57726644 $123,242 28 $20562264 $166.96.00 581,105 24 $163,717 08 $817.549.68 Midwest South Stuffed Sear Stuffed Elephant Stuffed Frog Stuffed Koala Stuffed Panda Sed Unicorn Grand Total Shipping Costs by Product and Region Northeast West Grand total 5 26,004.00 $ 43,048.44 5 32066 20 5 41647.80 5 13166044 5 54,810.00 $ 72,06084 55 56,971.80 $36232034 $36,405 60 5 52861.20 5 3075300 5 34,081.00 $174,101.40 3 14,094.00 $ 20,699 28 5 1457230 5 25364 40 5 74730 $ 36,875 60 3 26,664.00 $ 15,720.00 5 23.108 40 542358 00 31554820 5 30604.32 3182,717 40 524593808 91,394.12 LASHEAR 18,344.00 $ 27.997.50 149,743.20 298.131.00 Round other answers to the nearest whole unit or dollar 1 What were the sales of the Stuffed Bear product in dollars? 2 What were the shipping costs in dollars for the product with the fourth lowest shipping costs? 3. What were the units sold for the product with the second lowest number of units sold? 4 What were the sales in dollars for the product with the second lowest sales? 5 What was the shipping costs in dollars for the salesperson with the second highest shipping costs? What were the units sold for the salesperson with the fifth lowest number of units sold? 7 What were the sales in dollars for the salesperson with the third lowest sales? What were the shipping costs of the Stuffed Tiger product in the region with the Stuffed Tiger's third lowest shipping cost? 9 What were the shipping costs of the Stuffed Panda product in the region with the Shuffed Panda's third highest shipping cost? 10 What were the shipping costs of the Stuffed Bear product in the region with the Stuffed Bear's second lowest shipping cost? (1) (2) (3) (4) (5) (0) (2) (01 (0) (10) 20.00 22.00 Product ID Product 101 Stuffed Elephant 102 Stuffed Panda Cuddly Stuffed Animals Product Information Unit Selling Price Unit Manufacturing Cost Profit Margin by Product 8.75 11.25 9.50 12.50 103 Stuffed Koala 24.00 9.50 14.50 104 Stuffed Bear 21.00 10.75 10.25 105 Stuffed Tiger 23.00 11.75 11.25 106 Stuffed Horse 25.00 10.75 14.25 7 8 13 10 11 12 13 Code Cuddly Stuffed Animals Salesperson Information Salesperson 14 1301 James Polk 15 1302 Ulysses Grant 16 1303 Thomas Jefferson 17 1304 James Madison 18 1305 James Monroe 19 1306 Grover Cleveland 20 21 Title Sales Associate | Senior Sales Associate Senior Sales Associate Sales Associate | Sales Associate | Senior Sales Associate 22 Cuddly Stuffed Animals Region Information Code Region 2 3 4 5 6 1 Midwest IL IN IA KS MI 2 Northeast Cr ME MA NH 3 South AL DE FL 25 NE GA 4 West AK A2 CA 8 HI 83359 T 8 9 10 MN MO NE ND OH NY PA RI VT KY 35 LA MD MS NC MT NV NM OR ER 85 11 12 13 14 15 16 50 WI OK SC TN TX VA WV UT WA WY Cuddly Stuffed Animals Shipping Cost Per Unit Region Product ID Midwest Northeast South West 101 2.15 2.11 2.08 2.01 102 2.11 2.07 2.04 1.97 103 2.16 2.12 2.09 2.02 104 2.12 2.08 2.05 1.98 105 2.09 2.05 2.02 1.95 106 2.07 2.03 2.00 1.93 NO. QUESTION 1 What were the sales of the Stuffed Bear product in dollars? 2 What were the shipping costs in dollars for the product with the fourth lowest shipping costs? 3 What were the units sold for the product with the second lowest number of units sold? What were the sales in dollars for the product with the second lowest sales? ANSWER (Round amounts to the nearest dollar; enter percentages as nearest rounded whole number without the symbol, eg. 12 for 12.25%) 4 40 5 6 What were the sales in dollars for the product with the second lowest sales? What was the shipping costs in dollars for the salesperson with the second highest shipping costs? What were the units sold for the salesperson with the fifth lowest number of units sold? 7 What were the sales in dollars for the salesperson with the third lowest sales? 8 9 10. What were the shipping costs of the Stuffed Tiger product in the region with the Stuffed Tiger's third lowest shipping cost? What were the shipping costs of the Stuffed Panda product in the region with the Stuffed Panda's third highest shipping cost? What were the shipping costs of the Stuffed Bear product in the region with the Stuffed Bear's second lowest shipping cost