Job Order Costing Completing this activity will help you learn to Determine amounts in a job order cost accounting system for a manufacturing business Extract information from a data set using Excel formulas functions andor pivot tables Scenario Company A manufactures custom cabinets that it sells to eight different home builders The company began operations in January of this year The company uses a job order costing system to account for manufacturing costs The Data worksheet contains a list of jobs for eight home builders for the first year of operations Each row provides information about one cabinet manufacturing job including the name of the builder the county where the product will be installed number of direct labor hours direct labor per hour rate direct materials cost and status of the job completed or in progress Note that cell A5 contains the factory overhead rate per direct labor hour for all jobs Column A is not part of the data needed to create pivot tables Required Open spreadsheet Complete two columns on the Data worksheet Direct Labor and Factory Overhead Both are total amounts for the jobs R1 Cell I2 Enter a formula that uses number of direct labor hours and an hourly rate to determine total direct labor for the job fill in the blank 1 of 1 2790 R2 Cell J2 Enter a formula that uses number of direct labor hours and an hourly rate to determine total factory overhead for the job fill in the blank 1 of 1 3510 R3 Copy down the entries in cells I2 and J2 to all the other rows with data Steps to create a pivot table S1 Select a cell in the data on the Data worksheet S2 Select Insert PivotTable The data range will automatically be inserted A Create PivotTable window should appear S3 In the window under Choose where you want the PivotTable report to be placed select New Worksheet A new worksheet should appear with the PivotTable S4 A PivotTable Field List should pop up Drag fields into rows columns filters and values areas as needed If this gray work box does not appear right click in the pivot table and select Show Field List Questions Use the information on the Data worksheet and pivot tables formulas andor functions to answer the following questions Q1 What is the total cost of all jobs completed fill in the blank 1 of 1 709388200 Q2 What is the total cost of work in progress in Hall Union Pierce and Newton counties fill in the blank 1 of 1 Q3 What is the average direct materials cost for Caliber Creations rounded to the nearest dollar fill in the blank 1 of 1 39239 Q4 How many completed jobs for McNeil Builders PN Mull Associates and Spectrum Shores combined used more than 70 direct labor hours fill in the blank 1 of 1 16 Q5 The total cost of a manufactured product equals direct materials direct labor factory overhead indirect materials indirect labor factory overhead direct materials direct labor factory overhead indirect materials indirect labor factory overhead Check My Work