[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

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

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.

thank you