ce Wrap Text General Comments BIU - cr- A- + Merge & Center $ -% Insert Delete - Format 28 Conditional Format as Cell Formatting Table Styles Styles Font Alignment Sort & Find & Filter Select- Analyze Data Number Cells * Editing Analysis Sensity B D E G H J L M N O P 0 R S I Functions uctions 1. Answer using this worksheet 2. Read the instructions carefully, please take note that some of the questions are referring to a different table number 3. Don't just show the value in your answer but also the formula, we need to make sure that you used the right formula as instructed. 4. This test normally will take about 20 minutes. 5. Don't add or delete any rows or columns 6. After you finished save this file with using your LAST NAME aventory G3572 D5904 D1476 D0557 C9208 C1319 W0933 W6415 W1051 W1117 On Hand 2 9 18 41 82 169 338 169 338 169 Rail Yard 12 19 28 51 92 179 348 179 348 179 On Boat 18 29 38 61 102 189 358 189 358 189 Quality 28 39 48 71 112 199 368 199 368 199 Qty 38 49 58 81 122 209 378 209 378 209 1005 Sheet1 11:19 AM 3/30/2021 Type here to search B. D E F G H K L M N O P B 7. rom die Inventory above, find the value for each party that is in transitwia boak using Monikulor the Tollowing Mows below: ble 2 In Transit C9208 D5904 G3572 W0933 D0557 W1051 + 2. After filling the Table 2 above SORT the ROW in ascending order tainers to West Coast 26-Apr PMA S1898 2 JMST1607 9 BMOU1776 18 JMST1607 41 PMA S1898 82 BMOU1776 169 BMOU1776 338 PMA S1898 169 IMCT1607 338 Sheet1 + 29-Apr 12 19 28 51 92 179 348 179 318 30-Apr 22 29 38 61 102 189 358 189 258 1-May 32 39 48 71 112 199 368 199 368 2-May 42 49 58 81 122 209 378 209 378 100% 11:20 AM 3/30/2021 & Share Commen Arial 10 A A General 29 Wrap Text Merge & Center Paste AT O Insert 3X Delete Format GUN BIU $ % Conditional Format as Cell Formatting Table Styles Styles Sort Find a Filter Select Analyze Data Clipboard Font Alignment Number N Cells Editing Analysis Sensity D7 : x B D E F G H 1 J K M N 0 P P 38 39 A ling the table 2 sbor SORT the Row in scending den 40 41 Containers to West Coast 42 26 Apr 29-Apr 30-Apr 1-May 2-May 43 PMA S1898 2 12 22 32 42 JMST1607 44 9 19 29 49 39 18 BMOU1776 38 28 45 58 48 41 46 71 51 JMST1607 81 61 82 47 112 92 PMA S1898 122 102 179 189 169 199 209 48 BMOU1776 358 348 378 368 338 BMOU1776 49 189 199 179 209 169 PMAS1898 50 378 358 338 368 348 JMST1607 51 209 199 189 179 169 UMTS1911 52 53 54 55 3. Using the Containers to West Coast schedule, find the total number of pieces due to the West Coast on 4/30 for the following ROWs below (Table 4) using SUMIF functions: 56 57 Table 4 4/30 58 59 PMA S1898 60 BMOU1776 61 JMST1607 62 UMTS1911 E 63 + Sheet1 FO Arial 10 A Cup for Delete Sort & Find Filter Select Analyze Data Paste TAL Format % 898 41 Merge & Center - u Conditional Format as Cell Formatting Table Styles Styles Editing Anas Number Alignment Font Clipboard D7 M L N K J H G F E D B A ktore , file host wie wird hired hallall Man Who we the 55 56 7 Table 5 8 . W0933 D5904 W1117 C9208 W6415 D0557 5. From the Open Inventory table, try to get the TOTAL of open parts for each of the Rows below: (Use Sum function) Total Table 6 G3572 D5904 D1476 D0557 C9208 C1319 W0933 W6415 W1117 W1051 H F G 6. Filter the parts in the Re-Worked Parts table below that will show 2001 BOBB only. (Don't remove the filter once you are done) 1 J K M N Re-Worked Parts 2001 ESSR 2001 PDQU 2001 BQBB 2001 PDQU 2001 ESSR 2001 BQBB 2001 BQBB 2001 ESSR 2001 PDQU 2001 BOBB Inspection Water Dmg Wrong Clip Connector 2 12 22 32 9 19 29 39 18 28 38 48 41 51 61 82 92 112 169 179 189 199 338 348 358 368 169 179 189 199 338 348 358 368 169 179 189 199 102 Lost 42 49 58 81 122 209 378 209 378 209 7. Use Table 8 and create a Pivot table in cell G114 showing the due date of each part with the following specifications: Rows Columns Body Part Number Date QTY I Table 8 Part Number Day 2001 PDQU WED 2001 AMTQ WED Date 08/15/18 08/15/18 no 11G/10 QTY 36 36 20 7 A DARI TUL Sheet1 TTT Type here to search B. D F G . K Body QTY QTY 112 113 114 Table 8 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 Part Number Day 2001 PDQU WED 2001 AMTQ WED 2001 PDQU THU 2001 BQBB THU 2001 PDQU FRI 2001 AMTQ FRI 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 2001 PDQU MON 2001 AMTQ MON 2001 PDQU TUE 2001 BQBB TUE 2001 PDQU WED 2001 BQBB WED 2001 CQBQ WED 2001 AMTQ WED 2001 PDQU THU 2001 BQBB THU 2001 CQBQ THU 2001 AMTQ THU 2001 PDQU FRI 2001 ROBR FRI Date 08/15/18 08/15/18 08/16/18 08/16/18 08/17/18 08/17/18 08/18/18 08/18/18 08/18/18 08/18/18 08/20/18 08/20/18 08/21/18 08/21/18 08/22/18 08/22/18 08/22/18 08/22/18 08/23/18 08/23/18 08/23/18 08/23/18 08/24/18 08/24/18 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 72 36 72 36 72 72 138 Alignment Formatting al as Table Number Styles A F G H K 72 72 72 72 0 370 24 1044 252 0 0 B 2001 PDQU FRI 2001 BQBB FRI 2001 CQBQ FRI 2001 AMTQ FRI 2001 ESSR SAT 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 2001 ESSR MON 2001 PDQU MON 2001 BQBB MON 2001 CQBQ MON 2001 +1TQ MON 2001 ESSR THU 2001 PDQU THU 2001 BQBB THU 2001 CQBQ THU 2001 AMTQ THU 2001 ESSR SAT 2001 PDQU SAT 2001 BOBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 08/24/18 08/24/18 08/24/18 08/24/18 09/01/18 09/01/18 09/01/18 09/01/18 09/01/18 10/01/18 10/01/18 10/01/18 10/01/18 10/01/18 11/01/18 11/01/18 11/01/18 11/01/18 11/01/18 12/01/18 12/01/18 12/01/18 12/01/18 12/18/18 0 864 288 0 0 0 540 324 0 0 0 370 136 1 = Merge & Center $ 40 00 2x Dele Dard Font Conditional Format as Cell Formatting Table Styles Styles A For Alignment R Number Ce A B D E F G H J K M N 8. Using Table 8 sort this table by part number 9. Using Table & determine the total of the part number below using SUMIE Total Part Number 2001 PDQU 2001 AMTQ 2001 BQBB 2001 CQBQ 2001 ESSR 10. Using the PIVOT table that you created in question 7, get the QTY for the month of October using VLOOKUP functions Oct QTY Part Number 2001 PDQU 2001 AMTQ 2001 BQBB 2001 CQBQ 2001 ESSR Sheet1 ce Wrap Text General Comments BIU - cr- A- + Merge & Center $ -% Insert Delete - Format 28 Conditional Format as Cell Formatting Table Styles Styles Font Alignment Sort & Find & Filter Select- Analyze Data Number Cells * Editing Analysis Sensity B D E G H J L M N O P 0 R S I Functions uctions 1. Answer using this worksheet 2. Read the instructions carefully, please take note that some of the questions are referring to a different table number 3. Don't just show the value in your answer but also the formula, we need to make sure that you used the right formula as instructed. 4. This test normally will take about 20 minutes. 5. Don't add or delete any rows or columns 6. After you finished save this file with using your LAST NAME aventory G3572 D5904 D1476 D0557 C9208 C1319 W0933 W6415 W1051 W1117 On Hand 2 9 18 41 82 169 338 169 338 169 Rail Yard 12 19 28 51 92 179 348 179 348 179 On Boat 18 29 38 61 102 189 358 189 358 189 Quality 28 39 48 71 112 199 368 199 368 199 Qty 38 49 58 81 122 209 378 209 378 209 1005 Sheet1 11:19 AM 3/30/2021 Type here to search B. D E F G H K L M N O P B 7. rom die Inventory above, find the value for each party that is in transitwia boak using Monikulor the Tollowing Mows below: ble 2 In Transit C9208 D5904 G3572 W0933 D0557 W1051 + 2. After filling the Table 2 above SORT the ROW in ascending order tainers to West Coast 26-Apr PMA S1898 2 JMST1607 9 BMOU1776 18 JMST1607 41 PMA S1898 82 BMOU1776 169 BMOU1776 338 PMA S1898 169 IMCT1607 338 Sheet1 + 29-Apr 12 19 28 51 92 179 348 179 318 30-Apr 22 29 38 61 102 189 358 189 258 1-May 32 39 48 71 112 199 368 199 368 2-May 42 49 58 81 122 209 378 209 378 100% 11:20 AM 3/30/2021 & Share Commen Arial 10 A A General 29 Wrap Text Merge & Center Paste AT O Insert 3X Delete Format GUN BIU $ % Conditional Format as Cell Formatting Table Styles Styles Sort Find a Filter Select Analyze Data Clipboard Font Alignment Number N Cells Editing Analysis Sensity D7 : x B D E F G H 1 J K M N 0 P P 38 39 A ling the table 2 sbor SORT the Row in scending den 40 41 Containers to West Coast 42 26 Apr 29-Apr 30-Apr 1-May 2-May 43 PMA S1898 2 12 22 32 42 JMST1607 44 9 19 29 49 39 18 BMOU1776 38 28 45 58 48 41 46 71 51 JMST1607 81 61 82 47 112 92 PMA S1898 122 102 179 189 169 199 209 48 BMOU1776 358 348 378 368 338 BMOU1776 49 189 199 179 209 169 PMAS1898 50 378 358 338 368 348 JMST1607 51 209 199 189 179 169 UMTS1911 52 53 54 55 3. Using the Containers to West Coast schedule, find the total number of pieces due to the West Coast on 4/30 for the following ROWs below (Table 4) using SUMIF functions: 56 57 Table 4 4/30 58 59 PMA S1898 60 BMOU1776 61 JMST1607 62 UMTS1911 E 63 + Sheet1 FO Arial 10 A Cup for Delete Sort & Find Filter Select Analyze Data Paste TAL Format % 898 41 Merge & Center - u Conditional Format as Cell Formatting Table Styles Styles Editing Anas Number Alignment Font Clipboard D7 M L N K J H G F E D B A ktore , file host wie wird hired hallall Man Who we the 55 56 7 Table 5 8 . W0933 D5904 W1117 C9208 W6415 D0557 5. From the Open Inventory table, try to get the TOTAL of open parts for each of the Rows below: (Use Sum function) Total Table 6 G3572 D5904 D1476 D0557 C9208 C1319 W0933 W6415 W1117 W1051 H F G 6. Filter the parts in the Re-Worked Parts table below that will show 2001 BOBB only. (Don't remove the filter once you are done) 1 J K M N Re-Worked Parts 2001 ESSR 2001 PDQU 2001 BQBB 2001 PDQU 2001 ESSR 2001 BQBB 2001 BQBB 2001 ESSR 2001 PDQU 2001 BOBB Inspection Water Dmg Wrong Clip Connector 2 12 22 32 9 19 29 39 18 28 38 48 41 51 61 82 92 112 169 179 189 199 338 348 358 368 169 179 189 199 338 348 358 368 169 179 189 199 102 Lost 42 49 58 81 122 209 378 209 378 209 7. Use Table 8 and create a Pivot table in cell G114 showing the due date of each part with the following specifications: Rows Columns Body Part Number Date QTY I Table 8 Part Number Day 2001 PDQU WED 2001 AMTQ WED Date 08/15/18 08/15/18 no 11G/10 QTY 36 36 20 7 A DARI TUL Sheet1 TTT Type here to search B. D F G . K Body QTY QTY 112 113 114 Table 8 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 Part Number Day 2001 PDQU WED 2001 AMTQ WED 2001 PDQU THU 2001 BQBB THU 2001 PDQU FRI 2001 AMTQ FRI 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 2001 PDQU MON 2001 AMTQ MON 2001 PDQU TUE 2001 BQBB TUE 2001 PDQU WED 2001 BQBB WED 2001 CQBQ WED 2001 AMTQ WED 2001 PDQU THU 2001 BQBB THU 2001 CQBQ THU 2001 AMTQ THU 2001 PDQU FRI 2001 ROBR FRI Date 08/15/18 08/15/18 08/16/18 08/16/18 08/17/18 08/17/18 08/18/18 08/18/18 08/18/18 08/18/18 08/20/18 08/20/18 08/21/18 08/21/18 08/22/18 08/22/18 08/22/18 08/22/18 08/23/18 08/23/18 08/23/18 08/23/18 08/24/18 08/24/18 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 72 36 72 36 72 72 138 Alignment Formatting al as Table Number Styles A F G H K 72 72 72 72 0 370 24 1044 252 0 0 B 2001 PDQU FRI 2001 BQBB FRI 2001 CQBQ FRI 2001 AMTQ FRI 2001 ESSR SAT 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 2001 ESSR MON 2001 PDQU MON 2001 BQBB MON 2001 CQBQ MON 2001 +1TQ MON 2001 ESSR THU 2001 PDQU THU 2001 BQBB THU 2001 CQBQ THU 2001 AMTQ THU 2001 ESSR SAT 2001 PDQU SAT 2001 BOBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 08/24/18 08/24/18 08/24/18 08/24/18 09/01/18 09/01/18 09/01/18 09/01/18 09/01/18 10/01/18 10/01/18 10/01/18 10/01/18 10/01/18 11/01/18 11/01/18 11/01/18 11/01/18 11/01/18 12/01/18 12/01/18 12/01/18 12/01/18 12/18/18 0 864 288 0 0 0 540 324 0 0 0 370 136 1 = Merge & Center $ 40 00 2x Dele Dard Font Conditional Format as Cell Formatting Table Styles Styles A For Alignment R Number Ce A B D E F G H J K M N 8. Using Table 8 sort this table by part number 9. Using Table & determine the total of the part number below using SUMIE Total Part Number 2001 PDQU 2001 AMTQ 2001 BQBB 2001 CQBQ 2001 ESSR 10. Using the PIVOT table that you created in question 7, get the QTY for the month of October using VLOOKUP functions Oct QTY Part Number 2001 PDQU 2001 AMTQ 2001 BQBB 2001 CQBQ 2001 ESSR Sheet1