Please solve the following excel problem using the data below Hawaiian Sugar Company manufactures three products (white sugar, brown sugar, and powdered sugar) in a continuous production process Senior management has asked the controller to conduct an activity based costing study The controller identified the amount of factory overhead required by the critical activities of the organization as follows Activity Activity Cost Pool Production $468,000 Setup 168,000 Inspection 85,000 Shipping 144,000 Customer service 50,000 Total $915,000 The activity bases identified for each activity are as follows Activity Activity Base Production Machine hours Setup Number of setups Inspection Number of inspections Shipping Number of customer orders Customer service Number of customer service requests The activity base usage quantities and units produced for the three products were determined from corporate records and are as follows Machine Hours Number of Setups Number of Inspections Number of Customer Orders Number of Customer Service Requests Units White sugar 3,200 100 200 800 40 8,000 Brown sugar 2,000 150 300 2,200 250 5,000 Powdered sugar 2,000 150 500 1,000 110 5,000 Total 7,200 400 1,000 4,000 400 18,000 Each product requires 0 4 machine hour per unit Instructions 1 Determine the activity rate for each activity 2 Determine the total and per unit activity cost for all three products Round to the nearest cent 3 Why aren't the activity unit costs equal across all three products since they require the same machine time per unit 1 Customer Production Setup Inspection Shipping Service Total activity cost $ 468,000 $ 168,000 $ 85,000 $ 144,000 Total activity base machine hours setups insp cust ord req Activity rate machine hour setup insp cust ord req White Sugar Brown Sugar Activity Base Usage x Activity Base Usage Activity Cost Activity Base Usage x Activity Rate Activity Cost Activity 3,200 machine hours machine hour 2,000 machine hours machine hour Production 100 150 Setup setups setup setups setup XXXXX Inspection 200 XXXXX inspection 300 inspection inspections inspections 800 customer orders customer order 2,200 customer orders customer order Shipping Customer service 40 requests request 250 requests request Total activity cost 8,000 units 5,000 units Units unit Activity cost per unit unit Powdered Sugar Activity Activity Base Usage x Activity Rate Activity Cost Production 2,000 machine hours machine hour Setup 150 setups setup Inspection 500 inspections XXXXX inspection Shipping 1,000 customer orders customer order Customer service 110 requests request Total activity cost Units 5,000 units Activity cost per unit unit

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 24, 2024

Please solve the following excel problem using the data below. Hawaiian Sugar Company manufactures three products (white sugar, brown sugar, and powdered sugar) in a

Please solve the following excel problem using the data below.

Hawaiian Sugar Company manufactures three products (white sugar, brown sugar, and powdered sugar) in a continuous production process. Senior management has asked the controller to conduct an activity-based costing study. The controller identified the amount of factory overhead required by the critical activities of the organization as follows:

Activity

Activity Cost Pool

Production

$468,000

Setup

168,000

Inspection

85,000

Shipping

144,000

Customer service

50,000

Total

$915,000

The activity bases identified for each activity are as follows:

Activity

Activity Base

Production

Machine hours

Setup

Number of setups

Inspection

Number of inspections

Shipping

Number of customer orders

Customer service

Number of customer service requests

The activity-base usage quantities and units produced for the three products were determined from corporate records and are as follows:

Machine Hours

Number of Setups

Number of Inspections

Number of Customer Orders

Number of Customer Service Requests

Units

White sugar

3,200

100

200

800

8,000

Brown sugar

2,000

150

300

2,200

250

5,000

Powdered sugar

2,000

150

500

1,000

110

5,000

Total

7,200

400

1,000

4,000

400

18,000

Each product requires 0.4 machine hour per unit.

Instructions

1.Determine the activity rate for each activity.
2.Determine the total and per-unit activity cost for all three products. Round to the nearest cent.
3.Why aren't the activity unit costs equal across all three products since they require the same machine time per unit?

1 . Customer Production Setup Inspection Shipping Service Total activity cost $ 468,000 $ 168,000 $ 85,000 $ 144,000 Total activity base machine hours setups insp. cust. ord. req. Activity rate /machine hour /setup /insp. /cust. ord. /req. White Sugar Brown Sugar Activity Base Usage x Activity Base Usage = Activity Cost Activity Base Usage x Activity Rate Activity Cost Activity 3,200 machine hours /machine hour 2,000 machine hours /machine hour Production 100 150 Setup setups /setup setups /setup XXXXX Inspection 200 XXXXX /inspection 300 /inspection inspections inspections 800 customer orders /customer order 2,200 customer orders /customer order Shipping Customer service 40 requests /request 250 requests /request Total activity cost 8,000 units 5,000 units Units /unit Activity cost per unit /unit Powdered Sugar Activity Activity Base Usage x Activity Rate Activity Cost Production 2,000 machine hours /machine hour Setup 150 setups /setup Inspection 500 inspections XXXXX /inspection Shipping 1,000 customer orders /customer order Customer service 110 requests /request Total activity cost Units 5,000 units Activity cost per unit /unit