Ginn Tips on Activity 5.3
Dr. Ginn’s Tips for Doing Problem 7-42 1, 3
A. Please check the data file in the questions and answers discussion forum. Make sure you are
using the right data to solve the problem.
B. Consider the fundamental concepts you are expected to demonstrate. Problem 7-42 is on cost
volume profit analysis. It is about solving for the breakeven point. Breakeven charts graphically
represent operating leverage. The higher the fixed costs are, the higher the operating leverage.
Firms with high operating leverage show a steeper decline in losses as the volume drops below
the breakeven point and steeper increase in profits after the volume passes the breakeven
point.
C. Find a model in the chapter that provides an example of breakeven analysis. There are two kinds
of breakeven charts. I prefer the ones that have a line for fixed costs, variable costs, total costs,
and total revenue. These illustrate the concept of operating leverage.
D. Lay out the problem. Your task is to look at all the information given in the text and transfer it
for analysis.
E. Use Excel. Graphical Solution.
To solve the problem graphically, first open an Excel file and create a worksheet labeled “Data”.
Next, transcribe the information given in the text to the Data worksheet.
It is always good practice to develop worksheets that are interactive, so create another
worksheet and copy and paste all the information from the data worksheet onto the
“Interactive” worksheet.
Next, identify the cells that are a function of changes in volume, color them for easy reference,
and construct formulae in the necessary cells to make the worksheet interactive for purposes of
pro forma planning. Sales will be a function of volume multiplied by price, so create a volume
cell and a price cell. The sales cell will be calculated by a formula in the cell that refers to volume
and price. Similarly, for calculation of variable costs, use the same volume cell but create a
variable cost per unit cell. Variable costs will be calculated by a formula in the cell that refers to
cost per unit and volume. This interactive worksheet will help clarify the cells that change due to
volume.
For the graphical solution, you need the data to make a graph or chart. Open a new worksheet
and label it “Graphs.” Construct a row that gives you the data you will need to make a graph.
You will need lines for volume, net income, sales revenue, variable expenses, fixed expenses,
and total expenses.
You can generate this data fairly easily by establishing a column of volumes. After you have
entered two or three cells to establish the pattern of numbers, you can extend the column using
fill handle.
If the first row is constructed using an algebraic formula to determine cell values. You can
extend all the values from the first row downward using fill handle. Note that fixed costs are a
constant and do not change. However, you need to generate a column of fixed expenses so that
you can graph it.
Select the relevant columns for your chart or charts.
Go to the insert tab on the menu and choose the desired chart option.
Note that there are two formats for CVP charts, so make a profit volume graph on one
worksheet tab and do an alternate format graph with variable expenses, fixed expenses, total
expenses, and sales revenue on another worksheet tab.
Algebraic Solution. To check the accuracy of your graph. Take the formula from the first row and
calculate the breakeven using “goal seek.” Go to Data/Data Tools/What If Analysis/Goal Seek.
Set the Net Income to the desired level and see what volume is required. The goal is net income
and volume is what you change. Compare the breakeven point derived from the Goal Seek to
the breakeven points in each of your charts. If the all come out the same, then you can be
confident that you know the breakeven point.
F. Complete the analysis even if you have doubts.
G. Take a break and allow your brain to synthesize.
H. Note that the text provides a check figure of $8,000,000 for the breakeven point. Review and
amend your work until you get these results.
To solve any accounting problems I recommend that you first lay out the data given.
Kelly Kettle Inc.
Sales Volume
Sales
Operating Expenses
Variable Expenses
Fixed Expenses
Total Expenses
Net Income
revenue per unit
variable cost per unit
contribution margin per unit
breakeven point in units
safety margin in units
safety margin in dollars
$
$
$
$
$
$
$
160,000
16,000,000
$
$
14,400,000
1,600,000
6,400,000
8,000,000
100
40
60
133333.3333
26,667
2,666,667
See how I coded the colored cells to accept changes in volume.
When the data are created with formulae,
you can enter changes in volume and the results will change.
You can do a sensitivity analysis by subjectively determining
an expected volume, a high volume, and a low volume
You can do a very sophisticated analysis by combining Monte Carlo simulation with Excel.
http://office.microsoft.com/en-us/excel-help/introduction-to-monte-carlo-simulation-HA010282777.aspx
Kelly Kettle Inc.
Sales Volume
Sales
Operating Expenses
Variable Expenses
Fixed Expenses
Total Expenses
Net Income
revenue per unit
variable cost per unit
contribution margin per unit
contribution margin percentage
breakeven point in units
safety margin in units
safety margin in dollars
160,000
$ 16,000,000
$ 6,400,000
$ 8,000,000
$ 14,400,000
$ 1,600,000
$
$
$
100
40
60
60%
133,333
26,667
$ 2,666,667
Kelly Kettle Inc.
Sales Volume
Sales
Operating Expenses
Variable Expenses
Fixed Expenses
Total Expenses
Net Income
revenue per unit
variable cost per unit
contribution margin per unit
contribution margin percentage
breakeven point in units
safety margin in units
safety margin in dollars
160,000
$ 16,000,000
$
$
6,400,000
8,000,000
$ 14,400,000
$ 1,600,000
$
$
$
$
100
40
60
60%
133,333
26,667
2,666,667
Put the data in a formula.
Extend down at different volumes to have data to graph.
Sales Volume Net Income
Variable Expenses
Fixed Expenses
0
10000
20000
30000
40000
50000
60000
70000
80000
90000
100000
110000
120000
130000
140000
150000
160000
170000
180000
190000
200000
210000
220000
230000
240000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
(8,000,000)
(7,400,000)
(6,800,000)
(6,200,000)
(5,600,000)
(5,000,000)
(4,400,000)
(3,800,000)
(3,200,000)
(2,600,000)
(2,000,000)
(1,400,000)
(800,000)
(200,000)
400,000
1,000,000
1,600,000
2,200,000
2,800,000
3,400,000
4,000,000
4,600,000
5,200,000
5,800,000
6,400,000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
400,000
800,000
1,200,000
1,600,000
2,000,000
2,400,000
2,800,000
3,200,000
3,600,000
4,000,000
4,400,000
4,800,000
5,200,000
5,600,000
6,000,000
6,400,000
6,800,000
7,200,000
7,600,000
8,000,000
8,400,000
8,800,000
9,200,000
9,600,000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
8,000,000
Sales Revenue
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
1,000,000
2,000,000
3,000,000
4,000,000
5,000,000
6,000,000
7,000,000
8,000,000
9,000,000
10,000,000
11,000,000
12,000,000
13,000,000
14,000,000
15,000,000
16,000,000
17,000,000
18,000,000
19,000,000
20,000,000
21,000,000
22,000,000
23,000,000
24,000,000
Total Expenses
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
8,000,000
8,400,000
8,800,000
9,200,000
9,600,000
10,000,000
10,400,000
10,800,000
11,200,000
11,600,000
12,000,000
12,400,000
12,800,000
13,200,000
13,600,000
14,000,000
14,400,000
14,800,000
15,200,000
15,600,000
16,000,000
16,400,000
16,800,000
17,200,000
17,600,000
Kelly Kettle Inc.
You can solve by setting up the algebraic formula and using Goal Seek
Go to Data/Data Tools/What If Analysis/Goal Seek.
Set the Net Income to the desired level and see what volume is required.
The goal is net income and volume is what you change.
Net Income
Kelly Kettle Inc.
Sales Volume
Sales
Operating Expenses
Variable Expenses
Fixed Expenses
Total Expenses
Net Income
revenue per unit
variable cost per unit
contribution margin per unit
contribution margin percentage
breakeven point in units
safety margin in units
safety margin in dollars
$
$
$
$
$
$
$
160,000
16,000,000
$
$
14,400,000
1,600,000
6,400,000
8,000,000
100
40
60
60%
133,333
26,667
2,666,667
Goal Seek
Sales Price
$
$
Sales Volume
100.00
100.00
Sales Revenue
Variable Expenses
Variable Cost Per Unit
Fixed Expenses Total Expenses Net income
0 $
$
$
40 $
8,000,000 $
8,000,000 $
(8,000,000)
133333.3333 $ 13,333,333 $
5,333,333 $
40 $
8,000,000 $ 13,333,333 $
-
$30,000,000
$25,000,000
$20,000,000
Variable Expenses
Fixed Expenses
$15,000,000
Sales Revenue
$10,000,000
Total Expenses
$5,000,000
$1 2 3 4 5 6 7 8 9 10111213141516171819202122232425
8000000
6000000
4000000
2000000
0
-2000000
-4000000
-6000000
-8000000
-10000000
Sales Volume
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Net Income
Purchase answer to see full
attachment