Question

The business manufactures custom patios made of concrete, brick, fiberglass, and lumber- depending upon customer preference. The company's fiscal year is the calendar year. At the beginning of July, selected balances were as follows:

Direct Materials Inventory, July 1	$4200
Work-In-Process Inventory, July 1	5540*
Manufacturing Overhead Applied to Date	32640
Actual Manufacturing Overhead to Date	31650

*Details for Work in Process

	Job 85		Job 86		Job 87		Total
Direct Materials	$600		800		900
Direct Labor	320		540		580
Manufacturing Overhead	400		675		725
=	1320	+	2015	+	2205	=	5540

(the last row are the separate columns added together and then totaled at the end).

During July, total direct materials purchased were $4900. Overhead costs incurred were $3800. Direct materials and direct labor used were as follows:

	Materials Requested Amounts	Labor Time Ticket Amounts
Job 85	$1100	$840
Job 86	500	360
Job 87	1300	1200
Job 88	2000	800

The Company uses conventional overhead application with overhead charged to jobs at the rate of $1.25 per dollar of direct labor cost. The patios for Jobs 85 and 87 were completed during July and sold at cost plus a 30 percent markup.

Prepare an Excel Spreadsheet that determines the cost of each job at the end of July. Then program cells that determine end of July balance of direct materials inventory, end of July balance of work in process inventory, end of July balance of finished goods, sales for July, cost of goods sold for July, and gross profit margin for July. On the lower part of the spreadsheet: Prepare a formal cost of goods manufactured schedule for the month of July.

(the company only deals with underapplied or overapplied overhead at the end of December therefore it is not needed in July).

Answer 1

		Direct material	Work in process	Finished goods
	Beginning inventory	4200	5540	0
	Material purchased	4900
	Material used	-4900	4900
	Labor		3200
	Overheads applied		4000
	Works finished		-10515	10515
	Units sold			-10515
	Ending inventory	4200	7125	0
		Job 85	Job 86	Job 87	Job 88
	Beginnig inventory	1320	2015	2205
	Material	1100	500	1300	2000
	Labor	840	360	1200	800
	Overheads applied	1050	450	1500	1000
	Total	4310	3325	6205	3800
	Works finished job 85 + job 87	10515
	Work in process at end job 86 + Job 88	7125
	Sales	13669.5	(10515*1.30)
	Cost of goods sold	10515
	Gross Margin	3154.5
	Schedule for manufacturing cost
	beginning raw material	4200
Add	Purchase of raw material	4900
	Total	9100
Less	Ending Raw material	4200
	Raw material used in production		4900
	Direct labor cost		3200
	Factory overheads applied		4000
	Total manufacturing cost		12100
Add	Beginning work in progress		5540
			17640
less	Ending work in progress		7125
	Cost of goods manufactured		10515
Add	Beginning finished goods inventory		0
	Cost of goods available for sale		10515
Less	Ending finished goods inventory		0
	Cost of goods sold		10515