Problem 2 Cost Volume Analysis All analysis and calculations and report must be done in a single (ONE) Excel file Put your name at the top of the worksheet Make Excel do all of the calculations (Instructor must be able to see your cell reference formulas ) Include graph interpretation below the graph Make sure it is clear, complete, and easy to find A manager is trying to determine which of three production processes to implement to produce a component for a new product line Process A would entail a variable cost of $17 per unit and an annual fixed cost of $150,000 Process B would entail a variable cost of $11 per unit and an annual fixed cost of $250,000 Process C would entail a variable cost of $20 per unit and an annual fixed cost of $180,000 Develop three separate models in your spreadsheet to calculate Total cost for each process The models must be flexible and able to calculate Total cost for any Quantity produced Create a Cost Volume graph that shows total cost lines for all three options (Volume should range from 0 to 30,000) Write an interpretation of your graph Attached is the spreadsheet to be used D E F G H 1 J K L M Cost volume analysis 1 0 9 0 8 0 7 0 6 0 5 0 4 0 3 0 2 0 1 0 0 0 2 0 4 0 6 0 8 1 B C 1 Cost volume analysis 2 3 Enter the fixed and variable costs for the 4 options in the data area You may enter a 5 volume at which to perform a volume analysis 6 7 8 Data 9 10 Fixed cost 11 Variable cost 12 13 Volume (optional) 0 14 15 Results 16 Breakeven points Units Dollars 17 A vs B DIV 0 DIV 0 18 A vs DIV 0 DIV 0 19 Bvs C DIV 0 DIV 0 20 21 Volume analysis units A B 22 Total Cost 0 23 24 25 Graph 26 Units A B 27 0 0 28 DIV 0 DIV 0 DIV 0 29 30 31 32 33 34 35 Units ABC 0 0 CV 0 0 DIV 0 D E F G H 1 J K L M Cost volume analysis 1 0 9 0 8 0 7 0 6 0 5 0 4 0 3 0 2 0 1 0 0 0 2 0 4 0 6 0 8 1 B C 1 Cost volume analysis 2 3 Enter the fixed and variable costs for the 4 options in the data area You may enter a 5 volume at which to perform a volume analysis 6 7 8 Data 9 10 Fixed cost 11 Variable cost 12 13 Volume (optional) 0 14 15 Results 16 Breakeven points Units Dollars 17 A vs B DIV 0 DIV 0 18 A vs DIV 0 DIV 0 19 Bvs C DIV 0 DIV 0 20 21 Volume analysis units A B 22 Total Cost 0 23 24 25 Graph 26 Units A B 27 0 0 28 DIV 0 DIV 0 DIV 0 29 30 31 32 33 34 35 Units ABC 0 0 CV 0 0 DIV 0

The Answer is in the image, click to view ...

Question: Problem 2: Cost-Volume Analysis All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top

Problem 2: Cost-Volume Analysis

All analysis and calculations and report must be done in a single (ONE) Excel file.
Put your name at the top of the worksheet.
Make Excel do all of the calculations. . (Instructor must be able to see your cell-reference formulas.)
Include graph interpretation below the graph. Make sure it is clear, complete, and easy to find.

A manager is trying to determine which of three production processes to implement to produce a component for a new product line. Process A would entail a variable cost of $17 per unit and an annual fixed cost of $150,000. Process B would entail a variable cost of $11 per unit and an annual fixed cost of $250,000. Process C would entail a variable cost of $20 per unit and an annual fixed cost of $180,000.

Develop three separate models in your spreadsheet to calculate Total cost for each process. The models must be flexible and able to calculate Total cost for any Quantity produced.
Create a Cost-Volume graph that shows total cost lines for all three options. (Volume should range from 0 to 30,000)
Write an interpretation of your graph

Attached is the spreadsheet to be used:

Problem 2: Cost-Volume Analysis All analysis and

D E F G H 1 J K L M Cost-volume analysis 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 0 0.2 0.4 0.6 0.8 1 B C 1 Cost-volume analysis 2 3 Enter the fixed and variable costs for the 4 options in the data area. You may enter a 5 volume at which to perform a volume analysis. 6 7 8 Data 9 | 10 Fixed cost 11 Variable cost 12 13 Volume (optional) 0 14 15 Results 16 Breakeven points Units Dollars 17 A vs. B #DIV/0! #DIV/0! 18 A vs. #DIV/0! #DIV/0! 19 Bvs. C #DIV/0! #DIV/0! 20 21 Volume analysis @ units A B 22 Total Cost 0 23 24 25 Graph 26 Units A B 27 0 0 28 #DIV/0! #DIV/0! #DIV/0! 29 30 31 32 33 34 35 Units ABC 0 0 CV 0 0 #DIV/0! D E F G H 1 J K L M Cost-volume analysis 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 0 0.2 0.4 0.6 0.8 1 B C 1 Cost-volume analysis 2 3 Enter the fixed and variable costs for the 4 options in the data area. You may enter a 5 volume at which to perform a volume analysis. 6 7 8 Data 9 | 10 Fixed cost 11 Variable cost 12 13 Volume (optional) 0 14 15 Results 16 Breakeven points Units Dollars 17 A vs. B #DIV/0! #DIV/0! 18 A vs. #DIV/0! #DIV/0! 19 Bvs. C #DIV/0! #DIV/0! 20 21 Volume analysis @ units A B 22 Total Cost 0 23 24 25 Graph 26 Units A B 27 0 0 28 #DIV/0! #DIV/0! #DIV/0! 29 30 31 32 33 34 35 Units ABC 0 0 CV 0 0 #DIV/0

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

Problem 2: Cost-Volume Analysis All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet. Make Excel do all of the calculations. ....

MGMT 3213 Problem 2: Cost-Volume Analysis All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet. Make Excel do all of the...

Problem 2 Cost-Volume Analysis - All analysis and calculations and report must be done in a single (ONE) Excel file. - Put your name at the top of the worksheet. - Make Excel do all of the...

MGMT 3213 Problem 2 Cost-Volume Analysis - All analysis and calculations and report must be done in a single (ONE) Excel file. - Put your name at the top of the worksheet. - Make Excel do all of the...

Problem 1 Forecasting - All analysis and calculations and report must be done in a single (ONE) Excel file. - Put your name at the top of the worksheet. - Make Excel do all of the calculations....

Part 1 Forecasting (30 pts) All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet. Make Excel do all of the calculations....

formulas needed MGMT 3213 Part 1 Forecasting (30 pts) All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet. Make Excel do all...

All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet. Make Excel do all of the calculations. (Instructor must be able to see...

They can be biomarkers of exposure: a) Metallothionein and acetylcholinesterase b) Acetylcholinesterase and vitamins c) N/A

4. A bar of length 1000 mm and diameter 30 mm is centrally bored for 400 mm, the bore diameter being 10 mm as shown below. Under a load of 30 kN, if the extension of the bar is 0.222 mm, what is the...

When should the service auditor obtain a representation letter from management? At the same time as when assessing the design of controls. Before obtaining an understanding of the service...

What is an example of constructive feedback? provide reference