Use Statistical Regression to Estimate Cost Behavior
Statistical regression allows you to apply basic statistical techniques to estimate cost behavior. Don’t panic! Excel (or a statistical analysis package) can quickly figure this information out for you.
Before starting, make sure you’ve installed the Microsoft Office Excel Analysis ToolPak. To confirm whether you already have it, click on Data and look for an item in the drop-down menu that says Data Analysis.
If you don’t see this item, you need to install the ToolPak. Go to Excel Options and click on Add-Ins. From here, go to the Manage box and click on Excel Add-ins and then Go. From the Add-Ins Available box, click on the Analysis ToolPak check box and then click on OK.
Enter the data into Excel.
Create a table of data in Excel, listing each month’s production activity level and total cost.
Run a regression analysis in Excel.
Run a regression on the data in Excel by following these steps:
Click on Data.
Click on Data Analysis and then Regression; then click on OK.
Under Input Y Range, enter the range for the total cost data, including the heading.
Under Input X Range, enter the production data, including the heading.
Click on the box for Labels.
Select an output range on your spreadsheet.
Click on OK.
When regressing total cost on a cost driver, the total cost data always goes into the Input Y Range, and the cost driver data always goes into the Input X Range.
From all the information shown in the output, you really only need two numbers. In the bottom table, look at the column marked Coefficients. The number labeled Intercept ($39,739) is a statistical estimate of the fixed cost. The number labeled Production ($66.69) gives you a statistical estimate of the variable cost per unit.
Based on these regression results, you can determine that making 1,000 units would create total variable costs of $66,690 (1,000 units x $66.69 per unit). Total fixed costs would equal $39,739, so total costs would be $106,429:
Total cost = (Variable cost per unit x Units produced) + Total fixed cost
Total cost = ($66,690 x 1,000 units) + $39,739 = $106,429
Statistical regression analysis provides useful information to judge the reliability of your estimates. An Adjusted R-square close to 1 (the one in the figure is approximately 0.99498) indicates that the model fits the data. Low P-values of the coefficients (here, 1.713 x 10–10 and 4.861 x 10–13) indicate that the model has high statistical significance. In other words, this model looks pretty accurate.