The basic information that Charlie gave us is reproduced below. Open Microsoft Excel (or another spreadsheet) on your computer and recreate the table to the right, using the same cell coordinates (e.g., enter the sales price in cell C3, the material cost in C5 and so on.) All cells which represent dependent values (those that are calculated) should be cell referenced For example, cell C9 should be =sum(C5:C8). Similarly, C10 should be =B10*C3. This use of cell referencing is very important. One of the advantages of a computer spreadsheet is the ability to recalculate an entire worksheet quickly after changing one or more variables. if you have entered values (i.e., numbers) in cells like C9, your worksheet will not recalculate correctly when upstream values are changed..

After you get the basic data entered, set up the data table [below], again using the same cell coordinates as are used in the illustration. [Of course, there is nothing special about those cells, but using the same coordinates will help you to be sure everything is the same.] Be sure to use cell referencing throughout the table.

Checking the data table, we find that $1,200 is the dollar sales figure for 4 units. That is consistent with our previous analysis, and with the basic math: 4 units at $300 each is $1,200. Total costs are $520 [fixed] and $780 [variable; = 195 * 4], for a total of $1,200, QED!*

There are several things to notice about the data above. Note that the increases in costs are exactly as our understanding of cost behavior would have predicted. Note also that at very small volumes, Charlie is losing his shirt. However, at 4 units per month, there is neither a profit nor a loss, and at 5 units per month, Charlie can make $130 per month. What is the relationship between the incremental profit per chair and the contribution margin?

Answer

Now let's develop a graphical representation of Charlie's situation. After you get all the cells filled in, use the mouse to highlight the range D3:H14, then click on the chart wizard to create a scatter plot graph. [We use a scatter plot because when Excel creates a line graph, the origin and the zero point do not align correctly. ] You after you get the basic graph, you can click on each set of data points to add a line so that you graph looks like the one below.

Note that the break even point is at 4 units. At all volumes greater than 4 units, the revenue line is higher than the total cost line; below the breakeven point, the reverse is true.

When you are finished with this portion of the exercise, click "next."

*Quod erat demonstrandum, Latin for "what we were trying to demonstrate."

Copyright © 2004 Gerald M. Myers. All rights reserved. This site has been developed as aid to instructors and students in managerial accounting. The scenarios contained herein are not intended to reflect effective or ineffective handling of managerial situations. Any resemblance to existing organizations is purely coincidental.

Last modified: August 03, 2005