Determine costs using Activity Based Costing System

Green Vision manufactures a line of Virtual Reality Headsets and management has identified the following overhead costs and related cost drivers for the coming year:

Overhead item

Expected Cost

Cost Driver

Quantity

Setup costs

$$42,000$

Number of setups

$80$

Ordering costs

$$12,000$

Number of orders

$1,200$

Machine costs

$$52,000$

Machine hours

$45,000$

Power

$$11,000$

Kilowatt hours

$125,000$

Gold Silver

Total Direct Materials $$2,500$ $$1,850$

Total Direct Labour $$1,200$ $$1,200$

Units completed $650650$

Number of setups $1012$

Number of orders $150150$

Machine hours $2,0002,000$

Kilowatt hours $8,5009,000$

Required:

Design a spreadsheet to determine the cost of each product using an activity$-$based costing system. worksheet include a data section and clearly show the total costs for each product.

Additional copies of original spreadsheet to complete the following 'What$-$if$-$Analysis':

Green Vision is trying to reduce its unit costs. Management believes that the two easiest areas to do this is in setup and power. Calculate the new cost information for each of the following unrelated situations.

$[$a$]$ What are the new unit costs of each product if annual setup costs are reduced by $25\%?$

$[$b$]$ What is the new unit cost of each product if the number of setups for each product can be halved?

$[$c$]$ What is the new unit cost of each product if annual power costs are reduced by $70\%?$

In a separate worksheet, show summary of the unit costs from both Part $1$ and Part $2$ for analysis.

$3.$ Make another copy of original worksheet. In this worksheet will be extending the data and the answer section to include a third VR Headset line called Green, using the following information.

Bronze

Total Direct Materials $$2,250$

Total Direct Labour $$1,100$

Units completed $600$

Number of setups $5$

Number of orders $70$

Machine hours $2500$

Kilowatt hours $2000$

Calculate the total and unit cost for the new Headset line.