Cost Structure: Ideas for a Practical Calculation - Pt. 3

The statistical approach consists in the study of the so-called statistical regression , which in turn consists in determining, with particular techniques, a linear mathematical function that expresses in the best possible way the relationship between two variables, in our case the number of lamps produced and the Total Cost of the department.

This particular technique is the so-called least squares method, which is based on the fact that the best match with real data is obtained when the sum of the squares of the differences between observed data and theoretical data is minimized; which in other words means that it is necessary to find a function that minimizes the sum of the values ​​highlighted in column H of the previous figure (in the empirical method this sum was equal to 498,457).

To do this, it is advisable to create specific columns on which to perform intermediate calculations, for example by arranging the data as in columns E and F shown in the figure:

In fact, it can be easily demonstrated (with a series of steps that would be superfluous here!) that the slope b of the interpolating line (which obviously represents the unit Variable Cost CVu ), is given by the following ratio:

To the Numerator (column E):

which is nothing other than the sum – for all 20 measurements in the example – of the product of the waste of each of the 2 variables (number of pieces produced and total cost) by its own average value (189.1 is the average of the pieces produced and €3,997.18 is the average of the total costs).

In the Denominator (column F):

which is nothing other than the sum – for all 20 measurements in the example – of the squares of the deviations of the independent variable (the number of lamps produced) from its mean value, which as we have just seen is equal to 189.1 units.

The unit Variable Cost will therefore be equal to:

Once the slope of the line has been found (i.e. the CVu ), the intercept a , which will represent the Fixed Costs, according to the least squares method is given by:

Which in our case becomes:

The line of Total Costs of the lamps department, thus identified with the statistical method, will therefore be the following:

CT = €1,045.46 + €15.61 × Units produced

This is its graphic representation:

As can be seen from the graph, unlike the straight line found with the previous "empirical" method, this straight line does not pass through the minimum and maximum points; however, its adherence to the truth, i.e. its ability to represent the observed phenomenon (trend of the Total Cost as a function of the pieces produced), is greater than the previous approach, as shown by the comparison with the sum of the squares of the deviations which is reduced from 498,457 to 435,136 (see the following figure).

At this point, after this brief review of statistics (necessary to better understand what we are doing!), it is worth pointing out that the same result could easily be achieved in a few seconds with much fewer calculations, using the numerous statistical functions that Excel provides us, and which allow us to obtain all the information we need in just a few steps (just knowing the right formulas!)

We summarize them in a screenshot that includes all the various calculation methods, highlighting the Excel formulas used from time to time:

Summarizing the various statistical methods:

• method #1 is the most laborious one, which we have seen so far step by step;
• method #2 involves using the FORECAST function to be applied when the independent variable (no. lamps produced) is equal to 0: in this way the Fixed Costs are found; by carrying out the calculation with the independent variable equal to 1, and subtracting the Fixed Costs, the cost to produce 1 unit is obtained, i.e. the CVu ;
• method #3 involves using the TREND function; the reasoning is similar to the FORECAST function, only the syntax of the formula changes slightly;
• method #4 involves using the INTERCEPT and SLOPE functions, in order to find the Fixed Costs and the CVu respectively;
• method #5 involves using the LINEST function but without further statistics; in this way the function returns only the values ​​of the intercept (Fixed Costs) and the slope ( CVu ) of the interpolating line;
• Method #6 involves the use of the LINEST function but with various statistics calculated by Excel, some of which are too sophisticated for the purposes we are interested in; among the various statistics proposed, the one that presents a certain degree of interest is certainly the coefficient of determination R ² , which in the figure we see to be equal to 0.8038 and which we will talk about shortly.

There is actually also a method #7, which involves the calculation not through a function but through the use of a scatter graph , with activation of the so-called trend line , by accessing the appropriate menu of the graph elements, located on the right side of the graph itself, as shown in the figure:

Once the trend line is activated, it is possible to highlight both its formula and the measure of its reliability , always measured by the ^R2 coefficient , by checking the two boxes indicated below:

The result is the following graph (we had already seen it previously), easy to interpret and full of valuable information:

(continued in part 4 )