Don't forget to enable macros before you start editing the Excel file. The HW2 Start File...

Transcribed Image Text:

Don't forget to enable macros before you start editing the Excel file. The HW2 Start File contains data imported into Excel from Microsoft's Northwind database. There are worksheets for the company's customers, products, product categories, and transactions. Each transaction is for a product purchased by a customer, but when a customer purchases multiple products at the same time, there are several corresponding rows in the Transactions table, one for each product purchased. 1. The ID columns (the first four columns) allow you to look up names of customers, products, and product categories. However, some of the IDs in the Transactions may have error. There are three types of errors. a. First, an ID in the Transactions sheet might not correspond to any customer, product, or product category. b. Second, because each order is by a single customer, a given OrderID should correspond to only one CustomerID, but there may be some Order IDs that have two or more CustomerIDs associated with them. c. Third, a given product ID should always correspond to the same product CategoryID, but there may be some productIDs that are associated with more than one CategoryID. 2. Besides the corrupted IDs, there is one other potential type of error, concerning dates. Shipping dates can be blank (for orders that haven't yet shipped), but they shouldn't be before the corresponding order dates. Find all corrupted IDs and shipping dates in the Transactions sheet. Highlight all bad data in yellow. You don't need to change them (because in most cases there is no way of knowing the correct values). Save the file with the name HW2 YourLastName.xlsm and upload it to the proper Assignment link in Brightspace. A B E F G H 1 Orderl Customerll Productl ProductCategoryll OrderDat ShippedDat Quant UnitPri Disco 2 10400 19 29 6 1/1/1997 1/16/1997 21 $99.00 0% 34 3 10400 19 35 1 1/1/1997 1/16/1997 35 $14.40 0% 4 10400 19 49 3 1/1/1997 1/16/1997 30 $16.00 0% 66 5 10401 65 30 8 1/1/1997 1/10/1997 18 $20.70 0% 6 10401 65 56 5 1/1/1997 1/10/1997 70 $30.40 0% 7 10401 65 65 2 1/1/1997 1/10/1997 20 $16.80 0% 8 10401 65 71 4 1/1/1997 1/10/1997 60 $17.20 0% 9 10402 10 10402 11 10403 222 23 5 1/2/1997 1/10/1997 60 $7.20 0% 63 2 1/2/1997 1/10/1997 65 $35.10 0% 16 3 1/3/1997 1/9/1997 21 $13.90 15% 12 10403 20 48 3 1/3/1997 1/9/1997 70 $10.20 15% 13 10404 49 26 3 1/3/1997 1/8/1997 30 $24.90 5% 14 10404 49 42 5 1/3/1997 1/8/1997 40 $11.20 5% 15 10404 49 49 3 1/3/1997 1/8/1997 30 $16.00 5% 16 10405 47 3 2 1/6/1997 1/22/1997 50 $8.00 0% 17 10406 62 1 1 1/7/1997 1/13/1997 10 $14.40 0% 18 10406 62 21 3 1/7/1997 1/13/1997 30 $8.00 10% 19 10406 62 28 7 1/7/1997 1/13/1997 42 $36.40 10% 20 10406 62 36 8 1/7/1997 1/13/1997 5 $15.20 10% 21 10406 62 40 8 1/7/1997 1/13/1997 2 $14.70 10% 22 10407 56 11 4 1/7/1997 1/30/1997 30 $16.80 0% 23 10407 56 69 4 1/7/1997 1/30/1997 15 $28.80 0% 24 10407 56 71 4 1/7/1997 1/30/1997 15 $17.20 0% 25 10408 23 37 8 1/8/1997 1/14/1997 10 $20.80 0% 26 10408 23 54 6 1/8/1997 1/14/1997 6 $5.90 0% 27 10408 23 62 3 1/8/1997 1/14/1997 35 $39.40 0% 28 10409 54 14 7 1/9/1997 1/14/1997 12 $18.60 0% 29 10409 54 21 3 1/9/1997 1/14/1997 12 $8.00 0% 30 10410 10 33 4 1/10/1997 1/15/1997 49 $2.00 0% 31 10410 10 59 6 1/10/1997 1/15/1997 16 $44.00 0% 32 10411 10 41 8 1/10/1997 1/21/1997 25 $7.70 20% 33 10411 10 44 2 1/10/1997 1/21/1997 40 $15.50 20% 34 10411 10 59 4 1/10/1997 1/21/1997 9 $44.00 20% 35 10412 87 14 7 1/13/1997 1/15/1997 20 $18.60 10% 36 10413 41 1 1 1/14/1997 1/16/1997 24 $14.40 0% 37 10413 41 62 3 1/14/1997 1/16/1997 40 $39.40 0% 38 10413 41 76 1 1/14/1997 1/16/1997 14 $14.40 0% 39 10414 21 19 3 1/14/1997 1/17/1997 18 $7.30 5% Log Transactions Customers Products Product Categories Don't forget to enable macros before you start editing the Excel file. The HW2 Start File contains data imported into Excel from Microsoft's Northwind database. There are worksheets for the company's customers, products, product categories, and transactions. Each transaction is for a product purchased by a customer, but when a customer purchases multiple products at the same time, there are several corresponding rows in the Transactions table, one for each product purchased. 1. The ID columns (the first four columns) allow you to look up names of customers, products, and product categories. However, some of the IDs in the Transactions may have error. There are three types of errors. a. First, an ID in the Transactions sheet might not correspond to any customer, product, or product category. b. Second, because each order is by a single customer, a given OrderID should correspond to only one CustomerID, but there may be some Order IDs that have two or more CustomerIDs associated with them. c. Third, a given product ID should always correspond to the same product CategoryID, but there may be some productIDs that are associated with more than one CategoryID. 2. Besides the corrupted IDs, there is one other potential type of error, concerning dates. Shipping dates can be blank (for orders that haven't yet shipped), but they shouldn't be before the corresponding order dates. Find all corrupted IDs and shipping dates in the Transactions sheet. Highlight all bad data in yellow. You don't need to change them (because in most cases there is no way of knowing the correct values). Save the file with the name HW2 YourLastName.xlsm and upload it to the proper Assignment link in Brightspace. A B E F G H 1 Orderl Customerll Productl ProductCategoryll OrderDat ShippedDat Quant UnitPri Disco 2 10400 19 29 6 1/1/1997 1/16/1997 21 $99.00 0% 34 3 10400 19 35 1 1/1/1997 1/16/1997 35 $14.40 0% 4 10400 19 49 3 1/1/1997 1/16/1997 30 $16.00 0% 66 5 10401 65 30 8 1/1/1997 1/10/1997 18 $20.70 0% 6 10401 65 56 5 1/1/1997 1/10/1997 70 $30.40 0% 7 10401 65 65 2 1/1/1997 1/10/1997 20 $16.80 0% 8 10401 65 71 4 1/1/1997 1/10/1997 60 $17.20 0% 9 10402 10 10402 11 10403 222 23 5 1/2/1997 1/10/1997 60 $7.20 0% 63 2 1/2/1997 1/10/1997 65 $35.10 0% 16 3 1/3/1997 1/9/1997 21 $13.90 15% 12 10403 20 48 3 1/3/1997 1/9/1997 70 $10.20 15% 13 10404 49 26 3 1/3/1997 1/8/1997 30 $24.90 5% 14 10404 49 42 5 1/3/1997 1/8/1997 40 $11.20 5% 15 10404 49 49 3 1/3/1997 1/8/1997 30 $16.00 5% 16 10405 47 3 2 1/6/1997 1/22/1997 50 $8.00 0% 17 10406 62 1 1 1/7/1997 1/13/1997 10 $14.40 0% 18 10406 62 21 3 1/7/1997 1/13/1997 30 $8.00 10% 19 10406 62 28 7 1/7/1997 1/13/1997 42 $36.40 10% 20 10406 62 36 8 1/7/1997 1/13/1997 5 $15.20 10% 21 10406 62 40 8 1/7/1997 1/13/1997 2 $14.70 10% 22 10407 56 11 4 1/7/1997 1/30/1997 30 $16.80 0% 23 10407 56 69 4 1/7/1997 1/30/1997 15 $28.80 0% 24 10407 56 71 4 1/7/1997 1/30/1997 15 $17.20 0% 25 10408 23 37 8 1/8/1997 1/14/1997 10 $20.80 0% 26 10408 23 54 6 1/8/1997 1/14/1997 6 $5.90 0% 27 10408 23 62 3 1/8/1997 1/14/1997 35 $39.40 0% 28 10409 54 14 7 1/9/1997 1/14/1997 12 $18.60 0% 29 10409 54 21 3 1/9/1997 1/14/1997 12 $8.00 0% 30 10410 10 33 4 1/10/1997 1/15/1997 49 $2.00 0% 31 10410 10 59 6 1/10/1997 1/15/1997 16 $44.00 0% 32 10411 10 41 8 1/10/1997 1/21/1997 25 $7.70 20% 33 10411 10 44 2 1/10/1997 1/21/1997 40 $15.50 20% 34 10411 10 59 4 1/10/1997 1/21/1997 9 $44.00 20% 35 10412 87 14 7 1/13/1997 1/15/1997 20 $18.60 10% 36 10413 41 1 1 1/14/1997 1/16/1997 24 $14.40 0% 37 10413 41 62 3 1/14/1997 1/16/1997 40 $39.40 0% 38 10413 41 76 1 1/14/1997 1/16/1997 14 $14.40 0% 39 10414 21 19 3 1/14/1997 1/17/1997 18 $7.30 5% Log Transactions Customers Products Product Categories