Question

(Spreadsheet Problem)

You run a manufacturing facility. Last year your facility manufactured 21 products with the following characteristics:

Products	Number of Parts in the Product	Quantity Manufactured	Fabrication Time (hours/part)	Design and Prototyping (Eng. hours)
1	13	100	120	14
2	10	234	98	8
3	34	1000	389	57
4	56	2000	600	110
5	112	9	1000	350
6	34	50	340	32
7	78	100	800	200
8	22	100	200	22
9	43	250	415	78
10	89	1000	900	300
11	6	50	60	4

Activity-Based Costing (ABC)

Products	Number of Parts in the Product	Quantity Manufactured	Fabrication Time (hours/part)	Design and Prototyping (Eng. hours)
12	113	50	1150	400
13	212	50	2000	1000
14	19	1000	200	17
15	28	1245	300	30
16	111	20	1116	356
17	44	250	450	70
18	100	69	1000	347
19	55	345	567	86
20	34	25	335	40
21	12	500	123	12

In addition, the following data is known about last year:

- 1.1 million labor hours were used to build the 21 products (note, “labor hours” and “fabrication hours” are not the same)

- $37/hour labor rate

- Assume there is no inflation

Activity	Cost ($)	Cost Driver	Driver Quantity Data
Design and Prototype	$290,000	Engineering Hours
Programming, Setup and Tooling	$150,000	Number of Setups	21
Fabrication	$70,000,000	Fabrication Hours
Receiving	$150,000	Number of Receipts	312
Packing and Shipping	$150,090	Number of Customers	43

You are considering manufacturing the following 3 new products:

	Product A	Product B	Product C
Number of Parts in the Product	23	46	212
Number of Setups	1	1	1
Number of Receipts	12	3	32
Number of Customers	3	1	7
Quantity Required	25	154	1000

Use ABC to determine how much you should quote customers for each of the products (assume no profit in the quotes). Your answer should be based on last year’s history (do not assume that products A, B, and C have or are necessarily going to be built)

Hints:

1)You will need to figure out the number of engineering hours and fabrication hours needed for the three new products

2)You can figure out the labor hours associated with each new product from last year’s ratio of labor hours to fabrication hours.

Answer 1