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?
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
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)
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.
MBA Managerial Accounting
1. Elm, Inc. had the following income statement for last period:
Cost of Sales (manufacturing) 24,000
Selling and General Administrative 6,000
Net Income $20,000
If costs of sales was 75% variable and 25% fixed, and Selling and General Expense was 60% variable and 40% fixed, prepare a contribution format income statement and calculate its contribution margin percentage.
2. Sammy Company has a variable cost percentage of 40% on a product that sells for $50 per unit.
Fixed costs are $40,000. Sammy wants to know how many units must be sold to:
a) Break even
b) Earn a profit of $28,000
Ignore income taxes.
3. Heaven Company had the following functional income statement for the month of May, 2014:
Calculate Heaven's break-even sales in units.
4. The Top Cat Corporation has the following current data:
Selling price per unit $40
Variable costs per unit $15
Fixed costs $260,000
Units sold 25,000
Calculate Top Cat Corporation's current operating leverage.
5. The Top Cat Corporation has the following data for 2014:
Selling price per unit $25
Variable costs per unit $12
Fixed costs $60,000
Units sold 40,000
Calculate Top Cal's operating leverage at the end of 2014, assuming that 2014 sales decrease to 30,000 units.
6. Portland Manufacturing had the following data for the past three months.
Using the high-low method, estimate Portland's total fixed costs, contribution margin ratio and break even point in sales dollars for April. Portland expects to sell 10,000 units for $50 per unit.
#7. Assume the Southeast Furniture Company sells two kinds of picnic tables, pine and redwood. At a 2:1 unit sales mix in which Southeast sells two pine tables for every redwood table, the following revenue and cost information is available.
(see attached file)
Assuming a 2:1 sales mix, calculate Southeast Furniture's current monthly average unit contribution margin, break-even sales volume, and number of units of Pine and Redwood tables at break-even point.
#8. Ontario Outdoors is a manufacturer of outdoor items. The company is considering the possibility of offering a new sleeping bag that would sell for $150 each. Cost to manufacture these sleeping bags includes $40 in materials and $35 in direct labor for each sleeping bag. Variable marketing and selling costs would be $15 each. In order to manufacture these sleeping bags, the company would need to incur $120,000 in fixed costs for new equipment.
a. Compute the break-even point of the sleeping bag in units sold.
b. What would be the total revenue at the break-even point?
c. How many units would Ontario need to sell to earn a profit of $21,000?
d. If fixed costs in fact are $150,000 rather than $120,000, how many units would need to be sold in order to earn $21,000?