Need help urgently for question 1

QUESTION 1 (9 marks)

John Ltd. manufactures a product which passes through 2 departments, Boiling and Cooling.

Processing first begins in the Boiling department. After boiling of all the materials have been completed, the product is transferred to Cooling department. In the Cooling department, further materials are added when processing is 25% complete (all the materials in the Cooling department are added at this point only).

Conversion costs are incurred uniformly throughout the process in the Cooling department.

Work in process in the Cooling Department as at 1 Nov amounted to 50,000 units (40% complete). The costs included in these units are as follows :

Boiling Department	$900,000
Cooling Department : - Direct materials - Direct labour - Overheads	$740,000 $210,000 $?

During the month of Nov, 140,000 units were completed in the Cooling Department and transferred to finished goods inventory. Units transferred in from Boiling in Nov were charged at $19 per unit. Cooling department?s costs in Nov were :

Materials added Direct labour Overheads

$2,850,000 $2,225,000 $?

Overhead is allocated on the basis of 110% of direct labour cost.

At the end of the month, 60,000 units were still in process in Cooling. These were estimated to be 30% complete.

Required :

(a) Prepare the Cooling Department production cost report for the month of Nov 2015 using (round up to 2 decimal place) :

• Weighted Average method
• FIFO method (9 marks)

QUESTION 1 (9 marks) John Ltd. manufactures a product which passes through 2 departments, Boiling and Cooling. Processing first begins in the Boiling department. After boiling of all the materials have been completed, the product is transferred to Cooling department. In the Cooling department, further materials are added when processing is 25% complete (all the materials in the Cooling department are added at this point only). Conversion costs are incurred uniformly throughout the process in the Cooling department. Work in process in the Cooling Department as at 1 Nov amounted to 50,000 units (40% complete). The costs included in these units are as follows : Boiling Department $900,00 0 Cooling Department : - Direct materials $740,000 - Direct labour $210,000 - Overheads $? During the month of Nov, 140,000 units were completed in the Cooling Department and transferred to finished goods inventory. Units transferred in from Boiling in Nov were charged at $19 per unit. Cooling department's costs in Nov were : Materials added $2,850,000 Direct labour $2,225,000 Overheads $? Overhead is allocated on the basis of 110% of direct labour cost. At the end of the month, 60,000 units were still in process in Cooling. These were estimated to be 30% complete. Required : (a) Prepare the Cooling Department production cost report for the month of Nov 2015 using (round up to 2 decimal place) : (i) (ii) Weighted Average method FIFO method (9 marks) QUESTION 2 (28 marks) Instructions (for Q2 only) : (i) Based on the following case study information, build a spreadsheet model using Excel 2003 (or later) to answer the accompanying problems. The spreadsheet model should include a \"Data Input Section\" (whereby all raw / original data from the case should be entered), \"Additional Information Section\" (whereby other relevant information for the individual problems should be entered) and an \"Output Section\" (whereby the final answers to each problem is presented). Program your spreadsheet to perform all necessary calculations. Do not \"hard code\" any amounts, use the addition, subtraction, multiplication, division operations or any other specialised formulas. (ii) (iii) (iv) Print a copy of the spreadsheet with the answers / results to the problems. (v) Print a copy of the spreadsheet showing the formulas used therein. Note : Please submit a copy of your CD with your Excel file. Case study information : Timmy Inc. produces 2 types of products, \"Ronny\" and \"Sonny\". It expects to produce 70,000 units of Ronny and 60,000 units of Sonny. Total direct material cost in 2015 is expected to be $55.00 per unit for Ronny and $100.00 per unit for Sonny. Timmy expects to pay wages of $20.00 per direct labour hour. Ronny requires 2 direct labour hours per unit, whilst Sonny requires 1 direct labour hours per unit to complete. Other manufacturing costs are considered as overhead. The estimated total annual overhead for the year is $3,500,000. Currently, Timmy allocates its overheads based on direct labour hours. The company is considering the use of activity-based costing (ABC) system to allocate all its overheads based on the following information : Moulding Quality control (Machine hours) (No of inspections) Shipping (No of customer orders) Ronny 36,000 20 500 Sonny 44,000 30 1,500 Timmy estimates that Moulding related costs amounts to $2,800,000; Quality control related costs amounts to $150,000; and Shipping related costs amounts to $550,000. Required : (a) Calculate the total cost of each product (assuming that the expected no. of units are produced) using the traditional costing system. (b) Calculate the total cost of each product (assuming that the expected no. of units are produced) using the activity-based costing system. (c) Suppose the company decides to use ABC to set their selling price. What would be the minimum selling price per unit for each product if the company requires a gross profit margin of 45% for all products? (d) Assume that Moulding cost increased by 20% and Shipping cost decreased by $150,000. Calculate the total cost of Ronny only (assuming that the expected no. of units are produced and the total driver units remains the same) using the activity-based costing system. (Parts (a) to (d) - 21 marks) (e) Distinguish between an activity's \"trigger\" and its root cause (give an example of each). The product line manager commented \"Although in the past this product has been very profitable, based in our recent reports, it appears that we might need to drop this product\TIMMY INC RONNY UNITS TO BE PRODUCED Direct Material costs labour hours labour cost per hour Overhead per year SONNY 70000 $55 2 60000 $100 1 $20 $3,500,000 Additional Information Over head costs are allocated based on labour hours for traditional costing Inspection costs amount to 150000 shipping costs amounted to 550000 Moulding costs amounted to 2800000 For question d Moulding costs increase by 20% Shipping cost reduce by 150000 all other costs and drivers remain same Qa. Total cost of each product based on the traditional cost system Ronny Units to be produced Direct material costs per unit Cost of Direct materials labour hours per unit Labour cost per hour Direct labour costs per unit Total labour costs overheads allocation Total costs Sonny 70000 $55 $3,850,000 2 $20 $40 $2,800,000 2333333.333333 $8,983,333 60000 $100 $6,000,000 1 20 20 1200000 1166666.666667 $8,366,667 Q2b cost of each product using the activity based costing units to be produced direct material costs per unit cost of direct materials Labour hours per unit labour costs per unit Direct labour costs per unit total direct labour costs Overheads allocation Moulding Hours in moulding cost allocated due to moulding Quality control No of Inspections costs allocated to inspection Shipping No of customer orders Costs allocated to Shipping 70000 55 3850000 2 20 40 2800000 36000 1260000 44000 1540000 20 60000 30 90000 500 137500 1500 412500 8107500 total costs of production 60000 100 6000000 1 20 20 1200000 9242500 Q2c. Setting price using the ABC plus margin Sonny Total Costs Units Produced costs per unit Profit Margin Price Ronny 8107500 9242500 70000 60000 115.8214285714 154.0416666667 45% 45% 210.5844155844 280.0757575758 $211 $280 Q2d. Units to be produced Direct material costs Direct labour costs Moulding cost increase by 20% and shipping cost reduce by 150000, new costs using ABC Ronny Sonny 70000 60000 3850000 6000000 2800000 1200000 Inspection costs Shipping costs Moulding Costs Total costs($) 60000 100000 1512000 8322000 90000 300000 1848000 9438000 TIMMY INC RONNY UNITS TO BE PRODUCED Direct Material costs labour hours labour cost per hour Overhead per year SONNY 70000 $55 2 60000 $100 1 $20 $3,500,000 Additional Information Over head costs are allocated based on labour hours for traditional costing Inspection costs amount to 150000 shipping costs amounted to 550000 Moulding costs amounted to 2800000 For question d Moulding costs increase by 20% Shipping cost reduce by 150000 all other costs and drivers remain same Qa. Total cost of each product based on the traditional cost system Ronny Units to be produced Direct material costs per unit Cost of Direct materials labour hours per unit Labour cost per hour Direct labour costs per unit Total labour costs overheads allocation Total costs Sonny 70000 $55 $3,850,000 2 $20 $40 $2,800,000 2333333.333333 $8,983,333 60000 $100 $6,000,000 1 20 20 1200000 1166666.666667 $8,366,667 Q2b cost of each product using the activity based costing units to be produced direct material costs per unit cost of direct materials Labour hours per unit labour costs per unit Direct labour costs per unit total direct labour costs Overheads allocation Moulding Hours in moulding cost allocated due to moulding Quality control No of Inspections costs allocated to inspection Shipping No of customer orders Costs allocated to Shipping 70000 55 3850000 2 20 40 2800000 36000 1260000 44000 1540000 20 60000 30 90000 500 137500 1500 412500 8107500 total costs of production 60000 100 6000000 1 20 20 1200000 9242500 Q2c. Setting price using the ABC plus margin Sonny Total Costs Units Produced costs per unit Profit Margin Price Ronny 8107500 9242500 70000 60000 115.8214285714 154.0416666667 45% 45% 210.5844155844 280.0757575758 $211 $280 Q2d. Units to be produced Direct material costs Direct labour costs Moulding cost increase by 20% and shipping cost reduce by 150000, new costs using ABC Ronny Sonny 70000 60000 3850000 6000000 2800000 1200000 Inspection costs Shipping costs Moulding Costs Total costs($) 60000 100000 1512000 8322000 90000 300000 1848000 9438000 TIMMY INC RONNY UNITS TO BE PRODUCED Direct Material costs labour hours labour cost per hour Overhead per year SONNY 70000 $55 2 60000 $100 1 $20 $3,500,000 Additional Information Over head costs are allocated based on labour hours for traditional costing Inspection costs amount to 150000 shipping costs amounted to 550000 Moulding costs amounted to 2800000 For question d Moulding costs increase by 20% Shipping cost reduce by 150000 all other costs and drivers remain same Qa. Total cost of each product based on the traditional cost system Ronny Units to be produced Direct material costs per unit Cost of Direct materials labour hours per unit Labour cost per hour Direct labour costs per unit Total labour costs overheads allocation Total costs Sonny 70000 $55 $3,850,000 2 $20 $40 $2,800,000 2333333.333333 $8,983,333 60000 $100 $6,000,000 1 20 20 1200000 1166666.666667 $8,366,667 Q2b cost of each product using the activity based costing units to be produced direct material costs per unit cost of direct materials Labour hours per unit labour costs per unit Direct labour costs per unit total direct labour costs Overheads allocation Moulding Hours in moulding cost allocated due to moulding Quality control No of Inspections costs allocated to inspection Shipping No of customer orders Costs allocated to Shipping 70000 55 3850000 2 20 40 2800000 36000 1260000 44000 1540000 20 60000 30 90000 500 137500 1500 412500 8107500 total costs of production 60000 100 6000000 1 20 20 1200000 9242500 Q2c. Setting price using the ABC plus margin Sonny Total Costs Units Produced costs per unit Profit Margin Price Ronny 8107500 9242500 70000 60000 115.8214285714 154.0416666667 45% 45% 210.5844155844 280.0757575758 $211 $280 Q2d. Units to be produced Direct material costs Direct labour costs Moulding cost increase by 20% and shipping cost reduce by 150000, new costs using ABC Ronny Sonny 70000 60000 3850000 6000000 2800000 1200000 Inspection costs Shipping costs Moulding Costs Total costs($) 60000 100000 1512000 8322000 90000 300000 1848000 9438000 TIMMY INC RONNY UNITS TO BE PRODUCED Direct Material costs labour hours labour cost per hour Overhead per year SONNY 70000 $55 2 60000 $100 1 $20 $3,500,000 Additional Information Over head costs are allocated based on labour hours for traditional costing Inspection costs amount to 150000 shipping costs amounted to 550000 Moulding costs amounted to 2800000 For question d Moulding costs increase by 20% Shipping cost reduce by 150000 all other costs and drivers remain same Qa. Total cost of each product based on the traditional cost system Ronny Units to be produced Direct material costs per unit Cost of Direct materials labour hours per unit Labour cost per hour Direct labour costs per unit Total labour costs overheads allocation Total costs Sonny 70000 $55 $3,850,000 2 $20 $40 $2,800,000 2333333.333333 $8,983,333 60000 $100 $6,000,000 1 20 20 1200000 1166666.666667 $8,366,667 Q2b cost of each product using the activity based costing units to be produced direct material costs per unit cost of direct materials Labour hours per unit labour costs per unit Direct labour costs per unit total direct labour costs Overheads allocation Moulding Hours in moulding cost allocated due to moulding Quality control No of Inspections costs allocated to inspection Shipping No of customer orders Costs allocated to Shipping 70000 55 3850000 2 20 40 2800000 36000 1260000 44000 1540000 20 60000 30 90000 500 137500 1500 412500 8107500 total costs of production 60000 100 6000000 1 20 20 1200000 9242500 Q2c. Setting price using the ABC plus margin Sonny Total Costs Units Produced costs per unit Profit Margin Price Ronny 8107500 9242500 70000 60000 115.8214285714 154.0416666667 45% 45% 210.5844155844 280.0757575758 $211 $280 Q2d. Units to be produced Direct material costs Direct labour costs Moulding cost increase by 20% and shipping cost reduce by 150000, new costs using ABC Ronny Sonny 70000 60000 3850000 6000000 2800000 1200000 Inspection costs Shipping costs Moulding Costs Total costs($) 60000 100000 1512000 8322000 90000 300000 1848000 9438000