Each assignment group has a different file. Download your group’s file from Moodle and save.（make everything look good, Header, Word & Excel） Then save it under a different name so you have two files that are exactly the same. The first is the raw data and can be kept in case of errors. The second is your working copy. This is the file you will work on. It is a good idea to save regularly using a different name so you can revert to an earlier copy if you need to. E.g. Sampson 1, Sampson 2 etc.
Complete the Student Details sheet before commencing work. You are provided with the quarterly profit figures for 3 different lists of 80 companies. The Raw Data sheet has the 3 lists together and the data relating to each list (profit for group of companies) has been copied onto the sheets following. Instructions on submission For each part you must use Excel functions to perform calculations and prepare tables as indicated.
Do not sort the data.All statistical findings and charts must be copied into a Word document. You must also prepare a written analysis of your findings.
Your submission will consist of: ·Official cover sheet ·Word document including a copy of all statistical findings and charts from Excel as well as analyses as outlined in the brief. ·Excel file. (This is the final copy of your working file which will have the raw data provided plus your statistical operations, graphs and charts).
PART A - Descriptive statistics (Assume population)
1.Using Excel statistical functions, determine for each list, the Mean, Median, Mode, Range, Standard deviation and Co-efficient of variation.
2.Comment on the reliability of the mean as a measure of central tendency for each group of companies. Support your answer with calculations. For which group of companies is the mean the most reliable measure?
3.Draw a Box and Whisker Plot for each list. (Do in Excel or Word)
PART B – Grouping data and visual presentation
For each list, use Excel functions to perform the following:
1.Decide on a suitable class (depend on your range) interval size and use the “Bin” method to prepare a frequency table. Between 5 and 8 class intervals is best.
2.Use the frequency table to prepare a Column chart. Include a suitable title and axis labels. Format as required to make the chart look good. (Copy to Word, explain as: we have decided to use our interval because…..)
3.Prepare another frequency table including an additional class interval below and above (with 0 frequency) and use this table to prepare a line graph. Add a title and other formatting as necessary. (You can draw a curve)
4.How would you describe the curve you have created in Q3. above? Is the curve normal or skewed? If skewed, is it positive or negative? Indicate on the curve the approximate position of the mean, the median and the mode.
PART C – Probability
(Use the mean and Standard Deviation, assume it’s a normal curve) Assume that the profit earned by the 80 companies in each group forms a normal curve with the mean and standard deviation as calculated in PART A.
Use Area under the normal curve theory to answer the following about the quarterly profit earned by the different groups of companies.For each part, sketch (using a drawing program if necessary), the curve showing the required area.
For Group A
1.What percentage of companies would you expect to earn a profit above $175,000?
2.What percentage of companies would you expect to earn a profit below $180,000?
3.How many companies would you expect to earn a profit between $160,000 and $170,000?
For Group B
1.What percentage of companies would you expect to earn a profit above $270,000?
2.What percentage of companies would you expect to earn a profit below $250,000?
3.What level of profit would separate the top 10% of companies?
For Group C
1.What percentage of companies would you expect to earn a profit above $180,000?
2.How many companies would you expect to earn a profit below $200,000?
3.Calculate the profit amounts (lower and upper limit) that define the earnings of the middle 50% of companies.
PART D – Regression
(comparing data) Copy the company group data onto a new sheet, ensuring there is an empty column between each listand sort each list individually in order from lowest to highest. The empty column between without sorting the entire 3 lists, you should).
1.Prepare regression analyses showing the relationship between the companies. You will need to do 3 of these; A:B, A:C and B:C.
2.Which 2 listshave the strongest relationship? How strong is it? What is the formula derived from the regression analysis which describes the relationship between the independent variable (one group) and the dependent variable (the other group)?
Use this formula to estimate the profit of the dependent variable at two levels of profit in whole $10,000’s of the independent variable. E.g.
The formula derived is: Y = 52587 + .9265X. So, if a company in Group X has a profit of $180,000, the profit of the corresponding company in Group Y is $52587 + .9265 x 180,000 = $219,357.
Your answer must appear in your Excel file using appropriate cell references and also in the Word document.
3.For all lists, use the formula derived from the regression analysis to estimate the mean of another list using the mean for each list calculated in PART 1. How close is the estimate to the actual answer? If they are not close, can you explain why? (Hint: Refer to your answers to PART B.)