Appendix 2A: Least-Squares Regression Computations

Least-Squares Regression ComputationsAppendix 2ALearning Objective 8Analyze a mixed cost using a scattergraph plot and the least-squares regression method.The Data SetMatrix, Inc. wants to know its average fixed cost and variable cost per meals served. Using the data to the right, let’s see how to do a Scattergraph plot using Microsoft Excel.Scattergraph PlotRight click on any of the grid values and enter the text as shown. The X axis should read “Units (Meals), and the Y axis should read Total Cost.Scattergraph PlotRight click on anydata point in the scattergraph plot and selecting “Add Trendline” from the graph submenu. The relationship appears to be linear, so we can apply the regression model. Simple Regression Using ExcelYou will need three pieces of information from your regression analysis:Estimated Variable Cost Per Unit (line slope)Estimated Fixed Costs (line intercept)Goodness of fit, or R2To get these three pieces information we will need to use three Excel functions in the Statistical Group of special functions.SLOPE, INTERCEPT, and RSQSimple Regression Using ExcelPlace your cursor in cell F4 and press the = key. Click on the pull down menu and scroll down to “Statistical” and scroll until you see “Slope.”Simple Regression Using ExcelFor “Known_y’s” highlight cells D4 through D19, and for “Known_x’s” highlight cells C4 through C19. Notice the Slope is calculated at 2.7684.Simple Regression Using ExcelPlace your cursor in cell F5 and press the = key. Click on the pull down menu and scroll down to “Statistical” and scroll until you see “Intercept.”1. In the Known_y’s box, enter D4:D19 for the range.2. In the Known_x’s box, enter C4:C19 for the range. Simple Regression Using Excel Here is the estimate of the fixed costs.Finally, we will determine the “goodness of fit”, or R2, by using the RSQ function. Move your cursor to Cell F6.Simple Regression Using Excel1. In the Known_y’s box, enter D4:D19 for the range.2. In the Known_x’s box, enter C4:C19 for the range. Simple Regression Using ExcelHere is the estimate of R2.Simple Regression Using ExcelNotice that Excel depicts its least-squares regression equation as Y = bX + a. We can easily rearrange with equation.End of Appendix 2A