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