Question

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 to answer questions about your findings. 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. When you have completed your spreadsheet, answer the following questions: 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 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 percentagewith 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 group. 16. Save your work to a disk, and print a copy for your files.

Answer 1

1.

Breakeven points (units) = Fixed Cost ÷ (Selling price - Variable cost)

= Selling price ÷ Contribution

Breakeven point (Rupees) = Breakeven point (units) * Selling price

Proposal C:

Fixed cost $ 1,10,000, Selling price per unit $99, Variable cost per unit $49

Break even point units

= $1,10,000 ÷ ($99-$49)

=$1,10,000 ÷ $50

= 2200 units

Break even point in $

= 2200 units * $99

=$2,17,800

Proposal B:

Fixed Overhead Cost $1,10,000, Selling price per unit $129

Variable Cost per unit $ 55

Breakeven point units

=$1,10,000 ÷ ($129 - $55)

=$1,10,000 ÷ $ 74

= 1487 Units Approximately

Break even point $

=1487 units * $129

=$1,91,823

Proposal A:

Fixed cost $1,10,000 Selling price per unit $99

Variable cost per unit $55

Break even units

=$1,10,000 ÷ ($99 - $55)

=$1,10,000 ÷ $44

= 2500 units

Breakeven sales

= 2500 units * $99

= $2,47,500

2.

Increase in Selling price per unit from $99 to $129 has reduced break even units to 1487 from 2500 and Break even sales from $2,47,800 to $1,91,823. Because Higher selling price and constant variable cost leads to higher contribution leading to reduction in break even point.

3.

Change in proposal C does not impacted Breakeven point in Units as well as $ as B because even though the variable cost reduces from $55 to $49 i.e by $6 , their is also change in sale from $129 to $99 i.e by $30 impacting over all contribution. So as contribution per unit is less than that of B, the breakeven point would be higher.