I Cassatt ABC Question

During the height of the COVID 19 pandemic, many people developed new hobbies. As a result, Impressionist Industries is facing a dilemma. The demand for paint sets increased dramatically. Due to supply chain interruptions, the cost of materials to make paint sets also increased. Impressionist Industries manufactures only two products, Water-Color Paint Sets and Oil-Based Paint Sets. To generate adequate profit and cover its expenses throughout the value chain, Cassatt prices its paint sets at 400% of the manufacturing cost. The company is concerned because of the shortage of some materials. The owner and CEO Mary Cassatt questions whether the cost numbers generated by the accounting system are correct. She has just learned about ABC and wants to reanalyze her pricing a cost structure based upon anticipated costs using an ABC system.

Information about the companys products this year are expected to be as follows:

Water-Color Sets

Total direct material costs: $650,000

Total direct labor cost: $195,000

Production volume: 300,000 Water-Color paint sets

Oil-Based Sets

Total direct material cost: $950,000

Total direct labor cost: $105,000

Production volume: 100,000 Oil-Based Paint Sets

Currently, the company applies manufacturing overhead on the basis of direct labor hours. The company incurred $900,000 of manufacturing overhead this year and 20,000 direct labor hours (13,000 direct labor hours making Water-Color sets and 7,000 making Oil-Based sets). The ABC team identified three primary production activities that generate manufacturing overhead costs:

Materials Handling ($100,000); driven by the number of material orders handled

Machine Processing ($600,000); driven by machine hours

Packaging ($200,000); driven by packaging hours

The companys only two products required the following activity levels during the year:

	Direct Labor Hours	Material Orders Handled	Machine Hours	Packaging Hours
Water-Color	13,000	100	25,000	3,000
Oil-Based	7,000	500	15,000	7,000
Total	20,000	600	40,000	10,000

Requirements:

1. Using the companys current costing system (volume-based costing) find the unit cost of producing one Water-Color Paint Set. Be sure to include all of the product costs.
2. Using the companys current costing system (volume-based costing) find the total unit cost of producing one Oil Based Paint Set. Be sure to include all of the product costs.
3. Using Activity-Based Costing find the total unit cost of producing one Water-Color Paint Set. Be sure to include all of the product costs.
4. Using Activity-Based Costing find the total unit cost of producing one Oil Based Paint Set. Be sure to include all of the product costs.

Round to the nearest penny and express your answers in the form $99.99.

I need this in excel sheet too. Please be precise.