To help budget future costs, you need to separate your fixed costs and variable costs from a total cost formula. The formula for total costs can be expressed as:

Total Costs = Fixed Costs + (Variable Cost per Unit × Cost Driver Activity)

You can use the linear regression function in Microsoft Office Excel 2003 to estimate these fixed and variable costs and develop your cost formula. Linear regression is a statistical technique that fits a line to a set of data points to show the relationship between two variables. One variable is a dependent variable. Its value is influenced by the second variable, known as the independent variable.

When there is close correlation between two variables, the linear regression tool can also help you understand how costs will change for a given forecasted activity level. Understanding this cost behavior will help you create a more accurate budget.

Follow these steps to run the linear regression function in Excel:

Step 1: Enter data and set up software

1. Open a new worksheet in Excel. You can enter your own data on the worksheet or use the data in the example that follows.
2. Create headings for the worksheet. For example, to evaluate the cost behavior of a manufacturing company, label columns Month, Total Production Costs, and Units of Output.
3. In the Total Production Costs column, enter the costs you want to evaluate. Total production costs represent the dependent variable.
4. In the Units of Output column, enter the expected cost driver of your production costs. Units of output represent the independent variable.

   

Step 2: Run the linear regression function

1. On the Tools menu, click Data Analysis. Click Regression, and then click OK.

    Note   If the Data Analysis command is not available, click Add-Ins on the Tools menu, select the Analysis ToolPak check box, and then click OK. You may see a message asking whether you want to install the ToolPak. Click Yes and follow the instructions. During installation, you may be asked to insert the original disk that came with your Microsoft Office System software.

   

2. In the Regression dialog box, make sure that the insertion point is in the Input Y Range box, and then in your worksheet, highlight the Total Production Cost column, including the column heading.
3. Place your insertion point in the Input X Range box, and then in your worksheet, highlight the Units of Output column, including the column heading.
4. Select the Labels check box.
5. Under Output options, click New Worksheet Ply: and type Regression Output in the text box.
6. Under Residuals, select the Line Fit Plots check box, and click OK to run the regression analysis.

   

7. Your new worksheet with the linear regression output should look something like the following:

   

 Note   To adjust column widths to see the entire regression output, select a column. On the Format menu, point to Column, and then click AutoFit Selection. If the scatter graph appears small, click the chart. Place your cursor on a corner of the chart and drag to enlarge the chart.

Step 3: Interpret the Regression Output

In the linear regression output, the Intercept coefficient is your estimate of fixed costs. The Units of Output coefficient is your variable costs per unit.

The accuracy of these fixed and variable cost estimates depends on the relationship between your production costs variable and units of output variable. In general, a strong correlation between the two variables results in a more accurate estimate of future costs.

Using the linear regression output, you have a few ways to evaluate the relationship between your variables:

• R Square value   In the Regression Statistics box in the regression output, the R Square value indicates how much of the variation in the dependent variable (Production Costs) can be explained by the independent variable (Units of Output). In this case, the R Square value is 0.9544, meaning that 95.44% of the dependent variable variation can be explained by the independent variable. This shows a strong correlation between the two variables.

  

• Scatter graph   The scatter graph in the output data is a statistical diagram that shows the regression line along with the data points. If the data points are close to the regression line, there is a good correlation between the independent and dependent variables (the R Square value mentioned earlier gives a quantitative measure of this correlation). If the regression line and data points appear far apart, there is a poor correlation between your two variables, and you may need to find another independent variable or conduct additional analysis.

  

• Residuals   The residuals in the Residual Output section show how each data point differs from the predicted point of the regression line. The smaller the residual value, the better your prediction.

  

You may want to refer to a basic statistics book to learn more about linear regression and how to interpret the output, because there are many nuances.

Step 4: Use your regression output to help with budgeting activities

Assuming you have a strong correlation between your two variables, you now have enough information to estimate your future costs. To continue the example of a manufacturing company, the formula for total production costs can be expressed as follows:

Total Production Costs = Fixed Costs + (Variable Costs per Unit × Cost Driver Activity)

Use the rounded values of the Intercept coefficient ($12,320) and the Units of Output coefficient ($20.96) from the regression output to estimate your cost behavior:

Total Production Costs = $12,320 + ($20.96 × Cost Driver Activity)

This formula can now be used to estimate future costs during your budgeting process. For example, let's say that you expect to produce 2,500 units next month. This number is your cost driver activity. Your expected production costs would be as follows:

Total Production Costs = $12,320 + ($20.96 × 2,500) = $64,720

Use the linear regression function in Excel to help you better segregate fixed and variable costs. This Excel feature helps you develop an understanding of cost behavior and predict accurate future costs for budgeting and planning purposes.