extra-credit-header-2018

Performing a simple linear regression in Excel

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

When teaching cost behavior in a managerial or cost accounting course, we explain that there are various methods a company can use to estimate its fixed and variable costs, including regression analysis. The next time you cover this topic, consider teaching students how to perform a simple linear regression analysis in Excel.  Below is an example screenshot illustrating 12 months of cost data for a restaurant.  

ec1-regression-analysis-example

To perform a simple linear regression to estimate the restaurant's fixed and variable costs, perform the following steps.  

Step 1: Click on the Data tab, and then click Data Analysis (in the Analysis group). (Note: If you don't have the Data Analysis option, you will need to install the Analysis ToolPak add-in. See our previous article on how to do this.)

ec2-data-analysis

Step 2: Select Regression and then click OK.

ec3-regression

Step 3: In the Regression dialog box that pops up, you must identify your Y variable, which is the dependent variable. In this example the dependent variable is the "total costs" column. Select the data in this column.

ec4-regression-input-y-range

Step 4: You must also identify your X variable, which is your independent variable. In this example the independent variable is the "# of meals" column. Select the data in this column, then click OK

ec5-regression-input-x-range

The default option is for the regression results to be inserted into a new worksheet. You will see the results in a different place if you have selected a different output option. We chose to have the regression results inserted into Sheet2 of our workbook, and the results are below:  

ec6-summary-output

Now that you have the regression results, you can discuss with the students the key pieces of information being displayed, including the coefficients (the intercept representing the fixed costs, and the X variable 1 representing the variable costs) and how to interpret the R square and adjusted R square values.

The next time you teach cost behavior, consider expanding your students' Excel skills by teaching them how to perform a simple linear regression, one of the many options within the Data Analysis function.

Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent. Ohio; Jennifer Cainas, CPA, DBA, is a clinical professor at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.

SPONSORED REPORT

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.