Yazaki Test

profileabparsley
MTSUExcelTest.xlsx
Home>Business & Finance homework help>Yazaki Test

Sheet1

Excel Functions
Instructions
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
Open Inventory
On Hand Rail Yard On Boat In Quality Mfg Qty
G3572 2 12 18 28 38
D5904 9 19 29 39 49
D1476 18 28 38 48 58
D0557 41 51 61 71 81
C9208 82 92 102 112 122
C1319 169 179 189 199 209
W0933 338 348 358 368 378
W6415 169 179 189 199 209
W1051 338 348 358 368 378
W1117 169 179 189 199 209
1. From the Inventory above, find the value for each part # that is in transit via boat using Vlookup for the following Rows below:
Table 2
In Transit
C9208
D5904
G3572
W0933
D0557
W1051
2. After filling the Table 2 above SORT the ROW in ascending order
Containers to West Coast
26-Apr 29-Apr 30-Apr 1-May 2-May
PMAS1898 2 12 22 32 42
JMST1607 9 19 29 39 49
BMOU1776 18 28 38 48 58
JMST1607 41 51 61 71 81
PMAS1898 82 92 102 112 122
BMOU1776 169 179 189 199 209
BMOU1776 338 348 358 368 378
PMAS1898 169 179 189 199 209
JMST1607 338 348 358 368 378
UMTS1911 169 179 189 199 209
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:
Table 4
4/30
PMAS1898
BMOU1776
JMST1607
UMTS1911
4. From the Open Inventory table above, find how many parts are scheduled to be produced using Vlookup for the following Rows below:
Table 5
Mfg Qty
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)
Table 6 Total
G3572
D5904
D1476
D0557
C9208
C1319
W0933
W6415
W1117
W1051
6. Filter the parts in the Re-Worked Parts table below that will show 2001 BQBB only. (Don't remove the filter once you are done)
Re-Worked Parts
Inspection Water Dmg Wrong Clip Connector Lost
2001 ESSR 2 12 22 32 42
2001 PDQU 9 19 29 39 49
2001 BQBB 18 28 38 48 58
2001 PDQU 41 51 61 71 81
2001 ESSR 82 92 102 112 122
2001 BQBB 169 179 189 199 209
2001 BQBB 338 348 358 368 378
2001 ESSR 169 179 189 199 209
2001 PDQU 338 348 358 368 378
2001 BQBB 169 179 189 199 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 Part Number
Columns Date
Body QTY
Table 8 Part Number Day Date QTY
2001 PDQU WED 08/15/18 36
2001 AMTQ WED 08/15/18 36
2001 PDQU THU 08/16/18 36
2001 BQBB THU 08/16/18 36
2001 PDQU FRI 08/17/18 36
2001 AMTQ FRI 08/17/18 36
2001 PDQU SAT 08/18/18 36
2001 BQBB SAT 08/18/18 36
2001 CQBQ SAT 08/18/18 36
2001 AMTQ SAT 08/18/18 36
2001 PDQU MON 08/20/18 36
2001 AMTQ MON 08/20/18 36
2001 PDQU TUE 08/21/18 36
2001 BQBB TUE 08/21/18 36
2001 PDQU WED 08/22/18 36
2001 BQBB WED 08/22/18 36
2001 CQBQ WED 08/22/18 36
2001 AMTQ WED 08/22/18 36
2001 PDQU THU 08/23/18 72
2001 BQBB THU 08/23/18 36
2001 CQBQ THU 08/23/18 72
2001 AMTQ THU 08/23/18 36
2001 PDQU FRI 08/24/18 72
2001 BQBB FRI 08/24/18 72
2001 CQBQ FRI 08/24/18 72
2001 AMTQ FRI 08/24/18 72
2001 ESSR SAT 09/01/18 0
2001 PDQU SAT 09/01/18 370
2001 BQBB SAT 09/01/18 24
2001 CQBQ SAT 09/01/18 1044
2001 AMTQ SAT 09/01/18 252
2001 ESSR MON 10/01/18 0
2001 PDQU MON 10/01/18 0
2001 BQBB MON 10/01/18 0
2001 CQBQ MON 10/01/18 864
2001 AMTQ MON 10/01/18 288
2001 ESSR THU 11/01/18 0
2001 PDQU THU 11/01/18 0
2001 BQBB THU 11/01/18 0
2001 CQBQ THU 11/01/18 540
2001 AMTQ THU 11/01/18 324
2001 ESSR SAT 12/01/18 0
2001 PDQU SAT 12/01/18 0
2001 BQBB SAT 12/01/18 0
2001 CQBQ SAT 12/01/18 370
2001 AMTQ SAT 12/18/18 136
8. Using Table 8 sort this table by part number
9. Using Table 8 determine the total of the part number below using SUMIF
Part Number Total
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
Part Number Oct QTY
2001 PDQU
2001 AMTQ
2001 BQBB
2001 CQBQ
2001 ESSR

SAGOT

YNAN MSExcel Proficiency Confirmation
Instruction
1. Answer using this worksheet
2. Read the instruction carefully, please take note that some of the questions are referring 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 15 minutes but if you need more than 15 minutes please let us know.
5. After you finished save this file with using your LAST NAME
Table 1 Colum 1 Colum 2 Colum 3 Colum 4 Colum 5
Row A 2 12 22 32 42
Row B 9 19 29 39 49
Row C 18 28 38 48 58
Row D 41 51 61 71 81
Row E 82 92 102 112 122
Row F 169 179 189 199 209
Row G 338 348 358 368 378
Row H 169 179 189 199 209
Row I 338 348 358 368 378
Row J 169 179 189 199 209
1. In Table 1 above try to get the data in Column 3 using Vlookup for the following Rows below
Table 2 Column 3
Row A 22 NG C9208
Row C 38 NG D5904
Row E 102 NG G3572
Row F 189 NG W0933
Row G 358 NG D0557
Row I 358 NG W1051
2. After filling the Table 2 above SORT the ROW in ascending order
3. Using Table 3 find the total of Column 4 for the following ROWs below (Table 4) using SUMIF functions
Table 3 Colum 1 Colum 2 Colum 3 Colum 4 Colum 5
Row A 2 12 22 32 42
Row B 9 19 29 39 49
Row C 18 28 38 48 58
Row B 41 51 61 71 81
Row A 82 92 102 112 122
Row C 169 179 189 199 209
Row C 338 348 358 368 378
Row A 169 179 189 199 209
Row B 338 348 358 368 378
Row D 169 179 189 199 209
Table 4 Column 4
Row A 343 NG
Row B 478 NG
Row C 615 NG
Row D 199 NG
4. In Table 1 above try to get the data in Column 2 using Vlookup for the following Rows below
Table 5 Column 2
Row G 348 NG
Row B 19 NG
Row J 179 NG
Row E 92 NG
Row H 179 NG
Row D 51 NG
5. In Table 1 above try to get the TOTAL for each of the ROWs below
Table 6 Total
Row A 110 NG
Row B 145 NG
Row C 190 NG
Row D 305 NG
Row E 510 NG
Row F 945 NG
Row G 1790 NG
Row H 945 NG
Row I 1790 NG
Row J 945 NG
6. Filter Table 7 below that will show ROW C only. (Don't remove the filter once you are done)
Table 7 Colum 1 Colum 2 Colum 3 Colum 4 Colum 5
Row A 2 12 22 32 42
Row B 9 19 29 39 49
Row C 18 28 38 48 58
Row B 41 51 61 71 81
Row A 82 92 102 112 122
Row C 169 179 189 199 209
Row C 338 348 358 368 378
Row A 169 179 189 199 209
Row B 338 348 358 368 378
Row C 169 179 189 199 209
7. Use Table 8 and create a Pivot table with the following specification
Rows Part Number
Columns Date
Body QTY
Table 8 Part Number Day Date QTY
24010 3JA0A SAT 09/01/12 0 Sum of QTY Date
24010 3JA0A MON 10/01/12 0 Part Number 08/15/12 08/16/12 08/17/12 08/18/12 08/20/12 08/21/12 08/22/12 08/23/12 08/24/12 09/01/12 10/01/12 11/01/12 12/01/12 Grand Total
24010 3JA0A THU 11/01/12 0 24010 3JA0A 0 0 0 0 0
24010 3JA0A SAT 12/01/12 0 24010 3JA1A 36 36 36 36 36 36 36 72 72 370 0 0 0 766
24010 3JA1A WED 08/15/12 36 24010 3JA1B 36 36 36 36 36 72 24 0 0 0 276
24010 3JA1A THU 08/16/12 36 24010 3JA1C 36 36 72 72 1044 864 540 370 3034
24010 3JA1A FRI 08/17/12 36 24010 3JA1E 36 36 36 36 36 36 72 252 288 324 136 1288
24010 3JA1A SAT 08/18/12 36 Grand Total 72 72 72 144 72 72 144 216 288 1690 1152 864 506 5364
24010 3JA1A MON 08/20/12 36
24010 3JA1A TUE 08/21/12 36
24010 3JA1A WED 08/22/12 36
24010 3JA1A THU 08/23/12 72
24010 3JA1A FRI 08/24/12 72
24010 3JA1A SAT 09/01/12 370
24010 3JA1A MON 10/01/12 0
24010 3JA1A THU 11/01/12 0
24010 3JA1A SAT 12/01/12 0
24010 3JA1B THU 08/16/12 36
24010 3JA1B SAT 08/18/12 36
24010 3JA1B TUE 08/21/12 36
24010 3JA1B WED 08/22/12 36
24010 3JA1B THU 08/23/12 36
24010 3JA1B FRI 08/24/12 72
24010 3JA1B SAT 09/01/12 24
24010 3JA1B MON 10/01/12 0
24010 3JA1B THU 11/01/12 0
24010 3JA1B SAT 12/01/12 0
24010 3JA1C SAT 08/18/12 36
24010 3JA1C WED 08/22/12 36
24010 3JA1C THU 08/23/12 72
24010 3JA1C FRI 08/24/12 72
24010 3JA1C SAT 09/01/12 1044
24010 3JA1C MON 10/01/12 864
24010 3JA1C THU 11/01/12 540
24010 3JA1C SAT 12/01/12 370
24010 3JA1E WED 08/15/12 36
24010 3JA1E FRI 08/17/12 36
24010 3JA1E SAT 08/18/12 36
24010 3JA1E MON 08/20/12 36
24010 3JA1E WED 08/22/12 36
24010 3JA1E THU 08/23/12 36
24010 3JA1E FRI 08/24/12 72
24010 3JA1E SAT 09/01/12 252
24010 3JA1E MON 10/01/12 288
24010 3JA1E THU 11/01/12 324
24010 3JA1E SAT 12/01/12 136
8. Using Table 8 sort this table by part number
9. Using Table 8 determine the total of the part number below using SUMIF
Part Number Total
24010 3JA0A 0 1
24010 3JA1A 766 NG
24010 3JA1B 276 NG
24010 3JA1C 3034 NG
24010 3JA1E 1288 NG
10. Using the PIVOT table that you created in question 7, get the QTY for the month of October using VLOOKUP functions
Part Number Oct QTY 1
24010 3JA0A 0 1
24010 3JA1A 0 1
24010 3JA1B 0 1
24010 3JA1C 864 NG
24010 3JA1E 288 NG