subject: management accounting

chapter : Activity based costing systems

3. Make another copy of your original worksheet. In this worksheet you will be extending the data and the answer section to include a third mobile phone line called Green, using the following information. Green Direct Materials $1,250 Direct Labour $800 Units completed 600 Number of setups 3 Number of orders 2 Machine hours 2,500 Kilowatt hours 2,000 Calculate the total and unit cost for the new mobile phone line then create a pdf printout of this final worksheet to submit for marking. Preview the printout first to make sure that the worksheet will print neatly on one page, then save to pdf.TASK 3 - Determine costs using Activity Based Costing System Honeybee Technology manufactures a line of mobile phones 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 $24,000 Number of setups 60 Ordering costs $9,000 Number of orders 900 Machine costs $36,000 Machine hours 36,000 Power $5,000 Kilowatt hours 100,000 Blue Pink Direct Materials $1,300 $1,525 Direct Labour $900 $900 Units completed 500 500 Number of setups 6 10 Number of orders 60 120 Machine hours 1,500 2,000 Kilowatt hours 6,000 5,000Required: 1. Design a spreadsheet to determine the cost of each product using an activity-based costing system. Your worksheet should include a data section and clearly show the total costs for each product. 2. Create additional copies of your original spreadsheet to complete the following 'What-if-Analysis': Honeybee Technology 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 can be reduced by 50%? c. What is the new unit cost of each product if annual power costs are reduced by 70%? In a separate worksheet, prepare a summary of the unit costs from both Part 1 and Part 2 for analysis