Question

Process Cost Excel Project Create a new Excel spreadsheet and name it "Last name_PC". You project is to create a model for a production cost report using the weighted average method for the month of May. Following good Excel design techniques, you should have an input area in which you put the department information for the month, and an output area that calculates the production cost report. As always, you should have only formulas or references in your output area. You should also include directions in a text box) for users. After completion of the production cost report using the weighted average method, create a new worksheet and label the tab "FIFO". Create a production cost report using the same information using the FIFO method. Department Data: Information about units: Units in beginning WIP Started during the month Units in Ending WIP 21,000 240,000 20,000 Percentage of Completion: Beginning Inventory: Direct materials Conversion Ending Inventory: Direct Materials Conversion 40% 70% 30% 60% Costs in Beginning WIP: Direct materials Conversion $210,000 $135,000 Costs incurred during the month: Direct Materials Direct Labor Overhead $1,150,000 $45,000 $605,000
Please do Weighted Average & FIFO, I will post another question for the second part if needed.

Answer 1

Weighted Average

	Physcial units	Materials	Conversion
Work in Process Beginning	21000
Started during the period	240000
To account for	261000
Units transferred to next department	241000	241000	241000
Ending Work in Process :	20000	6000	12000
Accounted for	261000	247000	253000
	Total	Materials	Conversion
Cost of Beginning Work in Process	$       3,45,000	$         2,10,000	$          1,35,000
Cost added during the period	$     18,00,000	$       11,50,000	$          6,50,000
Total Cost	$     21,45,000	$       13,60,000	$          7,85,000
Equivalent units of Production		247000	253000
Cost per equivalent unit	$               8.61	$                5.51	$                 3.10
	Materials	Conversion	Total
Ending work in Process Inventory
Equivalent units of Production	6000	12000
Cost per equivalent unit	$               5.51	$                3.10
Cost of Ending work in Process Inventory	$          33,036	$            37,233	$             70,269
Units Completed and transferred out
Units transferred to next department	241000	241000
Cost per equivalent unit	$               5.51	$                3.10
Cost of units transferred out	$     13,26,964	$         7,47,767	$       20,74,731
Reconciliation
Costs to be accounted for :
Cost of beginning work in process inventory	$       3,45,000
Costs added to the production during the year	$     18,00,000
Total Costs to be accounted for	$     21,45,000
Cost of Ending work in Process Inventory	$          70,269
Cost of units transferred out	$     20,74,731
Total Costs accounted for	$     21,45,000

FIFO

	Physcial units	Materials	Conversion
Work in Process Beginning	21000
Started during the period	240000
To account for	261000
Work in Process Beginning	21000	12600	6300
Units started and completed	220000	220000	220000
Units transferred to next department	241000	232600	226300
Ending Work in Process :	20000	6000	12000
Accounted for	261000	238600	238300
	Total	Materials	Conversion
Cost added during the period	$     18,00,000	$       11,50,000	$          6,50,000
Equivalent units of Production		238600	238300
Cost per equivalent unit	$               7.55	$                4.82	$                 2.73
	Materials	Conversion	Total
Ending work in Process Inventory
Equivalent units of Production	6000	12000
Cost per equivalent unit	$               4.82	$                2.73
Cost of Ending work in Process Inventory	$          28,919	$            32,732	$             61,651
Units Completed and transferred out
Cost of Beginning Work in Process	$       2,10,000	$         1,35,000	$          3,45,000
Cost of work on Beginning Work in Process	$     60,729.00	$      17,184.00	$       77,913.00
Total Cost of Beginning WIP	$ 2,70,729.00	$   1,52,184.00	$   4,22,913.00
Units started and completed	220000	220000
Cost per equivalent unit	$               4.82	$                2.73
Cost of units started and completed	$    10,60,352	$        6,00,084	$       16,60,436
Cost of units transferred out	$    13,31,081	$        7,52,268	$       20,83,349
Reconciliation
Costs to be accounted for :
Cost of beginning work in process inventory	$       3,45,000
Costs added to the production during the year	$     18,00,000
Total Costs to be accounted for	$     21,45,000
Cost of Ending work in Process Inventory	$          61,651
Cost of units transferred out	$     20,83,349
Total Costs accounted for	$     21,45,000