Excel Assignment for Business Statistics

Objective: When you use Excel it is rather like a trick - you don't know what is happening behind the answer; therefore we get you to do manual calculations for your tutorial work. The manual calculations show you why and how your software arrives at a particular value. In the business world you will not be calculating manually. You will make use of the computer. Therefore you need experience in using Excel. We have chosen Excel (instead of Minitab or SPSS or R) because students have access to Excel (either in the university's labs and/or on their home computer). Note that the CBS Labs use Excel 2013 which is only slightly different to Excel 2010.

Assignment - How badly does logging damage tropical rainforests? One study compared forest plots in Borneo that had never been logged with similarly sized plots nearby that had been logged a decade earlier.

Data: Table 1 shows sample data for the number of tree species in 20 unlogged and 20 logged plots*.

* The data in Table 1 is 'made-up' for the purpose of the assignment. However, a real-life study was conducted by C.H. Cannon, D.R. Peart and M. Leighton, "Tree species diversity in commercially logged Bornean rainforest,' Science, 281 (1998), pp. 1366-1367.

Table 1:  Tree Species

Open an Excel spread sheet (you may use Excel 2003, 2007, 2010 or 2013) and enter the data which is shown above. You will now make your data unique by taking the last 4 digits of your student identification number and putting the first two digits instead of XX in row 11 of the 'Un-Logged' data and the last two digits instead of YY at row 11 of the 'Logged' data. For example: If the last 4 digits of your student number are 1234 then put 12 instead of XX in the 'Un-Logged' column and 34 instead of YY in the 'Logged' column. You will now be working with n₁ = n₂ = 21.

Question 1: a) Use fx to find the modes and median for the 'Un-Logged' data. Write down the name of the function to verify that you used Excel. Explain what is meant by the term 'modes'.

b) Show a table that displays all the summary statistics for the 'Logged' data and highlight (i.e. make bold) the values for the mean, standard deviation and standard error. What is the standard error? Calculate the standard error manually to verify Excel's result.

Question 2: a) Display a histogram for the 'Logged' data using class widths of 3 What are the values for the skew and for the kurtosis?

b) Display an ogive* for the 'Logged' data and estimate the proportion of tree species that exceed 18.

*When copying your Excel output to 'Word' include the 'bin-frequency- cumulative % table.

Question 3: Use an appropriate function on fx to estimate the population mean number of tree species using 'Logged Plots' data with 99% confidence. Copy onto your Word document the formula shown in the formula toolbar. Excel returns (gives you) a single value. Explain what the value refers to. Use this value to manually complete the interval estimate and thus verify the value Excel gave. Note: 'Manually' means you use the computer as a typewriter. It means you don't use a program to solve the problem. It does not mean 'by hand' in this case.

Question 4: Borneo has one of the most biologically diverse ecosystems in the world. Logging can endanger tree and animal life. In 114 unlogged plots 15 pygmy elephants, 4 Sumatran rhinos, 12 baboons and 3 sloths were detected. Show two different ways of displaying this information to best advantage (neither of these displays must be a frequency distribution table).

Question 5: a) The time taken for field workers to mark out a plot for logging is normally distributed with a mean of 92 minutes and a standard deviation of 12 minutes. What is the probability that the time is between 88 and 95 minutes? Use an appropriate Excel function (fx) to determine the probability. Write down the formula shown in the formula bar.

b) A logger knows that, on average, it takes two hours to clear eight tree roots. Find the probability that a logger will clear between four and nine tree roots in a one hour period.

c) If the probability of finding a tree species with an extensive subterranean root system is 0.70 and twelve plots were selected, find the probability that at least four of the twelve plots contain a tree species with an extensive subterranean root system.

Question 6: Can we conclude at the 10% significance level that logging significantly reduces the number of species in a plot after a decade? Highlight the relevant critical value or p-value and the test statistic to aid you in interpreting the data.

Question 7: Heights of females are believed to be normally distributed with a mean of 165 cm and a standard deviation of 5 cms. What is the probability that a randomly selected female will have a height:

a) Greater than 17 cm?

b) More than 150 cm?

c) Between 155 and 175 cm? Can you determine the approximate probability before calculating it?

d) Between 170 and 180 cm?

Question 8: The lifetime of a certain brand of television tubes is normally distributed variable with a mean of 80 months and a standard deviation of 10 months. The manufacturing wants to replace only 2% of faculty television. What should the warranty be?

Question 9: A supplier of batteries guarantees that his batteries will last 65 hours. After receiving complaints from his customers an electronic retailer randomly selected 6 batteries from his stock and tested them. The result, in lifetime hours, were:

70 65 59 66 62 61

Test the hypothesis at the 5% level of significance.

Question 10: Five married couples were observed and the number of mistakes made by each person are recorded in the table below. The aim is to test the theory that husband make more mistakes than wives at the 5% level of significance.

Attachment:- Assignment Files.rar