• 100% Satisfaction Guarantee

Linda_us, Finance, Accounts & Homework Tutor
Category: Homework
Satisfied Customers: 7139
Experience:  Post Graduate Diploma in Management (MBA)
19873544
Linda_us is online now

# 2-65 CVP and Break-Even Goal Create an Excel spreadsheet

### Resolved Question:

2-65 CVP and Break-Even
Goal: Create an Excel spreadsheet to perform CVP analysis and show the relationship
between price, costs, and break-even points in terms of units and dollars. Use the results
Scenario: Phonetronix is a small manufacturer of telephone and communications devices.
Recently, company management decided to investigate the profitability of cellular phone
production. They have three different proposals to evaluate. Under all the proposals, the
fixed costs for the new phone would be \$110,000. Under proposal A, the selling price of the
new phone would be \$99 and the variable cost per unit would be \$55. Under proposal B, the
selling price of the phone would be \$129 and the variable cost would remain the same.
Under proposal C, the selling price would be \$99 and the variable cost would be \$49.
1. What are the break-even points in units and dollars under proposal A?
2. How did the increased selling price under proposal B impact the break-even points in
units and dollars compared to the break-even points calculated under proposal A?
3. Why did the change in variable cost under proposal C not impact the break-even points
in units and dollars as significantly as proposal B did?
Step-by-Step:
1. Open a new Excel spreadsheet.
2. In column A, create a bold-faced heading that contains the following:
Row 1: Chapter 2 Decision Guideline
Row 2: Phonetronix
Row 3: Cost-Volume-Profit (CVP) Analysis
Row 4: Today’s Date
3. Merge and center the four heading rows across columns A through D.
4. In Row 7, create the following bold-faced, right-justified column headings:
Column B: Proposal A
Column C: Proposal B
Column D: Proposal C
Note: Adjust cell widths when necessary as you work.
5. In Column A, create the following row headings:
Row 8: Selling price
Row 9: Variable cost
Row 10: Contribution margin
Row 11: Contribution margin ratio
Skip a row
Row 13: Fixed cost
Skip a row
Row 15: Break-even in units
Skip a row
Row 17: Break-even in dollars
6. Use the scenario data to fill in the selling price, variable cost, and fixed cost amounts
for the three proposals.
7. Use the appropriate formulas from this chapter to calculate contribution margin,
contribution margin ratio, break-even in units, and break-even in dollars.
8. Format all amounts as:
Number tab: Category: Currency
Decimal places: 0
Symbol: None
Negative numbers: Red with parenthesis
9. Change the format of the selling price, contribution margin, fixed cost, and break-even
in dollars amounts to display a dollar symbol.
10. Change the format of both contribution margin headings to display as indented:
Alignment tab: Horizontal: Left (Indent)
Indent: 1
ISBN: 0-536-47129-0
Introduction to Management Accounting: Chapters 1-17, Fourteenth Edition, by Charles T. Horngren, Gary L. Sundem, William O. Stratton,
90 Part 1: Focus on Decision Making
11. Change the format of the contribution margin amount cells to display a top border,
using the default line style.
Border tab: Icon: Top Border
12. Change the format of the contribution margin ratio amounts to display as a percentage
with two decimal places.
Number tab: Category: Percentage
Decimal places: 2
13. Change the format of all break-even headings and amounts to display as bold-faced.
14. Activate the ability to use heading names in formulas under Tools ? Options:
Calculation tab: Check the box: Accept labels in formulas
15. Replace the cell-based formulas with “word-based” equivalents for each formula used
in Proposal A.
Example: Contribution margin for proposal B would be:
= (‘Selling price’ ‘Proposal B’) - (‘Variable cost’ ‘Proposal B’)
Note: The tic marks used in the example help avoid naming errors caused by data having similar titles (i.e., “contribution
margin” and “contribution margin ratio”). The parentheses help clarify groupings.
Select “Learn about labels and names in formulas” from the right-hand panel.
16. Save your work to a disk, and print a copy for your files.
Submitted: 4 years ago.
Category: Homework
Expert:  Linda_us replied 4 years ago.
HI Customer

Also in future whenever you request me put "FOR LINDA" in front of your post and not in " Already Tried" filed :-)

Regards

Linda
Customer: replied 4 years ago.

Hi Linda,

Sunday evening would be nice.

That was the correct graph you found for the previous question.

Thanks again,

bb

Expert:  Linda_us replied 4 years ago.
Hi Customer

I have completed the assignment but the only issue is that I am not able to do formatting as specified in step 14 & 15.

14. Activate the ability to use heading names in formulas under Tools ? Options: Calculation tab: Check the box: Accept labels in formulas 15. Replace the cell-based formulas with “word-based” equivalents for each formula used in Proposal A. Example: Contribution margin for proposal B would be: = (‘Selling price’ ‘Proposal B’) - (‘Variable cost’ ‘Proposal B’)

Let me know if this is fine with you so that I can go ahead and upload the solution.

Regards

Linda
Customer: replied 4 years ago.
Linda, it's ok, you can continue as is. Thank you.
Expert:  Linda_us replied 4 years ago.
You can view this answer by clicking here to Register or Login and paying \$3.
Linda_us, Finance, Accounts & Homework Tutor
Category: Homework
Satisfied Customers: 7139
Experience: Post Graduate Diploma in Management (MBA)

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

### What Customers are Saying:

• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Last | Next >
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
• He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX

• ### Manal Elkhoshkhany

#### Satisfied Customers:

4522
More than 5000 online tutoring sessions.
< Last | Next >

### Manal Elkhoshkhany

#### Satisfied Customers:

4522
More than 5000 online tutoring sessions.

### LogicPro

#### Satisfied Customers:

3458
Expert in Java C++ C C# VB Javascript Design SQL HTML

### Linda_us

#### Satisfied Customers:

3124
Post Graduate Diploma in Management (MBA)

### Chris M.

#### Satisfied Customers:

2385
Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)

### F. Naz

#### Satisfied Customers:

1988
Experience with chartered accountancy

### Bizhelp

#### Satisfied Customers:

1876
Bachelors Degree and CPA with Accounting work experience

### Seanna

#### Satisfied Customers:

1781
3,000+ satisfied customers, all topics, A+ work

## Related Homework Questions

Chat Now With A Tutor
Linda_us
3125 Satisfied Customers
Post Graduate Diploma in Management (MBA)