Linear regression is a method of estimating the portion of a cost that is variable and the portion that is fixed. This method models the relationship between an activity and the total cost by fitting a linear equation to the data. Unlike the high-low method which uses only two data points, linear regression uses all data points in constructing the cost equation, making it much superior to the high-low method. A linear regression generates information for a cost equation in the same form as the other methods of estimating costs: Y = VCx + TFC, where 'x' is the independent variable (the activity) and 'Y' is the total cost (dependent variable).
While there are several software programs that generate linear regressions, using Excel® is relatively easy and is a business tool frequently used by managers. It is also a business tool that is installed on the majority of home and business computers. As such, you will learn how to run (generate) a regression using Excel®.^{1}
Interpreting the Regression Output
While a number of statistical items are generated in the regression output, your primary interest is the components of the cost function found in the last section of the summary output. Linear regression output for a home moving company that packs and moves residents to new homes appears below.
Intercept is the y-intercept which is the estimate of total fixed costs for each period. X Variable 1 represents the estimated variable cost per unit, or the slope of the cost equation. The cost equation for the moving company is:
Y = 3,815.69x + 828,814
We read this formula as 'Total cost equals variable cost of $3,815.69 times the number of residences moved plus fixed costs of $828,814'. Always express unit costs (i.e., the unit variable cost) with two decimals, and total costs (fixed costs) with no decimals.
Is the Data 'Good'?
A scatter graph is often prepared prior to running a regression to pre-assess the relationship between two variables. A weak or nonexistent relationship between the activity and the total cost indicates that the linear regression output will not provide a useful cost equation. Assessing the quality of the cost equation (regardless of the cost estimation used) is beyond the scope of managerial accounting. As such, you will focus solely on generating a cost equation and how to use it to estimate future costs.
Data Analysis ToolPak
To use the linear regression tool in Excel, the Data Analysis Toolpak must be installed. To verify if it is installed, click Data from the Excel® menu. If you see the Data Analysis command in the Analysis group (far right), the Data Analysis Toolpak is already installed. If it is not installed, follow the directions in the Appendix at the end of this chapter to install it.
Walk Through Problem
Wilson Company provided the following information concerning the number of monthly service calls provided and the total cost incurred for its pest control operations for the each month during 2016:
Year | Number of Service Calls | Total Cost |
January | 1,040 | $33,600 |
February | 1,200 | 36,300 |
March | 1,260 | 37,800 |
April | 1,100 | 35,500 |
May | 1,220 | 36,600 |
June | 1,010 | 32,900 |
July | 1,190 | 36,200 |
August | 1,050 | 33,400 |
September | 1,210 | 37,700 |
October | 1,250 | 37,400 |
November | 1,060 | 33,800 |
December | 1,280 | 38,100 |
Run a linear regression using the regression tool in Excel®. Write the cost equation in standard form. Determine the estimated cost of providing 1,140 service calls for January, 2017.
Solution
Step 1: Open the Excel® program. Copy and paste the data for Wilson Company into columns A, B, and C beginning in row 1 of a blank worksheet.
Step 2: Because the regression output occupies 9 columns and 13 rows, to avoid placing the regression output on top of your data, select a location in the worksheet in which there are enough empty rows and columns to accommodate the output. Place your mouse pointer in cell A15 since that is an acceptable location.
Step 3: Select the Data ribbon menu, then Data Analysis command on the Analysis tab. A popup box will appear. Scroll down and select Regression. Click OK.
Step 4: The Regression wizard will be displayed. In the Input Y Range field, select all the values in the total cost column of the data table---cells C2 through C13. Do not select the total.
Step 5: In the Input X Range field, select all the activity values---cells B2 to B13 in the same manner as Step 4.
Step 6: In the Output Range field, select cell A15 so that the regression output will begin in that cell
Step 7: Verify you have no checks in all remaining boxes. Your wizard should be identical to the graphic below:
Step 8: Click OK to 'run' the regression. Verify your output appears as follows, noting that the column widths may differ based on your worksheet settings.
Step 9: Write the cost formula based on the regression output as:
Y = 19.08x + 13,720
The total cost is $13,720 plus $19.08 times the number of service calls. Always express unit costs (i.e., the variable cost) with two decimals, and total costs (fixed costs) with no decimals.
Step 10: The total cost expected if 1,140 service calls are made is:
Y = (19.0812465 x 1,140) + 13,720.2593 = $35,473
Appendix - How to Install the Data Analysis Tool Pak
Open Excel 2010. Choose the Data tab from the menu ribbon in Excel. If there is no Data Analysis item on the ribbon (look to the far right), follow the steps below to install.
Click the File menu option, and then click Options.
Click Add-Ins, and then in the Manage box (near the bottom), select Excel Add-ins.
Click Go.
In the Add-Ins available section, select the Analysis ToolPak check box, and then click OK. If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
You will be immediately returned to your original worksheet. Click the Data tab from the menu ribbon and you will see the Data Analysis option on the Analysis tab at the far right.
__________________
^{ 1}This chapter explains how to run a regression using Excel 2010. Directions for other versions of Excel may differ.
This page was last
edited on
Sunday January 18, 2015 04:18 PM
Website designed and maintained by
dtanner@unf.edu
Copyright - 1999-2015 University of North Florida. All rights
reserved