In: Accounting
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) |
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 |