+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 29

Thread: Regression questions

  1. #1
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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?
    Attached Files

  2. #2
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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. #3
    Fortran must die
    Points: 58,790, Level: 100
    Level completed: 0%, Points required for next Level: 0
    noetsi's Avatar
    Posts
    6,532
    Thanks
    692
    Thanked 915 Times in 874 Posts

    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. #4
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Regression questions

    Quote Originally Posted by noetsi View Post
    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. #5
    Fortran must die
    Points: 58,790, Level: 100
    Level completed: 0%, Points required for next Level: 0
    noetsi's Avatar
    Posts
    6,532
    Thanks
    692
    Thanked 915 Times in 874 Posts

    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. #6
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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.
    Attached Files

  8. #7
    TS Contributor
    Points: 12,501, Level: 73
    Level completed: 13%, Points required for next Level: 349

    Posts
    951
    Thanks
    0
    Thanked 103 Times in 100 Posts

    Re: Regression questions

    Quote Originally Posted by USMstudent85 View Post
    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. #8
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Regression questions

    Quote Originally Posted by noetsi View Post
    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. #9
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Regression questions

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

  12. #10
    TS Contributor
    Points: 12,501, Level: 73
    Level completed: 13%, Points required for next Level: 349

    Posts
    951
    Thanks
    0
    Thanked 103 Times in 100 Posts

    Re: Regression questions

    Quote Originally Posted by USMstudent85 View Post
    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. #11
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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. #12
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Regression questions

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

  15. #13
    TS Contributor
    Points: 12,501, Level: 73
    Level completed: 13%, Points required for next Level: 349

    Posts
    951
    Thanks
    0
    Thanked 103 Times in 100 Posts

    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. #14
    Points: 1,297, Level: 19
    Level completed: 97%, Points required for next Level: 3

    Posts
    46
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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. #15
    TS Contributor
    Points: 12,501, Level: 73
    Level completed: 13%, Points required for next Level: 349

    Posts
    951
    Thanks
    0
    Thanked 103 Times in 100 Posts

    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)

+ Reply to Thread
Page 1 of 2 1 2 LastLast

           




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats