Read these Instructions carefully! For October 2021, TruBent Machining's manufacturing activities generated the records shown in the Data sheet. TruBent worked on numerous Jobs that month and each data record pertains to the inputs for each job. The records are in chronological order and each record has fields for :Year, Month, Day, Job No., Type, Sub-Category, and Quantity (measured in appropriate input units -- for example, labor and machine quantities are measured in hours.) In order to complete the assignment you will need to do the following activities: 1. Watch video and use the practice data (optional if you already know to work with Excel Tables and PivotTables). 2. Prepare the Data by adding Unit Costs and Item Costs as explained on the Data tab. 3. Create an Excel table using the data in the Data tab. 4. Use the Excel table to create a PivotTable located in the Pivot tab. 5. Answer the questions given below. a. Some of the questions pertain to the specific job high-lighted in Green below. Note that this job is keyed to your UIN. Therefore be sure that you have entered your UIN and Name on the "Name & ID" tab. b. Some of the questions pertain to the entirety of the data set and not a particular job. Among the objectives are to determine the total amount of costs added in October to WIP and to Job No. 6501 The Costs per unit for each type of input by category are given in the table below. Machine costs are treated as overhead. Direct costs pertain to materials and labor. There are two types of labor, "skilled" and "normal". Each has a separate cost per labor hour. There are three types of materials used by TruBent, once again each material is measured in an appropriate input unit and the cost per unit is with respect to that input unit. Type OH Category Machine T1 Cost per unit $ 30.00 Machine T2 $ 41.00 Machine T3 $ 48.00 Materials Mat X1 $ 5.25 Mat X2 $ 7.50 Mat X3 $ 13.20 Labor Normal $ 29.00 Skilled $ 41.00 Required - Part 1 Questions 1 to 6 pertain only to Job No. ===> Enter your answers in the appropriate Yellow shaded cell!!! Determine the total Direct Material costs added (to WIP) in October 6501 Q1 Determine the total Direct Labor costs added in October Q2 Determine the total Machine 1 related OH costs added in October Q3 Determine the total Machine 2 related OH costs added in October Q4 Determine the total Machine 3 related OH costs added in October Q5 Determine the total manufacturing costs added in October Q6 Required - Part 2 Questions 7 to 12 pertain to all work done in October (i.e., include all jobs) Determine the total Direct Material costs added in October Q7 Determine the total Direct Labor costs added in October Determine the total Machine 1 related OH costs added in October Q8 Q9 Determine the total Machine 2 related OH costs added in October Q10 Determine the total Machine 3 related OH costs added in October Determine the total manufacturing costs added in October Q11 Q12 Use this data for creating a Table and then for creating a PivotTable. When you create your PivotTables be sure to place the PivotTable somewhere on the Pivot tab. See figure below for how to do this. This is important to make sure you do not over-write your data and also to make it easier for me to look at your work. Create PivotTable Choose the data that you want to analyze. Select a table or range Table Range: Data!$A$1:$F$501 Use an external data source No data fields have been retrieved. Choose where to place the PivotTable. New worksheet Existing worksheet Table/Range: Pluati$A$2 Year Month Day Job No. Type Category Quantity Unit Cost Item Cost 2021 10 1 6500 Material X1 2.1 2021 10 1 6501 Machine T2 7.9 2021 10 1 6501 Machine T3 14.6 2021 10 1 6502 Material X3 5.7 2021 10 1 6503 Labor Normal 13.75 2021 10 1 6503 Machine T3 3.6 2021 10 1 6503 Material X1 3.4 2021 10 1 6503 Material X2 3.4 2021 10 1 6504 Machine T1 11.05 2021 10 1 6505 Machine T1 6.4 2021 10 1 6505 Machine T1 6.45 2021 10 1 6506 Labor Skilled 3.25 2021 10 1 6506 Machine T2 15.5 2021 10 1 6506 Material X1 4.4 2021 10 1 6506 Material X1 15.8 2021 10 1 6508 Machine T2 14.95 2021 10 1 6508 Machine T3 19.5 2021 10 1 6508 Material X1 5.5 2021 10 1 6509 Labor Normal 10.25 2021 10 1 6513 Labor Normal 11.25 2021 10 1 6513 Labor Normal 6.75 Type Machine Category Rate T1 2021 10 1 6516 Labor Normal 10 T2 2021 10 1 6516 Labor Normal 7.75 T3 2021 10 1 6516 Machine T1 10.8 Material X1 2021 10 1 6516 Material X1 4.4 X2 2021 10 1 6516 Material X1 14.6 X3 2021 10 1 6517 Machine T1 10.6 Labor Normal 2021 10 1 6519 Labor Normal 8.5 Skilled 2021 10 1 6521 Machine T3 10.9 2021 10 1 6521 Material X3 8.6 2021 10 1 6522 Material X3 5.5 2021 10 1 6523 Machine T1 8 2021 10 1 6523 Machine T2 8.95 2021 10 1 6523 Material X1 5 2021 10 1 6525 Labor Skilled 0.5 2021 10 1 6525 Machine T1 7.2 2021 10 1 6525 Machine T3 17.25 2021 10 1 6525 Material X1 8.3 2021 10 1 6526 Labor Normal 6.75 2021 10 1 6527 Labor Normal 11.75 2021 10 1 6528 Material X1 11 2021 10 1 6529 Machine T1 6.25 2021 10 1 6530 Machine T2 12.2 2021 10 1 6530 Machine T3 19.45 2021 10 2 6500 Machine T3 5.9 2021 10 2 6501 Labor Normal 7 2021 10 2 6504 Labor Normal 8.5 2021 10 2 6505 Material X3 3.5 2021 10 2 6506 Labor Skilled 6.75 2021 10 2 6506 Machine T2 9.3 2021 10 2 6506 Material X1 2.6 2021 10 2 6507 Machine T1 8.85 2021 10 2 6507 Machine T2 8.9 2021 10 2 6507 Material X3 9.9 2021 10 2 6508 Machine T3 7.65 2021 10 2 6508 Material X1 3.9 2021 10 2 6508 Material X2 4.2 2021 10 2 6509 Machine T1 6.6 2021 10 2 6509 Machine T2 5.35 2021 10 2 6510 Labor Normal 9 2021 10 2 6510 Machine T1 11.6 2021 10 2 6510 Material X1 1.3 2021 10 2 6512 Labor Normal 11.25 2021 10 2 6513 Labor Skilled 2.75 2021 10 2 6513 Machine T3 7.25 2021 10 2 6513 Machine T3 2.15 2021 10 2 6514 Material X1 16.7 2021 10 2 6516 Machine T2 8.5 2021 10 2 6518 Machine T3 0.4 Cancel OK Use the blue area to create a look-up table that includes the unit costs for the various inputs Once you have this table completed add a column to Data set called Unit Cost. In this column use the function XLOOKUP to find the unit cost for each sub-category. Next add another column to the Data set called Item cost. This is the Qnt Unit Cost for each record You should complete this step before creating your PivotTable. Here are a few additional hints that you may find helpful 1. If you use a PivotTable result in a formula or refer to it in another cell, the value will change every time you change the PivotTable. 2. A work around for the above problem is to create additional PivotTables. Fortunately this can be done using Copy and Paste. a. Create a new tab to hold your new PivotTable b. Select a cell inside the old PivotTable, then choose PivotTable Analyze from the ribbon c. Now select the Select Icon > Entire PivotTable. Then select Copy or Control-C (Windows) Command-C (Mac) d. Go to the new tab and paste the PivotTable. You can now modify this PivotTable to answer a new question without changing the earlier PivotTable(s). 3. If you want data on a particular category of costs you can use categories or sub-categories as a filter by selecting the items you want on the filter you will get results that exclude the other categories. 4. It is possible to display results in other formats, one that might be handy is % of column total. For example, f you want to know what % of skilled labor hours were added to each job, you could create a PivotTable with with jobs in the Rows, skilled selected as the Filter, and Qnt selected as the > Values. Now to get % of Column go to the information icon for Qnt, this will bring up a dialog box. Pick "Show data as" then pick "% of Column Total". Note the last default is "No Calculation" 5. Here is a link to a video that goes over the XLOOKUP function in Excel https://youtu.be/xDUIDHPV1RE 2021 10 2 6509 Machine T1 6.6 2021 10 2 6509 Machine T2 5.35 2021 10 2 6510 Labor Normal 9 2021 10 2 6510 Machine T1 11.6 2021 10 2 6510 Material X1 1.3 2021 10 2 6512 Labor Normal 11.25 2021 10 2 6513 Labor Skilled 2.75 2021 10 2 6513 Machine T3 7.25 2021 10 2 6513 Machine T3 2.15 2021 10 2 6514 Material X1 16.7 2021 10 2 6516 Machine T2 8.5 2021 10 2 6518 Machine T3 0.4 2021 10 2 6520 Labor Normal 12 2021 10 2 6523 Machine T3 13.7 2021 10 2 6524 Labor Skilled 7.75 2021 10 2 6524 Machine T2 10.3 2021 10 2 6524 Machine T3 2.5 2021 10 2 6525 Labor Skilled 0.75 2021 10 2 6525 Material X1 16.8 2021 10 2 6526 Material X1 1 2021 10 2 6528 Machine T1 12.3 2021 10 2 6528 Material X1 12.9 2021 10 2 6529 Machine T2 10.95 2021 10 2 6529 Material X1 19.2 2021 10 2 6529 Material X2 29.1 2021 10 2 6530 Labor Normal 5.5 2021 10 2 6530 Labor Skilled 6.25 2021 10 3 6500 Machine T1 13.45 2021 10 3 6500 Machine T2 12.65 2021 10 3 6500 Material X3 8.6 2021 10 3 6501 Machine T1 7.85 2021 10 3 6501 Machine T3 8.95 2021 10 3 6501 Material X1 3.4 2021 10 3 6502 Labor Normal 10.75 2021 10 3 6503 Machine T2 4.25 2021 10 3 6505 Machine T1 11.8 2021 10 3 6505 Machine T2 15.1 2021 10 3 6507 Machine T1 6.95 2021 10 3 6509 Machine T1 6.05 2021 10 3 6510 Labor Skilled 5.5 2021 10 3 6511 Material X1 18.8 2021 10 3 6513 Labor Normal 10.75 2021 10 3 6513 Material X2 4.4 2021 10 3 6514 Material X3 1 2021 10 3 6514 Material X3 8.7 2021 10 3 6516 Machine T3 12.55 2021 10 3 6517 Labor Normal 12.25 2021 10 3 6517 Material X1 2021 10 3 6518 Labor Normal 14.75 2021 10 3 6518 Machine T1 2021 10 3 6519 Machine T1 2021 10 3 6519 Machine T2 2021 10 3 6519 Material X1 2021 10 3 6520 Material X1 2021 10 3 6522 Labor Normal 12.25 2021 10 3 6522 Material X2 2021 10 3 6522 Material X2 2021 10 3 6523 Machine T1 2021 10 3 6523 Machine T3 2021 10 3 6524 Labor Skilled 2021 10 3 6525 Material X1 2021 10 3 6525 Material X2 2021 10 3 6530 Machine T3 2021 10 3 6530 Material X2 2021 10 3 6530 Material X3 2021 10 4 6500 Machine T3 2021 10 4 6500 Material X1 2021 10 4 6501 Material X1 2021 10 4 6502 Machine T1 2021 10 4 6502 Material X1 2021 10 4 6502 Material X2 2021 10 4 6504 Material X2 2021 10 4 6505 Labor Normal 12.25 22 , 2021 10 4 6505 Labor Normal 2021 10 4 6505 Material X1 2021 10 4 6506 Material X1 2021 10 4 6507 Material X1 2021 10 4 6507 Material X1 2021 10 4 6508 Machine T1 2021 10 4 2021 10 4 6509 Machine T1 6510 Material X2 2021 10 4 6510 Material X2 2021 10 4 6511 Labor Skilled 2021 10 4 6511 Material X1 2021 10 4 6512 Machine T1 2021 10 4 2021 10 4 6513 Machine T2 6513 Material X1 2021 10 4 6514 Machine T2 2021 10 4 6515 Material X1 2021 10 4 2021 10 4 2021 10 4 2021 10 2021 10 4 6516 Machine T1 6516 Machine T2 6516 Machine T3 6516 Machine T3 6516 Material X2 2021 10 4 6518 Machine T1 2021 10 4 2021 10 4 2021 10 4 2021 10 4 2021 10 4 2021 10 4 6518 Material X1 6519 Material X2 6520 Machine T2 6522 Machine T2 6522 Material X1 6523 Machine T1 2021 10 4 6524 Machine T2 2021 10 4 6524 Material X3 2021 10 4 6527 Material X1 2021 10 4 6528 Material X2 2021 10 4 6529 Machine T2 2021 10 4 6530 Material X3 2021 10 5 6501 Labor Normal 2021 10 5 6501 Machine T3 2021 10 5 6502 Labor Normal 2021 10 5 6504 Material X3 2021 10 5 6505 Labor Normal 2021 10 5 6505 Machine T1 2021 10 5 6505 Material X2 2021 10 5 6506 Material X3 2021 10 5 6506 Material X3 2021 10 5 6507 Labor Normal 2021 10 5 6510 Machine T3 2021 10 5 6512 Material X1 2021 10 5 6513 Material X2 2021 10 5 6515 Machine T1 2021 10 5 6515 Material X1 2021 10 5 6516 Machine T1 2021 10 5 6516 Material X1 2021 10 5 6516 Material X1 2021 10 5 6517 Labor Normal 2021 10 5 6517 Material X2 2021 10 5 6518 Machine T1 2021 10 5 6518 Material X1 2021 10 5 6519 Labor Skilled 2021 10 5 6520 Labor Normal 2021 10 5 6521 Material X1 2021 10 5 2021 10 5 6522 Machine T1 6523 Material X1 2021 10 5 6524 Machine T1 2021 10 5 6525 Labor Normal 2021 10 5 6525 Labor Normal 2021 10 5 6525 Material X1 2021 10 5 6526 Machine T2 2021 10 5 6527 Machine T2 2021 10 5 6528 Machine T1 2021 10 5 6528 Machine T2 2021 10 5 6530 Machine T1 2021 10 5 6530 Material X3 2021 10 6 6500 Material X2 2021 10 6 6501 Machine T1 2021 10 6 6501 Machine T2 2021 10 6 6503 Labor Normal 9.75 2021 10 6 6507 Material X1 16.3 2021 10 6 6510 Machine T1 12.8 2021 10 6 6513 Material X1 14.2 2021 10 6 6514 Machine T2 13.4 2021 10 6 6518 Machine T1 12.3 2021 10 6 6518 Material X3 3 2021 10 6 6519 Machine T1 9.2 2021 10 6 6519 Material X1 3.8 2021 10 6 6519 Material X2 1.2 2021 10 6 6520 Machine T3 16.65 2021 10 6 6520 Material X1 16.9 2021 10 6 6520 Material X3 5.6 2021 10 6 6521 Machine T1 11.85 2021 10 6 6523 Machine T2 4.65 2021 10 6 6524 Machine T3 3 2021 10 6 6527 Machine T2 14.1 2021 10 6 6528 Labor Normal 12.25 2021 10 6 6528 Machine T1 12.85 2021 10 7 6502 Labor Normal 10.5 2021 10 7 6502 Machine T1 7.5 2021 10 7 6503 Material X2 16 2021 10 7 6506 Labor Normal 5 2021 10 7 6506 Machine T1 13.45 2021 10 7 6506 Machine T3 15.2 2021 10 7 6506 Material X1 0.8 2021 10 7 6507 Labor Normal 10.5 2021 10 7 6509 Labor Skilled 6.75 2021 10 7 6510 Machine T1 6.35 2021 10 7 6511 Labor Skilled 9 2021 10 7 6512 Labor Skilled 6 2021 10 7 6513 Material X1 19.1 2021 10 7 6513 Material X1 5.4 2021 10 7 6516 Labor Skilled 5 2021 10 7 6516 Machine T3 8.45 2021 10 7 6519 Labor Normal 14.75 2021 10 7 6519 Labor Normal 9.75 2021 10 7 6519 Material X1 8.9 2021 10 7 6519 Material X2 29.8 2021 10 7 6520 Machine T1 10.15 2021 10 7 6521 Labor Skilled 8.75 2021 10 7 6524 Material X2 23.5 2021 10 7 6525 Labor Skilled 2.25 2021 10 7 6526 Labor Normal 9.25 2021 10 7 6526 Labor Normal 9.75 2021 10 7 6526 Labor Normal 13.25 2021 10 7 6526 Material X1 1.4 2021 10 7 6526 Material X3 3.3 2021 10 7 6527 Machine T1 10.8 2021 10 8 6500 Machine T3 10.35 2021 10 8 6504 Labor Normal 11.5 2021 10 8 6504 Labor Normal 9.25 2021 10 8 6505 Material X1 1.8 2021 10 8 6507 Material X1 1.7 2021 10 8 6509 Machine T2 4.45 2021 10 8 6509 Machine T3 3.25 2021 10 8 6510 Labor Normal 5.25 2021 10 8 6511 Material X1 1.2 2021 10 8 6512 Machine T1 6.55 2021 10 8 6512 Material X1 19.2 2021 10 8 6512 Material X1 19.7 2021 10 8 6512 Material X2 14.6 2021 10 8 6513 Machine T1 12.15 2021 10 8 6513 Machine T3 16.2 2021 10 8 6513 Material X1 15.2 2021 10 8 6514 Machine T1 10.2 2021 10 8 6514 Machine T3 13.3 2021 10 8 6516 Machine T1 8.7 2021 10 8 6517 Machine T3 11 2021 10 8 6517 Material X2 23.9 2021 10 8 6517 Material X3 3.1 2021 10 8 6518 Machine T2 6.05 2021 10 8 6518 Material X1 10.5 2021 10 8 6518 Material X3 2.2 2021 10 8 6519 Machine T2 13.9 2021 10 8 6520 Machine T1 12.5 2021 10 8 6521 Machine T2 8.25 2021 10 8 6522 Machine T2 9.3 2021 10 8 6523 Machine T1 10.9 2021 10 8 6523 Material X1 9.5 2021 10 8 6526 Material X2 15.8 2021 10 8 6529 Material X3 4.2 2021 10 9 6500 Machine T3 16.05 2021 10 9 6502 Material X1 13.5 2021 10 9 6504 Machine T1 11.85 2021 10 9 6504 Material X2 0.1 2021 10 9 6505 Labor Skilled 7.25 2021 10 9 6507 Labor Skilled 4.5 2021 10 9 6507 Labor Skilled 3.25 2021 10 9 6508 Material X1 18.1 2021 10 9 6509 Material X1 14.8 2021 10 9 6510 Labor Skilled 1.5 2021 10 9 6511 Labor Normal 7 2021 10 9 6511 Labor Normal 9 2021 10 9 6511 Machine T3 15.85 2021 10 9 6511 Machine T3 10.25 2021 10 9 6511 Material X2 19.7 2021 10 9 6512 Labor Normal 12 2021 10 9 6512 Machine T1 9.9 2021 10 9 6514 Labor Normal 11 2021 10 9 6514 Material X1 16.6 2021 10 9 6515 Machine T3 7.5 2021 10 9 6515 Material X1 14.3 2021 10 9 6516 Labor Skilled 4.5 2021 10 9 6517 Labor Normal 11.75 2021 10 9 6520 Labor Normal 5 2021 10 9 6521 Labor Normal 8.25 2021 10 9 6521 Material X2 13 2021 10 9 6522 Machine T2 7.2 2021 10 9 6522 Machine T2 12.05 2021 10 9 6526 Material X1 17.1 2021 10 9 6527 Machine T2 11.35 2021 10 9 6528 Material X1 13.9 2021 10 9 6528 Material X1 10.4 2021 10 9 6529 Labor Normal 7.5 2021 10 10 6500 Labor Normal 11.75 2021 10 10 6500 Labor Skilled 5 2021 10 10 6500 Material X1 10.8 2021 10 10 6501 Material X1 12.8 2021 10 10 6502 Material X3 4.8 2021 10 10 6503 Machine T1 11.6 2021 10 10 6503 Machine T2 9.95 2021 10 10 6503 Machine T3 0.45 2021 10 10 6505 Labor Skilled 5.75 2021 10 10 6506 Labor Skilled 1 2021 10 10 6508 Machine T1 10.9 2021 10 10 6514 Material X2 4 2021 10 10 6515 Material X1 17.6 2021 10 10 6517 Labor Normal 10.75 2021 10 10 6520 Labor Normal 8.5 2021 10 10 6520 Material X2 28.5 2021 10 10 6520 Material X2 21.8 2021 10 10 6521 Machine T2 7.45 2021 10 10 6521 Material X2 24.1 2021 10 10 6524 Material X2 10 2021 10 10 6525 Material X2 7.5 2021 10 10 6527 Machine T1 10.3 2021 10 10 6527 Material X1 5.5 2021 10 10 6528 Labor Skilled 3.5 2021 10 10 6528 Machine T3 12.7 2021 10 10 6529 Machine T1 6.6 2021 10 10 6530 Labor Skilled 4.5 2021 10 10 6530 Labor Skilled 4.25 2021 10 10 6530 Machine T1 7.25 2021 10 11 6501 Machine T1 10.8 2021 10 11 6501 Machine T3 10.3 2021 10 11 6501 Material X1 7.8