Question

In: Accounting

Task Smokey and the Bandit produces “sports towels.” These are towels that has the promotion of...

Task

Smokey and the Bandit produces “sports towels.” These are towels that has the promotion of an event or a logo printed on it. These towels come in five sizes: regular, mid-size, hand, large, special. Data has been collected related to direct materials and direct labor for the five types of towels. Smokey and the Bandit has also collected information on four possible cost drivers (units, orders, machine hours, labor hours). All this information is listed below.

Towel

Units

Sales Price

Material Cost per unit

Labor Cost per unit

Regular

46,000

$3.60

$0.65

$0.37

Mid-Size

25,500

$3.20

$0.55

$0.33

Hand

131,800

$2.55

$0.41

$0.31

Large

28,500

$4.50

$0.70

$0.45

Special

4,500

$4.00

$0.67

$0.48

Towel

Orders

Machine Hours

Labor Hours

Regular

640

5,640

1,341

Mid-Size

1,310

8,730

1,400

Hand

304

7,600

3,198

Large

950

4,750

845

Special

90

1,125

144

Total overhead cost to be allocated: $94,360

Required:

Use the VLOOKUP function to construct a spreadsheet that will allocate overhead for each of these alternative drivers and will calculate the total per unit cost (round to two decimal points) for each type of towel. Each time you construct a spreadsheet for a cost driver, copy and paste (use paste special - Values function) this to a new worksheet to summarize the effect of different cost drivers on the overhead allocated and the resulting cost per unit. Name the new worksheet as “Summary Worksheet”. Therefore, your summary worksheet should contain 4 spreadsheets to show the effect of the four cost drivers on the Overhead calculated and the unit costs.

After constructing your spreadsheet answer the following questions separately under Section B?

What is the total cost per unit of each type of towel when machine hours are used as the cost driver for overhead allocation?

How much overhead is allocated to the Hand towels when direct labor hours are used as the cost driver for overhead allocation?

What is the total cost of the Special towels when direct labor hours are used as the cost driver for overhead allocation?

What is the total cost per unit of the Large towels when the number of orders is used as the cost driver for overhead allocation?

What is the allocation rate when units are used as the cost driver?

NOTE

Here is what i have so far in excel. I am not sure if my calculations are wrong.

Possible Drivers Regular Mid-Size Hand Large Special Total
1 Units 46000 25500 131800 28500 4500 236300
2 Orders 640 1310 304 950 90 3294
3 Machine Hours 5640 8730 7600 4750 1125 27845
4 Labor Hours 1,341 1,400 3,198 845 144 6,928
Overhead Rate: Total overhead cost to be allocated: $94,360
Overhead Cost 94,360 0.399322895 Per Units
1 Units 236300
Cost Regular Mid-Size Hand Large Special Total
Direct Material $29,900.00 $14,025.00 $54,038.00 $19,950.00 $3,015.00 $120,928.00
Direct Labor $17,020.00 $8,415.00 $40,858.00 $12,825.00 $2,160.00 $81,278.00
Allocated Overhead 10182.73381
Total ($57,102.73)
Cost Per Unit ($42.58)

Solutions

Expert Solution

Ans-

Cost as per individual driver Units Orders Machine Hours Labor Hours
Regular 46,000 640 5640 1341
Mid-Size 25,500 1310 8730 1400
Hand 131,800 304 7600 3198
Large 28,500 950 4750 845
Special 4,500 90 1125 144
Total 236,300 3,294 27,845 6,928
Overhead Cost 94,360 94,360 94,360 94,360
Allocation rate as per individual driver 0.399322895 28.64602307 3.388759203 13.62009238
When Unit as driver
No of Units 46000 25500 131800 28500 4500
Regular Mid-Size Hand Large Special TOTAL
Direct Material $ 29,900.00 $ 14,025.00 $    54,038.00 $ 19,950.00 $ 3,015.00 $ 120,928.00
Labor Cost $ 17,020.00 $    8,415.00 $    40,858.00 $ 12,825.00 $ 2,160.00 $    81,278.00
Allocated Overhead $ 18,368.85 $ 10,182.73 $    52,630.76 $ 11,380.70 $ 1,796.95 $    94,360.00
Total cost $ 65,288.85 $ 32,622.73 $ 147,526.76 $ 44,155.70 $ 6,971.95 $ 296,566.00
Cost Per unit $            1.42 $            1.28 $               1.12 $            1.55 $          1.55
When orders as driver
No of Orders 640 1310 304 950 90
Regular Mid-Size Hand Large Special TOTAL
Direct Material $ 29,900.00 $ 14,025.00 $    54,038.00 $ 19,950.00 $ 3,015.00 $ 120,928.00
Labor Cost $ 17,020.00 $    8,415.00 $    40,858.00 $ 12,825.00 $ 2,160.00 $    81,278.00
Allocated Overhead $ 18,333.45 $ 37,526.29 $      8,708.39 $ 27,213.72 $ 2,578.14 $    94,360.00
Total cost $ 65,253.45 $ 59,966.29 $ 103,604.39 $ 59,988.72 $ 7,753.14 $ 296,566.00
Cost Per unit $            1.42 $            2.35 $               0.79 $            2.10 $          1.72
When Machine Hours as driver
No of machine Hours 5640 8730 7600 4750 1125
Regular Mid-Size Hand Large Special TOTAL
Direct Material $ 29,900.00 $ 14,025.00 $    54,038.00 $ 19,950.00 $ 3,015.00 $ 120,928.00
Labor Cost $ 17,020.00 $    8,415.00 $    40,858.00 $ 12,825.00 $ 2,160.00 $    81,278.00
Allocated Overhead $ 19,112.60 $ 29,583.87 $    25,754.57 $ 16,096.61 $ 3,812.35 $    94,360.00
Total cost $ 66,032.60 $ 52,023.87 $ 120,650.57 $ 48,871.61 $ 8,987.35 $ 296,566.00
Cost Per unit $            1.44 $            2.04 $               0.92 $            1.71 $          2.00
When Labor Hours as driver
No of Labor Hours 1341 1400 3198 845 144
Regular Mid-Size Hand Large Special TOTAL
Direct Material $ 29,900.00 $ 14,025.00 $    54,038.00 $ 19,950.00 $ 3,015.00 $ 120,928.00
Labor Cost $ 17,020.00 $    8,415.00 $    40,858.00 $ 12,825.00 $ 2,160.00 $    81,278.00
Allocated Overhead $ 18,264.54 $ 19,068.13 $    43,557.06 $ 11,508.98 $ 1,961.29 $    94,360.00
Total cost $ 65,184.54 $ 41,508.13 $ 138,453.06 $ 44,283.98 $ 7,136.29 $ 296,566.00
Cost Per unit $            1.42 $            1.63 $               1.05 $            1.55 $          1.59

Related Solutions

Smokey and the Bandit produces outdoor activity clothing. The product line consists of pants, jackets, tops,...
Smokey and the Bandit produces outdoor activity clothing. The product line consists of pants, jackets, tops, and accessories. Data has been collected related to direct materials and direct labor for the four product lines. Smokey and the Bandit has also collected information on four possible cost drivers (units, batches, machine hours, labor hours). All this information is listed below. Construct a spreadsheet that will allocate overhead for each of these alternative drivers and will calculate the total per unit cost...
Smokey and the Bandit produces outdoor activity clothing. The product line consists of pants, jackets, tops,...
Smokey and the Bandit produces outdoor activity clothing. The product line consists of pants, jackets, tops, and accessories. Data has been collected related to direct materials and direct labor for the four product lines. Smokey and the Bandit has also collected information on four possible cost drivers (units, batches, machine hours, labor hours). All this information is listed below. Construct a spreadsheet that will allocate overhead for each of these alternative drivers and will calculate the total per unit cost...
Direct Labor and Direct Materials Variances, Journal Entries Jameson Company produces paper towels. The company has...
Direct Labor and Direct Materials Variances, Journal Entries Jameson Company produces paper towels. The company has established the following direct materials and direct labor standards for one case of paper towels: Paper pulp (3 lbs. @ $0.40) $ 1.20 Labor (2 hrs. @ $12) 24.00     Total prime cost $25.20 During the first quarter of the year, Jameson produced 50,000 cases of paper towels. The company purchased and used 150,700 pounds of paper pulp at $0.38 per pound. Actual direct labor...
Direct Labor and Direct Materials Variances, Journal Entries Jameson Company produces paper towels. The company has...
Direct Labor and Direct Materials Variances, Journal Entries Jameson Company produces paper towels. The company has established the following direct materials and direct labor standards for one case of paper towels: Paper pulp (3 lbs. @ $0.40) $ 1.20 Labor (2 hrs. @ $12) 24.00     Total prime cost $25.20 During the first quarter of the year, Jameson produced 50,000 cases of paper towels. The company purchased and used 150,700 pounds of paper pulp at $0.37 per pound. Actual direct labor...
Direct Labor and Direct Materials Variances, Journal Entries Jameson Company produces paper towels. The company has...
Direct Labor and Direct Materials Variances, Journal Entries Jameson Company produces paper towels. The company has established the following direct materials and direct labor standards for one case of paper towels: Paper pulp (3 lbs. @ $0.40) $ 1.20 Labor (2 hrs. @ $12) 24.00     Total prime cost $25.20 During the first quarter of the year, Jameson produced 45,000 cases of paper towels. The company purchased and used 135,700 pounds of paper pulp at $0.38 per pound. Actual direct labor...
Aquella Company produces towels for hotels. The following standards to produce a large towel have been...
Aquella Company produces towels for hotels. The following standards to produce a large towel have been established: Direct materials (2.2 kilograms @ $6.30) $13.86 Direct labour (1.5 hour @ $16.60) $24.90 Standard prime cost for each large towel $38.76 During the year, 72 000 kilograms of material were purchased and used for manufacturing 30 000 large towels, with the following actual prime costs: Direct materials $436 000 Direct labour $775 770 (for 44 900 hours) Required: i. Compute the material...
5. Belville has a comparative advantage in the production of towels, but Russville has an absolute...
5. Belville has a comparative advantage in the production of towels, but Russville has an absolute advantage in the production of towels. If these two countries decide to trade, a. Russville should export towels to Belville. b. Without additional information about opportunity costs, this question cannot be answered. c. Belville should export towel to Russville. d. trading towels would provide no net advantage to either country. 11. For a country that chooses not to trade and remains self-sufficient a. the...
The Rover 6 is a new custom-designed sports car. An analysis of the task of building...
The Rover 6 is a new custom-designed sports car. An analysis of the task of building the Rover 6 reveals the following list of relevant activities, their immediate predecessors, and their duration:2 JOB LETTER DESCRIPTION IMMEDIATE PREDECESSOR(S) NORMAL TIME (DAYS) A Start - 0 B Design A 8 C Order special accessories B 0.1 D Build frame B 1 E Build doors B 1 F Attach axles, wheels, gas tank D 1 G Build body shell B 2 H Build...
Do you think the promotion/relegation system should/could be applied to the MLB or NFL professional sports...
Do you think the promotion/relegation system should/could be applied to the MLB or NFL professional sports leagues in the U.S.? Why or why not? Given the revenue structure of U.S. professional sports teams and European football teams, which leagues will be hurt most if games/matches are played with no or fewer fans attending (ticket sales) because of covid-19? Why?
Do you think the promotion/relegation system should/could be applied to the MLB or NFL professional sports...
Do you think the promotion/relegation system should/could be applied to the MLB or NFL professional sports leagues in the U.S.? Why or why not? Given the revenue structure of U.S. professional sports teams and European football teams, which leagues will be hurt most if games/matches are played with no or fewer fans attending (ticket sales) because of covid-19? Why?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT