关键词 > MSCI242

MSCI 242 Spreadsheet Modelling for Management EXAMINATIONS 2022 PART II

发布时间：2024-05-22

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

EXAMINATIONS 2022

PART II (Second Year, Third and Final Year)

MSCI 242 Spreadsheet Modelling for Management - Resit Exam

Submission Rules

• Deliverable is a completed Excel workbook uploaded to the exam Moodle, addressing all the questions

• All answers must be presented in the correct answer cells on the User sheet

• The User sheet should also include your 8-digit ID number

• All answers to the questions must be formula or function-based i.e. not simple/static values

• The formulas must be dynamic and will be tested to ensure they can respond to changes in the data

• Only correct results will receive the marks. No partial marks will be awarded

• The model should not be password protected and not contain any links to external files

• It is the student’s responsibility to upload the correct file

• The model will be assessed on a University-spec. PC-version of Excel 2019

Data Collection

All datasets are held in a folder on theresit exam Moodle called ‘Exam Datasets ’

Background

This task is based on a modified version of the optional 242 workshop – Data Handling with Unusual Datablocks.

However, this task differs from the original workshop version in four ways.

1. The raw data is different

2. The number of business codes is 8

3. An additional variable has now been included: whether the employee is full-time (FT) or part-time (PT)

4. The number of employees is different

Data Structure and Sheets

• there are 2 sheets in the supplied dataset

• the Data sheet contains the raw data for the 8 business codes

• the User sheet is where you must submit all your answers, in the correct answer cells

Variables

Business Code	listed as B1, B2, B3 and so on up to B8
Age	age of employee in years
Service	number of complete years the employee has worked for the business
Salary	annual pre-tax salary, including any bonuses, in £
Grade	the salary band for the employee; a value 1 to 11
Edu	the education level of the employee; a value 1 to 7
Absence	total days absent last year, either due to illness and/or training
FT or PT	whether the employee is full-time (FT) or part-time (PT)

Section A Global Analysis

For this section, all answers must be formula-based, not static/pasted values.

You may insert new sheets, add intermediate formulas and calculations but do not move any of the answer cells, or insert any rows or columns to the User sheet

All questions in this section are worth 2 marks each.

Question 1

What is the average age of the employees?

Round your result to 2 decimal places (not simply format the cell to show this value)

Question 2

What is the age of the youngest employee?

Question 3

What is the age of the oldest employee?

Question 4

How many employees are there with the age given for question 2?

Question 5

What is the average salary? Round your result to the nearest integer.

Question 6

What is the highest salary for an employee?

Question 7

What is the lowest salary for an employee?

Question 8

What is the median number of absences?

Question 9

What is the lowest number of absences for an employee?

Question 10

What is the highest number of absences for an employee?

Question 11

How many employees do not have a numerical value listed for their salary?

Question 12

How many employees have zero absences listed?

Question 13

How many employees have an education level of 1?

Question 14

How many employees have an education level of 6 or 7?

Question 15

How many employees are full-time?

Question 16

What is the average age of the full-time employees? Round your result to 2 decimal places.

Question 17

What is the age of the oldest full-time employee?

Question 18

What is the age of the oldest part-time employee?

Question 19

What is the highest number of absences for apart-time employee?

Question 20

How many part-time employees have zero absences?

Section B Analysis by Business Code

The following questions will require you to construct summary table(s) by Business Code.

All questions in this section are worth 2 marks each.

Question 21

Which of the eight business codes (B1, B2, B3,… B8) has the most employees?

Question 22

How many employees does the business code given in question 21 have?

Question 23

What is the highest average salary for a business code? Round your result to the nearest integer.

Question 24

Which business code has the highest average salary?

Question 25

What is the lowest average age for a business code? Round your result to 2 decimal places.

Question 26

Which business code has the lowest average age?

Question 27

What is the highest maximum salary for a business code?

Question 28

Which business code has the highest maximum salary?

Question 29

What is the lowest maximum absences for a business code?

Question 30

Which business code has the lowest maximum absences?

Question 31

What is the highest number of full-time employees for a business code?

Question 32

Which business code has the highest number of full-time employees?

Question 33

What is the lowest number of part-time employees for a business code?

Question 34

How many of the eight business codes have the number of part-time employees given in question 33?

Question 35

What is the highest percentage of full-time employees for a business code? Round your result to 2 decimal places.

Question 36

Which business code has the highest percentage of full-time employees?

Question 37

What is the lowest percentage of part-time employees for a business code? Round your result to 2 decimal places.

Question 38

Which business code has the lowest percentage of part-time employees?

Question 39

Which business code has the most employees earning over £50,000?

Question 40

What is the average salary for full-time employees with an education level of 5 or above, aged between 30 and 60 (inclusive)? Round your result to the nearest integer value.

Section C Advanced Analysis

Questions 41 and 42 are worth 5 marks. Question 43 is worth 10 marks.

Question 41

How many full-time employees1 have a salary valueless than the average salary for their business code?

Question 42

Based only on employees with an education level of 3 or lower, what is the age of the full-time employee with the 2nd highest salary?

Question 43

There are a few missing salaries in the dataset, given as ‘n/a’ values.

If all the missing salaries were to be replaced with an identical salary value, what would the minimum salary value need to be, such that business code B5 would register the highest average salary for all employees and the highest average salary for the full-time employees? Present your minimum salary as an integer value.