We get a 0.64 correlation coefficient between volume of units and cost of production. Next, we can plot the data on a scatter plot to see if it looks linear.Īs the data seems a bit dispersed, let us calculate it’s correlation. The data points show us the unit volume of each batch and the corresponding production costs. We have the following data on the costs for producing the last ten batches of a product. Let us take a look at an example to see the Least-Squares Regression Model in action. If we look at a graphical representation of the linear cost function, a is what we call the y-intercept of the line and equals the approximate Fixed Costs at any activity level, and b is the slope of the line and represents the Variable Costs per unit. To calculate those we sum the variables in all observable data points and divide them by the number of data points, or we derive a simple average:Ĭhanging the means in the formula above with those formulas, we get an extended formula for the Fixed Costs: We use this formula to calculate the Variable Costs when we apply the Least-Squares Method:Īfter calculating the Variable Costs (b) per unit, we can then compute the Fixed Costs via the formula:
Using the normal equations and the process of elimination we can derive a formula for b. Looking into differential calculus, we get the following normal equations: This is because the technique uses the squares of the variables, which increases the impact of outliers. We need to be careful with outliers when applying the Least-Squares method, as it is sensitive to strange values pulling the line towards them. We are trying to determine the line that is closest to all observations at the same time. The Least Squares model aims to define the line that minimizes the sum of the squared errors. In statistics, the lower error means better explanatory power of the regression model. The idea behind the calculation is to minimize the sum of the squares of the vertical distances (errors) between data points and the cost function.
#Least squares linear regression excel equations series
Least-Squares Regression calculates a line of best fit to a set of data pairs, i.e., a series of activity levels and corresponding total costs. The function can then be used to forecast costs at different activity levels, as part of the budgeting process or to support decision-making processes. The Least-Squares regression model is a statistical technique that may be used to estimate a linear total cost function for a mixed cost, based on past cost data. Units are the units of measurement for the activity.VC is the variable cost associated with each additional unit of activity.FC represents the Fixed Costs that do not change depending on activity unit volume changes.When looking into costs, we can present the formula like this: However, most people consider the Least-Squares Method more accurate, as it computes Fixed and Variable Costs mathematically. Like the High-Low Method and other methods, the Least-Squares Method follows the same simple linear cost function:
Linear regression is considered the most accurate method in segregating costs into Fixed and Variable components.