Question

Moravia Company processes and packages cream cheese. The following data have been compiled for the month of April. Conversion activity occurs uniformly throughout the production process. $ 22.000 4500 $26.500 100.000 80.000 Work in process. Apr 1-10,000 unts: Direct material 100% complete, cost of Conversion 20% complete, cost of Balance in work in process, April Units started during April Units completed during April and transferred out to finished goods inventory Work in process, April 30: Direct material: 100% complete Conversion 33% complete Costs incurred during April Direct material Conversion costs Direct labor Applied manufacturing overhead ... Total conversion costs $198.000 $ 52,800 105 600 $158.400 Required: Prepare schedules to accomplish each of the following process costing steps for the month of April. Use the weighted-average method of process costing. 1. Analysis of physical flow of units. 2. Calculation of equivalent units. 3. Computation of unit costs. 4. Analysis of total costs. 5. Build a spreadsheet: Construct an Excel spreadsheet to solve all of the preceding requirements. Show how the solution will change if the following data change the April I work-in-process costs were $66,000 for direct material and S18.000 for conversion.

I need help (with #5, Build spreadsheet) the excel portion of my homework, Moravia Company, Problem 4-29 from the Managerial Accounting 11th ed. My homework is due tonight but would like to see the excel by this afternoon at 5pm is possible.

Thank you

Answer 1

Answer 5 (a):

Here is report meeting requirements 1, 2, 3 and 4:

Moravia Company Production Cost Report (Weighted Average Method) For the month of April 1. Analysis of physical flow of units: Unit Reconciliation: Units to account for: Beginning work in process - April 1 Started during the month Total units to account for Total units accounted for: Units transferred to Finished goods Ending work in process (Balancing figure) Total units accounted for 10,000 100,000 110,000 80,000 30,000 110,000 2. Calculation of Equivalent Units of production: Equivalent units of production (EUP) - weighted average method Units %Material 80,000 100% 30,000 100 Units transferred to Finished goods Ending work in process Total Units 3. Cost per equivalent unit of production Beginning work in process Cost incurred during the month Total costs / Equivalent units of production Cost per equivalent unit of production (rounded to 2 decimals) EUP - Material % Conversion EUP - Conversion 80,000 100% 80,000 30,000 33.33% 10,000 110,000 90,000 Material Conversion $22,000 $4,500 $198,000 $158,400 $220,000 Costs $162,900 110,000 EUP 90,000 $2.00 $1.81 Costs EUP 4. Analysis of Total Costs: Total Costs to account for: Beginning Work in process Cost added during the month Total costs to account for: Total costs accounted for Difference due to rounding cost/unit $26,500 $356,400 $382,900 $382,900 so Cost per EUP Total cost 80,000 $2.00 $160,000.00 80,000 $1.81 $144,800.00 $304,800.00 Total costs accounted for: Cost of units transferred out: Direct material Conversion Total cost transferred out Cost of ending work in process Direct material Conversion Total cost of ending work in process Total costs accounted for 30,000 10,000 $2.00 $1.81 $60,000.00 $18,100.00 $78,100.00 $382,900.00

The above excel with 'show formula' is as below (it cannot be accommodated in 1 image; hence given in 2 images):

Moravia Company Production Cost Report (Weighted Average Method) For the month of April 10000 100000 =B8+B9 80000 =B14-B12 5 1. Analysis of physical flow of units: 6 Unit Reconciliation: 7 Units to account for: 8 Beginning work in process-April 1 9 Started during the month 10 Total units to account for 11 Total units accounted for: 12 Units transferred to Finished goods 13 Ending work in process (Balancing figure) 14 Total units accounted for 15 16 2. Calculation of Equivalent Units of production: 17 Equivalent units of production (EUP) - weighted average method 18 19 Units transferred to Finished goods 20 Ending work in process 21 Total Units 22 3. Cost per equivalent unit of production 23 Beginning work in process 24 Cost incurred during the month 25 Total costs %Material =B13 EUP - Material % Conversion EUP - Conversion =B19*C19 =B19*E19 1 =B20*c20 1/3 =B20*E20 =D20+D19 =F20+F19 Material Conversion 22000 4500 198000 158400 1=D23+D24 Costs =F23+F24 Costs

в Material 22000 198000 =D23+D24 =D21 =D25/D26 Conversion 4500 158400 =F23+F24 =F21 =F25/F26 Costs Costs EUP =D23+F23 =D24+F24 =B32+B31 =E46 22 3. Cost per equivalent unit of production 23 Beginning work in process 24 Cost incurred during the month 25 Total costs 26 / Equivalent units of production 27 Cost per equivalent unit of production (rounded to 2 decimals) 28 29 4. Analysis of Total Costs: 30 Total Costs to account for: 31 Beginning Work in process 32 Cost added during the month 33 Total costs to account for: 34 Total costs accounted for 35 Difference due to rounding cost/unit 36 37 Total costs accounted for: 38 Cost of units transferred out: 39 Direct material 40 Conversion Total cost transferred out 42 Cost of ending work in process 43 Direct material 44 Conversion Total cost of ending work in process 46 Total costs accounted for 47 EUP =D19 =F19 Cost per EUP Total cost =D27 =C39*B39 =F27 =C40*B40 41 =D39+D40 =D20 =D27 =C43*B43 =C44*B44 =F20 45 =D43+D44 =E45+E41

Answer 5 (b):

If April 1 cost of work in process costs were $66,000 for direct material and $18,000 for conversion then:

Moravia Company Production Cost Report (Weighted Average Method) For the month of April 1. Analysis of physical flow of units: Unit Reconciliation: Units to account for: Beginning work in process - April 1 Started during the month Total units to account for Total units accounted for: Units transferred to Finished goods Ending work in process (Balancing figure) Total units accounted for 10,000 100,000 110,000 80,000 30,000 110,000 2. Calculation of Equivalent Units of production: Equivalent units of production (EUP) - weighted average method Units %Material 80,000 100% 30,000 100% Units transferred to Finished goods Ending work in process Total Units 3. Cost per equivalent unit of production Beginning work in process Cost incurred during the month Total costs / Equivalent units of production Cost per equivalent unit of production (rounded to 2 decimals) EUP - Material % Conversion EUP - Conversion 80,000 100% 80,000 30,000 33.33% 10,000 110,000 90,000 Material Conversion $66,000 $18,000 $198,000 $158,400 $264,000 Costs $176,400 110,000 EUP 90,000 $2.40 $1.96 EUP 4. Analysis of Total Costs: Total Costs to account for: Beginning Work in process Cost added during the month Total costs to account for: Total costs accounted for Difference due to rounding cost/unit $84,000 $356,400 $440,400 $440,400 $0 Cost per EUP Total cost 80,000 $2.40 $192,000.00 80,000 $1.96 $156,800.00 $348,800.00 Total costs accounted for: Cost of units transferred out: Direct material Conversion Total cost transferred out Cost of ending work in process Direct material Conversion Total cost of ending work in process Total costs accounted for 30,000 10,000 $2.40 $1.96 $72,000.00 $19,600.00 | $91,600.00 $440,400.00