RECENT ASSIGNMENT

words process contained question and the excel contained data. please read carefully.
---------------
Statistic Assignment QBM117

Question 1

Marks will be awarded for:
• the correct answers and appropriate detailed working;
• clear and valid explanations where appropriate;
• the correct use of Excel where appropriate;
• correct and appropriate labelling of all graphs.

Download the data set ‘auction results.xlsx’ from the Assignment folder in the resources section of Interact. The data provided in auction results.xls show the Sydney auction results for the week ending 30 June 2013. The variables in this data set are: Beds, Type, Price and Result representing the number of bedrooms, the type of property (house or unit), the selling price (if sold) and the result of the auction respectively, as well as the auction date and the name of the selling agent
a. Most real data sets you encounter will contain errors. This one is no exception. List as many of these errors as you can find.
You may want to leave completing this question till later. As you work with the data set you will encounter some of these errors so just make a note of them as you find them.
Since we cannot contact the real estate agents to follow up on these errors, for the purpose of this assignment we will ignore them and work with the data set as best we can.
b. Annette and Danny are home owners in the Sydney region who are planning to sell their three bedroom house over the next few months. They are considering putting it up for auction so are interested in using these data to gain an insight into the current Sydney auction market.

? Using the complete data set, generate a three way pivot table report of ‘beds’ by ‘type’ by ‘result’. Use ‘type’ as a row label.

Include the table as part of the submitted assignment.
? Use it to answer the following vendors’ questions about the properties listed for auction in Sydney for the week ending 30 June 2013.


I. How many properties were originally listed for auction for the week in question? How many of these were houses?
II. How many houses were withdrawn from sale? How many units were withdrawn from sale?
III. How many 3 bedroom houses were passed in? Express this as a percentage of all the houses listed for auction that week excluding those houses that were withdrawn from sale.
IV. How many 3 bedroom houses were sold at auction that week? How many 3 bedroom units?
V. Of all the properties listed for auction, how many 3 bedroom units, including those that were sold prior to and those that were sold after,were sold that week? Then, express this as a percentage of all the 3 bedroom units listed for auction that week not including those that were withdrawn


? Separate the data into two data sets, one consisting of the units data only and one containing the houses data only.
(A) Use Excel to generate separate tables of descriptive statistics for houses and units for the variable Price’ and include them in your assignment submission. Round both means to the nearest thousand dollars and both standard deviations to the nearest dollar
(B) What was the price of the cheapest property sold that week? Look further afield to find out what type of property it was, how many bedrooms it had, whether it sold at auction or before or after, and which real estate agent sold it?
(C) Using the data set for houses only, use Excel to prepare a frequency distribution and histogram of the variable ‘Price’ for the house data. Use $500 000 as the upper limit of the first class and a class width of $500 000.
(D) Generate a boxplot for the variable ‘Price’for the houses data only. Include the 5-number summary generated by Excel and the boxplot with your assignment submission.


? Answer the following questions regarding the houses data only and indicate the piece of output in (d), (e) and (f) which provided the answer.

I. How many houses had a sale price listed?

II. How many outliers are there in the distribution of the selling prices of houses ? What is the value of the largest outlier?

III. 75% of houses sold for $x or less. What is x?

IV. Comment on the shape of the distribution of the ‘Price’ variable for houses only (skewed, symmetric, direction of skewness if relevant, unimodal, bimodal, etc.). Provide as much evidence as you can from the output generated in parts d., e., and f.

V. If a media outlet were to quote the average selling price (of houses listed for auction that week), would it be more appropriate to quote the mean or the median price? Why?

Insert the Excel formula used in your answer to part d

Question 2. A company producing ballpoint pens has been experiencing a 10% rate of faulty pens. This defect rate is constant and the chance of a sampled pen being defective is independent of other pens being defective.
Part D.. A random sample of 400 pens is selected, what is the probability that fewer than 30 are defective? Answer here used excel....
Part E. For this same sample of 400 pens, what is the expected number of defective pens?