Formatting Template For Excel Assignment With Questions And Answers

This formatting template has been developed to provide guidance to students when preparing this assignment. It is difficult to mark the assignment if Excel components are inserted as appendices in the assignment. This template should be used to ensure the Excel components are included in the correct places throughout the assignment.

Question 1

1. Typed or handwritten answers to be inserted here.
2. Copy table and complete with typed or handwritten answers.
3. Typed or handwritten answers to be inserted here.
4. Cut and paste the pivot table from Excel here.
5. and f. Typed or handwritten answers to be inserted here.
6. i. Cut and paste the pivot table from Excel here.
7. Cut and paste the bar chart from Excel after the pivot table.
8. Typed or handwritten answer for this part to be inserted after the bar chart.
9. i. Cut and paste the descriptive statistics table from Excel here.

Typed or handwritten answer for parts ii., iii. and iv. to be inserted after the descriptive statistics table.

1. Cut and paste the frequency distribution from Excel here.

Cut and paste the histogram from Excel after the frequency distribution.

1. Typed or handwritten answers to be inserted here.

Question 2

Handwritten answers for all parts to be inserted here.

Question 3

Handwritten answers for all parts to be inserted here.

Question 4

Handwritten answers for all parts to be inserted here.

Question 5

1. Handwritten answer to be inserted here.

Question 6

1. Handwritten answer to be inserted here.

Question 7

Handwritten answers for all parts to be inserted here.

Part a

1. Variables from the dataset, which are Qualitative and Normal, are Suburb, Address, Type, Result and Agent.
2. There are no variables in the dataset, which are both Qualitative and Normal.
3. Variable from the dataset, which is Quantitative and ratio is price.
4. The variable from the dataset, which is both Quantitative and Ordinal, is bedroom.

Part b

Result Code	Overall Outcome
PI, NB, VB	Property not sold
SP, PN	Property sold prior to auction
S, SN	Property sold at auction
SA, SS	Property sold after auction
W	Withdrawn from auction

Part c

1. By Suburb and Address, the four properties where the number of bedrooms is missing is shown by the following table:

Suburb	Address
Darlington	9/299 Abercrombie St.
Kirribilli	49/20 Carabella St.
Manly	1/19-23 Pittwater Rd.
North Sydney	307/54 High St.

1. The properties which are errors, from the above identified properties are given as follows:

Suburb	Address	Bedrooms	Type	Price	Result	Agent
Darlington	9/299 Abercrombie St		u	N/A	PN	Blues Point Real Estate
North Sydney	307/54 High St		u	N/A	PN	Blues Point Real Estate

These two properties are the errors because the price of the property is missing in both the cases and both of the properties were sold before auction. Thus, these two properties do not have any records during the auction.

Part d

Part e

1. It can be seen from the pivot table that there were 467 properties, which was listed for auction in that day.
2. The total number of properties that were sold that day (at auction, prior or after) is 244 + 31 + 121 = 396.
3. The percentage of the number of properties sold at auction is = 396/467 = 84.8%.

Part f

The number of four bedroom houses listed for the auction that day is 106. These include 102 houses, 4 duplex and 1 townhouse.

Out of these 106 houses, 91 were sold at the auction, prior or after.

The percentage of the number of four bedroom houses sold (at the auction, prior or after) is 91/106 = 85.8%

Of all the listed properties, the clearance rate is 84.8% and for the four bedroom houses, the clearance rate is 85.8%. Thus, it can be said the rate of clearance for the four bedroom houses is better than the rate of clearance of all the properties overall for that week.

Part g

1. The two way pivot table for type by result is given below:
2. The single horizontal component chart is given below:
3. The two types of properties which had approximately the same proportion of properties passed in that week are township types of properties and duplex types of properties as identified from the graph above.

Part h

1. The table of descriptive statistics for price is

Price
Mean	1790575
Standard Error	63516.72
Median	1566250
Mode	1150000
Standard Deviation	963278.8
Sample Variance	9.28E+11
Kurtosis	16.14936
Skewness	2.850252
Range	8871500
Minimum	428500
Maximum	9300000
Sum	4.12E+08
Count	230

From the above table, the median selling price is $1566250.

The standard deviation of these selling prices (expressed to the nearest thousands of dollars) is $963279.

The cheapest house sold that week was sold at a selling price of $428500.

It can be seen from the table that the house with the cheapest selling price was a three bedroom house located in San Remo.

1. The required sample variance and its scientific notation is given by the following table:

	Sample Variance
Actual Number Value	927906035169.89
Scientific Notation	9.27906E+11

1. The frequency distribution table and histogram as obtained using Excel are given below:

Household Selling Prices	Frequency
700000	6
1500000	100
2300000	78
3100000	29
3900000	8
4700000	7
5500000	1
6300000	0
7100000	0
7900000	0
8700000	0
9500000	1
More	0

In order to quote the prices of houses in Sydney, the median of the prices are considered and not the mean. The mean of a set of numbers is usually determined by adding all the numbers divided by the number of values. Thus, it involves all the values of the dataset. The median is denoted by the middlemost value of the data. It does not involve all the values of the data. Thus, it can be said that the median is more reliable than the mean. In this case, the end class is open. Thus, mean will not be accurate. Hence, median is a more appropriate measure.

Part a

Let A be the event that the number of need to be repaired.

Therefore,

1. P (A = 1) = 0.17

P (A= 2) = 0.08

P (A > 2) = 0.06

Thus, P (no repairs) = P (A = 0) = 1 – [P (A = 1) + P (A= 2) + P (A > 2)]

Question 2

= 1 – (0.17 + 0.08 + 0.06)

= 0.69

1. P (No more than one repair) = P (A ≤1) = P (A = 0) + P (A = 1)

= 0.69 + 0.17

=0.86

1. P (Some repairs) = P (A ≥1) = 1- P (A = 0)

= 1 – 0.69

= 0.31

Part b

The probability distribution of X is given in the following table where X is the number of cars repaired by a mechanic in a day.

No. of cars (X)	6	7	8	9	10
Probability	0.15	0.25	0.3	0.23	0.07

The mean number of cars repaired in a day is given by;

= (6*0.15) + (7*0.25) + (8*0.3) + (9*0.23) + (10*0.07)

= 7.82

= 8 [approx.]

The formula for calculating the standard deviation of the number of cars repaired in a day is given by

The necessary calculations to find out the standard deviation is given as follows:

No. cars X	Probability	Xi-μ	(Xi-μ)^2	Pi*(Xi-μ)^2
6	0.15	-1.82	3.3124	19.8744
7	0.25	-0.82	0.6724	4.7068
8	0.3	0.18	0.0324	0.2592
9	0.23	1.18	1.3924	12.5316
10	0.07	2.18	4.7524	47.524
Total	1	0.9	10.162	84.896

The required standard deviation is .

Part c

The sample that Paul selected is bias because

• The sample was drawn on the basis of the participation of the people of one particular day. The result can vary a lot in the next day or the day after that. The number of people watching the particular cinema today might not be the same on any other day. Thus, the sample could have been collected for a week and not a day.
• Only female participants were considered in the sample. Considering only female participants will not give a clear analysis of the frequency of people visiting the movies.

Part d

The table showing the members of Sydney golf club is given below:

Age Range	Male	Female	Total
Under 18	55	23	78
18 to 30	165	45	201
31 to 50	300	90	390
Over 50	180	42	222
Total			900

The number of male members to be sampled from the age group of 31 to 50 years is (300/900)*90 = 30.

The number of female members to be sampled = (200/900)*90 = 20.

Part a

From the records of a company, it has been found out that four types of incidents are described in the information. The events are, day shift workers, night shift workers, the workers who turned up and workers who did not turn up.

Let the day shift workers be denoted as A and the workers who did not turn up be denoted as B. Then, the night shift workers will be denoted as   and the workers who turned up is denoted by . The following diagram can give the probability tree containing the above information.

The tree clearly states the following information:

P (A) = 0.7, P () = 1 – 0.7 = 0.3

Again, P (B|D) = 0.2 P ( = 1 - 0.02 = 0.98.

Further, P (B/) = 0.04, P (/ = 1 – 0.04 = 0.96

Therefore, the percentage of day-shift workers who are not present on any given day = (0.7 * 0.2) * 100 = 1.4%

The percentage of night shift workers are absent on any given day = (0.3 * 0.4) * 100 = 1.2%

Therefore, the total percentage of workers who are absent on any given day = 1.4% + 1.2% = 2.6%

Part b

Absenteeism will be independent of the shift worked if P (B|A) = P (B). Here, this is not the case. Therefore, absenteeism depends on the working shifts.

Part a

1. P (Z > 0.4)

= 1 – P (Z < 0.4)

= 1 – 0.6554

= 0.3466.

1. P (-1.35 ≤Z ≤ 1.25)

= P (Z ≤ 1.25) – P (Z ≤ -1.35)

= 0.8944 – 0.0885

=0.8059.

Part b

1. The probability of making 30 sales = BINOM.DIST (30, 300, 0.12, FALSE) = 0.042101.
2. The probability of making more than 30 sales = 1 - BINOM.DIST (31, 300, 0.12, FALSE) = 0.949998.

Part c

1. The probability of less than two collisions in a six month period = POISSON.DIST(2, 1.8, TRUE) = 0.730621086.
2. The probability of one collision in a two month period = POISSON.DIST(1, 1.8, FALSE) ÷ 3 = 0.09918.

Part a

In the given problem, the random variable described follows a poisson distribution defined as . The mean is denoted as λ, which is equal to 13 in this problem.

Part b

The probability of the company receiving 13 emergency calls in a specified month is

P (X ≥ 13) = 1 – P (X ≤ 12) = 1 – 0.1099398 = 0.8900602.

Part c

Mean = λ = 13/30 = 0.433.

The probability of getting more emergency calls than what the company can handle is

P (X > 3) = 1 – P (X < 3) = 1 – 0.000675 = 0.999325.

Part a

In the problem given, the random variable that has been described follows a Binomial Distribution. The distribution is given by

Part b

Here mean is given by (n*p) = 0.75 * 20 = 15. Thus, the probability of 20 customers to be satisfied in a sample of 25 customers i

Part c

The expected number of dissatisfied customers for n = 50 is 50 * 0.25 = 13.

Part d

The required probability is given by BINOM.DIST(100, 150, 0.75, TRUE) = 0.013618601

Part a

The problem given in this question follows a normal distribution with mean = µ and variance = .

Part b

P (X < 50000)

= P (Z < -2.5)

= 0.00621

The required proportion of tyres, which fail before the warranty expires, is 0.00621.

Part c

P (X > 58500)

= P (Z > 1.5)

= 1 – 0.93319

= 0.668.

= 6.68 %.

Therefore the claim that the tyre will last longer than 58,500 km has a chance of atleast 10% is wrong.

Part d

P (X< 54700)

= P (Z < -1.5)

= 0.06681

The probability that the average lifetime of these tyres is less than 54700 km is 0.06881.