Chapter 02A – Lecture Notes
2A-1
I. Appendix 2A: Least-Squares Regression Computations
(Slide #1 is a title slide)
Learning Objective 8: Analyze a mixed cost using a
scattergraph plot and the least-squares regression method.
a. The data set
i. Assume that you have the following data set and that
you wish to use Microsoft Excel to estimate the
variable and fixed cost components of your total meals
cost.
ii. The first step is to prepare a scattergraph plot to see if a
linear relationship exists between the number of meals
served and the total cost.
1. To prepare the plot, highlight the data in cells C4
through D19. From the Charts group within the
Insert tab, select the “Scatter” subgroup and then
click on the choice that has no lines connecting
the data points.
2. To insert labels for the X-axis and Y-axis, go to
the Layout tab in Excel. Then, within the Labels
group, select Axis titles.
3. Your scattergraph plot should appear as shown on
this slide.
iii. The second step is to determine the intercept a, the
slope b, and the R2. The intercept represents the
estimated fixed costs. The slope represents the
estimated variable cost per unit. The R2 tells what
percentage of the variation in the dependent variable
(cost) is explained by variation in the independent
variable (activity).
3
5
6
7
2
4
Chapter 02A – Lecture Notes
2A-2
1. Begin by right clicking on any data point in the
scattergraph plot. This should produce the screen
shown on this slide. Notice that under
“Trend/Regression Type” you should select
“linear.” Similarly, under “Trendline Name” you
should select “Automatic.”
2. Next to the word “Backward” you should input
the lowest value for the independent variable,
which in this example is 1,260 meals. Taking this
particular step instructs Excel to extend your fitted
line until it intersects the Y-axis.
3. Finally, you should check the two boxes at the
bottom of the screen that say “Display Equation
on chart” and “Display R–squared value on chart.”
Then click “Close.”
4. This will automatically insert a line within the
scattergraph plot that minimizes the sum of the
squared errors. It will also automatically insert the
estimated least-squares-regression equation and
R2 (which is approximately 93% in this example)
into the scattergraph plot.
iv. Notice that Excel depicts its least-squares regression
equation as Y = bX + a, or in this example Y = $2.768X
+ $2,618.72. This can easily be translated to the format
of Y= a + bX that is used in the textbook as follows: Y =
$2,618.72 + $2.768X.
10
9
8