90.2K

Verified Solution

Link Copied!

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:

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$

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$

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, and/or functions to answer the following questions.

Q1. What is the total cost of all jobs completed? fill in the blank 1 of 1$

Q2. What is the total cost of work in process in Elbert, Union, Macon, and Gordon 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$

Q4. How many completed jobs for McNeil Builders, PN Mull Associates, and Spectrum Shores combined used more than 61 direct labor hours? fill in the blank 1 of 1

table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl17 {color:#0070C0;} .xl20 {color:red; font-style:italic;} .xl21 {font-weight:700; text-align:center;} .xl22 {color:windowtext; font-weight:700; text-align:center;} .xl23 {text-align:center;} .xl24 {color:windowtext; text-align:center;} .xl26 {color:#0070C0; text-align:center;} .xl30 {text-align:center; border:1.0pt solid windowtext; background:#FFFFCC; mso-pattern:black none;} .xl31 {color:windowtext; text-align:left;} .xl34 {color:windowtext;} .xl35 {color:red; font-size:8.0pt; font-style:italic;}

table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl17 {color:#0070C0;} .xl20 {color:red; font-style:italic;} .xl21 {font-weight:700; text-align:center;} .xl22 {color:windowtext; font-weight:700; text-align:center;} .xl23 {text-align:center;} .xl24 {color:windowtext; text-align:center;} .xl26 {color:#0070C0; text-align:center;} .xl30 {text-align:center; border:1.0pt solid windowtext; background:#FFFFCC; mso-pattern:black none;} .xl31 {color:windowtext; text-align:left;} .xl34 {color:windowtext;} .xl35 {color:red; font-size:8.0pt; font-style:italic;}

Factory overhead		Job Number	Builder	County	Direct Labor Hours	Direct Labor Rate	Direct Materials	Direct Labor	Factory Overhead	Status
rate per direct		188	Rothschild Construction	Fayette	64	$62	$33,099	$3,968	$4,800	Completed
labor hour		287	Rothschild Construction	Fayette	69	$37	$42,601	$2,553	$5,175	In progress
		172	Quinn Quality Homes	Walton	61	$59	$22,329	$3,599	$4,575	Completed
$75		224	Spectrum Shores	Union	68	$55	$26,628	$3,740	$5,100	Completed
		254	Quinn Quality Homes	Nassau	46	$41	$54,030	$1,886	$3,450	Completed
Column A is not part of		162	Quinn Quality Homes	Nassau	80	$61	$25,968	$4,880	$6,000	Completed
the data for the pivot tables.		237	PN Mull Associates	McDuffie	84	$57	$27,450	$4,788	$6,300	Completed
		140	Villa Communities	Rockdale	58	$52	$45,333	$3,016	$4,350	In progress
		252	Rothschild Construction	Macon	56	$60	$30,485	$3,360	$4,200	In progress
		292	Villa Communities	Pierce	64	$50	$50,632	$3,200	$4,800	In progress
		119	Quinn Quality Homes	Nassau	45	$46	$53,845	$2,070	$3,375	Completed
		164	Villa Communities	Rockdale	51	$49	$21,857	$2,499	$3,825	Completed
		197	Villa Communities	Fulton	63	$40	$40,334	$2,520	$4,725	In progress
		184	Villa Communities	Rockdale	75	$43	$28,751	$3,225	$5,625	Completed
		134	Spectrum Shores	Elbert	61	$47	$26,797	$2,867	$4,575	Completed
		157	Villa Communities	Rockdale	74	$59	$31,978	$4,366	$5,550	Completed
		105	Caliber Creations	Queens	71	$59	$26,947	$4,189	$5,325	Completed
		145	Spectrum Shores	Elbert	65	$64	$49,770	$4,160	$4,875	Completed
		223	Villa Communities	Rockdale	66	$56	$21,190	$3,696	$4,950	In progress
		250	Spectrum Shores	Bryan	48	$50	$24,354	$2,400	$3,600	In progress
		296	McNeil Builders	Putnam	66	$46	$35,726	$3,036	$4,950	Completed
		294	PN Mull Associates	Telfair	81	$59	$42,357	$4,779	$6,075	In progress
		220	Harper Homes	Gordon	78	$38	$39,722	$2,964	$5,850	Completed
		214	Harper Homes	Gordon	73	$63	$47,372	$4,599	$5,475	Completed
		117	PN Mull Associates	Telfair	53	$40	$39,624	$2,120	$3,975	Completed
		169	Quinn Quality Homes	Walton	59	$40	$22,966	$2,360	$4,425	Completed
		282	McNeil Builders	Putnam	49	$36	$41,082	$1,764	$3,675	In progress
		236	Caliber Creations	Lanier	67	$57	$22,746	$3,819	$5,025	Completed
		161	PN Mull Associates	McDuffie	72	$46	$58,877	$3,312	$5,400	In progress
		113	Rothschild Construction	Macon	63	$47	$39,810	$2,961	$4,725	In progress
		288	Caliber Creations	Queens	72	$60	$42,015	$4,320	$5,400	Completed
		179	Villa Communities	Rockdale	50	$46	$51,367	$2,300	$3,750	Completed
		233	Rothschild Construction	Suffolk	60	$65	$47,757	$3,900	$4,500	In progress
		248	Villa Communities	Rockdale	72	$47	$43,009	$3,384	$5,400	In progress
		295	Spectrum Shores	Union	46	$36	$36,314	$1,656	$3,450	In progress
		257	PN Mull Associates	Telfair	57	$48	$43,318	$2,736	$4,275	In progress
		225	McNeil Builders	Irwin	50	$35	$27,161	$1,750	$3,750	Completed
		221	Villa Communities	Fulton	67	$57	$55,593	$3,819	$5,025	Completed
		255	Caliber Creations	Newton	56	$50	$41,269	$2,800	$4,200	Completed
		281	Spectrum Shores	Bryan	50	$61	$42,813	$3,050	$3,750	Completed
		136	Caliber Creations	Newton	74	$35	$20,711	$2,590	$5,550	In progress
		275	Rothschild Construction	Fayette	84	$41	$30,608	$3,444	$6,300	Completed
		142	Spectrum Shores	Union	60	$59	$48,782	$3,540	$4,500	Completed
		205	McNeil Builders	Sumter	61	$43	$37,568	$2,623	$4,575	In progress
		228	Spectrum Shores	Elbert	79	$44	$20,687	$3,476	$5,925	Completed
		106	PN Mull Associates	McDuffie	57	$44	$28,916	$2,508	$4,275	Completed
		167	PN Mull Associates	Oconee	79	$57	$46,045	$4,503	$5,925	Completed
		199	PN Mull Associates	Oconee	47	$39	$51,639	$1,833	$3,525	Completed
		122	Quinn Quality Homes	Haralson	83	$39	$29,128	$3,237	$6,225	Completed
		289	Harper Homes	Gordon	58	$41	$49,814	$2,378	$4,350	Completed
		200	McNeil Builders	Sumter	83	$52	$46,972	$4,316	$6,225	In progress
		284	Spectrum Shores	Union	68	$50	$41,858	$3,400	$5,100	Completed
		194	PN Mull Associates	McDuffie	57	$57	$58,021	$3,249	$4,275	Completed
		204	Spectrum Shores	Elbert	82	$46	$52,608	$3,772	$6,150	Completed
		215	Rothschild Construction	Macon	64	$48	$34,834	$3,072	$4,800	Completed
		230	Rothschild Construction	Suffolk	49	$47	$42,892	$2,303	$3,675	Completed
		262	Caliber Creations	Lanier	81	$42	$50,914	$3,402	$6,075	Completed
		283	Harper Homes	Gordon	56	$55	$38,708	$3,080	$4,200	In progress
		192	Rothschild Construction	Macon	50	$65	$29,444	$3,250	$3,750	Completed
		244	Rothschild Construction	Suffolk	79	$44	$39,466	$3,476	$5,925	Completed
		195	Rothschild Construction	Macon	51	$50	$52,390	$2,550	$3,825	Completed
		186	Harper Homes	Gordon	51	$52	$49,809	$2,652	$3,825	Completed
		158	McNeil Builders	Putnam	65	$56	$57,318	$3,640	$4,875	Completed
		132	PN Mull Associates	Telfair	67	$47	$26,644	$3,149	$5,025	Completed
		128	PN Mull Associates	Oconee	71	$51	$36,315	$3,621	$5,325	In progress
		201	Rothschild Construction	Fayette	51	$43	$45,047	$2,193	$3,825	Completed
		217	McNeil Builders	Irwin	59	$47	$39,264	$2,773	$4,425	Completed
		272	Villa Communities	Rockdale	50	$59	$40,343	$2,950	$3,750	In progress
		159	McNeil Builders	Irwin	48	$54	$38,955	$2,592	$3,600	Completed
		226	Caliber Creations	Newton	49	$47	$52,417	$2,303	$3,675	Completed
		210	Quinn Quality Homes	Nassau	83	$62	$47,030	$5,146	$6,225	Completed
		277	Rothschild Construction	Suffolk	63	$43	$48,557	$2,709	$4,725	In progress
		298	PN Mull Associates	McDuffie	59	$51	$46,922	$3,009	$4,425	Completed
		166	Quinn Quality Homes	Walton	72	$40	$55,777	$2,880	$5,400	Completed
		241	PN Mull Associates	McDuffie	51	$63	$47,906	$3,213	$3,825	Completed
		147	Spectrum Shores	Elbert	61	$48	$42,711	$2,928	$4,575	Completed
		148	Caliber Creations	Queens	62	$44	$35,179	$2,728	$4,650	In progress
		165	Caliber Creations	Lanier	77	$45	$33,702	$3,465	$5,775	Completed
		211	Spectrum Shores	Union	61	$46	$19,876	$2,806	$4,575	Completed
		160	Villa Communities	Rockdale	63	$35	$23,710	$2,205	$4,725	Completed
		293	Rothschild Construction	Suffolk	79	$64	$35,435	$5,056	$5,925	Completed
		219	Villa Communities	Fulton	46	$39	$50,893	$1,794	$3,450	Completed
		232	Quinn Quality Homes	Nassau	58	$44	$19,484	$2,552	$4,350	In progress
		269	PN Mull Associates	Oconee	65	$64	$32,754	$4,160	$4,875	In progress
		207	Spectrum Shores	Union	77	$42	$57,005	$3,234	$5,775	Completed
		259	Caliber Creations	Newton	78	$37	$32,078	$2,886	$5,850	Completed
		131	Rothschild Construction	Fayette	84	$47	$52,566	$3,948	$6,300	Completed
		123	Rothschild Construction	Macon	85	$43	$35,523	$3,655	$6,375	Completed
		173	Quinn Quality Homes	Haralson	76	$35	$35,745	$2,660	$5,700	Completed
		202	Rothschild Construction	Macon	59	$57	$38,902	$3,363	$4,425	Completed
		183	Rothschild Construction	Macon	60	$48	$54,414	$2,880	$4,500	Completed
		151	Harper Homes	Hall	49	$35	$27,732	$1,715	$3,675	Completed
		109	Quinn Quality Homes	Nassau	60	$51	$30,144	$3,060	$4,500	In progress
		144	Quinn Quality Homes	Walton	45	$58	$21,108	$2,610	$3,375	Completed
		218	PN Mull Associates	Telfair	80	$51	$49,092	$4,080	$6,000	Completed
		129	McNeil Builders	Irwin	69	$45	$31,809	$3,105	$5,175	In progress
		212	Villa Communities	Fulton	76	$38	$43,440	$2,888	$5,700	In progress
		153	Rothschild Construction	Suffolk	71	$57	$44,926	$4,047	$5,325	Completed
		216	Caliber Creations	Queens	83	$38	$41,416	$3,154	$6,225	Completed
		124	Spectrum Shores	Union	84	$47	$40,495	$3,948	$6,300	Completed
		154	Quinn Quality Homes	Nassau	80	$62	$48,607	$4,960	$6,000	In progress
		235	Spectrum Shores	Elbert	55	$38	$31,379	$2,090	$4,125	In progress
		156	PN Mull Associates	Oconee	57	$36	$30,210	$2,052	$4,275	Completed
		133	Rothschild Construction	Macon	54	$63	$50,500	$3,402	$4,050	Completed
		247	Caliber Creations	Newton	45	$61	$58,149	$2,745	$3,375	Completed
		138	Caliber Creations	Queens	71	$42	$46,556	$2,982	$5,325	Completed
		187	PN Mull Associates	Oconee	46	$38	$46,858	$1,748	$3,450	In progress
		116	Quinn Quality Homes	Nassau	84	$41	$49,745	$3,444	$6,300	In progress
		206	McNeil Builders	Irwin	46	$58	$40,549	$2,668	$3,450	Completed
		191	Caliber Creations	Newton	69	$50	$55,378	$3,450	$5,175	Completed
		152	Spectrum Shores	Elbert	79	$51	$49,647	$4,029	$5,925	In progress
		177	Caliber Creations	Lanier	70	$57	$36,107	$3,990	$5,250	In progress
		273	Rothschild Construction	Fayette	76	$65	$23,272	$4,940	$5,700	Completed
		114	Villa Communities	Rockdale	48	$63	$29,011	$3,024	$3,600	Completed
		121	Spectrum Shores	Elbert	57	$48	$30,277	$2,736	$4,275	Completed
		238	Rothschild Construction	Suffolk	49	$36	$47,931	$1,764	$3,675	In progress
		260	PN Mull Associates	Telfair	76	$59	$32,540	$4,484	$5,700	In progress
		234	Villa Communities	Pierce	59	$36	$53,208	$2,124	$4,425	Completed
		149	Spectrum Shores	Union	70	$48	$48,208	$3,360	$5,250	Completed
		279	Spectrum Shores	Elbert	79	$58	$20,546	$4,582	$5,925	Completed
		271	Quinn Quality Homes	Walton	77	$63	$47,322	$4,851	$5,775	Completed
		286	Harper Homes	Hall	60	$52	$47,091	$3,120	$4,500	Completed
		276	Villa Communities	Pierce	58	$61	$34,274	$3,538	$4,350	Completed
		291	Quinn Quality Homes	Walton	55	$44	$51,619	$2,420	$4,125	Completed
		227	PN Mull Associates	Telfair	46	$54	$27,705	$2,484	$3,450	In progress
		108	Rothschild Construction	Fayette	64	$62	$56,831	$3,968	$4,800	Completed
		297	Quinn Quality Homes	Walton	62	$43	$56,231	$2,666	$4,650	Completed
		101	Quinn Quality Homes	Nassau	62	$54	$30,584	$3,348	$4,650	Completed
		270	Quinn Quality Homes	Nassau	65	$57	$25,804	$3,705	$4,875	Completed
		208	Harper Homes	Chattooga	69	$55	$58,360	$3,795	$5,175	Completed
		222	Spectrum Shores	Elbert	49	$47	$35,663	$2,303	$3,675	Completed
		280	Rothschild Construction	Macon	60	$37	$55,827	$2,220	$4,500	In progress
		242	Harper Homes	Hall	52	$45	$31,859	$2,340	$3,900	In progress
		171	Villa Communities	Pierce	69	$47	$48,999	$3,243	$5,175	Completed
		243	Rothschild Construction	Macon	82	$38	$46,878	$3,116	$6,150	Completed
		143	Harper Homes	Gordon	47	$60	$37,953	$2,820	$3,525	Completed
		246	Quinn Quality Homes	Nassau	47	$49	$49,898	$2,303	$3,525	Completed
		115	Harper Homes	Hall	73	$50	$41,264	$3,650	$5,475	Completed
		112	Harper Homes	Gordon	70	$47	$29,599	$3,290	$5,250	Completed
		120	McNeil Builders	Sumter	78	$54	$33,594	$4,212	$5,850	Completed
		110	Harper Homes	Chattooga	59	$54	$56,627	$3,186	$4,425	In progress
		103	Spectrum Shores	Bryan	69	$53	$21,627	$3,657	$5,175	Completed
		180	Villa Communities	Pierce	62	$43	$25,274	$2,666	$4,650	In progress
		178	Villa Communities	Fulton	47	$57	$51,968	$2,679	$3,525	Completed
		102	McNeil Builders	Sumter	59	$37	$30,163	$2,183	$4,425	In progress
		126	Rothschild Construction	Fayette	58	$63	$23,613	$3,654	$4,350	Completed
		127	McNeil Builders	Putnam	50	$52	$21,628	$2,600	$3,750	Completed
		300	Rothschild Construction	Macon	66	$64	$56,548	$4,224	$4,950	Completed
		213	Quinn Quality Homes	Nassau	74	$58	$38,169	$4,292	$5,550	Completed
		170	Rothschild Construction	Macon	80	$48	$24,204	$3,840	$6,000	Completed
		251	Spectrum Shores	Union	57	$35	$30,695	$1,995	$4,275	Completed
		150	PN Mull Associates	Oconee	82	$51	$26,884	$4,182	$6,150	Completed
		185	Quinn Quality Homes	Walton	51	$52	$24,277	$2,652	$3,825	Completed
		146	Villa Communities	Fulton	67	$38	$46,027	$2,546	$5,025	Completed
		290	Harper Homes	Hall	52	$53	$50,483	$2,756	$3,900	In progress
		135	Rothschild Construction	Suffolk	55	$53	$55,544	$2,915	$4,125	In progress
		253	PN Mull Associates	Oconee	75	$56	$47,166	$4,200	$5,625	Completed
		130	Harper Homes	Chattooga	64	$41	$44,990	$2,624	$4,800	Completed
		111	Villa Communities	Fulton	64	$41	$37,284	$2,624	$4,800	Completed
		137	Harper Homes	Gordon	69	$38	$57,082	$2,622	$5,175	Completed
		263	Caliber Creations	Newton	63	$48	$57,235	$3,024	$4,725	Completed
		299	Harper Homes	Hall	78	$51	$23,439	$3,978	$5,850	Completed
		245	Harper Homes	Gordon	54	$54	$22,443	$2,916	$4,050	Completed
		189	Caliber Creations	Lanier	52	$51	$55,736	$2,652	$3,900	In progress
		278	Quinn Quality Homes	Haralson	59	$53	$39,807	$3,127	$4,425	Completed
		268	Caliber Creations	Newton	79	$48	$43,093	$3,792	$5,925	Completed
		265	Spectrum Shores	Bryan	72	$43	$32,526	$3,096	$5,400	Completed
		190	Spectrum Shores	Union	46	$59	$19,592	$2,714	$3,450	Completed
		256	Harper Homes	Gordon	78	$61	$33,134	$4,758	$5,850	Completed
		168	PN Mull Associates	McDuffie	74	$41	$24,559	$3,034	$5,550	Completed
		176	PN Mull Associates	Telfair	85	$65	$47,731	$5,525	$6,375	Completed
		193	Villa Communities	Rockdale	83	$42	$51,942	$3,486	$6,225	Completed
		209	Quinn Quality Homes	Nassau	75	$36	$19,631	$2,700	$5,625	In progress
		175	PN Mull Associates	Oconee	78	$41	$47,897	$3,198	$5,850	In progress
		107	Rothschild Construction	Macon	46	$47	$40,024	$2,162	$3,450	Completed
		174	Villa Communities	Pierce	53	$49	$55,699	$2,597	$3,975	Completed
		203	Rothschild Construction	Suffolk	70	$64	$41,737	$4,480	$5,250	Completed
		231	Villa Communities	Pierce	58	$49	$35,511	$2,842	$4,350	Completed
		182	PN Mull Associates	Oconee	68	$47	$46,674	$3,196	$5,100	Completed
		196	Villa Communities	Pierce	59	$39	$29,320	$2,301	$4,425	In progress
		267	PN Mull Associates	Telfair	49	$41	$52,895	$2,009	$3,675	Completed
		155	Harper Homes	Chattooga	72	$52	$46,665	$3,744	$5,400	Completed
		141	McNeil Builders	Irwin	72	$57	$33,440	$4,104	$5,400	In progress
		274	Villa Communities	Fulton	60	$57	$29,353	$3,420	$4,500	In progress
		266	Rothschild Construction	Macon	54	$58	$37,033	$3,132	$4,050	Completed
		181	Spectrum Shores	Bryan	53	$49	$32,906	$2,597	$3,975	Completed
		104	Rothschild Construction	Fayette	84	$51	$58,761	$4,284	$6,300	Completed
		125	PN Mull Associates	Telfair	58	$47	$53,976	$2,726	$4,350	In progress
		118	PN Mull Associates	Telfair	54	$62	$47,828	$3,348	$4,050	Completed
		239	Harper Homes	Hall	60	$45	$19,450	$2,700	$4,500	Completed
		229	Spectrum Shores	Elbert	72	$63	$44,163	$4,536	$5,400	Completed
		261	Rothschild Construction	Fayette	65	$62	$40,777	$4,030	$4,875	Completed
		264	Rothschild Construction	Fayette	47	$63	$53,980	$2,961	$3,525	Completed
		258	Villa Communities	Pierce	81	$40	$27,545	$3,240	$6,075	Completed
		139	Quinn Quality Homes	Walton	70	$39	$50,510	$2,730	$5,250	Completed
		163	McNeil Builders	Sumter	63	$35	$52,163	$2,205	$4,725	Completed
		240	Spectrum Shores	Bryan	65	$49	$52,865	$3,185	$4,875	Completed
		198	PN Mull Associates	McDuffie	46	$52	$27,220	$2,392	$3,450	In progress
		249	Spectrum Shores	Elbert	82	$60	$51,098	$4,920	$6,150	Completed
		285	Caliber Creations	Newton	74	$64	$24,777	$4,736	$5,550	Completed

Answer & Explanation Solved by verified expert

See Answer