Question

Please show formulas using excel

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 1 work-in-process costs were $66,000 for direct material and $18,000 for conversion.

Work in process, April 1—10,000 units: Direct material: 100% complete, cost of .........................................................................................................$ 22,000

Conversion: 20% complete, cost of ................................................................................................................. 4,500

Balance in work in process, April 1 ..................................................................................................................$  26,500

Units started during April ......................................................................................................................................100,000

Units completed during April and transferred out to finished-goods inventory .................................................80,000

Work in process, April 30:

Direct material: 100% complete

Conversion: 33 1 ⁄ 3 % complete

Costs incurred during April:

Direct material ..................................................................................................................................................$ 198,000

Conversion costs: Direct labor ...................................................................................................................................................$ 52,800

Applied manufacturing overhead ................................................................................................................ 105,600

Total conversion costs .................................................................................................................................$158,400

Answer 1

If opening WIP cost changes:

Formula's SHeet:

Units Charged to production: Inventory in Progress, April 1 Received from materials storeroom Total units accounted for 10000 1,00,000 1,10,000 Units to be assigned costs: Inventory in Progress, April 1 Started and completed in April Transferred to Department in April Inventory in process, April 30 Total units to be assigned costs Equivalent Units Whole units Direct Materials Conversion 10000 0 8000 70,000 70,000 70,000 80,000 70,000 78,000 30,000 30,000 10000 1,10,000 1,00,000 88,000 Cost Information Costs per equivalent unit: Total costs for April in Sifting Department Total equivalent units Cost per equivalent unit Direct Materials Conversion 1,98,000 1,58,400 1,00,000 88,000 1.98 1.8

Costs assigned to production: Direct Materials Conversion Total ($) 22,000 4500 26,500 14400 14400 Costs allocated to completed and partially completed units: Inventory in process, April 1 balance To complete inventory in process, April 1 Cost of completed April 1 work in process Started and completed in April Total cost of units transferred out Inventory in process, April 30 Total costs assigned by the Sifting Department 1,38,600 40,900 1,26,000 2,64,600 3,05,500 18,000 77,400 59,400 3,82,900

Units Charged to production: Inventory in Progress, April 1 Received from materials storeroom Total units accounted for 10000 1,00,000 1,10,000 Units to be assigned costs: Inventory in Progress, April 1 Started and completed in April Transferred to Department in April Inventory in process, April 30 Total units to be assigned costs Equivalent Units Whole units Direct Materials Conversion 10000 0 8000 70,000 70,000 70,000 80,000 70,000 78,000 30,000 30,000 10000 1,10,000 1,00,000 88,000 Cost Information Costs per equivalent unit: Total costs for April in Sifting Department Total equivalent units Cost per equivalent unit Direct Materials Conversion 1,98,000 1,58,400 1,00,000 88,000 1.98 1.8

Costs assigned to production: Direct Materials Conversion Total ($) 66,000 18000 84,000 14400 14400 Costs allocated to completed and partially completed units: Inventory in process, April 1 balance To complete inventory in process, April 1 Cost of completed April 1 work in process Started and completed in April Total cost of units transferred out Inventory in process, April 30 Total costs assigned by the Sifting Department 1,38,600 98,400 1,26,000 2,64,600 3,63,000 18,000 77,400 59,400 4,40,400