Question

Required: Supply the missing data in the following cases. Each case is independent of the others. Case 1 2 $ 4,800 $ $ 6,300 3,300 4,300 5,300 7,300 3.300 4.300 9.300 5,300 18,800 2.800 20.300 3,300 2.150 1,300 3,150 Direct materials Direct labour Manufacturing overhead Total manufacturing costs Beginning work-in-process inventory Ending work-in-process inventory Cost of goods manufactured Sales Beginning finished goods inventory Cost of goods manufactured Goods available for sale Ending finished goods inventory Cost of goods sold Gross margin Operating expenses Net income 18,300 30,200 1,300 22.500 2.800 19,300 36,300 40,300 18,100 20,400 3,800 1,800 4,300 50% 40% 3,750 $ 4.300 5,600 $ 9,300

can you show the formula u used in excel. thank you!

Answer 1

Case No.1

Direct Labour = Total Manufacturing Costs - Direct Materials - Manufacturing Overhead

Direct Labour = 18,800 - 4,800 - 5,300

Direct Labour = $ 8,700

Ending Work in Process Inventory = Total Manufacturing Costs + Beginning Work in Process Inventory - Cost of Goods Manufactured

Ending Work in Process Inventory = 18,800 + 2,800 - 18,300

Ending Work in Process Inventory = $ 3,300

Cost of Goods Manufactured = $ 18,300

Goods Available for Sale = Cost of Goods Manufactured + Beginning Finished Goods Inventory

Goods Available for Sale = 18,300 + 1,300

Goods Available for Sale = $ 19,600

Cost of Goods Sold = Sales - Gross Margin

Cost of Goods Sold = 30,200 - 12,080 = $ 18,120

Gross Margin = 40% of Sales

Gross Margin = 40% * 30,200 = $ 12,080

Ending Finished Goods Inventory = Goods Available for Sale - Cost of Goods Sold

Ending Finished Goods Inventory = 19,600 - 18,120 = $ 1,480

Operating Expenses = Gross Margin - Net Income

Operating Expenses = 12,080 - 4,300

Operating Expenses = $ 7,780

Case 2

Total Manufacturing Costs = Direct Materials + Direct Labour + Manufacturing Overhead

Total Manufacturing Costs = 6,300 + 3,300 + 4,300

Total Manufacturing Costs = $ 13,900

Cost of Goods Manufactured = Total Manufacturing Costs + Beginning Work in Process Inventory - Ending Work in Process Inventory

Cost of Goods Manufactured = 13,900 + 2,150 - 1,300

Cost of Goods Manufactured = $ 14,750

Goods Available for Sale = Cost of Goods Manufactured + Beginning Finished Goods Inventory

Goods Available for Sale = 14,750 + 2,800

Goods Available for Sale = 17,550

Cost of Goods Sold = Goods Available for Sale - Ending Finished Goods Inventory

Cost of Goods Sold = 17,550 - 1,800

Cost of Goods Sold = $ 15,750

Gross Margin = Sales - Costs of Goods Sold

Gross Margin = 22,500 - 15,750

Gross Margin = $ 6,750

Net Income = Gross Margin - Operating Expenses

Net Income = 6,750 - 3,750

Net Income = $ 3,000

Case 3

Manufacturing Overhead = Total Manufacturing Costs - Direct Materials - Direct Labour

Manufacturing Overhead = 20,300 - 5,300 - 7,300

Manufacturing Overhead = $ 7,700

Ending Work in Process Inventory = Total Manufacturing Costs + Beginning Work in Process Inventory - Cost of Goods Manufactured

Ending Work in Process Inventory = 20,300 + 3,300 - 19,300

Ending Work in Process Inventory = $ 4,300

Gross Margin = 50% of Sales

Gross Margin = 50% of 36,300

Gross Margin = $ 18,150

Cost of Goods Sold = Sales - Gross Margin

Cost of Goods Sold = 36,300 - 18,150

Cost of Goods Sold = $ 18,150

Cost of Goods Available for Sale = Cost of Goods Sold + Ending Finished Goods Inventory

Cost of Goods Available for Sale = 18,150 + 4,300

Cost of Goods Available for Sale = $ 22,450

Beginning Finished Goods Inventory = Cost of Goods Available for Sale - Costs of Goods Manufactured

Beginning Finished Goods Inventory = 22,450 - 19,300

Beginning Finished Goods Inventory = $ 3,150

Operating Expenses = Gross Margin - Net Income

Operating Expenses = 18,150 - 5,600

Opeu Expenses = $ 12,550

Case 4

Total Manufacturing Costs = Direct Materials + Direct Labour + Manufacturing Overhead

Total Manufacturing Costs = 3,300 + 4,300 + 9,300

Total Manufacturing Costs = $ 16,900

Beginning Work in Process Inventory = Cost of Goods Manufactured + Ending Work in Process Inventory - Total Manufacturing Costs

Beginning Work in Process Inventory = 18,100 + 3,150 - 16,900

Beginning Work in Process Inventory = $ 4,350

Beginning Finished Goods Inventory = Goods Available for Sale - Cost of Goods Manufactured

Beginning Finished Goods Inventory = 20,400 - 18,100

Beginning Finished Goods Inventory = $ 2,300

Cost of Goods Sold = Goods Available for Sale - Ending Finished Goods Inventory

Cost of Goods Sold = 20,400 - 3,800

Cost of Goods Sold = $ 16,600

Gross Margin = Sales - Cost of Goods Sold

Gross Margin = 40,300 - 16,600

Gross Margin = $ 23,700

Operating Expenses = Gross Margin - Net Income

Operating Expenses = 23,700 - 9,300

Operating Expenses = $ 14,400