1. ## Regression questions

Hey everybody. I want to start out by saying Thank You for taking the time to look at my thread. I have completed my Stats course and now I am on to Regression. I don't have much experience with Regression or EXCEL and I have several questions about this assignment so please be patient with me. I listed the assignment and the excel spreadsheet so everybody can look at what I am talking about. I will be posting several questions over the next few days so thanks for your time.

Select the EXCEL spreadsheet with the data set you need. Select Data/Data Analysis/ Regression and you will be presented with a menu box to set the parameters for the regression.

Remember x = the independent variable and y = the dependent variable (In cost estimation, y = total cost and x = the cost driver).

Select the Input Y Range by clicking on the spreadsheet icon in the Y window, then dragging the mouse down the “X” column and clicking on the icon again. . Be careful not to include the title of the column but only the numbers. Now select the Input X Range in the same manner entering the data for X (if there is more than one X, you can select multiple columns). Without selecting any other options on the regression menu, click on “OK.” EXCEL runs the regression and produces an output table on a new spreadsheet in the workbook. Under the regression statistics we will use the “ adjusted R Square” statistic (goodness of fit) to evaluate the relationship. We will also use the coefficient information to estimate the function expressing the relationship. The intercept term (a) is the constant in our linear function (y = a + bx). If there is only one X, this is called a simple regression. If there is more than one X, it is a multiple regression.

Write in the answers to the questions in a separate workbook in your main file. Show all work. For initial estimating equations, carry decimals at least to four places (0.000). When submitting your assignment, please submit the regression output and the answers to the questions in one worksheet using 2 tabs (or workbooks).

Data Set – (food.xls); Note that variable definitions are included on the spreadsheet. We will estimate the relation between weekly expenditures for food and weekly income.

Using regression, estimate: Y = a + bX, which in this case is

Weekly food expenditures = a + b (weekly income in \$100)

1. What is the adjusted R Square ?

2. Write the estimating equation for this relationship (remember, X is in hundreds of \$s).

3. A family has an annual gross income of \$100,000. Assuming there are 52 weeks in the year, what is their weekly food expenditure?

4. What percentage of their gross weekly income goes to buy food?

2. ## Re: Regression questions

Select the Input Y Range by clicking on the spreadsheet icon in the Y window, then dragging the mouse down the “X” column and clicking on the icon again. . Be careful not to include the title of the column but only the numbers. Now select the Input X Range in the same manner entering the data for X (if there is more than one X, you can select multiple columns). Without selecting any other options on the regression menu, click on “OK.”
My first question is about the instructions above. I am not sure which column would be X and which one would be Y. Would Column A be X or would Column B be the X

3. ## Re: Regression questions

My suggestion is to not do regression in Excel (having done so). It is much better in any statistical software.

1) Excel lists an Rsquared value so that is easy to report.

2) Just list this in the form Y =Bo + B1X1 +B2X2....+BnXn where Y is the reported dependent variable value, B0 is the intercept and the B's the slopes. Remember to put in the X's in terms of what units you are actually using.

3) Plug in the numbers for this into the equation that is generated (that is into each slope and the intercept) and see what you get.

4) You calculate food cost and divide by weekly income.

Excel generates very little in output for regression. It will be simple to see this information when you run it.

4. ## The Following User Says Thank You to noetsi For This Useful Post:

USMstudent85 (10-13-2011)

5. ## Re: Regression questions

Originally Posted by noetsi
My suggestion is to not do regression in Excel (having done so). It is much better in any statistical software.

1) Excel lists an Rsquared value so that is easy to report.

2) Just list this in the form Y =Bo + B1X1 +B2X2....+BnXn where Y is the reported dependent variable value, B0 is the intercept and the B's the slopes. Remember to put in the X's in terms of what units you are actually using.

3) Plug in the numbers for this into the equation that is generated (that is into each slope and the intercept) and see what you get.

4) You calculate food cost and divide by weekly income.

Excel generates very little in output for regression. It will be simple to see this information when you run it.
The thing is we have to use Excel. We went to class and were told that the course is now going to be done online. That is why I am kind of lost doing this.

6. ## Re: Regression questions

Can't you just run it in SPSS or SAS and report the numbers? Nothing you noted above must be generated in Excel. Just paste it into that. I believe for linear regression the results will be the same (you should test this out).

It is just a pain, and very limiting, to do regression in Excel. In the workplace most people asked to do regression will ask for a statistical software (if they hire you to do any type of stats they will fork over the money to run it - be sure to tell them you must have it).

7. ## Re: Regression questions

Question #1 was easy because it was listed under the summary output.

I am having difficulty setting up the equation with #2.

2. Write the estimating equation for this relationship (remember, X is in hundreds of \$s).

In our notes it says to use the formula Y= a + bX

This is the formula but I don't know which numbers to plug in: Weekly food expenditures= a + b (weekly income in \$100). I added an attachment of the summary output to help understand.

8. ## Re: Regression questions

Originally Posted by USMstudent85
My first question is about the instructions above. I am not sure which column would be X and which one would be Y. Would Column A be X or would Column B be the X
Food expenditure depends on income.

9. ## The Following User Says Thank You to Mean Joe For This Useful Post:

USMstudent85 (10-13-2011)

10. ## Re: Regression questions

Originally Posted by noetsi
Can't you just run it in SPSS or SAS and report the numbers? Nothing you noted above must be generated in Excel. Just paste it into that. I believe for linear regression the results will be the same (you should test this out).

It is just a pain, and very limiting, to do regression in Excel. In the workplace most people asked to do regression will ask for a statistical software (if they hire you to do any type of stats they will fork over the money to run it - be sure to tell them you must have it).
I honestly don't know anything about SPSS or SAS. Our teacher told us to use Excel so thats what I am doing.

11. ## Re: Regression questions

Originally Posted by Mean Joe
Food expenditure depends on income.
I figured that out. Thanks for the help though.

12. ## Re: Regression questions

Originally Posted by USMstudent85
In our notes it says to use the formula Y= a + bX

This is the formula but I don't know which numbers to plug in: Weekly food expenditures= a + b (weekly income in \$100).
Your a & b are in the Coefficients column. You've just got to put together which coefficient corresponds to a/which corresponds to b.

13. ## Re: Regression questions

The numbers I got for #2 don't make sense.

Y=a+bX

Sum of Y=11,342.94
Sum of a=11,342.94

Sum of b=784.19
Sum of X in hundreds = 78,419

11,342.94=11,342.94+784.19(78,419) This can't be right can it?

14. ## Re: Regression questions

Would this be right? Y=11,342.94+784.19X

15. ## Re: Regression questions

Look at your "Summary output" worksheet--the third table has the coefficients that you need to plug in for a and b.

16. ## The Following User Says Thank You to Mean Joe For This Useful Post:

USMstudent85 (10-13-2011)

17. ## Re: Regression questions

Ok. Y=a+bX...... So would the answer be Y=83.4160+10.2096 or would it be Y=83.4160+10.2096(times the sum of X)

18. ## Re: Regression questions

Since you're using Excel, you could plot your data (Y vs X), and add the regression line that fits the plot.

Since the plot is Y vs X, then the equation for the line would be Y=83.4160 + 10.2096X. Don't worry about the sum of X, it's not in your plot.

19. ## The Following User Says Thank You to Mean Joe For This Useful Post:

USMstudent85 (10-13-2011)