Managerial Statistics

need you to do is a step i'm missing which is step 8 and write a report on the excel assignment. Instructions for analyzation of the excel report are on the last page from step 13 to 20. If you have any further questions let me know. Excel Assignment Due: Dec 2nd 2015 at 23:59:59 (midnight), assignments submitted after the due date will NOT be graded. Your assignment consists of two parts. In the first part, you are to complete an excel-based analysis of a sample of real estate data from Zillow.com. The data file can be found on the course Blackboard site. When you create tables and graphs, be sure to follow the guidance for developing good graphs and tables as discussed in class. All tables and graphs should be properly labeled and have suitable titles. In the second part you have to write a short report where you interpret the results of the analysis. Save the report as a Microsoft Word Document and turn it in to your instructor together with the Excel file with your analysis. The specific requirements of the first part of your assignment are: 1. Download the La Jolla real estate data Excel file from Blackboard and save it with the following naming convention: “ last name, first name - Excel Assignment”. Failure to save the file with the appropriate naming convention will result in a deduction of points. 2. Calculate the price per square foot for each home in your data set. a. Insert a new column between the “Sqft” and “Beds” columns b. In cell H1 (which should be blank because you created a new column), type “Price/sqft” c. For each home in the data set, compute the price per square foot by dividing the price by the square footage (sqft). You must use Excel formulas to calculate these values, do not type the numbers by hand d. Format the values in this column to be the “Accounting” number type, rounded to two decimal places (this will ensure a $ is placed into the cell denoting the numbers pertain to dollars) 3. Using PHStat in Excel compute the descriptive statistics (mean, median, mode, minimum, maximum, range, variance, standard deviation, coefficient of variation, kurtosis, skewness, count and standard error) for variables Price, Sqft, Price/sqft, Beds, Baths, Stories and Lot Size. a. The results should appear on a new worksheet (tab), rename the worksheet to “3 Descriptive Statistics” b. Organize the results in the worksheet such that the name of the statistic (mean, median, mode, etc.) appears in column A and the values of the statistics for each variable appear in columns B-H. In cells B2 to H2, make sure the variable names are labeled properly to display which variable is being described. c. The values of all statistics must be in standard numeric format with two decimal places displayed. Resize the columns to ensure they are wide enough to display all digits for your statistical values 4. Using PHStat in Excel, create a contingency table displaying the different values of the “baths” variable across the columns and “beds” variable down the rows. Rename the new worksheet as “4-Contingency”. 5. Using PHStat in Excel, create a histogram of variable “Sqft”. The lower boundary of your first bin should be 1000 sqft and the class width for each bin should be 500 sqft (Bins should be created in column O and midpoints should be created in column P). a. Remember to label the new variable names in your original data set, to display which variable is being described. b. Rename the new worksheet to “5- Histogram” 6. Using PHStat in Excel, create a box plot of the “Price” variable. a. Rename the new worksheet “ 6 –Boxplot”. 7. Analyze the normality of variables “Price”, “Sqft”, “Price/Sqft”, “Beds”, “Baths”, “Stories” and “Lot Size” in your data set. You can do this by evaluating the proximity of the variables mean versus median which you can find in your worksheet “3-Descriptive Statisics”. 8. Using PHStat in Excel construct a 95% confidence interval estimate of the population characteristic of the price/sqft varaiable. a. Rename the new worksheet as “8 Confidence Interval” 9. Using PHStat in Excel, provide hypothesis test of variable “price/sqft” using a two tail test with 0.1 as the level of significance. Use 624 as the null hypothesis a. Rename the new worksheet as “9 Hypothesis Test” 10. Using PhStat in Excel, conduct a simple linear regression of “Price” as the dependent variable and “Sqft” as the independent variable. a. Provide a scatterplot of the regression line and rename the new worksheets as “10 a- regression” and “10 b- scatterplot” 11. Create a Pivot Table of variables “Sold on”, Price” and “pool”. a. Ensure you group the variable “sold on” into months. b. Rename the new worksheet as “11-Pivot Table” 12. Reorder the worksheets in your Excel spreadsheet such that they appear in the following order (from left to right): Original Data set (name of the city), “3 - Descriptive Statistics”, “4 - Contingency”, “5-Histogram”, “6 - Boxplot”, “8- Confidence Interval”, “9- Hypothesis test”, “10 a-Regression”, “10b-Scatterplot” and “11-Pivot Table”, and then any other worksheets created automatically by Excel in any order. The specific requirements of the second part of your assignment are 13. Write a short paragraph (50-100 words) summarizing the type of information (i.e. the type of variables, what is the data set capturing etc..) contained in the data set. 14. Write a short summary paragraph of your analysis in step 3 (Descriptive Statistics). 15. Write a sentence or few words about your analysis in step 4 (Contingency). 16. Write few words about the normality of your variables which you analyzed in step 7. 17. Interpret your results in step 8 (Confidence Interval) and step 9 (hypothesis test). Compare the results and write a short conclusion (few words). 18. Write few words about the analysis in step 10. Interpret the meaning of r-squared and the rest of the coefficients from your developed regression equation. 19. Write a short paragraph summarizing your analysis in step 11 (Pivot table). 20. Finally, based on your analysis summarize the characteristics of the type of houses sold over the four months (June – September) 2015.