Question
Instructions (for Q2 only) : (i) Based on the following case study information, build a spreadsheet model using Excel 2003 (or later) to answer the
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. (ii) 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). (iii) 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. Case study information : Toolbank Inc. produces 2 types of products, Grip and Hinge. It expects to produce 35,000 units of Grip and 50,000 units of Hinge. Direct material cost in 2022 is expected to be $25.00 per unit for Grip and $27.00 per unit for Hinge. Toolbanks total direct labour cost for Grip is expected to be $1,260,000 and $900,000 for Hinge. Other manufacturing costs are considered as overhead. The estimated total annual overhead for the year is $2,700,000. Currently, Toolbank allocates its overheads based on direct labour hours. Grip requires 2 direct labour hours, whilst Hinge requires 1 direct labour hours per unit to complete. The company is deciding to use the activity-based costing (ABC) system to allocate all its overheads. The company estimates that Processing costs amounts to $1,500,000; Maintenance costs amounts to $900,000; and Setup costs amounts to $300,000. Toolbank obtained the following information : Cost Driver Grip Hinge Processing Processing Hours 100,000 150,000 Maintenance Machine Hours 70,000 130,000 Setup No. of Setups 1 2
(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 27% for all products? (d) Assume that Processing cost increased by $200,000 and Setup cost decreased by 30%. Calculate the total cost of Grip only (assuming that the expected no. of units are produced and the total driver units remains the same) using the activity-based costing system.
Plz solve this (c) and (d) with detail explanation
QUESTION 2 (28 marks) Instructions (for Q2 only): (i) (ii) 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. Case study information: Toolbank Inc. produces 2 types of products, "Grip" and "Hinge". It expects to produce 35,000 units of Grip and 50,000 units of Hinge. Direct material cost in 2022 is expected to be $25.00 per unit for Grip and $27.00 per unit for Hinge. Toolbank's total direct labour cost for Grip is expected to be $1,260,000 and $900,000 for Hinge. Other manufacturing costs are considered as overhead. The estimated total annual overhead for the year is $2,700,000. Currently, Toolbank allocates its overheads based on direct labour hours. Grip requires 2 direct labour hours, whilst Hinge requires 1 direct labour hours per unit to complete. The company is deciding to use the activity-based costing (ABC) system to allocate all its overheads. The company estimates that Processing costs amounts to $1,500,000; Maintenance costs amounts to $900,000; and Setup costs amounts to $300,000. Toolbank obtained the following information: Processing Maintenance Setup Cost Driver Processing Hours Machine Hours No. of Setups Grip 100,000 70,000 1 Hinge 150,000 130,000 2 Required : (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 27% for all products? (d) Assume that Processing cost increased by $200,000 and Setup cost decreased by 30%. Calculate the total cost of Grip 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)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started