EXCELLENT GRADES
Using the dataset below, summarise the sum of HC FTE in a pivot table on a new sheet (named A1) by Region and Officer Code Group. Limit the pivot table to show only HC FTE for the "Employee" Person Type Long Description.
In the same pivot table and without making any amendments to the dataset below, aggregate the AVP and ASO Officer Code Groups into a new category called "AVP/ASO" and the STAFF, ENO, NEX and OTH Officer Code Groups into a new category called "STAFF/ANL".
In the same pivot table in addition to showing the sum of HC FTE, show the percentage HC FTE distribution of each Officer Code Group by Region.
Use SUMIFS and SUM functions to populate columns E and F in the table below using the "Ref" sheet as source data. Please do not make any changes to the "Ref" data sheet. Please bucket all "Officer Code Groups" other than MDR, DIR and VP into the "Other" category.
In column G in the table below, express the change from 31-Dec-12 to 31-Dec-13 as a percentage. Display the results to 1 decimal place.
Under the column below labelled "Q3a" (column D), write a vlookup formula using the "Ref" sheet as reference to show which IDs are common in both datasets.
For those IDs which appear in both datasets, write one formula which can be used in columns E through I (the same formula must work in all five columns) to pull the data from the associated columns. No additional columns can be added to the "Ref" sheet nor the table below.
Sort the table below by ID and under the column labelled "Q4a" (column G), write a formula which returns TRUE where the ID on the row is the same as the ID on the row above.
Under the column below labelled "Q4b" (column H), write a formula which satisfies the following conditions for all rows:
- Rows with an MDR Officer Code Group and a Private Banking, Asset Management or Investment Banking Per Owned Name (L2) should reflect as "Front Office MDR".
- Rows with a Shared Services Per Owned Name (L2) where the Product Name (L3) value is COCP Corp Centre S0700, SCEO CEO Functions Division or SSBC Centers Of Excellence Pro should reflect as "Shared Services Other".
- Other rows with a Shared Services Per Owned Name (L2) should return the Product Name (L3) value.
- All other rows should reflect the Per Owned Name (L2) value.
Represent each of the datasets below using an appropriate chart embedded in this sheet.
Officer Code Group
% Females
MDR
30%
DIR
35%
VP
40%
AVP
45%
STAFF
50%
Americas
Asia Pacific
Europe
Switzerland
MDR
100
80
120
300
DIR
500
600
550
400
VP
1000
800
2000
2500
AVP
3000
3000
3500
2500
STAFF
5000
7500
10000
4500
# Leavers
Attrition
31-Jan-13
100
13.2%
28-Feb-13
120
13.5%
31-Mar-13
150
13.6%
30-Apr-13
180
13.7%
31-May-13
220
14.0%
30-Jun-13
300
14.5%
31-Jul-13
250
14.3%
31-Aug-13
180
14.1%
30-Sep-13
160
13.9%
31-Oct-13
140
13.5%
30-Nov-13
80
13.1%
31-Dec-13
75
12.8%
Vol Leavers
Invol Leavers
Vol Attrition
Invol Attrition
31-Jan-13
80
20
9.1%
4.1%
28-Feb-13
100
20
9.2%
4.3%
31-Mar-13
120
30
9.4%
4.2%
30-Apr-13
100
80
9.6%
4.1%
31-May-13
180
40
10.1%
3.9%
30-Jun-13
160
140
10.2%
4.3%
31-Jul-13
200
50
11.0%
3.3%
31-Aug-13
150
30
10.0%
4.1%
30-Sep-13
100
60
9.9%
4.0%
31-Oct-13
40
100
9.8%
3.7%
30-Nov-13
40
40
9.2%
3.9%
31-Dec-13
30
45
9.1%
3.7%
Under the column below labelled Q6a (column D), write a formula which outputs the value between the "," and "-" references in the Data column. Text to columns is not allowed.
Under the column below labelled Q7a (column G), write a formula which concatenates the Title and Country columns and sort the table by this column.
Under the column below labelled Q7b (column H), write a formula which outputs the median salary for each value in the Q7a column. One formula must work for all rows in the table. You can add additional columns to the table for reference as needed.
7 years ago
Purchase the answer to view it
GMPeopleAnalyticsExcel.xlsx
