Scenario

You have been hired by your regional real estate company to determine if your region's housing prices and housing square footage are significantly different from those of the national market. The regional sales director has three questions that they want to see addressed in the report:

1. Are housing prices in your regional market lower than the national market average?
2. Is the square footage for homes in your region different than the average square footage for homes in the national market?
3. For your region, what is the range of values for the 95% confidence interval of square footage for homes in your market?

You are given a real estate data set that has houses listed for every county in the United States. In addition, you have been given national statistics and graphs that show the national averages for housing prices and square footage. Your job is to analyze the data, complete the statistical analyses, and provide a report to the regional sales director.

I provided data and answers to some of the questions in bold below, I have to use Excel examples explain the data above especially for histograms, I need a lot of help with that. if someone could help me step by step so I could be able to do it on my own on excel would be greatly appreciated.

Introduction

1. Region: Start by picking one region from the following list of regions: East North Central
2. Purpose: What is the purpose of your analysis?
3. Sample: Define your sample. Take a random sample of 500 house sales for your region.
   1. Describe what is included in your sample (i.e., states, region, years or months). From 2010 to 2020, 500 home sales from the East North Central area (Illinois, Michigan, Ohio, and Wisconsin) were chosen at random for my sample. For each home sale in the sample, the listing price, square footage, and number of beds and bathrooms are given.
4. Questions and type of test: For your selected sample, define two hypothesis questions (see the Scenario above) and the appropriate type of test for each. Address the following for each hypothesis:
   1. Describe the population parameter for the variable you are analyzing.
   2. Describe your hypothesis in your own words.
   3. Identify the hypothesis test you will use (1-Tail or 2-Tail). Is the average price of a home for sale in the Texas Central area less than the $279,000 national average? The one-tail test checks to see if the average is less than the population value of $279,000. Is the average price of a home for sale in the East North Central region the same as the national average of $279,000? Two-tail test: check to see if the mean is the same as the population parameter

4a. Level of confidence: Discuss how you will use estimation and confidence intervals to help you solve the problem.

Level of confidence: For both tests, I will use a 95% level of confidence. The confidence intervals will help you figure out if the sample mean is in a normal range or if it is actually the population mean.

1-Tail Test

1. Hypothesis: Define your hypothesis.
   1. Define the population parameter.
   2. Write null (Ho) and alternative (Ha) hypotheses. Note: For means, define a hypothesis that is less than the population parameter.
   3. Specify your significance level.

Test of one tail: HO. u > $279,000; ha: u < $279,000 Level of significance = 0.05

1. Data analysis: Summarize your sample data using appropriate graphical displays and summary statistics and confirm assumptions have not been violated to complete this hypothesis test.
   1. PROVIDE AT LEAST ONE HISTOGRAM OF SAMPLE DATA.
   2. In a table, provide summary statistics including sample size, mean, median, and standard deviation. Note: For quartiles 1 and 3, use the quartile function in Excel: =QUARTILE([data range], [quartile number])
   3. Summarize your sample data, describing the center, spread, and shape in comparison to the national information (under Supporting Materials, see the National Summary Statistics and Graphs House Listing Price by Region PDF). Note: For shape, think about the distribution: skewed or symmetric.
   4. Check the conditions.
      1. Determine if the normal condition has been met.
      2. Determine if there are any other conditions that you should check and whether they have been met. Note: Think about the central limit theorem and sampling methods.

The graph shows that the data is skewed to the right. With 500 people, the mean is $275,000 and the median is $250,000. The standard variation is $75,000. Numbers of quantiles Q1 = $200,000\ Q3 = $300,000 The spread looks like the shape of the country. The central limit theorem is used because the sample size is big. Like the national figures, the distribution is skewed to the right.

1. Hypothesis test calculations: Complete hypothesis test calculations.
   1. Calculate the hypothesis statistics.
      1. Determine the appropriate test statistic (t). Note: This calculation is (mean - target)/standard error. In this case, the mean is your regional mean, and the target is the national mean.
      2. Calculate the probability (p value). Note: This calculation is done with the T.DIST function in Excel: =T.DIST([test statistic], [degree of freedom], True) The degree of freedom is calculated by subtracting 1 from your sample size.

1. Interpretation: Interpret your hypothesis test results using the p value method to reject or not reject the null hypothesis.
   1. Relate the p value and significance level.
   2. Make the correct decision (reject or fail to reject).
   3. Provide a conclusion in the context of your hypothesis.
2. Our test statistic is (mean - population parameter)/standard error, which equals (255,000 -279,000)/7500, which is -3.2. This means that the probability of this happening is 0.0007. Because the p-value is less than 0.05, we can say that the average home price is less than the national average.

2-Tail Test

1. Hypotheses: Define your hypothesis.
   1. Define the population parameter.
   2. Write null and alternative hypotheses. Note: For means, define a hypothesis that is not equal to the population parameter.
   3. State your significance level.
2. Data analysis: Summarize your sample data using appropriate graphical displays and summary statistics and confirm assumptions have not been violated to complete this hypothesis test.
   1. PROVIDE AT LEAST ONE HISTOGRAM OF SAMPLE DATA.
   2. In a table, provide summary statistics including sample size, mean, median, and standard deviation. Note: For quartiles 1 and 3, use the quartile function in Excel: =QUARTILE([data range], [quartile number])
   3. Summarize your sample data, describing the center, spread, and shape in comparison to the national information. Note: For shape, think about the distribution: skewed or symmetric.
   4. Check the assumptions.
      1. Determine if the normal condition has been met.
      2. Determine if there are any other conditions that should be checked on and whether they have been met. Note: Think about the central limit theorem and sampling methods.

Two-tail test hypotheses: HO: = $279,000 Ha=/ $279,000 Level of significance = 005 The form, center, and spread of the data are like the one-tail test.

1. Hypothesis test calculations: Complete hypothesis test calculations.
   1. Calculate the hypothesis statistics.
      1. Determine the appropriate test statistic (t). Note: This calculation is (mean - target)/standard error. In this case, the mean is your regional mean, and the target is the national mean.]
      2. Determine the probability (p value). Note: This calculation is done with the TDIST.2T function in Excel: =T.DIST.2T([test statistic], [degree of freedom]) The degree of freedom is calculated by subtracting 1 from your sample size.
      3. The test statistic is (mean - population parameter)/standard error, which equals (255,00 - 279,000)/7500, or -3.2. The p-value is T.DIST.2T (3.2, 499), which is 0.014.
2. Interpretation: Interpret your hypothesis test results using the p value method to reject or not reject the null hypothesis.
   1. Compare the p value and significance level.
   2. Make the correct decision (reject or fail to reject).
   3. Provide a conclusion in the context of your hypothesis.
   4. Because the p-value is less than 0.05, we can say that the average home price is not the same as the national average.
3. Comparison of the test results: Revisit Question 3 from the Scenario section: For your region, what is the range of values for the 95% confidence interval of square footage for homes?
   1. Calculate and report the 95% confidence interval. Show or describe your method of calculation.
   2. We found that the average home price in the east north central area is less than the national average. This was true for both tests. 95% Cl for 1800sqft and 2100sqft of space

Final Conclusions

1. Summarize your findings: In one paragraph, summarize your findings in clear and concise plain language.
2. Discuss: Discuss whether you were surprised by the findings. Why or why not?
3. The sample data showed that the average home selling price in the East North Central region is less than the national average of $279,000. This was true for both hypotheses tests. This result doesn't come as a surprise to me because home prices are usually cheaper in the Midwest than in the west and coast of the US. The confidence levels help show that the real number is between 1800 and 2100 square feet.

I provided data below, I have to use Excel examples explain the data above especially for histograms, I need a lot of help with that. if someone could help me step by step so I could be able to do it on my own on excel would be greatly appreciated.

Region	State	County	listing price	$'s per square foot	square feet	listing price
East North Central	oh	summit	185,800	$101	1,847	185,800
East North Central	in	henry	235,000	$148	1,588	235,000
East North Central	oh	lawrence	248,300	$156	1,587	248,300
East North Central	il	ogle	236,600	$208	1,138	236,600
East North Central	mi	bay	145,100	$117	1,239	145,100
East North Central	in	allen	398,000	$113	3,525	398,000
East North Central	in	howard	304,300	$152	1,996	304,300
East North Central	il	kankakee	148,700	$115	1,293	148,700
East North Central	oh	allen	227,600	$147	1,550	227,600
East North Central	il	tazewell	278,700	$165	1,693	278,700
East North Central	oh	ashtabula	177,000	$107	1,658	177,000
East North Central	in	wayne	203,800	$141	1,441	203,800
East North Central	il	rock island	166,300	$127	1,305	166,300
East North Central	oh	hancock	380,300	$94	4,028	380,300
East North Central	mi	marquette	172,500	$120	1,433	172,500
East North Central	mi	wayne	213,800	$172	1,243	213,800
East North Central	mi	jackson	139,200	$116	1,201	139,200
East North Central	oh	richland	248,900	$132	1,880	248,900
East North Central	oh	washington	324,400	$156	2,081	324,400
East North Central	oh	pickaway	265,700	$143	1,853	265,700
East North Central	oh	mahoning	207,500	$123	1,688	207,500
East North Central	il	knox	205,100	$118	1,740	205,100