Question

Mary Ann is the wife of Kevin Lomax (an associate of John Milton) and earns a little extra money by making bee inspired accessories. She sells them on Saturday mornings in Central Park to joggers and other passerby’s. Sara charges $5 per accessory (unit) and has unit variable costs (beads, wire rings, etc.) of $2. Her fixed costs consist of small pliers, a glue gun, etc., which cost her $90.

a. Calculate Mary Ann’s break-even units

b. Prepare a profit-volume graph for Mary Ann

c. Prepare a cost-volume-profit graph for Mary Ann

PLEASE SHOW ALL WORK/CALCULATIONS & EXPLAIN HOW YOU CREATED THE GRAPHS IN EXCEL.

Answer 1

A.

Selling Price	$5
Variable cost per unit	$2
Fixed Cost	$90
Breakeven Units	Fixed cost/(Selling Price-Variable Cost per unit)					90/(5-2)	30 Units

B.

Units	Sales	Fixed cOSt	Variable Costs	Total Cost	Profit/Loss
5	25	90	10	100	-75
10	50	90	20	110	-60
15	75	90	30	120	-45
20	100	90	40	130	-30
25	125	90	50	140	-15
30	150	90	60	150	0
35	175	90	70	160	15
40	200	90	80	170	30
45	225	90	90	180	45
50	250	90	100	190	60
55	275	90	110	200	75
60	300	90	120	210	90

Sales = Units*Selling Price

Variable costs = Units*Variable Cost per unit

Total Costs = Variable costs + Fixed costs

Profit/Loss = Sales - Total costs

Create the table by using above formulas

Then, click on insert > Pivot table > select the complete table in the range > click on insert pivot chart now > Drag units to Axis fields and profit/loss to values

An output chart will be created as below showing quantity and profits there to.

C.

Put units in axis field and all others in values field.

The area above the point where Red and Purple i.e. total cost and sales line cross each other gives Net profit and the point of intersection is breakeven units.

Green line is for fixed costs