Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Can you show me the VLookup function calculations on Excel ? To make sure I am following the instructions. Can you help me answer the

Can you show me the VLookup function calculations on Excel ? To make sure I am following the instructions.

Can you help me answer the questions Under Section B?

Assignment:

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:

A. 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.

B. 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?

Submission requirements:

Submit the soft copy of Excel in the assignment drop box

Your Excel should have 2 worksheets:

- The first one with spreadsheet using VLOOKUP function

- The second with the summary spreadsheets and answer to the question 1 - 5

Other information

You must use the Vlookup function. Failure to use Vlook up function will result in deduction of 20 points

You must create a nicely formatted Excel spreadsheet for the calculations, you need to create a separate worksheet within your Excel file summarize the four spreadsheets for four cost drivers and to answer the questions 1 5 under section B.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Assurance And Auditing

Authors: Thomas Nelson

1st Edition

0170111342, 978-0170111348

More Books

Students also viewed these Accounting questions