Assignment 5 Regression Analysis: Estimating Relationships Part 1&2
Title: Analytical Techniques in Public Administration
Book: Business Analytics: Data Analysis and Decision Making 5th edition
Albright & Winston, 2015 Cengage Learning (ISBN: 978-1-133-58826-9)
Statistics Tool: Download DecisionTools Suite Industrial, Textbook Edition
Assignment 5 consists of three regression analysis problems, two due this week and the other due next week. Be sure to carefully document your spreadsheets and include comment boxes that contain the answers and rational to the included questions. Points will be deducted from poorly documented work – remember that you are working for Decision-Makers and must present clear, well-documented products.
This problem set consists of three problems. Use the Assignments option is Sakai to submit your worksheets and answers to the questions based on the material covered in Chapter 10 Estimating Relationships.
1) Analyze U.S. Department of Agriculture farm data and explore the relationship between the number of U.S. farms (x) and average farm size (y). Estimate a simple linear regression and interpret it. Identify at least one potential federal policy implication based on your analysis.
Here is the data file: Assign 5-1a USDA Farm Data (Note: If this link does not work you can go to Files Directory/Files and find this data file)
2) A power company located in southern Alabama wants to predict the peak power load (i.e., the maximum amount of power that must be generated each day to meet demand) as a function of the daily high temperature (X). A random sample of 25 summer days is chosen, and the peak power load and the high temperature are recorded each day.
- a) Create a scatterplot for these data. Comment on the observed relationship between Y and X.
- b) Estimate an appropriate regression equation to predict the peak power load for this power company. Interpret the estimated regression coefficients.
- c) Analyze the estimated equation’s residuals. Do they suggest that the regression equation is adequate? If not, return to part b above and revise your equation. Continue to revise the equation until the results are satisfactory.
- d) Use your final equation to predict the peak power load on a summer day with a high temperature of 100 degrees.
Here is the data file: Assign 5-1b Peak Power Data (Note: If this link does not work you can go to Files Directory/Files and find this data file)
Assignment 5 Regression Problem Set Part 2
Assignment 5 consists of three regression analysis problems, two were due last week and the other is due this week. Be sure to carefully document your spreadsheets and include comment boxes that contain the answers and rational to the included questions. Points will be deducted from poorly documented work – remember that you are working for Decision-Makers and must present clear, well-documented products.
Housing Pricing Structure in Mid-City
Sales of single-family houses have been brisk in Mid-City this year. This has been especially true in older, more established neighborhoods, where housing is relatively inexpensive compared to the new homes being built in the newer neighborhoods. Nevertheless, there are also many families who are willing to pay a higher price for the prestige of living in one of the newer neighborhoods.
The file below contains data on 128 recent sales in Mid-City. For each sale, the file shows the neighborhood (1, 2 or 3) in which the house is located, the number of offers made on the house, the square footage, whether the house is made primarily of brick, the number of bathrooms, the number of bedrooms, and the selling price. Neighborhoods 1 and 2 are more traditional neighborhoods, whereas neighborhood 3 is a newer, more prestigious neighborhood.
Use regression to estimate and interpret the pricing structure of houses in Mid-City. Be sure to carefully read the section of your text about dummy variables as you MUST use them in this problem.
Include your data with the dummy variables, your regression work, and answer these questions:
- Do buyers pay a premium for a brick house, all else being equal? Explain your answer using your regression work.
- Is there a premium for a house in neighborhood 3, all else being equal? Explain your answer using your regression work.
- Is there an extra premium for a brick house in neighborhood 3, in addition to the usual brick house premium? Explain your answer using your regression work.
- For purposes of estimation and prediction, could neighborhoods 1 and 2 be collapsed into a single “older” neighborhood? Explain your answer.
Here is the data file Assign 5-2 Mid-City.xlsx (Note: If this link does not work you can go to Files Directory/Files and find this data file)
So I guess some hints are in order for this assignment. Here you go:
- You are going to need to build three regression models to answer the questions included above.
- For each regression model, don’t forget to include these data elements in your model: Offers, SqFt, # of Bedrooms and # of Bathrooms. They are critical predictors. I’ll let you figure out what else to include.
- You are going to need to set up dummy variables for this model. If you have not watched the YouTube video about dummy variables then “get watching” (Lol) so that you know why you need them and what their role is in your regression model.
- a) The formula for the brick dummy variable looks like this, assuming that your Brick categorical variable is in Col E:
=IF($E2=”Yes”,1,0) … you type that into row 2 then copy it all the way down. - b) The formula for the neighborhood 2 dummy variable (Nbhd_2), assuming the Nnhd_2 categorical variable is in Col B:
=IF($B2=2,1,0) … you type that into row 2 then copy it all the way down.
Then you have to stretch just a bit and figure out how to use this formula for the neighborhood 3 dummy variable (Nbhd_3). Think it through. You can do this.
- c) Finally, to generate the dummy variables for Brick_Nbhd2 and Brick_Nbhd3 … we just use some very basic math rules. Remember that if you multiply anything times zero the answer is zero.
So just multiple your brick dummy variable times your Nbhd_2 dummy variable and if either are zero (meaning no) you will get a zero. If both are “1” then you will get a 1. Do the same for Brick_Nbhd3, but use the Nbhd3 dummy variable instead …
- Don’t forget to check for multi-collinearity. Your last hint – there is multi-collinearity, so you need to adjust for it. Don’t forget to do so.
