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	650	650
Number of setups	10	12
Number of orders	150	150
Machine hours	2,000	2,000
Kilowatt hours	8,500	9,000

Required:

1. 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.
2. 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 thenew 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.

PLEASE GIVE ME THE EXPLANATION IN EXCEL FORMAT