Scribd
Upload
220 views12 pages

Excel Cost Analysis: Weighted Average Method

The document shows the calculations to apply the weighted average method for a company's work in process inventory. It provides beginning inventory amounts and costs, production amounts and costs added during the period, and ending inventory amounts. It then calculates equivalent units, costs per equivalent unit, costs of ending inventory and units transferred out, and reconciles the total costs. Formulas are to be entered in cells marked with "?" to complete the weighted average method calculations.

Uploaded by

trungnt.study
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd
220 views12 pages

Excel Cost Analysis: Weighted Average Method

The document shows the calculations to apply the weighted average method for a company's work in process inventory. It provides beginning inventory amounts and costs, production amounts and costs added during the period, and ending inventory amounts. It then calculates equivalent units, costs per equivalent unit, costs of ending inventory and units transferred out, and reconciles the total costs. Formulas are to be entered in cells marked with "?" to complete the weighted average method calculations.

Uploaded by

trungnt.study
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd

714621380.

xls
Chapter 4: Applying Excel

Data
Beginning work in process inventory:
Units in process 200
Completion with respect to materials 55%
Completion with respect to conversion 30%
Costs in the beginning work in process inventory:
Materials cost $9,600
Conversion cost $5,575
Units started into production during the period 5,000
Costs added to production during the period:
Materials cost $368,600
Conversion cost $350,900
Ending work in process inventory:
Units in process 400
Completion with respect to materials 40%
Completion with respect to conversion 25%

Enter a formula into each of the cells marked with a ? below

Weighted Average method:

Equivalent Units of Production


Materials Conversion
Units transferred to the next department 4,800 4,800
Equivalent units in ending work in process inventory:
Materials 160
Conversion 100
Equivalent units of production 4,960 4,900

Costs per Equivalent Unit


Materials Conversion
Cost of beginning work in process inventory $ 9,600 $ 5,575
Costs added during the period 368,600 350,900
Total cost $ 378,200 $ 356,475
Equivalent units of production 4,960 4,900
Cost per equivalent unit $76.25 $72.75

Costs of Ending Work in Process Inventory and the Units Transferred Out
Materials Conversion Total
Ending work in process inventory:
Equivalent units 160 100
Cost per equivalent unit $76.25 $72.75
Cost of ending work in process inventory $12,200 $7,275 $19,475

Units completed and transferred out:


Units transferred to the next department 4,800 4,800
Cost per equivalent unit $76.25 $72.75
Cost of units transferred out $366,000 $349,200 $715,200

Cost Reconciliation

Page 1
[Link]
Costs to be accounted for:
Cost of beginning work in process inventory $ 15,175
Costs added to production during the period 719,500
Total cost to be accounted for $ 734,675
Costs accounted for as follows:
Cost of ending work in process inventory $ 19,475
Cost of units transferred out 715,200
Total cost accounted for $ 734,675

Page 2
[Link]
Chapter 5: Applying Excel

Data
Beginning work in process inventory:
Units in process 200
Completion with respect to materials 55%
Completion with respect to conversion 30%
Costs in the beginning work in process inventory:
Materials cost $9,600
Conversion cost $5,575
Units started into production during the period 5,000
Costs added to production during the period:
Materials cost $368,600
Conversion cost $350,900
Ending work in process inventory:
Units in process 400
Completion with respect to materials 40%
Completion with respect to conversion 25%

Enter a formula into each of the cells marked with a ? below

Weighted Average method:

Equivalent Units of Production


Materials Conversion
Units transferred to the next department ? ?
Equivalent units in ending work in process inventory:
Materials ?
Conversion ?
Equivalent units of production ? ?

Costs per Equivalent Unit


Materials Conversion
Cost of beginning work in process inventory ? ?
Costs added during the period ? ?
Total cost ? ?
Equivalent units of production ? ?
Cost per equivalent unit ? ?

Costs of Ending Work in Process Inventory and the Units Transferred Out
Materials Conversion Total
Ending work in process inventory:
Equivalent units ? ?
Cost per equivalent unit ? ?
Cost of ending work in process inventory ? ? ?

Units completed and transferred out:


Units transferred to the next department ? ?
Cost per equivalent unit ? ?
Cost of units transferred out ? ? ?

Cost Reconciliation

Page 3
[Link]
Costs to be accounted for:
Cost of beginning work in process inventory ?
Costs added to production during the period ?
Total cost to be accounted for ?
Costs accounted for as follows:
Cost of ending work in process inventory ?
Cost of units transferred out ?
Total cost accounted for ?

Page 4
[Link]
Chapter 5: Applying Excel

Data
Beginning work in process inventory:
Units in process 200
Completion with respect to materials 55%
Completion with respect to conversion 30%
Costs in the beginning work in process inventory:
Materials cost $9,600
Conversion cost $5,575
Units started into production during the period 5,000
Costs added to production during the period:
Materials cost ###
Conversion cost ###
Ending work in process inventory:
Units in process 400
Completion with respect to materials 40%
Completion with respect to conversion 25%

Enter a formula into each of the cells marked with a ? below

Weighted Average method:

Equivalent Units of Production


MaterialsConversion
Units transferred to the next department 4,800 4,800
Equivalent units in ending work in process inventory:
Materials 160
Conversion 100
Equivalent units of production 4,960 4,900

Costs per Equivalent Unit


MaterialsConversion
Cost of beginning work in process inventory $9,600 $5,575
Costs added during the period ### ###
Total cost ### ###
Equivalent units of production 4,960 4,900
Cost per equivalent unit $76.25 $72.75

Costs of Ending Work in Process Inventory and the Units Transferred Out
MaterialsConversionTotal
Ending work in process inventory:
Equivalent units 160 100
Cost per equivalent unit $76.25 $72.75
Cost of ending work in process inventory ### $7,275 ###

Units completed and transferred out:


Units transferred to the next department 4,800 4,800
Cost per equivalent unit $76.25 $72.75
Cost of units transferred out ### ### ###

Cost Reconciliation

Page 5
[Link]
Costs to be accounted for:
Cost of beginning work in process inventory ###
Costs added to production during the period ###
Total cost to be accounted for ###
Costs accounted for as follows:
Cost of ending work in process inventory ###
Cost of units transferred out ###
Total cost accounted for ###

Page 6
[Link]
Chapter 5: Applying Excel

Data
Beginning work in process inventory:
Units in process 100
Completion with respect to materials 55%
Completion with respect to conversion 30%
Costs in the beginning work in process inventory:
Materials cost $9,600
Conversion cost $5,575
Units started into production during the period 2,500
Costs added to production during the period:
Materials cost $368,600
Conversion cost $350,900
Ending work in process inventory:
Units in process 200
Completion with respect to materials 40%
Completion with respect to conversion 25%

Enter a formula into each of the cells marked with a ? below

Weighted Average method:

Equivalent Units of Production


Materials Conversion
Units transferred to the next department 2,400 2,400
Equivalent units in ending work in process inventory:
Materials 80
Conversion 50
Equivalent units of production 2,480 2,450

Costs per Equivalent Unit


Materials Conversion
Cost of beginning work in process inventory $ 9,600 $ 5,575
Costs added during the period 368,600 350,900
Total cost $ 378,200 $ 356,475
Equivalent units of production 2,480 2,450
Cost per equivalent unit $152.50 $145.50

Costs of Ending Work in Process Inventory and the Units Transferred Out
Materials Conversion Total
Ending work in process inventory:
Equivalent units 80 50
Cost per equivalent unit $152.50 $145.50
Cost of ending work in process inventory $12,200 $7,275 $19,475

Units completed and transferred out:


Units transferred to the next department 2,400 2,400
Cost per equivalent unit $152.50 $145.50
Cost of units transferred out $366,000 $349,200 $715,200

Cost Reconciliation

Page 7
[Link]
Costs to be accounted for:
Cost of beginning work in process inventory $ 15,175
Costs added to production during the period 719,500
Total cost to be accounted for $ 734,675
Costs accounted for as follows:
Cost of ending work in process inventory $ 19,475
Cost of units transferred out 715,200
Total cost accounted for $ 734,675

Page 8
[Link]
Chapter 5: Applying Excel

Data
Beginning work in process inventory:
Units in process 200
Completion with respect to materials 100%
Completion with respect to conversion 20%
Costs in the beginning work in process inventory:
Materials cost $2,000
Conversion cost $800
Units started into production during the period 1,800
Costs added to production during the period:
Materials cost $18,400
Conversion cost $38,765
Ending work in process inventory:
Units in process 100
Completion with respect to materials 100%
Completion with respect to conversion 30%

Enter a formula into each of the cells marked with a ? below

Weighted Average method:

Equivalent Units of Production


Materials Conversion
Units transferred to the next department 1,900 1,900
Equivalent units in ending work in process inventory:
Materials 100
Conversion 30
Equivalent units of production 2,000 1,930

Costs per Equivalent Unit


Materials Conversion
Cost of beginning work in process inventory $ 2,000 $ 800
Costs added during the period 18,400 38,765
Total cost $ 20,400 $ 39,565
Equivalent units of production 2,000 1,930
Cost per equivalent unit $10.20 $20.50

Costs of Ending Work in Process Inventory and the Units Transferred Out
Materials Conversion Total
Ending work in process inventory:
Equivalent units 100 30
Cost per equivalent unit $10.20 $20.50
Cost of ending work in process inventory $1,020 $615 $1,635

Units completed and transferred out:


Units transferred to the next department 1,900 1,900
Cost per equivalent unit $10.20 $20.50
Cost of units transferred out $19,380 $38,950 $58,330

Cost Reconciliation

Page 9
[Link]
Costs to be accounted for:
Cost of beginning work in process inventory $ 2,800
Costs added to production during the period 57,165
Total cost to be accounted for $ 59,965
Costs accounted for as follows:
Cost of ending work in process inventory $ 1,635
Cost of units transferred out 58,330
Total cost accounted for $ 59,965

Page 10
[Link]
Chapter 5: Applying Excel

Data
Beginning work in process inventory:
Units in process 200
Completion with respect to materials 100%
Completion with respect to conversion 40%
Costs in the beginning work in process inventory:
Materials cost $2,000
Conversion cost $800
Units started into production during the period 1,800
Costs added to production during the period:
Materials cost $18,400
Conversion cost $38,765
Ending work in process inventory:
Units in process 100
Completion with respect to materials 100%
Completion with respect to conversion 30%

Enter a formula into each of the cells marked with a ? below

Weighted Average method:

Equivalent Units of Production


Materials Conversion
Units transferred to the next department
Equivalent units in ending work in process inventory:
Materials
Conversion
Equivalent units of production

Costs per Equivalent Unit


Materials Conversion
Cost of beginning work in process inventory
Costs added during the period
Total cost
Equivalent units of production
Cost per equivalent unit

Costs of Ending Work in Process Inventory and the Units Transferred Out
Materials Conversion Total
Ending work in process inventory:
Equivalent units
Cost per equivalent unit
Cost of ending work in process inventory

Units completed and transferred out:


Units transferred to the next department
Cost per equivalent unit
Cost of units transferred out

Cost Reconciliation

Page 11
[Link]
Costs to be accounted for:
Cost of beginning work in process inventory
Costs added to production during the period
Total cost to be accounted for
Costs accounted for as follows:
Cost of ending work in process inventory
Cost of units transferred out
Total cost accounted for

Page 12

You might also like