Microsoft Office Tutorials and References
In Depth Information
One More Example
One More Example
On the sheet HHold Income in the Chapter 12 Excel file you can find the household
income of a sample of 1,000 customers and the amount of money they used to
purchase merchandise from an online appliances sales company. I would like to find
the relationship between the household income and purchases, so that I can try and
forecast, based on household income,
the amount a customer will spend on
appliances.
We can create a chart and find the trend line, the linear regression line, and the
R 2
of the relationship. In Figures 12.8 and 12.9 you can see the data and the scatter
chart I created.
The next step is to create the trend line. You can right-click any of the points,
then add the regression line and R 2 . I used the same procedure as before. The results
are shown in Figure 12.9: y
4.3532 and R 2
¼
0.0039x
¼
0.4895. (I formatted the
data points to be smaller on the chart.)
The slope of 0.0039 indicates that theoretically every additional dollar in
household income increases the potential purchasing of an individual of our popu-
lation by $0.0039. If you wanted to forecast the potential purchases of an individual
with a household income of $100,000 using the equation y
¼
0.0039x
4.3532 you
would forecast:
y
¼
0
:
0039
ð
100
;
000
Þ
4
:
3532
¼
$365
:
65
This result is not accurate since R 2 is low (R 2
¼
0.4895).
FIGURE 12.8 The Scatter Chart
Search JabSto ::




Custom Search