Madison Lighting has several plants located around the United States. The company's controller has imported its job cost data from the past year into Excel. The following is a sample of the table of the records in that Excel file. (Click the icon to view the Excel worksheet.) Read the requirements. 1. What was the total job cost for each lamp model? Choose the fields, if any, that you would add to the Pivot table report by using the dropdown list in each report area. (If you select a field for the Values section, select what type of calculation you would use to summarize the value field using the dropdown list. If an area of the report does not need any Pivot table fields, leave the input fields empty.) Filters Columns Data Table Rows Values What type of calculation you would use to summarize the value field B D E F G H 1 J 1 Date Plant DM DL MOH Job cost Cost per unit Job # Lamp model Units 5180 Desk 20 2 2/2/2019 Ohio 110.00 62.00 76.00 248.00 12.40 3 Floor 30 453.00 183.00 255.00 891.00 29.70 Requirements 2/4/2019 Maine 5185 2/5/2019 Ohio 5215 4 Table 25 227.50 112.50 147.50 487.50 19.50 5 2/9/2019 Texas 5231 Floor 40 612.00 248.00 348.00 1,208.00 30.20 6 Floor 60 870.00 354.00 498.00 1,722.00 28.70 7 2/11/2019 Texas 5267 2/12/2019 Ohio 5318 2/14/2019 Maine 5348 Table 15 141.00 70.50 91.50 303.00 20.20 8 Desk 101 62.00 33.00 40.00 135.00 13.50 For each of the situations listed below, assume that you would create a pivot table to answer the question. Indicate which field you would use for the Rows box, which field (if any) you would use for the Columns box, and which field you would use for the Values box. Also indicate what type of calculation you would use to summarize the value field. (Note: You do not need to create the pivot table in Excel in this exercise.) 1. What was the total job cost for each lamp model? 2. What was the total job cost for each lamp model by plant? 3. What was the average cost per unit for each lamp model? 4. What was the average cost for each lamp model in each plant? 9 Desk 25 145.00 77.50 92.50 315.00 12.60 2/16/2019 Texas 5353 2/17/2019 Texas 5488 10 Table 351 308.00 150.50 196.00 654.50 18.70 11 2/20/2019 Ohio 5490 Table 75 652.50 315.00 412.50 1,380.00 18.40 12 Floor 75 1.117.50 450.00 29.30 2/21/2019 Maine 5501 2/23/2019 Ohio 5508 630.00 2,197.50 80.00 13 Desk 20 122.00 68.00 270.00 13.50 Print Done Print Done