According to the output, 96.29 percent of the variance in total production costs is explained by the level of units produced-further evidence that the regression results will be useful in predicting total production costs. R-squared measures the percent of the variance in the dependent variable ( total production costs, in this example) explained by the independent variable ( units produced, in this example). Another way to assess the accuracy of the regression output is to review the R-squared statistic shown in cell B5. Thus the cost equation created from the regression analysis is likely to be useful in predicting total production costs.

Line Fit Plot and R-Squared: The plot shows that actual total production costs are very close to predicted total production costs calculated using the cost equation.

Total Production Costs = $43,276 + ($53.42 × Units Produced)

Cost Equation: The output shows that estimated fixed costs (shown as the Intercept coefficient in cell B17) total $43,276, and the estimated variable cost per unit (shown as the Units Produced coefficient in B18) is $53.42.

Here, we discuss key items shown in the regression output provided in step 3. Thus use one column (column A) to enter Total Production Costs data and another column (column B) to enter Units Produced data. The monthly data in Table 5.4 “Monthly Production Costs for Bikes Unlimited” includes Total Production Costs and Units Produced. Using a new Excel spreadsheet, enter the data points in two columns. Look for the Load the Analysis ToolPak option and follow the instructions given. If Data Analysis does not appear, go to the help button (denoted as a question mark in the upper right-hand corner of the screen) and type Analysis ToolPak. If Data Analysis appears, you are ready to perform regression analysis. Go to the Data tab on the top menu bar and look for Data Analysis. Confirm that the Data Analysis package is installed.

We now describe the steps to be performed in Excel to get this equation. The resulting equation to estimate production costs is Y = $43,276 + $53.42X.

Data analysis excel regression