Skip to main content
Business LibreTexts

5.5: Appendix: Performing Regression Analysis with Excel

  • Page ID
    863
  • [ "article:topic" ]

    Skills to Develop

    • Perform regression analysis using Excel.

    Question: Regression analysis is often performed to estimate fixed and variable costs. Many different software packages have the capability of performing regression analysis, including Excel. This appendix provides a basic illustration of how to use Excel to perform regression analysis. Statistics courses cover this topic in more depth. How is regression analysis used to estimate fixed and variable costs?

    Answer:

    As noted in the chapter, regression analysis uses a series of mathematical equations to find the best possible fit of the line to the data points. For the purposes of this chapter, the end goal of regression analysis is to estimate fixed and variable costs, which are described in the equation form of \(\text{Y} = \mathcal{f} + \mathcal{v} \text{X}\). Recall that the following Excel output was provided earlier in the chapter based on the data presented in Table 5.4 for Bikes Unlimited.

      Coefficients
    y-intercept 43,276
    x variable 53.42

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

    Step 1. Confirm that the Data Analysis package is installed.

    Go to the Data tab on the top menu bar and look for Data Analysis. If Data Analysis appears, you are ready to perform regression analysis. 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. Look for the Load the Analysis ToolPak option and follow the instructions given.

    Step 2. Enter the data in the spreadsheet.

    Using a new Excel spreadsheet, enter the data points in two columns. The monthly data in Table 5.4 includes Total Production Costs and Units Produced. Thus use one column (column A) to enter Total Production Costs data and another column (column B) to enter Units Produced data.

    Figure 5.5.1.png

    Step 3. Run the regression analysis.

    Using the same spreadsheet set up in step 2, select Data, Data Analysis, and Regression. A box appears that requires the input of several items needed to perform regression. Input Y Range requires that you highlight the y-axis data, including the heading (cells B1 through B13 in the example shown in step 2). Input X Range requires that you highlight the x-axis data, including the heading (cells C1 through C13 in the example shown in step 2). Check the Labels box; this indicates that the top of each column has a heading (B1 and C1). Select New Workbook; this will put the regression results in a new workbook. Lastly, check the Line Fit Plots box, then select OK. The result is as follows (note that we made a few minor format changes to allow for a better presentation of the data).

    Figure 5.5.2.png

    Step 4. Analyze the output.

    Here, we discuss key items shown in the regression output provided in step 3.

    • 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. Thus the cost equation is: $$\text{Y} = \$ 43,276 + \$ 53.42 \text{X}$$or $$\text{Total Production Costs} = \$ 43,276 + (\$ 53.42 \times \text{Units Produced})$$
    • 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. Thus the cost equation created from the regression analysis is likely to be useful in predicting total production costs. Another way to assess the accuracy of the regression output is to review the R-squared statistic shown in cell B5. R-squared13 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). 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.

    The discussion of regression analysis in this chapter is meant to serve as an introduction to the topic. To further enhance your knowledge of regression analysis and to provide for a more thorough analysis of the data, you should pursue the topic in an introductory statistics course.

    KEY TAKEAWAY

    Software applications, such as Excel, can use regression analysis to estimate fixed and variable costs.

    • Once the data analysis package is installed, historical data are entered in the spreadsheet, and the regression analysis is run.
    • The resulting data are used to determine the cost equation, which includes estimated fixed and variable costs.

    The line fit plot and R-squared statistic are used to assess the usefulness of the cost equation in estimating costs.

    REVIEW PROBLEM 5.9

    Refer to the monthly production cost data for Alta Production, Inc., in Note 5.21 "Review Problem 5.5". Use the four steps of regression analysis described in this appendix to estimate total fixed costs and variable cost per unit. State your results in the equation form \(\text{Y} = \mathcal{f} + \mathcal{v} \text{X}\).

    Answer:

    Regression analysis performed using Excel results in the following output:

      Coefficients
    y-intercept 703
    x variable 1,442.97

    Thus the total cost equation is:

    $$\text{Y} = \$ 703 + \$ 1,442.97 \text{X}$$

    Definition

    1. Measures the percent of the variance in the dependent variable explained by the independent variable.