Excel Regression Assign

Acct 2220 Zeigler: P2-27A - Using Excel for Regression Analysis (Pg 89)

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 estimationas 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 createCost Formulasthat help management predict future costs. Inthis 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, openthe postedExcel worksheet and use the data from P2-27A in adjacent columns (see format on reverse page). Add labelsso 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 checkboxes as is (i.e. default). Next, enter a cell addressin the dialog box where you want your output to be placed (“output range”) on your worksheet. Click OK to run the regression and review the output results. From the output, create a cost formula for predicting future costs in a Y=a+b(X) format (see above). How does this compare with the predictionsyou created with the High-Low and Scattergraph methods in the original P2-27A problem? Review the regression discussion in the text for an interpretation ofIntercept, 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. Add your name and class period (after adding a text box) anywhere on your sheet and print for collection.

See the suggested Excel format on the next page

SUGGESTED WORKSHEET FORMAT (Prepare in Excel)

Quinton Woodcrafting Company(QWC) - Predicting future cabinetry costs