BEO1106 Business Statistics 2020 Case Study I Regression and Hypothesis Testing 15 tasks X 2 marks = 30 marks Case Study I involved in estimating the advertise price of Melbourne properties by regression techniques. Answers are required to be typed into the BEO1106 Business Statistics Case Study I AnswerSheet 2020.docx and submitted to the Case Study I assignment dropbox along with your Sample Data for Regression Excel file. Both files should include your name and your student ID in the file names. The Case Study I will be submitted before Session 10. Introduction The price of a property can be determined by a number of factors. These factors may include (but not the least): The location, the land size, the size of the built area, the building type, the property type, number of rooms, number of bathroom and toilets, swimming pool, tennis court and so on. Market trend is also a very important factor to determine the property price. The sample data you collected for your assignment contain the following variables: V1 = Region where property is located (1 = North, 2 = West, 3 = East, 4 = Central) V2 = Property type (0 = Unit, 1 = House) V3 = Sale result (1 = Sold at auction, 2 = Passed-in, 3 = Private sale, 4 = Sold before auction). Note that a blank cell for this variable indicates that the property did not sell. V4 = Building type (1 = Brick, 2 = Brick veneer, 3 = Weatherboard, 4 = Vacant land) V5 = Number of rooms V6 = Land size (Square meters) V7 = Sold Price ($000s) V8 = Advertised Price ($000s). Requirement In relation to the Simple Regression topic of Business Statistics, for this Case Study, you are required to conduct a regression analysis to estimate the relation between Number of Rooms and Advertised Price of properties in Melbourne. Instruction You need to prepare a sample data using the Number of Rooms and the Advertised Price variables. You may find that V5 (Number of Rooms) variable has some missing observations in your sample. In order for Excel to estimate a regression equation, Excel requires a balanced data set. This means that both dependent variables and independent variables must have the same (balanced) number of observations in the data set. To balance the data set, we have to remove the observations which contain missing data. Refer to the steps in the Excel file Balancing the Sample Data for Regression Analysis.xIsx to assist you to construct your balanced sample data set for the regression analysis. Task 1 In the Answer Sheet provided, name the dependent variable (Y) and the independent variable (X). Provide a brief explanation to support your choice.Task 2 In a sentence, explain whether you expect a positive or a negative relation between the X and the Y variables. Task 3 Use Excel to produce a scatterplot using the independent variable for the horizontal (X) axis and the dependent variable as the vertical (Y) axis. Copy and paste the scatterplot to the Answer Booklet. Hint: Follow the graph presentation (in Step 5, Balancing the Sample Data for Regression Analysis.xlsx). Note:Title of the scatterplot and the labels for axes will account for 0.5 mark for each. Task 4 Follow the Excel procedure (select Data / Data Analysis / Regression) outlined on seminar note Slide 16, using the X variable and the Y variable you nominated in Task 1, generate regression estimation output tables. Copy the Regression Statistics and Coefficients tables (refer to Slide 27 and Slide 28) to the Answer Booklet. Task 5 Refer to the Regression Statistics table in Task 4, briefly describe the strength of the correlation between X and Y variables. Ensure your statement is supported by the statistic figure from the table. Task 6 Does the information shown in the Coefficients table agree with your expectation in Task 2? Briefly explain the reasoning behind your answer. Task 7 Refer to the Coefficients table, and follow the presentation on seminar note Slide 19, construct the least squares linear regression equation for the relationship between the independent variable and the dependent variable. Task 8 Interpret the estimated intercept and the slope coefficients. Task 9 Select one of the two following scenarios which describe your choice in Task 1. . In Task 1, if you nominated Number of Rooms is the independent variable, then you are asked to estimate the Advertised Price (dependent variable) of a property given the number of rooms of the property is 5. In Task 1, if you nominated Advertised Price is the independent variable, then you are asked to estimate the Number of Rooms (dependent variable) of a property given the advertised price is $1.55 (million).Task 10 With reference to the R Square value provided in the Regression Statistics table, explain whether you would trust your estimation in Task 9. Comment on whether your answer in Task10 agrees with the answer in Task 5 in terms of the strength of the linear relationship between X and Y. Task 11 State, symbolically, the null and altemative hypotheses for testing whether there is a positive linear relationship between Number of Rooms and Advertised Price in the population. Task 12 Use the Empirical Rule, state the z- value which is corresponding to 2.5% significant level. Task 13 Use the p-value approach to decide, at a 2.5% level of significance, whether the null hypothesis of the test referred to in Task 11 can be rejected (or not). Make sure you provide a justification for your decision. Task 14 Following the decision in Task13, provide a precise conclusion to the hypothesis test conducted in Task 13. Task 15 From information provided in the Coefficients table, construct a 95% confidence interval estimate of the gradient of the population regression line. Is this interval consistent with the conclusion to the hypothesis test you arrived at in Task 14? Briefly explain the reasoning behind your answer.\fQ R S I K | L M N O F C F G H B V5 267 814 896 1980 556 870 3800 4050 358 AWMALO 1944 624 386 836 669 255 332 2047.5 391 854 8956 330 194 440 389 8 1755 320 455 614 2192 710 4240 4716 5896 140 850 712 626 1034 254 704 1464 568 391 614 174 399 383 715 578 Step 3 Step 4 Step 5 Regression Step 1 Regression Step 2 + Step 1 1 Step 2A1 X V f V5 E F G H J K L M N P Q B D 15 267 814 896 1980 556 870 4050 358 332 2047.5 39 3956 330 194 1755 455 614 2192 4240 140 850 626 1034 254 46 171 Step 1 Step 2 Step 3 Step 4 Step 5 Regression Step 1 Regression Step 2 +Al X V V5 B C E F G H I K | L M NO F Q R S 140 624 854 850 704 391 578 170 814 358 986 2047.5 440 389 320 146 568 267 896 556 4050 1944 255 332 1956 330 455 614 2192 710 1240 626 614 383 718 37 669 712 1034 399 870 391 5896 1980 C0 00 00 00 1800 1755 47 4716 48 836 49 194 50 254 174 58 Step 1 Step 2 Step 3 Step 4 Step 5 Regression Step 1 Regression Step 2 +V5 Vo 140 524 854 850 704 391 578 170 814 358 12 986 2047.5 140 15 389 16 320 1464 568 267 Scatter Diagram: No. of Rooms Vs. Advertised Price 896 7000 4050 600 0 1944 255 5000 332 4000 956 Advertised Price ($'00 330 300 455 200 614 .. . 2192 100 0 710 0 4240 5 626 No. of Rooms 614 383 718 569 712 1034 399 870 391 5896 1980 3800 1755 4116 Step 1 Step 2 Step 3 Step 4 Step 5 Regression Step 1 Regression Step 2 +B C D E F G H M N P Q R Rooms Ad Price 140 624 854 850 704 391 578 170 SUMMARY OUTPUT 814 358 Regression Statistics 986 Multiple 0.45503 2047.5 R Square 0.207052 440 Adjusted 0.189031 389 Standard 1210.333 320 Observati 46 4 1464 18 4 568 ANOVA 267 of SS MS F gnificance F 896 Regressic 1 16830510 16830510 11.48915 0.001487 556 Residual 44 64455812 1464905 4050 Total 45 81286322 1944 255 Coefficientsandard Em t Stat P-value Lower 95%Upper 95%ower 95.09/pper 95.0% 332 Intercept -736.048 606.1101 -1.21438 0.23108 -1957.58 485.4863 -1957.58 485.4863 3956 Rooms 401.4032 118.4232 3.389565 0.001487 162.7369 640.0696 162.7369 640.0696 330 28 455 1 4 614 2192 710 4240 626 614 383 718 669 712 1034 399 870 42 391 5896 1980 8 3800 1755 4116 18 Step 1 Step 2 Step 3 Step 4 Step 5 Regression Step 1 Regression Step 2 +H22 X V A B C D E F G H I J K SUMMARY OUTPUT Regression Statistics Multiple R 0.455 R Square 0.207 8 Adjusted R Square 0.189 g Standard Error 1210.33252 10 Observations 46 11 12 13 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% 14 Intercept -736.0484 606.11 -1.21 0.2311 -1957.583 485.487 15 Rooms 401.403 118.423 3.39 0.0015 162.737 640.07 16 17 18 19 20 21