Zeigler: Using Excel for Regression Analysis
When analyzing the costs of a business, certain costs may include both fixed and variable components. This type of cost is known as a "mixed" cost. If we do not know the breakdown of these cost components, we can analyze the data and estimate (i.e. predict) what portion of a mixed cost is fixed and what portion is variable. Problem P2-27A will be used to examine the three approaches to cost estimation as presented in Chapter 2. In addition to the original P2-27A assign using the "Scattergraph" and "High-Low" methods, we have another choice. We can also perform Regression Analysis (aka: "Least Squares" Regression) on the same data with Excel. Regression is a statistical averaging technique that considers historical data observations and can help management predict future cost expectations. See page 74 of our text.
Per pg 74, our focus will be on the interpretation of the following summary output items:
1) Intercept: Represents the estimated fixed cost component of our cost formula equation.
2) X Variable 1: Represents the estimated variable cost component of our cost formula.
3) R Square (Coefficient of Determination): A statistical measure indicating the "correlation" (closeness of the relationship) of the data being analyzed. How accurate is the historical data in predicting the future? Does the data support a reasonable, consistent pattern of behavior? Do changes in "X" (activity) really cause (explain) changes in "Y" (total cost)?
We can use regression to create a Cost Formula that can help management predict future costs. In this case, we wish to predict the fixed and variable components of a series of total (mixed) cost observations. This formula can be written as:
Y (total predicted cost) = a (some fixed cost) + b (some variable cost per unit)* X (some activity level). Therefore, our cost prediction formula would be: Y = a + b(X) See page 74 footnote
B) Step by Step approach to complete Regression work in Excel:
1) Before starting, read pages 70-75 for an understanding of the topic at hand. Then, review the original assignment, on page 89, before proceeding.
Next, open the Excel worksheet (see Canvas) and use the data from P2-27A in adjacent columns (see format on reverse page). Add labels so others can understand your work.
Once you have completed this, click the "Data" tab on the top toolbar, then find "Data Analysis". Choose "Regression" to start the process.
2) Perform Regression: Now, enter the X and Y cell-ranges of your numerical data (only) into the regression dialog box. Leave all other check boxes as is (i.e. default). Next, enter a cell address in the dialog box where you want your output to be placed ("output range") on your worksheet. Click OK to run the regression. From the output, create a cost formula for predicting future costs in a Y=a+b(X) format. How does this compare with the predictions you created with the High-Low and Scattergraph methods in the original P2-27A problem? Add a "text box" and include your formulas for High-Low and Regression. Review the regression interpretation discussion in the text for Intercept, X Variable 1 and "R Square"
3) Next, let's create a graph of the data. First, highlight the X and Y range of data (include the column headers as well). Then, click on the "Insert" tab and choose "Scatter" chart. Choose the basic (first) scatter chart without lines. Under "Chart Tools", choose the "Design" drop-down menu from the toolbar. Notice the many pre-created designs that you could use. Next, select "Layout" and choose "Trendline". Add a "Linear Trendline". This line represents the basis for your Y = A + b(X) prediction equation (i.e. the regression line). Last, click on the trendline you created and then right-mouse click to obtain the "Format Trendline" dialog box. Select the two check boxes at the bottom relating to "Display Equation" and "R-squared" on the chart. Both should now show on the chart.
4) Widen your chart and notice how some points are fairly far from the trendline. These are most likely "Outlier" data points and could possibly be eliminated to get a better prediction (and therefore, R-Squared) of the relationship between X (level of activity) causing Y (total cost).
5) Clean up the chart, making sure to include the original data, regression output and your chart all on a SINGLE worksheet (see suggested format next page). Add your name and class period (after adding a "text box" on your worksheet) and upload the file by the due date.
See the suggested Excel format on the next page
Suggested Worksheet Format (Prepare in Excel - See Canvas for file)
Quinton Woodcrafting Company (QWC) - Predicting future cabinetry costs
Adding Regression to Problem
|
|
Number of Cabinets
|
Total
|
|
|
Produced
|
Cost
|
|
Month
|
(X)
|
(Y)
|
|
Jan
|
800
|
$21,000
|
|
Feb
|
|
|
|
Mar
|
|
|
Apr
|
|
|
|
May
|
|
|
|
Jun
|
|
|
|
Jul
|
|
|
|
Aug
|
|
|
|
Sep
|
|
|
|
Oct
|
|
|
|
Nov
|
|
|
|
Dec
|
|
|
|
|
|
|
B) OUTPUT: PLACE REGRESSION OUTPUT BELOW
C) YOUR CHART: Add labels, etc. and be sure it makes sense to the reader without your need to interpret it.