18 — Methods of Cost Behavior Analysis

18 — Methods of Cost Behavior Analysis


[MUSIC] I’m Larry Walther, this is Chapter 18 of principlesofaccounting.com. In the previous module we considered
the definitions of fixed cost and variable cost. And some of the implications for a business in terms of
understanding those costs. In this module, however,
we are going to consider. How we would determine the fixed and
variable nature of cost for a particular business. In other words,
we’re going to look at cost behavior. Now, some costs are not strictly fixed or strictly variable,
they are mixed in nature. Let me illustrate the concept of a mixed
cost with a very simple and clear example. Butler’s Car Wash has a contract for its water supply that provides a flat
monthly fee of $1,000 per month. Plus $3 for each thousand
gallons of water that are used. And so if we were to look at
this in spreadsheet form. In column A I’m showing the gallons that
could be used in a particular month. Column B is the variable costs
that would be incurred, for example on row 9 we can see $2,100. That’s 700,000 gallons, so 700 times $3 or $2,100 would be the variable
cost at that level of usage. The fixed cost however is
$1000 at each level of usage. And we have our total cost,
the accumulation of columns B and C here. And I’ve also plotted that to show
the total water cost starting at $1000, even if we use 0 gallons. And then it goes up in
a linear fashion for the variable cost as the usage continues
to increase along the horizontal axis. Oftentimes, a cost is not clearly fixed or variable in nature as it was for
Butler’s Car Wash. So we may need to do an analysis
to separate the total cost into presumed fixed and
variable components. One way for
doing this is the high-low method. It involves several steps. The first of which is to find
the highest level of activity and the lowest level of activity. And identify the cost for
each of those levels of activity. And see what the difference is
between the high cost and low cost. And see what the difference is in
the volume between the high level and the low level. And based on that we would
calculate a variable cost per unit. In other words, we have so
much change in volume and so much change in cost that
suggests the variability rate. The remaining amount in each
case would be the fixed cost. Recognize that this method can be
very imprecise if we have outliers or rough data points. In other words, if our high
point is way out of the norm or our low point is way out of the norm. Then our calculations could be suspect. So let’s see how this would work. Here I’ve got an example. We’ve identified our usage,
our highest level was 850,000 gallons. And our lowest level was 340,000 gallons. A difference of 510,000 gallons. We compare that to our costs. The highest cost was $3,550,
the lowest cost, $2,020, a difference of $1,530. So very simply,
we’ve got a range of 510,000 gallons and a cost range of $1,530. Based on that we can calculate
a variable cost per unit of $3. So we’re adopting an assumption that
our variable cost is $3 per unit. Notice our total cost at the high level
is 3,550, and at the low level, 2,020. Of that, our variable cost would be 2,550,
that is, 850 times 3. And our low cost would be 1,020,
that is, 340 times 3. The difference of the remaining
amount is fixed cost. And it’s shown to be $1,000
in each of the two columns. So here we would have identified
a high and a low point. To discern that our fixed cost was $1,000
and our variable cost was $3 per unit. A more statistically valid method for cost behavior analysis is
the method of least squares. It’s based on regression analysis. Recognize that a straight line can be
defined by the formula Y=a + bx. Where a is the intercept which would
infer our fixed cost component. b is the slope of the line which
infers our variable cost component. And x is the position on the X axis,
that is, the volume level. So the least squares method then defines a line that fits through
a set of points on a graph. Where the cumulative sum of the squared
distances between the points and the line is minimized. Spreadsheet programs can be used for
this analysis. This contrasts with
a scatter graph method. An alternative method where points
are simply plotted on a graph. And a line is drawn through the graph
to approximate these values. And so let’s look at how the least squares
method would work in this particular case. We have monthly data, we have units,
and we have total costs. So, that’s our data set. We’ve run a regression analysis
using the spreadsheets software. To determine that intercept is 138,533 and
the slope is 10.34. This suggests that for
this cost for this business. That the fixed cost is $138,000 and
the variable cost is $10 per unit. Now these values were derived from
formulas included in the spreadsheet. For example, in cell B17,
I calculated the R Square value. Which I’ll comment more on
in just a moment, as 0.798. The formulation in the spreadsheet, rRSQ(. For the range of data C2 to C13
regressed on the data B2 to B13. So that would be the formula for R Square. We would have a similar
formulation within cell B15 and B16 for our intercept and slope terms. So let’s look closer at the graph here. If we notice the point
that has an arrow drawn. It appears that that’s about 95,000
units on the horizontal axis. And about $1,500,000 on the vertical axis. That equates to the data point for
December. And if we had just done
a scatter graph technique. We would have simply drawn
the dots on the graph. And then drawn the line through the graph. And see where it hits the y-axis and
what the slope is as the line moves out. And we could approximate the formula for
the line there. But what regression analysis does is it optimizes that line
through those data points. Remember, it’s called regression
analysis or least squares regression. And so
if we look at this particular point. And we looked at each
of the similar points. And found the horizontal distance
between the point and the line. The sum of the squared distances would
be minimized with regression analysis. In other words, that’s deemed to be the
best fit line through those data points. Let’s finally close by thinking
about that R squared value. The R squared value of the least squares
line is a statistical calculation. That characterizes how well the line
fits to a particular set of data. In our example,
we saw an R squared value of 0.798. That simply means that
almost 80% of the variation can be explained by volume fluctuations. The outliers are fairly small, or in other words, the points are fairly
tightly clustered to the line. As a generalization,
the higher the R squared is, the closer it is to one,
the better fit is the line to the dataset. If the R squared value was exactly one, that would mean there is no variation
between the dots and the line. The line would pass exactly through
each of the dots precisely. If the R squared value was very small,
0.2. That would suggest that the line is
not explaining a lot of the variation. There is a lot of deviation
between the points and the line that R squared
finds as the best fit line. [MUSIC]

3 Comments

  1. randgoon456 says:

    How did you get the slope to visually start from the axis and forecast beyond data set?

  2. Larry Walther says:

    Some spreadsheet programs have a "trendline" feature in the chart options. "Forecast" trendline options may allow you to extend the line forward or backward a desired number of periods. In newer versions of Microsoft Excel, these options are under Chart Tools -> Layout -> Analysis -> Trendline.

  3. M. Alomery says:

    thank you

Leave a Reply

Your email address will not be published. Required fields are marked *