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, and/or 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: X Open spreadsheet Complete two columns on the Data worksheet: Direct Labor and Factory Overhead. Both are total amounts for the jobs. R1. Cell 12: Enter a formula that uses number of direct labor hours and an hourly rate to determine total direct labor for the job. $ 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. R3. Copy down the entries in cells 12 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 Pivot Table. 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 "S Field List.") Questions: Use the information on the Data worksheet and pivot tables, formulas, and/or functions to answer the following questions. Q1. What is the total cost of all jobs completed? Q2. What is the total cost of work in process in McDuffie, Chattooga, Fulton, and Bryan counties? $ Q3. What is the average direct materials cost for Quinn Quality Homes, rounded to the nearest dollar? $ Q4. How many completed jobs for McNeil Builders, PN Mull Associates, and Spectrum Shores combined used more than 60 direct labor hours? 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. 1 Factory overhead 2 rate per direct 3 labor hour 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 $81 Column A is not part of the data for the pivot tables. B Job Number 237 143 176 283 296 D Builder PN Mull Associates Harper Homes PN Mull Associates Harper Homes McNeil Builders PN Mull Associates 198 155 251 287 105 142 116 162 128 PN Mull Associates 298 PN Mull Associates 280 Rothschild Construction 161 PN Mull Associates 216 Caliber Creations 134 Spectrum Shores Harper Homes Spectrum Shores Rothschild Construction Caliber Creations Spectrum Shores Quinn Quality Homes Quinn Quality Homes E County McDuffie Gordon Telfair Gordon Putnam McDuffie Chattooga Union Fayette Queens Union Nassau Nassau Oconee McDuffie Macon McDuffie Queens Elbert F Direct Labor Hours 65 75 63 61 75 81 61 50 62 57 82 72 49 65 74 81 56 76 79 G Direct Labor Rate $48 $55 $50 $47 $36 $62 $49 $41 $45 $50 $35 $52 $37 $58 $38 $47 $63 $40 $50 H Direct Materials Direct Labor Factory Overhead $28,661 $47,339 $46,102 $45,763 $21,713 $51,163 $42,668 $48,817 $30,923 $34,296 $49,375 $53,980 $30,269 $56,397 $23,655 $33,635 $57,716 $39,650 $28.691 Status Completed Completed Completed In progress Completed In progress Completed Completed In progress Completed Completed In progress Completed In progress Completed In progress In progress Completed Completed 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 271 Quinn Quality Homes 103 Spectrum Shores 183 Rothschild Construction 117 PN Mull Associates 252 Rothschild Construction Macon 275 Rothschild Construction Fayette 152 Spectrum Shores Elbert Lanier 165 Caliber Creations 113 Rothschild Construction Macon PN Mull Associates 218 Telfair 135 Rothschild Construction Suffolk Pierce Queens PN Mull Associates Telfair Union Spectrum Shores Rothschild Construction Macon Fulton Bryan Haralson 276 148 227 190 107 197 181 173 Villa Communities Caliber Creations Villa Communities Spectrum Shores Quinn Quality Homes Walton Bryan Macon Telfair 47 60 65 83 53 75 69 52 62 63 62 55 71 51 72 48 55 65 77 $46 $50 $63 $61 $57 $38 $52 $36 $59 $55 $61 $36 $62 $60 $46 $60 $55 $62 $52 $29,765 $52,284 $50,866 $30,524 $23,084 $37,944 $27,959 $22,995 $33,347 $25,989 $28,632 $56,443 $50,380 $45,923 $23,877 $20,254 $36,622 $30,854 $58,811 Completed Completed Completed Completed In progress Completed In progress Completed In progress Completed In progress Completed In progress In progress Completed Completed In progress Completed Completed 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 Spectrum Shores Villa Communities Rothschild Construction Quinn Quality Homes Quinn Quality Homes Harper Homes 228 272 192 297 291 289 241 265 223 125 268 199 130 159 182 PN Mull Associates 263 Caliber Creations Spectrum Shores 211 123 Rothschild Construction 260 PN Mull Associates 164 Villa Communities PN Mull Associates Spectrum Shores Villa Communities PN Mull Associates Caliber Creations PN Mull Associates Harper Homes McNeil Builders Elbert Rockdale Macon Walton Walton Gordon McDuffie Bryan Rockdale Telfair Newton Oconee Chattooga Irwin Oconee Newton Union Macon Telfair Rockdale 57 60 65 70 51 67 85 49 47 73 50 50 77 65 74 83 61 65 51 78 $45 $38 $54 $57 $45 $64 $61 $47 $58 $54 $64 $35 $44 $50 $63 $40 $38 $51 $36 $53 $41,944 $24,676 $54,275 $51,545 $35,172 $55,117 $47,854 $49,697 $39,153 $43,350 $28,849 $58,577 $47,936 $57,482 $44,412 $46,098 $24,251 $47,691 $55,943 $32,139 Completed In progress Completed Completed Completed Completed Completed Completed In progress In progress Completed Completed Completed Completed Completed Completed Completed Completed In progress Completed 60612345601234 75 76 7 8 9 67 68 69 70 71 72 73 74 77 78 79 200 138 300 285 175 118 212 179 248 186 286 245 191 226 132 261 231 209 256 163 McNeil Builders Caliber Creations Rothschild Construction Caliber Creations PN Mull Associates PN Mull Associates Villa Communities Villa Communities Villa Communities Harper Homes Harper Homes Harper Homes Caliber Creations Caliber Creations PN Mull Associates Rothschild Construction Villa Communities Quinn Quality Homes Harper Homes McNeil Builders Sumter Queens Macon Newton Oconee Telfair Fulton Rockdale Rockdale Gordon Hall Gordon Newton Newton Telfair Fayette Pierce Nassau Gordon Sumter 55 50 70 46 46 79 60 76 53 81 83 59 46 76 60 69 63 60 68 68 $44 $48 $63 $35 $46 $56 $60 $61 $36 $53 $43 $53 $40 $41 $35 $65 $53 $60 $47 $59 $38,063 $52,732 $57,728 $38,544 $47,146 $23,655 $36,313 $55,007 $23,442 $32,420 $21,971 $57,165 $28,607 $58,362 $19,404 $50,635 $43,059 $51,153 $28,639 $34.630 In progress Completed Completed Completed In progress Completed In progress Completed In progress Completed Completed Completed Completed Completed Completed Completed Completed In progress Completed Completed 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 194 257 290 255 249 Spectrum Shores 250 Spectrum Shores 119 Quinn Quality Homes 184 Villa Communities PN Mull Associates PN Mull Associates 101 146 238 258 141 108 253 235 Harper Homes Caliber Creations Quinn Quality Homes Villa Communities Rothschild Construction Villa Communities McNeil Builders Rothschild Construction PN Mull Associates Spectrum Shores 270 Quinn Quality Homes 219 Villa Communities 214 Harper Homes McDuffie Telfair Hall Newton Elbert Bryan Nassau Rockdale Nassau Fulton Suffolk Pierce Irwin Fayette Oconee Elbert Nassau Fulton Gordon 84 47 61 69 72 54 56 74 60 84 52 53 45 57 51 52 69 71 57 $40 $63 $46 $43 $46 $53 $61 $48 $57 $61 $52 $58 $51 $63 $63 $36 $60 $37 $52 $46,893 $41,540 $48,193 $44,017 $44,168 $54,146 $55,771 $53,914 $40,685 $23,173 $52,805 $43,230 $22,280 $54,269 $35,771 $33,096 $42,650 $43,455 $31,185 Completed In progress In progress Completed Completed In progress Completed Completed Completed Completed In progress Completed In progress Completed Completed In progress Completed Completed Completed 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 188 195 177 229 111 279 193 139 292 299 288 112 230 102 259 284 104 266 106 203 Rothschild Construction Fayette Rothschild Construction Macon Caliber Creations. Lanier Spectrum Shores Villa Communities Spectrum Shores Villa Communities Quinn Quality Homes Villa Communities Harper Homes Caliber Creations Harper Homes Rothschild Construction McNeil Builders Caliber Creations Spectrum Shores Union Rothschild Construction Fayette Rothschild Construction Macon PN Mull Associates Rothschild Construction Suffolk Elbert Fulton Elbert Rockdale Walton Pierce Hall Queens Gordon Suffolk Sumter Newton McDuffie - 32 50 88 84 d 3 80 7 61 2 53 58 54 76 57 63 82 68 58 78 74 61 53 57 66 71 72 76 53 $61 $61 $58 $36 $38 $60 $42 $40 $43 $60 $64 $39 $50 $40 $63 $40 $53 $59 $57 $39 $54,889 $53,793 $22,739 $53,380 $52,374 $39,998 $45,442 $39,365 $36,523 $19,266 $30,862 $30,431 $26,623 $49,843 $36,726 $47,455 $56,002 $19,451 $24,005 $27,096 Completed Completed In progress Completed Completed Completed Completed Completed. In progress Completed Completed Completed Completed In progress Completed Completed Completed. Completed Completed Completed 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 221 Villa Communities 243 Rothschild Construction 158 137 213 178 206 Fulton Macon Putnam Gordon Nassau Fulton Irwin Rothschild Construction Macon Hall Hall Fayette Elbert Macon Macon Elbert Pierce Fulton Oconee McNeil Builders Harper Homes Quinn Quality Homes Villa Communities McNeil Builders 202 115 151 264 204 170 215 121 171 274 269 154 Quinn Quality Homes 267 PN Mull Associates Harper Homes Harper Homes Rothschild Construction Spectrum Shores Rothschild Construction Rothschild Construction Spectrum Shores Villa Communities Villa Communities. PN Mull Associates Nassau Telfair 72 61 52 60 61 83 58 74 54 78 56 66 54 55 48 60 59 84 55 45 $53 $64 $52 $45 $63 $65 $59 $37 $63 $65 $35 $43 $35 $35 $36 $61 $39 $60 $50 $45 $44,654 $26,499 $53,548 $28,699 $22,595 $49,613 $24,704 $40,474 $33,671 $20,023 $53,562 $20,023 $57,375 $32,583 $39,838 $38,325 $36,582 $30,196 $42,561 $22.064 Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed In progress In progress In progress Completed 130 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 207 207 Spectrum Shores Spectrum Shores 222 294 PN Mull Associates Spectrum Shores 224 122 210 110 242 247 144 133 114 174 109 180 232 244 281 234 Quinn Quality Homes Quinn Quality Homes Harper Homes Harper Homes Caliber Creations Quinn Quality Homes Rothschild Construction Villa Communities Villa Communities Quinn Quality Homes Villa Communities Quinn Quality Homes Rothschild Construction Spectrum Shores Villa Communities Icllall Union Elbert Telfair Union Haralson Nassau Chattooga Hall Newton Walton Macon Rockdale Pierce Nassau Pierce Nassau Suffolk Bryan Pierce 72 65 49 65 46 81 45 79 77 65 62 59 83 47 62 77 58 68 53 345 $50 $57 $42 $50 $47 $52 $60 $48 $56 $37 $42 $62 $55 $58 $65 $62 $38 $56 $64 $54,843 $52,960 $22,598 $19,964 $31,066 $49,483 $51,378 $20,110 $43,524 $40,654 $40,371 $47,189 $34,609 $32,622 $41,662 $48,632 $45,092 $58,243 $40,152 Completed Completed. In progress Completed Completed Completed In progress In progress Completed Completed Completed Completed Completed In progress In progress In progress Completed Completed Completed 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 B PN Mull Associates Rothschild Construction Caliber Creations Rothschild Construction Villa Communities Rothschild Construction PN Mull Associates 150 233 189 273 157 293 168 129 McNeil Builders 169 Quinn Quality Homes 217 McNeil Builders 185 Quinn Quality Homes 147 Spectrum Shores 220 Harper Homes 262 Caliber Creations 156 140 124 295 172 Quinn Quality Homes 246 Quinn Quality Homes PN Mull Associates Villa Communities Spectrum Shores Spectrum Shores Oconee Suffolk Lanier Fayette Rockdale Suffolk McDuffie Irwin Walton Irwin Walton Elbert Gordon Lanier Oconee Rockdale Union Union Walton Nassau F 74 60 51 61 47 70 55 69 53 74 55 61 65 84 78 70 66 53 69 70 $40 $62 $40 $42 $65 $54 $49 $61 $56 $48 $38 $64 $36 $45 $59 $59 $62 $50 $46 $52 $31,012 $58,098 $24,723 $31,067 $44,332 $58,834 $35,887 $33,663 $57,782 $24,381 $42,600 $54,774 $22,035 $30,952 $53,495 $47,878 $51,354 $30,364 $49,189 $54,119 Completed In progress In progress Completed Completed Completed Completed In progress Completed Completed Completed Completed Completed Completed Completed In progress Completed In progress Completed Completed 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 282 160 127 225 208 McNeil Builders Villa Communities McNeil Builders McNeil Builders Harper Homes 240 Spectrum Shores 167 PN Mull Associates 278 131 236 201 Putnam Rockdale Putnam Irwin Chattooga Bryan Oconee Haralson Fayette Lanier Fayette Fayette Newton Union Oconee Suffolk Quinn Quality Homes Walton 196 Villa Communities Pierce 205 McNeil Builders Sumter 145 Spectrum Shores Elbert Quinn Quality Homes Rothschild Construction Caliber Creations Rothschild Construction 126 Rothschild Construction 136 149 187 Caliber Creations Spectrum Shores PN Mull Associates 277 Rothschild Construction 166 83 71 50 81 59 59 46 66 60 74 67 54 47 53 83 65 64 64 83 77 $40 $56 $60 $54 $41 $56 $60 $47 $50 $53 $37 $38 $46 $43 $50 $43 $61 $37 $62 $57 $40,503 $35,199 $22,963 $45,359 $37,143 $45,435 $36,994 $46,095 $47,072 $27,098 $33,252 $57,879 $51,830 $53,131 $44,562 $56,457 $31,841 $36,590 $37,650 $37.935 In progress Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed Completed In progress Completed In progress In progress Completed In progress In progress Completed 198 199 200 201 120 254 153 239 McNeil Builders Quinn Quality Homes Rothschild Construction Harper Homes Sumter Nassau Suffolk Hall = 49 51 73 77 $52 $65 $41 $62 $28,434 $51,672 $26,628 $51,092 Completed Completed Completed. Completed