Quantitative Techniques for Business (BS2S81) Term 1 Report: Analysis

This report analyzes the 'Company Apple' dataset, comprising information on 474 staff members, using quantitative techniques. The report addresses four key tasks. Task 1 involves classifying variables, creating charts (pie, bar, histogram, frequency polygon), and calculating descriptive statistics like median and quartiles for the 'Salbeg' variable, including skewness analysis. Task 2 focuses on regression and correlation, using Excel to create scatterplots, calculate correlation coefficients, and determine regression equations between variables such as salary and age. Task 3 explores probability using pivot tables to analyze the relationship between sex and educational level, calculating various probabilities. Task 4 examines probability distributions, identifying continuous variables and constructing a probability distribution for the number of females in an office. The report also involves comparing actual salary distributions with the personnel manager's claims of normal distribution and salaries above £15,000, offering a comprehensive analysis of the employee data.

Quantitative Techniques for Business (BS2S81)
Term 1 – Assessment 1 (Statistics tasks and report)
Individual Assignment
The assignment is based largely upon the EXCEL data file, Company Apple.xls. This file
shows details of 474 staff working for a large organisation in Wales. It consists of 10 pieces
of information for each staff member. The file, together with explanation of the variables, is
available from your ‘Assessment’ folder on blackboard.
A new Managing Director has just joined your company and has asked you to analyse the
information from ‘Company Apple’ data file and write a formal report about the current
pictures of employees working in the company.
You are required to use the appropriate module concepts and techniques to complete the
following four tasks and analyse the ‘Company Apple’ data provided. The report should be
submitted in one single file within 2,500 words (+/- 1-%) by 18th December 2016 Sunday
23:55 (Week 12). You are to submit the assignment via TurnItIn, on the Bb site. If you are up
to 5 working days later than this you can gain a maximum mark of 40%. Later will be zero,
unless you have submitted for and been granted extenuating circumstances.
This assignment accounts for 50% of the marks awarded in the whole module.
NB General neatness and good presentation as well as arithmetic correctness, will be a factor
in the marking of your submissions. Please include any relevant calculations where used, as
well as appropriate supporting information. Do not submit any material that is not strictly
relevant to the tasks and assignment.
TASK BASED ON
Task 1 Summarising Data
Task 2 Regression and Correlation
Task 3 Probability
Task 4 Probability Distributions
For task 1 to 4, you should refer to the Excel file Company Apple.xls.
Task 1: Summarising Data
1. Classify the 10 variables by type (that is, nominal, ordinal, interval, ratio-scale), and
say whether they are quantitative or qualitative.
2. Using Excel, present the Job Category as a Pie Chart and as a Simple Bar Chart. Pay
attention to labels, titles and general neatness.
3. By means of Excel, use sensible grouping to organise the Salbeg data into a grouped
frequency distribution and cumulative frequency distribution.
1

Quantitative Techniques for Business (BS2S81)
Term 1 – Assessment 1 (Statistics tasks and report)
4. Using Excel construct a Histogram, Frequency Polygon and a Relative Cumulative
Frequency curve for the data obtained in Q3 (the variable Salbeg). Pay particular
attention to scale, labels, titles and general neatness.
5. By putting lines on the Relative Cumulative Frequency curve estimate the median and
quartiles of Salbeg. (can draw the lines using ‘Insert>Shapes>Lines’ tool or hand
draw them and scan the sheet in with your submission) What are the estimated
values?
6. Is the variable Salbeg symmetrical, negatively skew or positively skew? Write a short
note summarising the evidence for your answer.
Task 2: Regression & Correlation
1. Use Excel to obtain scatterplots and coefficients of correlation (r values please – not
R2 ) for the following pairs of variables:
(a) Salary Now and Age. (b) Salary at Beginning and Age.
(c) Salary Now and Salary at Beginning.
Produce the results. Write a short note on what you think these graphs and r values
tell you about the association between these variables.
2. Use Excel to calculate the regression equation of Salary Now on Salary at Beginning
and to draw the line of this equation through the scatterplot.
Write out the equation in full.
For someone who began with a salary of £20,000, what would your regression line
predict they would earn now? Would you have much faith in the accuracy of this
forecast? Briefly explain your answer.
2

Quantitative Techniques for Business (BS2S81)
Term 1 – Assessment 1 (Statistics tasks and report)
Task 3: Probability
1. Use Excel to construct a pivot table (two way table) of Sex and Educational Level.
Submit this table. Then submit the table as if it was to be included in a page of a
report: that is it must be completely self explanatory with titles etc.
2. Use the table from (1) to answer the following questions.
If a member of staff is selected at random, what is the probability that they are:
(a)in the Professional Qualification category.
(b)in the No Formal Qualification category.
(c)Female.
(d)Male.
(e)in the Professional Qualification category given that they are female.
(f) in the Professional Qualification category given that they are male.
(g)in the No Formal Qualification given that they are female.
(h) in the No Formal Qualification category given that they are male.
3. Is Educational Level independent of Sex in this company? Explain your answer.
Task 4: Probability Distributions
1. Which variables in the file are continuous?
2. A new office, within the company, is to be established with eight occupants. Using
the proportion of males to females in the company, and assuming an independent
random selection of each office member with constant probabilities, draw up a
probability distribution for the number of females in the office.
3. The Personnel Manager of the company has said "salaries are fairly distributed in this
firm over the whole salary range for both men and women, and we guarantee that
(eventually) everyone will have a salary above £15,000".
Assume that by 'fairly distributed" the personnel manager means 'normally
distributed'. Given this assumption, what would be the probability now of earning at
least £15,000 for males and for females (separately)? What are the actual numbers
(males and females) earning at least £15,000? Does a comparison of these
calculations support the personnel manager’s statement? Explain your answer.
3

Quantitative Techniques for Business (BS2S81)
Term 1 – Assessment 1 (Statistics tasks and report)
Assessment criteria:
Criteria Grade
awarded
30% Application of Theory
Concepts & techniques introduced in the module should
be applied appropriately.
30
40% Analysis of employees working in the company
Key issues should be clearly identified and articulated.
40
20% Supporting Evidence and Reference
The techniques and concepts applied should be supported
with evidence from different sources and correctly
attributed. 20
10% Presentation and Structure
Does the paper remain within the parameters set by the
instructions, while paying particular regard to the
conventions of report writing, spell checking, grammar and
appropriately referenced (Harvard style)
The report is well organised and logically constructed to
satisfy the requirements of cogency and synthesis while
being mindful of the needs of the reader.
10
TOTAL GRADE AWARDED
100
4