## Description

Part 1

Industries Inc. manufactures 2 products: A and B. A review of the company’s accounting records revealed the following per-unit costs and production volume:

Product A

Product B

Production volume(units)

3000

5000

Direct labor hours used

3

4

Direct material cost per unit

$40

Direct Labor cost per unit: $14 per hour

3 hours at $14

4 hours at $14

$60

$42

$56

Manufacturing overhead is budgeted at :

$1,798,000

Required:

a. Using direct labor hours as the base for assigning manufacturing overhead cost to products, compute the direct labor hours needed and the predetermined overhead rate using direct labor hours.

Hint: The predetermined overhead rate you compute should be $62, since I have given you the answer but I want to make sure you know how to determine the $62.

Direct labor hours, budget hours

Product A

Guidance:

Product B

Look at exhibit 5Â2 on page 172

Total direct labor hours

Predetermined Overhead

per direct labor hour

rate

b. Using the predetermined overhead rate you determined in “a” above, determine the unit product cost of each product.

Hint: Don’t forget that unit product cost includes direct material, direct labor, and Manufacturing overhead. Don’t make this complicated, since the numbers are already there for you.

Product A

Direct material cost per unit

Direct labor cost per unit

Applied manufacturing overhead per unit

Product B

$40

Total product cost per unit

Guidance:

Make sure you are using

cell references, by

referencing the cells above.

c. You are a recent hire to Industries Inc. and you have made it known that you just recently completed an MBA program that had a management accounting

course that taught you about another costing system that might be appropriate. During a meeting, you suggested that an ABC system might be a more

accurate costing system. Management has asked you to recompute the unit cost using the ABC costing approach, since the company is showing declining profits.

First complete the following chart that is set up like exhibit 5-7 on page 177, and there is a more detailed explanation of the 3 step calculation approach example

on page 176 exhibit 5-6. I have highlighted the columns in red you need to complete and I have started the table to guide you.

Activity

Setups

General Factory

Machine processing

Grand Total

Activity Cost pool

Product

Cost driver

Activity cost line

quantity for

for product production

Cost Driver Quantity Pool rate

Product line product line

line

volume

100

$2,180.00 Product A

90

$196,200

Product B

10

Total

100

20,000

Product A

5000

Product B

15000

Total

20000

2,200

Product A

1600

Product B

600

Total

2200

Cost driver

$218,000 Number of setups

1,360,000 Direct labor hours

220,000 Machine hours

$1,798,000

d. Complete the following table to determine the unit product costs from ABC, just like the exhibit 5-8 on page 178

Product A

Direct material cost per unit

Direct labor cost per unit

Total direct costs per unit

Product B

$40

Manufacturing overhead(based on ABC)

Setups

General Factory

Machine processing

Total product cost per unit

e. Write a short report (at least 75 words) that identifies factors that may account for the company’s declining profits.

Activity

cost per

unit

of

product

3,000

$65.40

5,000

3,000

5,000

3,000

5,000

Part 2

Please make sure you have reviewed the demonstration in the Course Documents area

for chapter 6 before attempting this problem. I have an audio step by step excel explanation

of multiple regression, simple regression, and the scattergraph. If you have a dial up modem this could take awhile to download.

I do have a word tutorial and my excel solution is also available in the course documents area. You need to have the data analysis tool available.

It would be good practice for you to try to prepare the demonstration problem using my tutorial before attempting to solve this problem.

This project focuses on chapter 6, but ties in chapter 5 by trying to choose an independent variable that relates to the cost we are trying to measure.

Food services company is looking at the costs involved with their catering services.

In the past the company has used meals produced as the cost driver for Overhead costs, but you have been recently

hired as a consultant and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for

overhead costs. You have decided to include number of deliveries in your analysis, too.

Do not retype the information above. You should use this sheet as your data sheet and your solution should be on separate worksheets.

When you have completed the requirements you should have this data sheet, 2-simple regression tables, 1-multiple regression table,

and 2-scattergraphs when you are done. I already did a simple regression and scattergraph using meals produced as the independent variable for you.

Required:

1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Overhead costs using each of the

cost drivers above. Make sure you show the equation of the line and R-squared on each of your scattergraphs.

I have gone ahead and developed the simple regression table and the scattergraph using Meals produced as the cost driver (I.e. independent variable)

You do not need to redo these, but you may want to use these as a verification that you know how to develop a scattergraph and a simple regression table.

The output and chart for your simple regression should be on separate sheets that should be named appropriately.

Type the equation of the line under the regression output on each of the simple regression worksheets.

To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the

control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost).

If you have done your scattergraphs correctly the equation of the line and the R-squared should match the output you have on your regression table.

(i.e. R-squared, Intercept, and X Variable.) Hint: the Y(dependent) variable is the same in all your scattergraphs and regression analyses.

2. Prepare a multiple regression table the 2 independent variables-Deliveries and meals produced. Do not try to prepare a scattergraph, since you can make a chart with multiple X’s.

Using the output from the multiple regression, give the equation to estimate overhead costs in terms of these 2 cost drivers. Excel reads left to right, so X1 will be Deliveries and X2 will be meals produced.

Type out the equation of the line on your multiple regression worksheet.

3. Assuming the following level of cost-driver volume, what is the estimated overhead

cost using simple regression for each of the cost drivers and using multiple regression to estimate the cost of overhead. You should have

3 computations for this answer (you need to determine 2 since I have calculated the first one for you).

Your calculation should only use cell references , since you should be estimated the cost by referencing the cost drivers below and the output from the regression tables.

Cost Drivers (independent variables):

Meals produced

13,500

Deliveries

1,200

Solution:

Meals Produced

Answer:

$68,227.15

You need to answer:

Deliveries

Answer:

Guidance:

I have estimated the overhead costs using the simple

regression table output and the estimated cost driver level.

Please click on the cell with my answer and you will see that I

have referenced the simple regression output worksheet that

uses meals as the independent variable.

Multiple regression

4. Using the simple and multiple regression analyses above, what would you recommend

to estimate the Overhead Costs? Why? Make sure you discuss the Coefficient of Determination in your response. Your comments should be at least 75 words.

Industries Inc. manufactures 2 products: A and B. A review of the company’s accounting records revealed the following per-unit costs and production volume:

Product A

Product B

Production volume(units)

3000

5000

Direct labor hours used

3

4

Direct material cost per unit

$40

Direct Labor cost per unit: $14 per hour

3 hours at $14

4 hours at $14

$60

$42

$56

Manufacturing overhead is budgeted at :

$1,798,000

Required:

a. Using direct labor hours as the base for assigning manufacturing overhead cost to products, compute the direct labor hours needed and the predetermined overhead rate using direct labor hours.

Hint: The predetermined overhead rate you compute should be $62, since I have given you the answer but I want to make sure you know how to determine the $62.

Direct labor hours, budget hours

Product A

Guidance:

Product B

Look at exhibit 5Â2 on page 172

Total direct labor hours

Predetermined Overhead

per direct labor hour

rate

b. Using the predetermined overhead rate you determined in “a” above, determine the unit product cost of each product.

Hint: Don’t forget that unit product cost includes direct material, direct labor, and Manufacturing overhead. Don’t make this complicated, since the numbers are already there for you.

Product A

Direct material cost per unit

Direct labor cost per unit

Applied manufacturing overhead per unit

Product B

$40

Total product cost per unit

Guidance:

Make sure you are using

cell references, by

referencing the cells above.

c. You are a recent hire to Industries Inc. and you have made it known that you just recently completed an MBA program that had a management accounting

course that taught you about another costing system that might be appropriate. During a meeting, you suggested that an ABC system might be a more

accurate costing system. Management has asked you to recompute the unit cost using the ABC costing approach, since the company is showing declining profits.

First complete the following chart that is set up like exhibit 5-7 on page 177, and there is a more detailed explanation of the 3 step calculation approach example

on page 176 exhibit 5-6. I have highlighted the columns in red you need to complete and I have started the table to guide you.

Activity

Setups

General Factory

Machine processing

Grand Total

Activity Cost pool

Product

Cost driver

Activity cost line

quantity for

for product production

Cost Driver Quantity Pool rate

Product line product line

line

volume

100

$2,180.00 Product A

90

$196,200

Product B

10

Total

100

20,000

Product A

5000

Product B

15000

Total

20000

2,200

Product A

1600

Product B

600

Total

2200

Cost driver

$218,000 Number of setups

1,360,000 Direct labor hours

220,000 Machine hours

$1,798,000

d. Complete the following table to determine the unit product costs from ABC, just like the exhibit 5-8 on page 178

Product A

Direct material cost per unit

Direct labor cost per unit

Total direct costs per unit

Product B

$40

Manufacturing overhead(based on ABC)

Setups

General Factory

Machine processing

Total product cost per unit

e. Write a short report (at least 75 words) that identifies factors that may account for the company’s declining profits.

Activity

cost per

unit

of

product

3,000

$65.40

5,000

3,000

5,000

3,000

5,000

Part 2

Please make sure you have reviewed the demonstration in the Course Documents area

for chapter 6 before attempting this problem. I have an audio step by step excel explanation

of multiple regression, simple regression, and the scattergraph. If you have a dial up modem this could take awhile to download.

I do have a word tutorial and my excel solution is also available in the course documents area. You need to have the data analysis tool available.

It would be good practice for you to try to prepare the demonstration problem using my tutorial before attempting to solve this problem.

This project focuses on chapter 6, but ties in chapter 5 by trying to choose an independent variable that relates to the cost we are trying to measure.

Food services company is looking at the costs involved with their catering services.

In the past the company has used meals produced as the cost driver for Overhead costs, but you have been recently

hired as a consultant and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for

overhead costs. You have decided to include number of deliveries in your analysis, too.

Do not retype the information above. You should use this sheet as your data sheet and your solution should be on separate worksheets.

When you have completed the requirements you should have this data sheet, 2-simple regression tables, 1-multiple regression table,

and 2-scattergraphs when you are done. I already did a simple regression and scattergraph using meals produced as the independent variable for you.

Required:

1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Overhead costs using each of the

cost drivers above. Make sure you show the equation of the line and R-squared on each of your scattergraphs.

I have gone ahead and developed the simple regression table and the scattergraph using Meals produced as the cost driver (I.e. independent variable)

You do not need to redo these, but you may want to use these as a verification that you know how to develop a scattergraph and a simple regression table.

The output and chart for your simple regression should be on separate sheets that should be named appropriately.

Type the equation of the line under the regression output on each of the simple regression worksheets.

To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the

control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost).

If you have done your scattergraphs correctly the equation of the line and the R-squared should match the output you have on your regression table.

(i.e. R-squared, Intercept, and X Variable.) Hint: the Y(dependent) variable is the same in all your scattergraphs and regression analyses.

2. Prepare a multiple regression table the 2 independent variables-Deliveries and meals produced. Do not try to prepare a scattergraph, since you can make a chart with multiple X’s.

Using the output from the multiple regression, give the equation to estimate overhead costs in terms of these 2 cost drivers. Excel reads left to right, so X1 will be Deliveries and X2 will be meals produced.

Type out the equation of the line on your multiple regression worksheet.

3. Assuming the following level of cost-driver volume, what is the estimated overhead

cost using simple regression for each of the cost drivers and using multiple regression to estimate the cost of overhead. You should have

3 computations for this answer (you need to determine 2 since I have calculated the first one for you).

Your calculation should only use cell references , since you should be estimated the cost by referencing the cost drivers below and the output from the regression tables.

Cost Drivers (independent variables):

Meals produced

13,500

Deliveries

1,200

Solution:

Meals Produced

Answer:

$68,227.15

You need to answer:

Deliveries

Answer:

Guidance:

I have estimated the overhead costs using the simple

regression table output and the estimated cost driver level.

Please click on the cell with my answer and you will see that I

have referenced the simple regression output worksheet that

uses meals as the independent variable.

Multiple regression

4. Using the simple and multiple regression analyses above, what would you recommend

to estimate the Overhead Costs? Why? Make sure you discuss the Coefficient of Determination in your response. Your comments should be at least 75 words.

## Reviews

There are no reviews yet.