Knowee
Questions
Features
Study Tools

Average Family Income Rentals 62 705 41 525 27 309 45 498 50 623 47 425 44 314 28 203 30 465 41 540 47 605 62 690 Create an Excel spreadsheet and enter the data using income as the independent variable (predictor) and number of daily rentals as the dependent variable (criterion). (a) Use Excel’s 1⁄4 correl function to find the correlation between these two variables, and round off the result to two decimal places. (b) Create an XY scatterplot of these two sets of data such that: • Top title: RELATIONSHIP BETWEEN INCOME AND RENTALS/DAY • x-axis title: AVERAGE FAMILY INCOME ($000) • y-axis title: RENTALS (per day) • re-size the chart so that it is 8 columns wide and 25 rows long • move the chart below the table (c) Create the least-squares regression line for these data on the scatterplot. (d) Use Excel to run the regression statistics to find the equation for the least squares regression line for these data and display the results below the chart on your spreadsheet. Use number format (2 decimal places) for the correlation and for the coefficients Now, answer these questions using your Excel: (1) What is the y-intercept? (2) What is the slope of the line? (3) What is the regression equation for these data (use two decimal places for the y-intercept and the slope)? (4) Use the regression equation to predict the average number of daily rentals you would expect for a retail area that had an average family income of $50,000.

Question

Average Family Income Rentals 62 705 41 525 27 309 45 498 50 623 47 425 44 314 28 203 30 465 41 540 47 605 62 690

Create an Excel spreadsheet and enter the data using income as the independent variable (predictor) and number of daily rentals as the dependent variable (criterion).

(a) Use Excel’s 1⁄4 correl function to find the correlation between these two variables, and round off the result to two decimal places. (b) Create an XY scatterplot of these two sets of data such that: • Top title: RELATIONSHIP BETWEEN INCOME AND RENTALS/DAY • x-axis title: AVERAGE FAMILY INCOME (000)•y−axistitle:RENTALS(perday)•re−sizethechartsothatitis8columnswideand25rowslong•movethechartbelowthetable(c)Createtheleast−squaresregressionlineforthesedataonthescatterplot.(d)UseExceltoruntheregressionstatisticstofindtheequationfortheleastsquaresregressionlineforthesedataanddisplaytheresultsbelowthechartonyourspreadsheet.Usenumberformat(2decimalplaces)forthecorrelationandforthecoefficientsNow,answerthesequestionsusingyourExcel:(1)Whatisthey−intercept?(2)Whatistheslopeoftheline?(3)Whatistheregressionequationforthesedata(usetwodecimalplacesforthey−interceptandtheslope)?(4)Usetheregressionequationtopredicttheaveragenumberofdailyrentalsyouwouldexpectforaretailareathathadanaveragefamilyincomeof000) • y-axis title: RENTALS (per day) • re-size the chart so that it is 8 columns wide and 25 rows long • move the chart below the table (c) Create the least-squares regression line for these data on the scatterplot. (d) Use Excel to run the regression statistics to find the equation for the least squares regression line for these data and display the results below the chart on your spreadsheet. Use number format (2 decimal places) for the correlation and for the coefficients Now, answer these questions using your Excel: (1) What is the y-intercept? (2) What is the slope of the line? (3) What is the regression equation for these data (use two decimal places for the y-intercept and the slope)? (4) Use the regression equation to predict the average number of daily rentals you would expect for a retail area that had an average family income of 50,000.

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution

I'm sorry, but as a text-based AI, I'm unable to create an Excel spreadsheet or perform Excel functions. However, I can guide you on how to do it.

  1. Open Excel and create a new spreadsheet.
  2. In the first column, enter the values for "Average Family Income". In the second column, enter the values for "Rentals".
  3. To find the correlation between these two variables, you can use the CORREL function. In a new cell, type =CORREL(A2:A13, B2:B13), assuming your data is in cells A2 to A13 and B2 to B13. Round off the result to two decimal places.
  4. To create an XY scatterplot, select your data, go to the "Insert" tab, and choose "Scatter". Add the appropriate titles as instructed.
  5. To create the least-squares regression line, right-click on one of the data points in your scatterplot, choose "Add Trendline", and then select "Linear". Make sure "Display Equation on chart" is checked.
  6. To run the regression statistics, you can use the LINEST function. In a new cell, type =LINEST(B2:B13, A2:A13). This will give you the slope and y-intercept of the regression line.
  7. The y-intercept is the second value in the LINEST result, and the slope is the first value.
  8. The regression equation is of the form y = mx + c, where m is the slope and c is the y-intercept.
  9. To predict the average number of daily rentals for a retail area with an average family income of $50,000, substitute this value into your regression equation.

Please note that you need to replace A2:A13 and B2:B13 with the actual cells where your data is located.

This problem has been solved

Similar Questions

The table below contains the value (in dollars) and the amount of annual rental income (in dollars) for a random sample of 45 houses.X, value Y, annual rental income289000 11648194000 11232140000 956877000 4576165000 13312125000 7904145000 8320310000 1248085000 707281000 665667500 6864155000 7488174000 10400225000 1248090000 6240270000 12896300000 12480130000 9776262000 10192200000 8320303000 12272244500 11232148000 8320121000 12064115000 7904240000 11648325000 12480135000 7488110000 7072170000 9568170000 12688165000 8528240000 12064208000 10400104000 790475000 7280190000 8320178000 11856240000 10192214000 8528200000 10608200000 12272126000 6240135000 8320200000 10400a) State the random variables.    rv X = of     rv Y = of b) The symbol and value of the correlation coefficient are as follows:    Round final answer to 3 decimal places.    =     Interpret this value:There is a strong relation between value and annual rental income for houses.c) The symbol and value of the coefficient of determination are as follows:    Round final answer to 3 decimal places.    =      Interpret this value:About % of the explained by the linear model with as its variable.

A survey was conducted to study the relationship between the annual income of a family and the amount of money the family spends on entertainment. Data were collected from a random sample of 280 families from a certain metropolitan area.Which of the following would be a meaningful display of the data from this study? A scatterplot A two-way table A pie chart Side-by-side boxplots A histogram

The scatter plot shows the number of hours worked and money spent on entertainment by each of 21 students. Also shown is the line of best fit for the data.Fill in the blanks below.y510152025303540455055606570x2468101214161820220AmountofmoneyspentonentertainmentindollarsNumberofhoursworked(a)For these 21 students, as the number of hours increases, the amount of money spent tends to ▼(Choose one)(b)For these 21 students, there is ▼(Choose one) correlation between the number of hours worked and the amount of money spent.(c)Using the line of best fit, we would predict that a student working 12 hours would spend approximately ▼(Choose one)

The following observations are given for two variables.Daily demand (y)Unit price (x)     18.00       3.00     20.00       5.00     22.00     11.00     24.00     12.00Average21.007.75Standard deviation2.584.43 Correlation coefficient is

For a sample of 40 large U.S. cities, the correlation between the mean number of square feet per office worker and the mean monthly rental rate in the central business district is -0.363.  At the .05 significance level, can we conclude that there is a negative association between the two variables.

1/1

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.